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

<krislioe_at_gmail.com> wrote in message news:c669f2fe-e8a9-4c37-b5f5-34797f1c7f44_at_w39g2000prb.googlegroups.com...
> 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.

so
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