Re: Rulebased optimizer problem

From: Peter Sharman <psharman_at_us.oracle.com>
Date: Fri, 08 Jan 1999 09:05:15 -0800
Message-ID: <36963ACA.394F7DC1@us.oracle.com>

Uwe

Believe it or not, it's caused by the order in which the indexes were created. In a situation like this, where multiple indexes can be used, the RBO (not sure about CBO) would choose the index with the lowest object_id from dba_objects (in other words, the first one created). To prove this, create them in the reverse order and you should see testi3 being used.

Hope this helps

Pete

Uwe Hoell wrote:

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

Regards

Pete

Peter Sharman                              Email: psharman_at_au.oracle.com
WISE Course Development Manager            Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education            (650)607 0109 (local)

San Francisco

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

Received on Fri Jan 08 1999 - 11:05:15 CST