long oracle query

Question

I've got really long and complicated query(Oracle 10g). It contains about ten select subqueries. The query works but it's too long. Should I somehow divide this query? I mean is there some standard how long/complicated could sql query be. The query works but it doesn't seem to me like the best solution. For example one subquery repeats there (it queries the table smaller then 20 rows), how could I make it to run it just once during this query? Maybe it's too general question Thanks for all answers Tonu

Answer 1

From version 9 onwards, you can factor your SQL code almost like any other code, using a feature called subquery factoring, also known as the with-clause.

The documentation: http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2075668

An example: http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2075888

Regards, Rob.

Answer 2

I can only suggest to use EXPLAIN PLAN a lot to figure out what the query optimizer is doing to reorganize the query.

An alternative approach may be to talk to the business and figure out what they truly want and look in the system if there is no information available which is closer to the problem domain.

I once had a situation like that regarding "On time deliveries" where the definition of "On Time Delivery" was butchered beyind recognition by the business middle management, eager to present a "good news show" and was bloated to the extreme because of special case handling. Pushing back, going to the Management Handbook, implementing the definition which was there, and using a handy aggregates table create by Oracle EBS, reduced the runtime from 25mins to 2 secs.

Answer 3

try looking into the with clause, it does do a subquery once, and then lets you reference the resuling rows over an dover again