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.
rem rem Script: index_desc_anomaly.sql rem Author: Jonathan Lewis rem Dated: Sept 2011 rem 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).
[…] just had cause to resurrect a blog note I wrote three years ago. The note says that an anomaly I discovered in 9.2.0.8 was fixed in 10.2.0.3 – and this is […]
Pingback by FBI Bug reprise | Oracle Scratchpad — January 12, 2015 @ 12:53 pm GMT Jan 12,2015 |