I obviously have too much time on my hands. I created the indexes starting
with testi3 through testi1 and verified that testi3 had the lowest object
number. The execution plan still chose testi2. There must be something
else going on...
Peter Sharman wrote in message <36963ACA.394F7DC1_at_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 Sun Jan 10 1999 - 16:16:19 CST
