Re: Low cardinality,high concurrent in OLTP, how to improve query performance?

From: Michael Austin <>
Date: Sat, 31 Jan 2009 19:07:27 -0600
Message-ID: <Gu6hl.12191$>

Michael Austin wrote:
> wrote:

>> On Feb 1, 6:44 am, Michael Austin <> wrote:
>>> wrote:
>>>> Hi sql gurus,
>>>> In our racle DB 10g application we have a table that is INSERTED
>>>> highly concurrently in OLTP application (but are NEVER UPDATED, Insert
>>>> Only).
>>>> There is a column : IS_PROCESSED VARCHAR2(1) , values is : 'Y' or
>>>> 'N' .
>>>> The rows will grows by a mill rows in a month. The default value is
>>>> 'N' then on nightly basis a JOB will run to do some process and update
>>>> to 'Y'.
>>>> So, only the daily data will have 'N' value, one day later it will
>>>> become 'Y'.
>>>> The requirement in the OLTP is there will be many concurrent access
>>>> that wil query :
>>>> SELECT X, Y, Z , SUM(A), SUM (B) from the_table WHERE IS_PROCESSED =
>>>> 'N' GROUP BY (X,Y,Z)
>>>> The docs says that B-Tree index wont help and Bitmap Index is not for
>>>> OLTP, what how can I avoid Full table Scan in this query ?
>>>> Thank you for your help,
>>>> xtanto
>>> I know of no reason to not create an index on this column. Yes it is can
>>> produce a somewhat lop-sided BTree but, it will prevent a FTS. If data
>>> is processed DAILY - you could create a partition scheme based on DATE
>>> and include that in your WHERE clause.
>>> If you are adverse to the single column index you could do:
>>> create index blah on blahblah (YN,x,y,z)
>>> and all queries that seletc x,y,z can include YN = Y or YN=no. Yes the
>>> index gets updated - not a bad thing but not great either..- Hide
>>> quoted text -
>>> - Show quoted text -
>> Hi,
>> after : create index idx1 on stock_summ (IS_PROCESSED,x,y,z) => still
>> Full Table Scan
>> What is other than Partition ?
>> Thank you,
>> xtanto
> Hopefully you are doing the testing on a test box... anyway - did you 
> gather statistics before trying the query?
> If that does not work, you can always test the effects of creating the 
> index with just IS_PROCESSED -followed by another gather stats.

It is Saturday and my brain is not fully engaged. I would create the index with a DATE,IS_PROCESSED and include a date range in the query This should sufficiently influence the CBO to use the index.

select x,y,z ... where date = today and IS_PROCESSED = 'N'. Received on Sat Jan 31 2009 - 19:07:27 CST

Original text of this message