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

>> krisl..._at_gmail.com 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 -

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. Received on Sat Jan 31 2009 - 19:00:52 CST