Re: with/inline view efficiency question

Mark D Powell (Mark.Powell_at_eds.com) wrote:
: 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

(of course, how do these differences escape my mind!)

: 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 --

thanks Received on Tue Jan 10 2006 - 12:29:59 CST