Re: Low cardinality,high concurrent in OLTP, how to improve query performance?
From: <krislioe_at_gmail.com>
Date: Sat, 31 Jan 2009 16:10:33 -0800 (PST)
Message-ID: <b86addfc-833d-46be-b710-c5664b6ec984_at_v5g2000pre.googlegroups.com>
On Feb 1, 6:44 am, Michael Austin <maus..._at_firstdbasource.com> wrote:
> 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
>
> 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..- Hide quoted text -
>
> - Show quoted text -
Date: Sat, 31 Jan 2009 16:10:33 -0800 (PST)
Message-ID: <b86addfc-833d-46be-b710-c5664b6ec984_at_v5g2000pre.googlegroups.com>
On Feb 1, 6:44 am, Michael Austin <maus..._at_firstdbasource.com> wrote:
> 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
>
> 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..- Hide quoted text -
>
> - Show quoted text -
Hi,
after : create index idx1 on stock_summ (IS_PROCESSED,x,y,z) => still Full Table Scan
What is other than Partition ?
Thank you,
xtanto
Received on Sat Jan 31 2009 - 18:10:33 CST