Re: Low cardinality in OLTP with high concurrency
From: <vipin.singla_at_gmail.com>
Date: Wed, 25 Mar 2009 03:21:53 -0700 (PDT)
Message-ID: <65a6c9c7-34af-475d-9f32-ccb523c96681_at_y33g2000prg.googlegroups.com>
On Mar 25, 3:10 pm, Ind-dba <oraclear..._at_googlemail.com> wrote:
> 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.
Date: Wed, 25 Mar 2009 03:21:53 -0700 (PDT)
Message-ID: <65a6c9c7-34af-475d-9f32-ccb523c96681_at_y33g2000prg.googlegroups.com>
On Mar 25, 3:10 pm, Ind-dba <oraclear..._at_googlemail.com> wrote:
> 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.
The application server is using connection pooling and it is expected inserts and updates will be there from multiple connection for multiple clients. If it would have been a single client then bitmap index could have been used, but it is not the case here. Received on Wed Mar 25 2009 - 05:21:53 CDT