Oracle - best index
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