Re: Low cardinality,high concurrent in OLTP, how to improve query performance?
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Sat, 31 Jan 2009 19:07:27 -0600
Message-ID: <Gu6hl.12191$D32.10820_at_flpi146.ffdc.sbc.com>
Michael Austin wrote:
>> 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
>>
>>
>>
>>
>>
Date: Sat, 31 Jan 2009 19:07:27 -0600
Message-ID: <Gu6hl.12191$D32.10820_at_flpi146.ffdc.sbc.com>
Michael Austin wrote:
> krislioe_at_gmail.com wrote:
>> 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
>>
>>
>>
>>
>>
> > > Hopefully you are doing the testing on a test box... anyway - did you > gather statistics before trying the query? > > > If that does not work, you can always test the effects of creating the > index with just IS_PROCESSED -followed by another gather stats.
It is Saturday and my brain is not fully engaged. I would create the index with a DATE,IS_PROCESSED and include a date range in the query This should sufficiently influence the CBO to use the index.
select x,y,z ... where date = today and IS_PROCESSED = 'N'. Received on Sat Jan 31 2009 - 19:07:27 CST