long oracle query

Question

Hi there, 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

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

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

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 4

thank you for the replies! I've used with clause and I've done restructurization of my sql a little more. In the explain plan cost and cardinality values have decreased quite rapidly. Does cardinality mean "how much record is used". Is cost something like needed CPU capacity? My sql query has still about 1000 letters without spaces. Could there be some problems with such a long query. Or doesn't it matter how long the query is. The most important are the results in explain plan?