Re: reducing LIO's

comments in-line
On Mar 31, 2005 3:56 PM, The Human Fly <sjaffarhussain_at_gmail.com> 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.

curious, if the cost using the index were really lower than the cost for the plan without the index, then the CBO will pick that execution plan. If however the CBO didn't use an index that I thought it should then my first reaction would not be, lets see if we can get it to use the index, but who is missing some information here me or the optimizer (usually it turns out to be me). in your case however it may be the cbo. The cbo reckons your query will return 2000 rows, but in fact it is only returning 54. (so it is wrong by a factor of 37) - investigating why this is so will - as wolfgang has suggested - likely be helpful.

> My
> question was, LIO can be reduced by creating indexs, if so, why my
> buffer_gets were higher with INDEX hint?

lio might be reduced or might be increased by using indexes

> Well, we can't avoid this query as it is mandatory query for every
> trading transaction.

You might still be able to avoid the sort if the order by is not mandatory.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l