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 ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, July 25, 2003 2:54 PM > 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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
