Re: Index Usage ?!

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.

• Original Message ----- From: "Prem Khanna J" <jprem_at_kssnet.co.jp> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, July 25, 2003 2:54 PM Subject: Re: Index Usage ?!

> Tanel,
>
> <quote>Are your other indexes analyzed?</quote>
> Yes.
>
> Case 1:
> If IDX_PROFILE_SHINKI is analyzed,CBO decides that index scan is costlier.
>
> Case 2:
> Now IDX_PROFILE_SHINKI is not analyzed (which works fine as of now),
> CBO takes default values and decides that index scan is cheaper.
>
> As per case 1,the I/O to read the index will be costlier.
> So CBO decides not to use the index.
>
> But as per case 2 (existing situation):
> After the decision is made by CBO,
> it still needs to read the index and then fetch table data.
> So the same I/O to read the index is going to happen as in case 1.
> ***** correct me if i'm wrong *****
>
> Aren't they contradictory ?!
>
> So how does case 2 benefit in terms of CPU or memory consumption ?
> and there is a big diff. in response time between both cases...how ?
>
> ...totally confused :(
>
> Regards,
> Jp.
>
> 25-07-2003 18:34:23, "Tanel Poder" <tanel.poder.003_at_mail.ee> wrote:
> >Hi!
> >> but there are seperate indexes on column PREF, FLAG and SEX.
> >> when those indexes are used ,cost=999.
> >> whereas when IDX_PROFILE_SHINKI ( a composite index on ENTPC,
> >> FLAG,SEX,PREF,ENTDAY) is used , cost=7.
> >>
> >> 1.whys there is a huge difference in COST ?
> >
> >As Wolfgang said, since your IDX_PROFILE_SHINKI index is unanalyzed, CBO
is
> >using default values, which look quite sexy costwise. Are your other
indexes
> >analyzed?
> >
> >> 2.does it mean that a composite index is better than individual ones ?
> >
> >Always depends. But I use them a lot. Optimizerwise - they might be
bigger
> >in sense of bytes & blocks, but again if they contain all columns
required
> >in qurey, that no table access is required, they can speed up lookups &
> >short range scans significantly.
> >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
Received on Fri Jul 25 2003 - 06:27:02 CDT