Oracle Scratchpad

October 12, 2007

Scalar Subqueries

Filed under: CBO,Execution plans — Jonathan Lewis @ 7:54 pm GMT Oct 12,2007

Here’s a question that recently appeared on the Oracle-L listserver.

For the plan, the cost of the parent should be equal or larger than the child. But in the following plan, the total cost (Id=0) is 2, but it’s child “HASH UNIQUE “(ID=1) has a cost of 2083 which is much larger than 2, how could it be like that?

 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
| Id | Operation                       | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|  0 | SELECT STATEMENT                |                        | 1     | 482   | 2 (0)      | 00:00:01 | 
|  1 |  HASH UNIQUE                    |                        | 1     | 48    | 2083 (1)   | 00:00:25 | 
|* 2 |   COUNT STOPKEY                 |                        |       |       |            |          | 
|  3 |    TABLE ACCESS BY INDEX ROWID  | ATTRIBUTE_VALUE        | 1     | 24    | 3 (0)      | 00:00:01 | 
|  4 |     NESTED LOOPS                |                        | 11    | 528   | 2082 (1)   | 00:00:25 | 
|* 5 |      TABLE ACCESS BY INDEX ROWID| ATTRIBUTE_VALUE        | 11    | 264   | 2049 (1)   | 00:00:25 | 
|* 6 |       INDEX RANGE SCAN          | IDX_ATTRIBUTE_VALUE_2  | 19155 |       | 59 (0)     | 00:00:01 | 
|* 7 |      INDEX RANGE SCAN           | IDX_ATTRIBUTE_VALUE_1  | 1     |       | 2 (0)      | 00:00:01 | 
|  8 |  TABLE ACCESS FULL              | PATH_RELATIONSHIP_TEMP | 1     | 482   | 2 (0)      | 00:00:01 | 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -          

Predicate Information (identified by operation id): 
- - - - - - - - - - - - - - - - - - - - - - - - - - 
  2 - filter(ROWNUM=1) 
  5 - filter("A"."ATTR_STR"=:B1)         

select 
	( 
	select 
		distinct b.attr_str 
	from 
		ATTRIBUTE_VALUE a, 
		ATTRIBUTE_VALUE b 
	where 
		a.locale_lang_id = :a 
	and	a.attr_id = :b 
	and	a.attr_str = x.product_line_name 
	and	a.node_id = b.node_id 
	and	b.locale_lang_id = :c 
	and	b.attr_id = :d 
	and	a.change_lifecycle_id = 0 
	and	b.change_lifecycle_id = 0 
	and	rownum = 1 
	) 
from 
	PATH_RELATIONSHIP_TEMP x 
;               

I’ve shown the plan and the query here (the original post omitted a couple of the lines from the Predicates section), with only small cosmetic changes from the original.

The answer to the question lies in the fact that the statement: “the cost of the parent should be equal or larger than the child” is not strictly true.

This query demonstrates the use of Scalar Subqueries, and one of the minor difficulties of dealing with costing. The basic plan is for the statement:

 	select {} from path_relationship_temp x; 

The main query then has a scalar subquery in the select list.

Oracle therefore shows two independent plans in the plan table.  One for the driving query – which has a cost of two, and one for the scalar subquery, which has a cost of 2083 each time it executes.

But Oracle does not “know” how many times the scalar subquery will run (even though in many cases it could predict a worst-case scenario), and does not make any cost allowance whatsoever for its execution in the total cost of the query.

The only oddity (in my eyes) about this plan is that I would have expected the plan for the driving query to appear as the first child of line zero, just as a visual clue that it is the driving query – but that just doesn’t seem to be the way it’s done.

5 Comments »

  1. Hi,

    As far as I remember this is different in different Oracle versions. Before 9i these scalar subqueries were not shown in the plan at all. In 9i they are shown in the explain plan but are not visible in the v$sql_plan (I don’t remember of v$sql_plan_statistics_all). In 10g it is shown everywhere correctly. However, this should be rechecked, but right now I only have access to some 10g systems. (similarly temp table transformation steps were not shown correctly in 9i, but are nice in 10g)

    Also, If you have more subqueries in the select list they are listed after each other and the main query is the last child.

    Sorry for not being 100 pct sure though…

    Comment by Andras Gabor — October 16, 2007 @ 6:22 am GMT Oct 16,2007 | Reply

  2. […] a very large partitioned table in that view, and you might notice it is referenced twice: once as a scalar subquery in the from clause – where a couple of “)” have turned into “|” […]

    Pingback by Cardinality feedback « Oracle Scratchpad — May 11, 2009 @ 7:32 pm GMT May 11,2009 | Reply

  3. Hi JL.

    A friend opened an SR about this, and Oracle Support answered by opening a bug:
    BUG 19533338 – EXECUTION PLAN OVERALL COST REPORTED IN ID 0 IS LESS THAN PREVIOUS OPERATION IDS

    Comment by Portilho (@rportilhoproni) — September 1, 2014 @ 11:59 am GMT Sep 1,2014 | Reply

    • I think the general case has been addressed in 12.1.0.1 with fix control 12563419, described as: “add cost of scalar subquery into the cost of outer query”, but I see your friend has an example where “temp table transformation” has appeared (not that they’re using 12c anyway)

      Comment by Jonathan Lewis — September 1, 2014 @ 12:09 pm GMT Sep 1,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,429 other followers