Re: Q: Indexes & Rule based Optimizer

On Tue, 17 Apr 2001 16:34:28 +0200, Uwe Schneider <us_at_webde-ag.de> wrote:

>Hi,
>
>we have found a strange Oracle behaviour when using the rule based
>optimizer (in 8.0.5 and 8.1.7):
>
>create table ixtest (
> a number(10),
> b number(10),
> c number(10));
>
>create index ixa on ixtest (a);
>
>create index ixbac on ixtest (b,a,c);
>
>SQL> select * from ixtest where a = 10 and b = 20;
>
>no rows selected
>
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'IXTEST'
> 2 1 INDEX (RANGE SCAN) OF 'IXA' (NON-UNIQUE)
>
>
>Oracle seems to disregard using index "ixbac" _although_ it is more
>selective than the single-column index "ixa". If the index were on (b,a)
>or if we would use an index hint, this situation would change. The
>behaviour is independent from the number of rows in ixtest or the Oracle
>version.
>
>Is there a way to convince the Oracle RBO to use index "ixbac" without
>an index hint?
>
>Best regards
>
>Uwe

First of all: please stop developing applications using the RBO. Development on it has stopped in 1993, and CBO has now so much more functionality and better access paths, that RBO must be considered inferior to CBO. Officially it has been desupported, and I just pray you prepare before the day Oracle removes it. This might be never, but then it might be 9i.
As to your question: it is quite obvious the ixbac is ignored, as the columns are in the wrong order. If the index would have been a,b,c it would have been selected.
This is yet another limitation of RBO which has been removed in the 8i CBO. Hth,

Sybrand Bakker, Oracle DBA Received on Tue Apr 17 2001 - 11:19:53 CDT