Re: Low cardinality in OLTP with high concurrency
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Wed, 25 Mar 2009 19:34:19 -0500
Message-ID: <i8Ayl.13514$pr6.3816_at_flpi149.ffdc.sbc.com>
vipin.singla_at_gmail.com 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
Date: Wed, 25 Mar 2009 19:34:19 -0500
Message-ID: <i8Ayl.13514$pr6.3816_at_flpi149.ffdc.sbc.com>
vipin.singla_at_gmail.com 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
Two things...
- if you only ever need to see the "AOF%" in uppercase, then fix the app or add a trigger to UPPER the field when it is inserted.
2)Depending on **proper** index(es), LIKE may not be your best choice - only testing will confirm or rule out this approach - you might try (incorporating #1)
where field between 'AOFA' and 'AZZZ' for example.
and if your data is partitioned (not sure I would use this field as a partition key due to the skewed data) you could limit it to a particular partition... Received on Wed Mar 25 2009 - 19:34:19 CDT