Re: Low cardinality,high concurrent in OLTP, how to improve query performance?

> Hi,
>
> after : create index idx1 on stock_summ (IS_PROCESSED,x,y,z) => still
> Full Table Scan

Of course it is. CBO will compute the number of IO requests you will need to do and will chose the cheaper plan. That is inevitably the plan with the fewer multi-block reads, much cheaper then a gazillion single block reads.

>
> What is other than Partition ?
>
> Thank you,
> xtanto
>

Hash cluster. You can create a hash cluster on that column and put your table into the hash cluster. It will speed up your queries but it will slow down your updates. If you update the cluster column from 'N' to 'Y', the update will copy the row from the 'N' part to the 'Y' part. You could also create two tables, one holding the daily data, the other one holding the historical data. The job would then have to delete the record in the daily table and insert it into the historical table. That would be the classic way of doing things. Hash cluster is, essentially, a substitute for list partitioning. You get the table partitioned by a small list of falues from your status column, which will help CBO to resolve predicates using that column.

-- 
http://mgogala.freehostia.com