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 <jonathan_at_jlcomp.demon.co.uk>
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
> <ORACLE-L_at_fatcity.com>
> 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: jonathan_at_jlcomp.demon.co.uk
>
> 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: 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).
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: jack_silvey_at_yahoo.com
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: 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).
Received on Thu May 09 2002 - 22:43:19 CDT
