Re: reducing LIO's

I was watching with amazement how people jumped onto index suggestions without as so much as question the data distribution. Maybe ORDT_STS='K' is so unselective, i.e. most of the orws have a value of 'K' for ORDT_STS, that the optimizer is correct in not using an index - assuming there is one. Likewise for ORDM_STS IN ('C','G','L'). Or, maybe those latter are the exception but without a histogram the optimizer wouldn't know it.
To answer the OP's question - NO, indexes do not necessarily reduce LIO, possibly on the contrary. Also, what is the goal? Reducing LIO or improving performance. Those two are not necessarily the same.

My advice: Run the sql with sql_trace on and compare the rowcounts with the optimizer's cardinality estimates for the corresponding steps. Look for vast discrepancies (orders of magnitude, multiples of hundreds or thousands). Then look for reasons why the optimizer miscalculated its estimate. If the estimates are fairly accurate then I am convinced that the optimizer does the right thing, given the material to work with. If that is not good enough you have to look at measures like MV.

The Human Fly wrote:
> Well, I have done it. I have created combination index on those two
> colums and when my query was not using the index, I have even forced
> it to use, it reduces the cost but buffer_get were very high. My
> question was, LIO can be reduced by creating indexs, if so, why my
> buffer_gets were higher with INDEX hint?
> Well, we can't avoid this query as it is mandatory query for every
> trading transaction.
>
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l