Re: Low cardinality in OLTP with high concurrency
From: Ind-dba <oraclearora_at_googlemail.com>
Date: Wed, 25 Mar 2009 03:10:01 -0700 (PDT)
Message-ID: <48308999-156b-4d03-bc85-e64689a8fdc4_at_s38g2000prg.googlegroups.com>
On Mar 25, 3:00 pm, vipin.sin..._at_gmail.com wrote:
> On Mar 25, 1:56 pm, Shakespeare <what..._at_xs4all.nl> wrote:
>
>
>
> > vipin.sin..._at_gmail.com schreef:
>
> > > Hi Gurus
>
> > > I am struct with a problem of slow performance of a query. The problem
> > > is because of low cardinality. A table contains about 1 million rows
> > > which increases daily. there is a column which contains 16 distinct
> > > values say AOFA, AOFB,SIGN etc etc.
>
> > > When i run a query with following clause
>
> > > Where UPPER(Field) LIKE 'AOF%'
>
> > > It takes to much time. I checked in the table in 1 million rows,
> > > about .5 million rows match this criteria. I have created a functional
> > > index on the table with Upper function. This improved the performance
> > > from 2 mins to 40 sec bt 40 sec is again not acceptable.
>
> > > So i though of bitmap index. But since it is OLTP where inserts/
> > > modifications run at night by utilities and selects is done by users
> > > in Day time, i have to drop this option to avoid any deadlock sort of
> > > things.
>
> > > Any suggestion on this.
>
> > > --
> > > Vipin
>
> > Do you need the 'upper' part in your query, sicne you have only 16
> > distinct values? Or did you count the distinct on UPPER(column) ?
>
> > Shakespeare
>
> Hi All
>
> We cannot restrict to 16 values. Actually this field is meant for any
> text data but at one location scenario is like - it is having only
> distinct 16 values out of 1 million rows. It can have any data without
> any constraint. What to do in such situation?
Date: Wed, 25 Mar 2009 03:10:01 -0700 (PDT)
Message-ID: <48308999-156b-4d03-bc85-e64689a8fdc4_at_s38g2000prg.googlegroups.com>
On Mar 25, 3:00 pm, vipin.sin..._at_gmail.com wrote:
> On Mar 25, 1:56 pm, Shakespeare <what..._at_xs4all.nl> wrote:
>
>
>
> > vipin.sin..._at_gmail.com schreef:
>
> > > Hi Gurus
>
> > > I am struct with a problem of slow performance of a query. The problem
> > > is because of low cardinality. A table contains about 1 million rows
> > > which increases daily. there is a column which contains 16 distinct
> > > values say AOFA, AOFB,SIGN etc etc.
>
> > > When i run a query with following clause
>
> > > Where UPPER(Field) LIKE 'AOF%'
>
> > > It takes to much time. I checked in the table in 1 million rows,
> > > about .5 million rows match this criteria. I have created a functional
> > > index on the table with Upper function. This improved the performance
> > > from 2 mins to 40 sec bt 40 sec is again not acceptable.
>
> > > So i though of bitmap index. But since it is OLTP where inserts/
> > > modifications run at night by utilities and selects is done by users
> > > in Day time, i have to drop this option to avoid any deadlock sort of
> > > things.
>
> > > Any suggestion on this.
>
> > > --
> > > Vipin
>
> > Do you need the 'upper' part in your query, sicne you have only 16
> > distinct values? Or did you count the distinct on UPPER(column) ?
>
> > Shakespeare
>
> Hi All
>
> We cannot restrict to 16 values. Actually this field is meant for any
> text data but at one location scenario is like - it is having only
> distinct 16 values out of 1 million rows. It can have any data without
> any constraint. What to do in such situation?
Oracle has improvised on bitmap locks 9i onwards. If there is a single process inserting/updating the data, i would suggest you to give it a try to use bitmap index.
To start with, you can have both your functional index and bitmap index, so if bitmap index creates an issue for inserts, you can drop it.
I think a proof of concept needs to be done before you rule bitmap indexes out. Received on Wed Mar 25 2009 - 05:10:01 CDT