Re: Rulebased optimizer problem

As I remember (I do not have the ORACLE Tuning Guide at hand), the rule based optimizer prefers an index that is fully matched by columns of the WHERE clause over an index that is only partially matched. It is not aware of the higher selectivity of the partially matched index.

If you use an index hint, the cost based optimizer is activated. Probably you work with an implicit OPTIMIZER_GOAL=RULE (in the init.ora or in a previous ALTER SESSION command). Using any hint other than RULE sets the cost based optimizer to work (if you have ever gathered statistics for the table).

BTW: why don't you drop the indexes testi1 and testi2? I can see no use in them. (OK, this is obviously a simplified example, but it will somehow reflect the real world).

Klaus

Uwe Hoell schrieb:

> Hello,
>
> I have a question about the optimizer
> (NT-Workgroupserver ver 7.3.3.4.0):
>
> create table test (a int, b int, c int, d int, f int)
>
> create index testi1 on test (a)
> create index testi2 on test (a, b)
> create index testi3 on test (a, b, c, f)
>
> select * from test where a=1 and b=2 and c=3 and d=4
>
> This select uses index testi2:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=RULE
> 1 0 TABLE ACCESS (BY ROWID) OF 'TEST'
> 2 1 INDEX (RANGE SCAN) OF 'TESTI2' (NON-UNIQUE)
>
> But why not index testi3 ?
>
> We have a similar problem in a large system, where are defined two
> indexes like testi2 and testi3. If we use an indexhint to force the
> optimizer to use indexi3 the select is much faster.
> Is this the right news-group to post such questions or does anybody know
> a beter one?
>
> Thans!

--
Klaus Eichler mailto:klaus.eichler_at_nbgm.siemens.de Siemens AG, A&D SH 52 http://www.ad.siemens.de Tel +49 911 895-2918
Fax +49 911 895-4802 Received on Fri Jan 08 1999 - 11:18:14 CST