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

<alanolya_at_invera.com> wrote in message
news:1173987941.871534.19430_at_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.
>

Having run a couple of tests on 9.2.0.8 and 10.2.0.1 it looks like a bug, If you have two indexes as described, then Oracle "loses" the first one you created when working out the 'single table access path'.

You have a perfect test case - raise an SR with Oracle, and send them the test case.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html