Oracle - best index?

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 29 Jan 2003 13:10:04 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7033C4557@lnewton.leeds.lfs.co.uk>

I suspect Cost based optimiser as rule would tend to use an index if it found one, so, the question has to be 'when did you last analyse the table (and indexes) ?'

If the last analysis to compute stats was done when there were only a small number of rows in the table, then the stats will be saying to CBO - do a FTS it's much quicker.

On the other hand, if the stats are up to date, it is possible that the queries you are sending don't allow the index to be used. It depends upon :

which columns you have indexed
do you allow nulls in the data ?
etc

I'd expect the PK to be used on your first search where you supply a value for Column A, but if the table is small then it is possible that a FTS is much quicker anyway.

What are your explain plans for the individual searches ? What are the indexes you have defined ?

Cheers,
Norman.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

Received on Wed Jan 29 2003 - 07:10:04 CST