Oracle - best index

From: Jens Meier <jens.meier_at_ddr.de>
Date: Wed, 29 Jan 2003 13:44:25 +0100
Message-ID: <b18ibj$vb1st$1_at_ID-109621.news.dfncis.de>



Hi,

[Quoted] can anyone help?

The Reason:

[Quoted] [Quoted]  Table with more then 300.000 (and daily more) rows and round 20 Columns.

[Quoted]  DB : Oracle 8.17

The Problem:

 Very slow searchresults, because we use different Searchterms to extract rows,

-> not ever hits an index

 Sample

[Quoted] 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

[Quoted] Sometimes we search with like %ColumnA

[Quoted] Sometimes we use ColumnC ColumnD

[Quoted] Sometimes we use ColumnD ColumnE ColumnF

[Quoted] Sometimes we use %ColumnA ColumnD ColumnE ColumnF

[Quoted] But sometimes we use ColumnD in a timerange

[Quoted] 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 [Quoted] sometimes very high answer times.

Can anybody help and give me some hints to perform the search?

[Quoted] Thanks for helping

Stefan Received on Wed Jan 29 2003 - 13:44:25 CET

Original text of this message