Oralce - best index ?

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 - 06:43:49 CST