Re: How can I further reduce physical read for this query short of throwing memory at it?

Thanks for all the questions! My answers are embedded.

>Is "D.ORG_NBR = '100000' and D.BRNC_ID = '5000'" a constant, or do the
>actual values change with each execution?

It varies.

>If you execute the query again immediately after the first execution
>finishes, how long does the second execution take and what are the block
>access counts for it?

About the same

> Is fulfilling this query the main load on your database? If you have to
> degrade performance on other things to make this better, is that OK?

It depends how much the degradation would be. By the way, I have tested some bitmap indexes and a join index. Still more testing to get better understanding of performance improvement.

>What about partioning? Do you have it? If so, on what? If not, can you
>do it?

The table is partition on a column not used in the query. I am currently trying to get approval to partition the table by SRC_SYS_CD, then subpartition by the current partition key.

Thanks
Jining Received on Mon Feb 13 2006 - 14:52:44 CST