Low cardinality in OLTP with high concurrency

From: <vipin.singla_at_gmail.com>
Date: Tue, 24 Mar 2009 23:56:23 -0700 (PDT)
Message-ID: <c8dceb62-19f0-4407-9d90-505a8b9c54dd_at_s38g2000prg.googlegroups.com>

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.

Received on Wed Mar 25 2009 - 01:56:23 CDT

Original text of this message