another optimizerproblem

I have the folowing:
(NT-Workgroupserver 7.3.3.4.0)

create table test1 (a int, b int, c int); alter table test1 add primary key (a);
create index testi11 on test1 (b, c);

create table test2 (d int, e int, f int); create index testi21 on test2 (d, e);

AND problems with an select:

SELECT /*+ USE_NL (test2) */ b

      FROM test2, test1
      WHERE test2.d=test1.a(+) 
       AND test1.b = 1234;

The plan is (we have to use the rulebased optimizer)

SELECT STATEMENT Optimizer=RULE (Cost=2 Card=1 Bytes=52)   NESTED LOOPS (Cost=2 Card=1 Bytes=52)     TABLE ACCESS (BY ROWID) OF 'TEST1' (Cost=1 Card=1 Bytes=39)       INDEX (RANGE SCAN) OF 'TESTI11' (NON-UNIQUE)     TABLE ACCESS (FULL) OF 'TEST2' We have in table test2 200.000 records and this select should use the compound index testi21, but even if I include this index in the hint, it is not used.

Only if I create a new (not! compound) index:

create index testi22 on test2 (d);

It will be used:

SELECT STATEMENT Optimizer=RULE (Cost=2 Card=1 Bytes=52)   NESTED LOOPS (Cost=2 Card=1 Bytes=52)     TABLE ACCESS (BY ROWID) OF 'TEST1' (Cost=1 Card=1 Bytes=39)       INDEX (RANGE SCAN) OF 'TESTI11' (NON-UNIQUE)     INDEX (RANGE SCAN) OF 'TESTI22' (NON-UNIQUE) Why? Is this a problem of compound inexes?

thanks 4 any ideas! Received on Mon Jan 11 1999 - 07:58:16 CST