Re: Oralce - best index ?

From: Jens Meier <jens.meier_at_ddr.de>
Date: Wed, 29 Jan 2003 17:10:58 +0100
Message-ID: <b18ueu$10ed2i$1@ID-109621.news.dfncis.de>

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> schrieb im Newsbeitrag news:3e37d1af$0$238$ed9e5944_at_reading.news.pipex.net...
> "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
>
>

Thanks for helping,

if I create a function based index using the substr function (last 3 characters), match this Index on '%3456'?

Thanks and have a nice day

Stefan Received on Wed Jan 29 2003 - 10:10:58 CST