Re: Index Usage ?!

Hi Tanel,

thanks a lot for your wonderful explanation. sorry to pester u you again with my doubts on the first day of the week.

My only doubt is:

	1.After analyzing the index,CBO doesn't read the index because
	it is costlier in terms of I/O and other resources.

	2.But without analyzing,CBO uses the index now,which is supposed 
	to be coslty,and hence incurs the same I/O and other resources .

	3.when the role of CBO is over,the h/w resources comes into play 
	to read the index.even now it reads the full index and the 
	response is faster.how is it so ?	

hope i am clear in telling u what i had understood ?! plz. excuse me if i'm sound silly and stupid.

of the above 3 points,where am i wrong ?

Regards,
Jp.

25-7-2003 21:24:45, "Tanel Poder" <tanel.poder.003_at_mail.ee> wrote:

>Hi!
>
>Your original post shows that when index was not analyzed, optimizer
used
>default statistics and found out that index scan is quite cheap.
Also, no
>sorting had to be done, since descending index range scan could be
used to
>satisfy your order by clause.
>
>Whe index was analyzed, then CBO actually saw, that index isn't that
good as
>defaul values showed, the number of leaf blocks was higher and also
the
>clustering factor was probably much-mugh higher, meaning that for any
>non-unique index key value it has to visit several different data
blocks to
>get all matching rows. That means lot's of IOs. Eventually CBO
decided that
>it's cheaper to do let say 1600 multiblock (plus one segment header +
>possibly bitmap) reads directly and scan through the whole table than
to
>traverse through index branches, scan leaf blocks and visit every
data block
>individually.
>
>So, with not-analyzed index, CBO had nothing else to do, than to be
>super-optimistic about the index (#LB, CLUF) thus using index in your
case.
>But when analyzed, CBO had accurate data, but did make bad decision
because
>too pessimistic values for optimizer_index_cost_adj and
>optimizer_index_caching. You should set them at session level for
testing to
>let say 50 and 90, but read the document below for understanding
those
>parameters.
>
>http://www.evdbt.com/SearchIntelligenceCBO.doc
>
>Cheers,
>Tanel.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Prem Khanna J
  INET: jprem_at_kssnet.co.jp

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).