Re: Q: Indexes & Rule based Optimizer

Comments inline.

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

 ...
> 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.

Not true:

SQL> create index ixabc on ixtest (a, b, c);

Index created.

SQL> select --+ rule
  2 * from ixtest
  3 where a = 10 and b = 20;

no rows selected

Execution Plan

   0 SELECT STATEMENT Optimizer=HINT: RULE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'IXTEST'    2 1 INDEX (RANGE SCAN) OF 'IXA' (NON-UNIQUE) The real reason was mentioned by Jonathan Lewis. IXA is chosen because it is a fully specified non-unique index. The workaround is to specify an index

SQL> create index ixba on ixtest (b, a);

Index created.

and then check:

SQL> select --+ rule
  2 * from ixtest
  3 where a = 10 and b = 20;

no rows selected

Execution Plan

   0 SELECT STATEMENT Optimizer=HINT: RULE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'IXTEST'    2 1 INDEX (RANGE SCAN) OF 'IXBA' (NON-UNIQUE) Martin Received on Tue Apr 17 2001 - 16:40:42 CDT