Index not used (10g)

Here’s an example of why execution plans can change after an upgrade to 10gR2.

I’ve created a table with an artificially exaggerated problem – dependent columns – that highlights some inconsistencies in the optimizer’s behaviour. Here’s the code:

create table t1
as
select
	mod(rownum,20) 		n1,
	mod(rownum,20)		n2,
	rpad('x',40,'x')	ind_pad,
	rpad('x',200)		padding
from
	all_objects
where
	rownum  <= 4000
;

create index t1_i1 on t1(n1, ind_pad, n2)
pctfree 91
;

-- collect statistics at this point

Note that there are 20 possible pairs of values for (n1, n2) – ignore the ind_pad column, it’s just something that I’ve used to increase the size of the index at the branch level without changing the arithmetic.

Here’s the query that I want to demonstrate:

select
	padding
from
	t1
where
	n1	= 2
and	ind_pad	= rpad('x',40,'x')
and	n2	= 2
;

From the optimizer’s perspective, the combined selectivity of the three predicates is 1/400 (by multiplying 1/20, 1, and 1/20), which results in a predicted cardinality of 10. Here’s the autotrace execution plan from 9.2.0.8 (with CPU costing disabled).

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12 Card=10 Bytes=2470)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=12 Card=10 Bytes=2470)
   2    1     INDEX (RANGE SCAN) OF 'T1_I1' (INDEX) (Cost=4 Card=10)

And here’s the plan from 10.2.0.1 (again with CPU costing disabled).

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |  2470 |    23 |
|*  1 |  TABLE ACCESS FULL| T1   |    10 |  2470 |    23 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1"=2 AND "IND_PAD"='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' AND "N2"=2)

Oops!

Let’s try hinting the code with an index() hint to see what we get, as that might give us a clue about why the change in plan has appeared :

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    10 |  2470 |   168 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    10 |  2470 |   168 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |   200 |       |    25 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N1"=2 AND "IND_PAD"='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' AND "N2"=2)

Note, especially, the increase in the cardinality predicted for the index, with the increased cost of scanning the index, and the massive increase in the cost of visiting the table.

What’s happened is this: for the purposes of scanning the index, the optimizer has used the number of distinct keys (user_indexes.distinct_keys) to determine how much of the index will be scanned, and how many rowids identified. This has increased the cost and cardinality of the index line quite dramatically – and since it is the “index selectivity with filtering” multiplied by the clustering_factor that dictates the cost of the table access by index, the total cost of the query has gone up as well. However, by the time that the optimizer gets down to the table, the calculation has fallen back to the “product of columns” style of calculation – so we end up with a prediction of a high cost returning a few rows.

In theory, of course, the new cost is a better estimate than the old – but for some people this change could be bad news at least for some queries.

Apart from the explicit change of plan shown in this trivial example, consider the impact this will have on more complex queries: if one step of the query is suddenly much more expensive, and only returns a few rows, the optimizer is quite likely to move that step to an earlier point in the plan to ensure that the cost is incurred as few times as possible.

General guideline for the upgrade to 10gR2: watch out for queries that contain predicates on every column of a multi-column non-unique index; their execution plans may change.

Update (Mar 2008): This note was one I had forgotten about when I wrote a newer version of the same thing, highlighting the next step in the evolution of the execution plan when I tried the same thing with 11g.