Re: with/inline view efficiency question

Malcolm, a WITH clause is really best used when the result of the WITH query is required more than one time in the body of the query such as where one averaged value needs to be compared against two or three times.

I think in your example what you want is to try coding the query as an inline view rather than as a scalar query (select list query) or as a WITH. select a.col1, a.col2, b.col3
from (select ... from the_values group by the_group) b,

         table_a a
where b.the_group = a.the_group

Obviously, the best way to make you choice is to code all three versions and time test them multiple times. But based on experience and the Oracle version the CBO does not seem to do as good as job optimizing with select list queries as it does with inline views.

HTH -- Mark D Powell -- Received on Tue Jan 10 2006 - 08:23:45 CST