Re: Oralce - best index ?

"Jens Meier" <jens.meier_at_ddr.de> wrote in message news:b18iaf$109khp$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...)
>

Your mileage may vary but I would suggest trying

a compound index on D,E,F - may be suitable for two different search criteria
a single column index on D. - should be used for your range scan possibly a compound index on C,D. - fits another of your search criteria No other indexes - unless any of the columns are foreign keys for other tables.

As you are doing DML on this table do not touch bitmap indices. Using like %columnA will not be able to use an index. If you actually want to match say the last 3 characters of your primary key you could build a function based index using the substr function, and change your where from like %A to where substr(columnA,..)=criteria.

Note this is a provisional recommendation which needs to be properly tested Received on Wed Jan 29 2003 - 07:05:51 CST