Re: Big SQLs

    Those statements are usually generated by program. The most common patterns are very long IN lists, and a large number of subqueries (usually querying the same tables with different values) and / or UNIONs. The only efficient way to deal with freak statements is to discuss with the developer, who usually has a very faint idea about the horrors that his/her code is generating.

For IN lists (which besides are very bad because as soon as the number of items vary the statement has to be reparsed, even with CURSOR_SHARING set to FORCE or SIMILAR), the question to ask is where the values are coming from. Chances are that they are output by another query, in which case merging the two queries with a JOIN looks like the sensible option. Other possibilities are building a comma (or anything else) separated string and dividing it on the fly, a well known technique I just keep explaining and explaining. (details here: http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause/512749#512749), which works as long as your list fits in less than 4,000 characters, or using a global temporary table for holding the values and joining with it. Large numbers of subqueries are often easy to deal with, because you often have

   and (col = (select blah from table where id = ...)

           or col = (select blah from table where id = ...)

and so on, which can be easily replaced by

          col in (select blah from table where id in (....)) (for the IN (), refer to what is said previously). UNION statements can also often be factorized (an incredible number of developers have never heard about WITH).

In other words, if you have a very big query, it's usually the same pattern that is repeated ad nauseam. Cut it down to two or three patterns, think about how you can perform the same thing by "visiting" each table as little times as you can, rewrite it, show it to the developers and see with them how they can devise a way to build the query your way. It usually requires a bit more cleverness on the query building side, but rewards are often high.

HTH

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>

--
http://www.freelists.org/webpage/oracle-l