Oracle Scratchpad

August 31, 2010

Filter “Bug”

Filed under: Execution plans,Oracle,subqueries — Jonathan Lewis @ 7:01 pm BST Aug 31,2010

Here’s an example to remind you how important it is to look at the “Predicate Information” supplied with an execution plan. Here are two execution plans that look very similar in shape – a continuous set of steps inwards and downwards from parent to child, with no “multi-child” parent rows:


--------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     1 |    19 |     4 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | MIN_MAX |     1 |    19 |     2 |
|*  2 |   INDEX UNIQUE SCAN            | MM_PK   |     1 |       |     1 |
|   3 |    SORT AGGREGATE              |         |     1 |     8 |       |
|   4 |     FIRST ROW                  |         |    10 |    80 |     2 |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)| MM_PK   |    10 |    80 |     2 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MM1"."ID_PARENT"=100 AND "MM1"."ID_CHILD"= (SELECT
MAX("MM2"."ID_CHILD") FROM "MIN_MAX" "MM2" WHERE "MM2"."ID_PARENT"=100))
5 - access("MM2"."ID_PARENT"=100)

And then this:


-------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    19 |    13 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | MIN_MAX |     1 |    19 |     3 |
|*  2 |   INDEX RANGE SCAN            | MM_PK   |     1 |       |     2 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| MIN_MAX |     1 |    11 |     2 |
|*  4 |     INDEX UNIQUE SCAN         | MM_PK   |     1 |       |     1 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("MM1"."ID_PARENT"=100)
filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "MIN_MAX" "MM2"
WHERE "MM2"."ID_CHILD"=:B1 AND "MM2"."ID_PARENT"=100 AND
"MM2"."STATUS"=99))
3 - filter("MM2"."STATUS"=99)
4 - access("MM2"."ID_PARENT"=100 AND "MM2"."ID_CHILD"=:B1)

If you want to interpret these plans in the standard way, you would simply apply a rule like: “each parent (optionally) calls its child to supply a row-source”.

In the case of the first plan this would give you the right answer. Working from line 1: We pick a row from a table min_max by unique scan of index mm_pk (line 2) where we’ve identified the unique key by a sort aggregate (line 3) of the first row (line 4) that we found by a min/max range scan of index mm_pk. The query that produced this plan is:

select
	small_vc
from	min_max mm1
where	mm1.id_parent = 100
and	mm1.id_child = (
		select max(mm2.id_child)
		from   min_max mm2
		where  mm2.id_parent = 100
	)
;

But if we try to apply the same rule to the second execution plan, it doesn’t work properly.

This is the query that generated the plan.

select	small_vc
from	min_max mm1
where	mm1.id_parent = 100
and	mm1.id_child in (
		select
			/*+ no_unnest */
			mm2.id_child
		from	min_max mm2
		where	mm2.id_parent = 100
		and	mm2.status = 99
	)
;

Note particularly the change from an “equality” subquery to an “IN” subquery.

There is a little oddity that appears in the normal “parent calls child” algorithm for reading execution plans  when you use a subquery to filter the rowsource generated by an index range scan. The filter operation that you would see if you were running a subquery against a table “disappears” – the optimizer folds into the range scan. [In fact, depending on version, the same disappearing trick occurs when you use the /*+ push_subq */ hint to make a subquery run early even when it's running against a table.]

Ideally I’d like to see a plan shaped like the following – but line 2a gets squeezed out, and line 2b (and its descendents, if any) slide one step to the left:


-------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    19 |    13 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | MIN_MAX |     1 |    19 |     3 |
|*  2a|   FILTER                      | MM_PK   |     1 |       |     2 |
|*  2b|    INDEX RANGE SCAN           | MM_PK   |     1 |       |     2 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| MIN_MAX |     1 |    11 |     2 |
|*  4 |     INDEX UNIQUE SCAN         | MM_PK   |     1 |       |     1 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2a - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "MIN_MAX" "MM2"
WHERE "MM2"."ID_CHILD"=:B1 AND "MM2"."ID_PARENT"=100 AND
"MM2"."STATUS"=99))
2b - access("MM1"."ID_PARENT"=100)
3 - filter("MM2"."STATUS"=99)
4 - access("MM2"."ID_PARENT"=100 AND "MM2"."ID_CHILD"=:B1)

So watch out when reading plans with subqueries – is your subquery operating as an access (driving) subquery, or as a camouflaged filter subquery. It’s easy to come to the wrong conclusion if all you do is check the shape of the plan.

10 Comments »

  1. Jonathan,

    Can you please explain why your “ideal” plan is better than the original plan?
    My understanding is the ideal plan will range scan the index and provide the rows to the next step which will filter them based on subquery. But the original plan will apply subquery filter and access predicate to the range scan, thereby filtering rows early. Or am I misunderstood on how “access” and “filter” predicates are applied?

    Comment by Narendra — September 1, 2010 @ 8:18 am BST Sep 1,2010 | Reply

    • Narenda,

      At a fairly low level what you’re describing is the difference between:

      call function A, then call function B with the result of A
      and
      call function A, which calls function B

      I don’t really think there’s going to be a significant difference whichever option you choose (although there may be some interesting ramifications in parallel queries) – but whichever action is chosen I think the plan could still be written to show the FILTER operation as a separate operation simply for clarity of view. (Remember, as I pointed out to Henish, execution plans don’t, and probably can’t, show us a perfect representation of what’s going on.)

      Comment by Jonathan Lewis — September 1, 2010 @ 5:22 pm BST Sep 1,2010 | Reply

  2. Apologies as I missed to add this in my previous post. I generally tend to read execution plan bottom-up, when checking predicates section. The earlier predicate is applied the better is my general approach. Hence the above question.

    Comment by Narendra — September 1, 2010 @ 8:20 am BST Sep 1,2010 | Reply

    • That’s a bit like the old “the first step is over to the right and near the top” description of where to start reading execution plans. It’s not accurate, and it would be quite hard to express the idea precisely, but I know what you mean.

      Comment by Jonathan Lewis — September 1, 2010 @ 5:24 pm BST Sep 1,2010 | Reply

  3. Hello Sir,

    
    --------------------------------------------------------------------------
    | Id  | Operation                      | Name    | Rows  | Bytes | Cost  |  
    --------------------------------------------------------------------------  
    |   0 | SELECT STATEMENT               |         |     1 |    19 |     4 |  
    |   1 |  TABLE ACCESS BY INDEX ROWID   | MIN_MAX |     1 |    19 |     2 |  
    |*  2 |   INDEX UNIQUE SCAN            | MM_PK   |     1 |       |     1 |  
    |   3 |    SORT AGGREGATE              |         |     1 |     8 |       |  
    |   4 |     FIRST ROW                  |         |    10 |    80 |     2 |  
    |*  5 |      INDEX RANGE SCAN (MIN/MAX)| MM_PK   |    10 |    80 |     2 |  
    --------------------------------------------------------------------------  
      
    Predicate Information (identified by operation id):  
    ---------------------------------------------------  
    2 - access("MM1"."ID_PARENT"=100 AND "MM1"."ID_CHILD"= (SELECT  
    MAX("MM2"."ID_CHILD") FROM "MIN_MAX" "MM2" WHERE "MM2"."ID_PARENT"=100))  
    5 - access("MM2"."ID_PARENT"=100) 
    

    in the above plan will sort happen first and get the first row? than why first row come before sort aggregate if we read plan from bottom up?

    OR

    will oracle stop scaning when it read value >100 and return the last value for id_child if my assumption is correct for index mm_pk define as (id_parent, id_child)?

    Thanks

    Comment by henish — September 1, 2010 @ 2:56 pm BST Sep 1,2010 | Reply

    • Henish,

      I think you just have to decide that the execution plan can’t always be a perfect description of everything that’s happening.

      The “sort aggregate” has to be there because there’s a max() in the subquery – even though the optimizer knows there will be only one row because of the “first row” and knows it won’t need to sort that one row. The oddity is just a side-effect of the min/max method.

      Comment by Jonathan Lewis — September 1, 2010 @ 5:06 pm BST Sep 1,2010 | Reply

  4. Is the change from an “equality” to an “IN” subquery really important for the execution plan? I cannot test it right now but i could imagine we would get a “Type 1″ plan (no filter, no multi-child parent rows) with the “IN” subquery, too. The only difference which “IN” brings is that the index access to the parent table will be a “range” instead of “unique” scan.

    What really makes the FILTER operation unavoidable is the NO_UNNEST hint isn’t it? It dictates that the subquery gets executed as is for every row from the parent – hence the filter.

    Comment by Todor Botev — September 2, 2010 @ 7:02 am BST Sep 2,2010 | Reply

  5. [...] did the choosing – how could it possible by wrong ! Given the number of times I’ve said “you must include the predicate section”, why isn’t it there ? (answer: it’s display_awr). In fact lines 5 and 6 were redundant [...]

    Pingback by Quiz Night « Oracle Scratchpad — November 21, 2010 @ 11:10 am BST Nov 21,2010 | Reply

  6. [...] during an index range scan – it’s a topic I wrote about a few months ago with the title “filter bug” because the plan shows (or, rather, fails to show) a “missing” filter operation, which [...]

    Pingback by Missing Filter « Oracle Scratchpad — February 29, 2012 @ 9:31 pm BST Feb 29,2012 | Reply

  7. […] reading execution plans – then decided to check if I had mentioned it before. I have: twice: originally in 2010, and then again in […]

    Pingback by Interesting Plan | Oracle Scratchpad — November 29, 2013 @ 8:56 am BST Nov 29,2013 | 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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,013 other followers