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 - 09:51:21 CST