Oracle Scratchpad

December 30, 2011


Filed under: Bugs,Execution plans,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 5:47 pm BST Dec 30,2011

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

create table t1
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		level <= 10000
	rownum			id,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
	generator	v1,
	generator	v2
	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 ?

	/*+ index(t1 t1_id_asc) */
	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:


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, and the behaviour has been fixed by (and possible earlier).

1 Comment »

  1. […] just had cause to resurrect a blog note I wrote three years ago. The note says that an anomaly I discovered in wasfixed in – and this is […]

    Pingback by FBI Bug reprise | Oracle Scratchpad — January 12, 2015 @ 12:53 pm BST Jan 12,2015 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by