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

Other related posts: