Re: Scalar queries in select clause

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 13 Apr 2009 09:03:43 +0100
Message-ID: <fOydnWOiTJlGbH_UnZ2dnUVZ8uqdnZ2d_at_bt.com>

"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 ?
>
> Rgds,
> Gokul

If you examine the plans in detail - or experiment with some smaller driving
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 of
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.html

Received on Mon Apr 13 2009 - 03:03:43 CDT