I think the interesting thing is, why did it look at an index not in select/where/sort operations at all?Best idea I have heard so far is that somehow it thinks that gathering the rowids from the index and doing a table lookup is better than a fts. LElking floated the idea that it might have to do with a high HWM and a low number of of rows, perhaps combined with an index on a not-null col. Even Oracle was reluctant to admit that it was accessing a wacko index until I pressed them for it. I asked them to give me the relevant CBO code, but noooooo, not even a decent 10053 doc.....
--- Jonathan Lewis <[EMAIL PROTECTED]> wrote: > > I believe that Waleed's response is essentially > correct. > The numbers still don't make sense, but ... > > a) 250,000 blocks with the mbrc = 64. The t/s cost > (according to my observations on 9.0.1 and > 8.1.7) should be based on an adjusted mbrc > of 15.5 > > 250,000 / 15.5 = 16,000 > > b) Using an index to hit every block, Oracle is > able to determine from data clustering stats > that many consecutive index leaf values will > hit a single data block, therefore can estimate > the single read-count via index as 250,000 - > but you have told it to reduce this by a factor > of 1/100 - for a total of 2,500. > > Unfortunately for the theory, both your costs > are 4,924 - so the T/S cost is close to the > traditional "table blocks / simple mbrc", and > index cost is too high be a factor of 2, which > may be due to some internal bitmap index > clustering fudge factor - some of the critical > bitmap index numbers apparently used to be > hard-coded constants. Possibly it just means > that your bitmap column has two values (and > of course I haven't allowed a count for the > number of leaf blocks in the index !). > > > Details notwithstanding - I suspect that > calculations > like the above are the reason why Oracle decided > that a full indexed path was cheaper than a scan. > > > > > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Author of: > Practical Oracle 8i: Building Efficient Databases > > Next Seminar - Australia - July/August > http://www.jlcomp.demon.co.uk/seminar.html > > Host to The Co-Operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > -----Original Message----- > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > Date: 09 May 2002 22:16 > choice > > > |Hi Johnathan, > | > |here is the skinny: > | > |db_file_multiblock_read_count = 64 > |number of rows=15m > |blocks=251071 > |empty_blocks=0 > |db_block_size=16384 > | > |total plan cost=4924 > |tablescan cost = 4924 > | > |Jack > | > | > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jonathan Lewis > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > 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). __________________________________________________ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
