Re: Low cardinality in OLTP with high concurrency

From: <>
Date: Wed, 25 Mar 2009 00:32:17 -0700 (PDT)
Message-ID: <>

On Mar 24, 11:56 pm, wrote:
> 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

have you tried to map the 16 distinct value to numerical value. 1~16? Add a column as the numerical representation and do search again? for example, all AOF% is mapped to a number in the range 0~5? Received on Wed Mar 25 2009 - 02:32:17 CDT

Original text of this message