Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Oracle Optmizer does not use the right index (Oracle 9.2.0.6)

Oracle Optmizer does not use the right index (Oracle 9.2.0.6)

From: <alanolya_at_invera.com>
Date: 15 Mar 2007 12:45:41 -0700
Message-ID: <1173987941.871534.19430@l75g2000hse.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US