Re: Low cardinality,high concurrent in OLTP, how to improve query performance?
From: Gerard H. Pille <ghp_at_skynet.be>
Date: Sun, 01 Feb 2009 16:31:20 +0100
Message-ID: <4985c047$0$2846$ba620e4c_at_news.skynet.be>
krislioe_at_gmail.com schreef:
> 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
Date: Sun, 01 Feb 2009 16:31:20 +0100
Message-ID: <4985c047$0$2846$ba620e4c_at_news.skynet.be>
krislioe_at_gmail.com schreef:
> 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
Just analyze the index on that column with a histogram size 2, so that when you query "is_processed = 'N'" it will use the index, and not when you query "is_processed = 'Y'". Received on Sun Feb 01 2009 - 09:31:20 CST