Rulebased optimizer problem

Hi

I believe this is a good example of why Oracle have switched to a cost based optimizer. Your question assumes that Oracle should be smart enough to use the index containing all 4 columns. However this may not always be the best choice. Without knowing the size of the various indexes and the selectivity of the values it is difficult to say what is the best choice. I believe (I cannot back it up) that oracle is selecting the index with 2 columns because it is assuming that this will be faster to scan than the index with 4 columns and will therefore produce a faster access. However your table is so large that this assumption is not true. I suggest you use a hint or start using the cost based optimizer and analyzing your tables.

-----Original Message-----
From: Uwe Hoell [mailto:hoell_at_aeb.de]
Posted At: Friday, January 08, 1999 7:51 AM Posted To: server
Conversation: Rulebased optimizer problem Subject: Rulebased optimizer problem

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! Received on Fri Jan 08 1999 - 15:26:09 CST