Re: Oracle - best index

[Quoted] Have you computed statistics on the table? "Jens Meier" <jens.meier_at_ddr.de> wrote in message news:b18ibj$vb1st$1_at_ID-109621.news.dfncis.de...
> Hi,
>
>
>
> can anyone help?
>
> The Reason:
>
> Table with more then 300.000 (and daily more) rows and round 20 Columns.
>
> DB : Oracle 8.17
>
> The Problem:
>
> Very slow searchresults, because we use different Searchterms to extract
> rows,
>
> -> not ever hits an index
>
> Sample
>
>
>
> ColumnA(PK) ColumnB ColumnC ColumnD ColumnE ColumnF
>
>
>
> ColumnA Unique
>
> ColumnB only 6 different entries
>
> ColumnC only 10 different entries
>
> ColumnD Date
>
> ColumnE only 6 different entries
>
> ColumnF only 50 different entries
>
>
>
> Sometimes we search with the exact ColumnA
>
> Sometimes we search with like %ColumnA
>
> Sometimes we use ColumnC ColumnD
>
> Sometimes we use ColumnD ColumnE ColumnF
>
> Sometimes we use %ColumnA ColumnD ColumnE ColumnF
>
> But sometimes we use ColumnD in a timerange
>
>
>
>
>
> I don't know which index is the best for this situation.
>
> (Which columns, Bitmap or normal index...)
>
>
>
>
>
> Now we have of course the PK as Index (standard)
>
> Then we have created many other combinations of columns as index,
>
> but the oracle optimizer doesn't match every time the best index, so we
have
> sometimes very high answer times.
>
>
>
> Can anybody help and give me some hints to perform the search?
>
>
>
> Thanks for helping
>
>
>
> Stefan
>
>
>
>
Received on Wed Jan 29 2003 - 13:51:17 CET