Re: Low cardinality,high concurrent in OLTP, how to improve query performance?
From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: Sat, 31 Jan 2009 17:06:33 -0800 (PST)
Message-ID: <22c3a7a5-9d50-45f8-b45f-6953676a00a3_at_w1g2000prk.googlegroups.com>
On Jan 31, 5:54 pm, 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
Date: Sat, 31 Jan 2009 17:06:33 -0800 (PST)
Message-ID: <22c3a7a5-9d50-45f8-b45f-6953676a00a3_at_w1g2000prk.googlegroups.com>
On Jan 31, 5:54 pm, 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
How about changing the database design so that rows initially go into one table then get inserted into the processed table each night?
What is the business point of having an OLTP system that repeatedly checks for only rows that are not processed and has ( whatever that means ) many people concurrently using that query.
Something in the whole design just doesn't sound right to me at least. If there is a design flaw in your database coding around it may eventually reach a breaking point. Received on Sat Jan 31 2009 - 19:06:33 CST