Re: Oracle - best index

Hello Jens,

I added some ideas in between your text.

"Jens Meier" <jens.meier_at_ddr.de> schrieb im Newsbeitrag 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
These Queries shouold be quick, because they should use the unique index.
>
> Sometimes we search with like %ColumnA
Looks like a full table scan, because there is no way of using an index-range-scan for this condition.
So this one could be slow and optimising will be hard I guess.
>
> Sometimes we use ColumnC ColumnD
At this column an index may help.
>
> Sometimes we use ColumnD ColumnE ColumnF
If the combination of these conditions have a high selectivity (less than 10% of the rows in the result-set, very rough approximation) again a concatenated index might help.
>
> Sometimes we use %ColumnA ColumnD ColumnE ColumnF
If the combination of the conditions "ColumnD ColumnE ColumnF" have a high selectivity (less than 10% of the rows in the result-set, very rough approximation) again a concatenated index might help.

>
> But sometimes we use ColumnD in a timerange
If the selectivity isn't big, you might try a bitmap index. This has to be tested.
>
>
>
>
>
> 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?

Have you thought of tracing the sql-statements for a certain period of time? Analyze these with tkprof or equivalent tools.
>
>
>
> Thanks for helping
>
>
>
> Stefan
>
>
>
>
Received on Thu Jan 30 2003 - 20:50:38 CET