Re: Low cardinality,high concurrent in OLTP, how to improve query performance?
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Sat, 31 Jan 2009 17:44:03 -0600
Message-ID: <Yi5hl.1197$Lr6.837_at_flpi143.ffdc.sbc.com>
krislioe_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:44:03 -0600
Message-ID: <Yi5hl.1197$Lr6.837_at_flpi143.ffdc.sbc.com>
krislioe_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.. Received on Sat Jan 31 2009 - 17:44:03 CST