FBI Bug

Here’s a funny little optimizer bug – though one that seems to have been fixed by at least 10.2.0.3. It showed up earlier on today in a thread on the OTN database forum. We’ll start (in 9.2.0.8) with a little table and two indexes – one normal, the other descending.

create table t1
as
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		level <= 10000
)
select
	rownum			id,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 10000
;

-- collect stats

create index t1_id_asc  on t1(id) compute statistics;
create index t1_id_desc on t1(id desc) compute statistics;

You’ll notice that the two indexes are on the same column, even though one of them has (redundantly) declared the column to be treated in descending order. If I now execute the following query, noting the hint to use index t1_id_asc, would you expect the presence of index t1_id_desc to make any difference to the plan ?

select 
	/*+ index(t1 t1_id_asc) */
	* 
from 
	t1
where
	id between 1001 and 2000
;

Here are the plans, first with t1_id_desc in place:

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     3 |   342 |     5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |     3 |   342 |     5 |
|*  2 |   INDEX RANGE SCAN          | T1_ID_ASC   |     3 |       |     4 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."ID">=1001 AND "T1"."ID"<=2000)
       filter(SYS_OP_DESCEND("T1"."ID")<=HEXTORAW('3DF4FDFF')  AND
              SYS_OP_DESCEND("T1"."ID")>=HEXTORAW('3DEAFF') )

Now after dropping index t1_id_desc

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |  1001 |   111K|    22 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |  1001 |   111K|    22 |
|*  2 |   INDEX RANGE SCAN          | T1_ID_ASC   |  1001 |       |     4 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."ID">=1001 AND "T1"."ID"<=2000)

The shape of the two plans hasn’t changed, of course, they’ve both obeyed my hint. Notice, however, the changes in the Predicate Information and the estimated cardinalities. With index t1_id_desc in place, the optimizer has found the definition of the hidden column representing the “id desc” and used it to generate some new predicates, introducing some extra factors into the calculation of selectivity.

In the case of the OTN query, the initial inputs were bind variables – which meant the new predicates looked like:

       filter(SYS_OP_DESCEND("D"."CREATEDAT")<=SYS_OP_DESCEND(:Z) AND
              SYS_OP_DESCEND("D"."CREATEDAT")>=SYS_OP_DESCEND(:Z))

In this version of Oracle this resulted in the predicates being treated as “guesses on an index range scan” – which resulted in the optimizer ignoring the appropriate index (until hinted) even though the cost calculation gave a lower cost.

Update 31 Dec 2011: It’s just occurred to me that this is another example of a case where you can drop an index that isn’t being used and find that execution plans can change as a side effect. (Conversely, you create an index that shouldn’t change any execution plans – and some plans change, even though they don’t use the index.) Luckily, as I pointed out above, this demo came from 9.2.0.8, and the behaviour has been fixed by 10.2.0.3 (and possible earlier).