Q: Indexes & Rule based Optimizer

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

-- 
Uwe Schneider - WEB.DE AG  - Amalienbadstr. 41  - 76227 Karlsruhe 
F&E / Database & Software Technologies - Tel. (+49) 721 94329 8320