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

From: gym dot scuba dot kennedy at gmail <>
Date: Sun, 01 Feb 2009 01:24:56 GMT
Message-ID: <IR6hl.296$>

<> wrote in message
> 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

One trick I ahve seen used is to use a function based index where you only assign values for the rows you want and all other values are NULL.

create index foo on the_table(decode(where_is_processed='N',NULL)) ; and run stats onthe index. Now the index will only consist of those records where the column where_is_processed='N'
Jim Received on Sat Jan 31 2009 - 19:24:56 CST

Original text of this message