Re: Scalar queries in select clause
Date: Mon, 13 Apr 2009 09:03:43 +0100
"Gokul" <gokulkumar.gopal_at_gmail.com> wrote in message news:b3870d85-e696-4687-a872-0af4e72db9ba_at_o11g2000yql.googlegroups.com...
>I have a bunch of queries that have scalar subqueries in the select
> clause. From the plan, I see the one with the scalar subqueries has
> lower cost. Am I missing something ? Is there real benefit in writing
> queries using scalar subqueries in the select clause ?
If you examine the plans in detail - or experiment with some smaller
data sets and use multiple scalar subqueries, you will realize that Oracle does
not include any real information about the scalar subqueries in the final cost of
the plan. It simply reports a "sub-plan" for executing each scalar subquery once,
and the cost of the query is essentially the cost of the driving query.
The optimizer does NOT allow for the number of times the scalar subquery
will run. Thanks to scalar subquery caching, it is possible for the number
executions to be between one and the number of rows returned by the driving query. Because of this, there is no simple rule that can tell you whether scalar
subquery caching will be a good thing or a bad thing.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Mon Apr 13 2009 - 03:03:43 CDT