Search

Top 60 Oracle Blogs

Recent comments

Filter “Bug”

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.