Re: Oracle - best index?

Thanks for helping.
"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> schrieb im Newsbeitrag news:E2F6A70FE45242488C865C3BC1245DA7033C4557_at_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) ?'
>

every night we compute statistics

> 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
>

 The problem is, i have many different searchcriteria to fetch an exact result. The table contains customerdata.

example, but not all:

- search with the custkey
- search with the status
- search with the status and creationdate (also between)
- search with the custkey ans status
- search with budget (also range)

Because we use a browser to access the customerdata with many different inputvalues (about 6 ) to restrict the data, which can be filled every constelation.

> 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 ?

We have an PK index.
And 3 other - but we guess a little bit, what criteria is the most used.

>
> 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 - 10:24:35 CST