Re: indexes

From: Rod Corderey <RodCorderey_at_Lane-Associates.com>
Date: Wed, 10 May 2000 07:33:49 +0100
Message-Id: <10493.105311@fatcity.com>

If your where clause is going to search on A and C, then to be reasonably sure of using that index you would place a dummy clause in the where clause
eg

        and B > ' ' 
or      and B >-1

this would ensure that the A,B,C index should be used, depending on the optimiser mode and other indexes available.

If you use

        and A = ....
        and C = ....

then if there is an index on A, it would be used in preference, as the extraction of C from the A,B,C index is incidental to the execution plan. Especially if the A index was a unique index.

You would need to bear in mind as well that the sorted result of A,B,C might not be very performance enhancing for a retrieval of A,C depending on the A,B set size as all B's within a single A.

Hope it helps,

Rod

-- 
Rod Corderey

Lane Associates
RodCorderey_at_Lane-Associates.com
http://www.Lane-Associates.com



> Webber Valerie H wrote:

> 

> I know this issue has been discussed before but I can't find the answer

> in my list archives.

> 

> If you have a composite index on a table consisting of columns A,B,C in

> that order, will the index be used if I have a query/where clause on

> columns A and C?

> 

> I know that it will if I query on A alone or A,B or A,B,C but I can't

> find documentation about A and C (no B)

> 

> Thanks in advance!

> Val

> 

> Valerie H. Webber

> Management Systems Designers, Inc

> Valerie.H.Webber_at_m1.irs.gov

Received on Wed May 10 2000 - 01:33:49 CDT