Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Oracle Optmizer does not use the right index (Oracle 9.2.0.6)
Hello,
Not sure whether this problem has been discussed earlier in this forum. Can someone suggest a workaround to the following problem which can be replicated by following the sequence of detailed steps I have given below. In summary, if you have a table with two composite indexes, both with the same fields, but one of them has a field in descending order, then the Optimizer loses track of the correct index to use for a specific query.
Thanks.
A.J.Andrews
See details below:
SQL*Plus: Release 9.2.0.6.0 - Production on Mon Feb 27 08:29:06 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> create table tab1 as select * from all_objects;
Table created.
SQL> set autotrace traceonly explain;
SQL> create index tab1ind1 on tab1(owner, object_name);
Index created.
SQL> analyze table tab1 compute statistics;
Table analyzed.
SQL> alter session set optimizer_mode=first_rows; Session altered.
SQL> select * from tab1 order by owner, object_name;
Execution Plan
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899 Bytes=372324)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172 Card=4899 Bytes=372324)
2 1 INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46 Card=4899)
===> Note here that the correct index tab1ind1 is being used for the
query.
SQL> create index tab1ind2 on tab1(owner, object_name desc);
Index created.
SQL> analyze table tab1 compute statistics;
Table analyzed.
SQL> select * from tab1 order by owner, object_name desc;
Execution Plan
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899 Bytes=372324)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172 Card=4899 Bytes=372324)
2 1 INDEX (FULL SCAN) OF 'TAB1IND2' (NON-UNIQUE) (Cost=48 Card=4899)
===> Again, the correct index tab1ind2 is being used.
SQL> select * from tab1 order by owner, object_name;
Execution Plan
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=288 Card=4899 Bytes=372324)
1 0 SORT (ORDER BY) (Cost=288 Card=4899 Bytes=372324) 2 1 TABLE ACCESS (FULL) OF 'TAB1' (Cost=3 Card=4899 Bytes=372324)
===> But, at this time the Optimizer has forgotten about the existence
of tab1ind1 and is going for a full table scan and a sort! Wrong
choice! Should be using index tab1ind1.
SQL> drop index tab1ind2;
Index dropped.
SQL> select * from tab1 order by owner, object_name;
Execution Plan
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899 Bytes=372324)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172 Card=4899 Bytes=372324)
2 1 INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46 Card=4899)
===> Once tab1ind2 is dropped, the Optimizer remembers the existence
of tab1ind1 and chooses the index scan for the very same query for
which it used a table scan earlier.
This problem is easily reproducible with any two indexes on a table, with both indexes being on the same set of columns, but with one of the indexes having a "desc" requirement on at least one of the columns. Received on Thu Mar 15 2007 - 14:45:41 CDT