Re: Oracle - best index

Jens Meier wrote:

> 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

Before you do anything else ... run EXPLAIN PLAN with your SQL statements and see which, if any, indexes are being used.

The consideration as to whether to use bitmap indexes relates to the table usage and cardinality. Bitmaps lend themselves well to static data and very low cardinality. From your posting it is impossible to tell any of this. I would suggest that you read up on indexes at http://tahiti.oracle.com.

With respect to which columns to index obviously indexing all 20 columns is not likely the optimal solution. I would look at the SQL being sent and determine which queries happen most frequently and take the most time. Then deal with the most common and most time consuming queries first.

Also consider with 300K rows added daily whether partitioning (if you have Enterprise Edition) might not be a way to segment your data for far faster retrieval.

Daniel Morgan Received on Wed Jan 29 2003 - 18:09:24 CET