How to analyze and design a complex SQL query?

Question

Oracle

I have to develop a complex report with various summary rows, which is built from 15-20 tables.

Are there any techniques for preliminary analysis and design of SQL code, similar to OOP?

Answer 1

"it is necessary to design as it is necessary, and if it is not necessary - it is not necessary to design" - what other analysis besides the general rules of SQL? type by index is better than without, and JOIN is better than IN
, there are stored procedures - you don’t need to fit everything in one query (and yes, temporary tables are fine)

Answer 2

the principle is very simple.
I use it when optimizing slow requests, only in a different direction.
If a complex query slows down, then I throw out all the joins and subqueries in turn from it until the slowdown persists. And then I'm already engaged in optimizing the simplified version. Usually there are two or three tables.
The same principle when designing, only in the opposite direction - first we get the basic data, a bare backbone, without decorations. If we get a value from the linked table by the primary key (let's say the name of the category by its id), then we add it last, since it does not affect anything. We start with the main table with data - and forward
. That is, roughly speaking, we first form the logic, then we add decorations.

Answer 3

SQL - "language of the STRUCTURED requests". also declarative, and here in general OOP. PL/SQL is a procedural language, as the name suggests. and any principles of programming in procedural languages ​​are applicable to it.
and structure it. break it down into procedures. algorithmize.

Answer 4

As Lentyuy already noted - SQL was originally aimed at a kind of "nerds" - so that they wrote "what they want to see." The rest is up to the interpreter-scheduler.
And you have to start with this. In 90% of cases, the sql planner will be able to build a query plan in such a way that it turns out to be much more optimal than an amateur optimizes.
And only in the case of a really slow response - it will be possible to talk about thin places and plunge into the magical world of indexes, hashes, memory limits, hints, etc. )