Apologies to the list.
The previous reply may have been informative,
but it was irrelevant. I failed to notice that the
indexed access path was followed up by a
table access by rowid. (This does explain why
the index_FFS path wasn't used, of course).
In this case, the cost of the indexed path would be
blevel + leaf_blocks + clustering_factor
(in 8.1.7.4).
Given that the clustering_factor for btree indexes
falls between the number of blocks in the table
and the number of rows in the table, you could
make the access path go either way by hacking
the clustering factor between two extremes.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
To: <[EMAIL PROTECTED]>
Sent: Wednesday, January 07, 2004 10:30 AM
>
> Check the costs of the two queries (autotrace will be
> sufficient).
>
> In this example, rule based uses the index because
> it exists and will return the right answer.
>
> Cost based works out that the scan and sort is
> cheaper.
>
> The cost of an index full scan is approximately
> blevel + leaf_blocks (columns from user_indexes).
>
> The cost of the tablescan is
> blocks / 'adjusted db_file_multiblock_read_count'
> If your dbfmbrc is 8, use 6.59
> If it's 16 use 10
> If it's 32 use 16.4
>
> The cost of the sort (which seems to be wrong
> in 8.1.7.4) is likely to be about the same as the
> cost of the tablescan.
>
>
> So, as an example, pretend your dbfmbrc is 16,
> then if the index is larger than 1/5th of the size
> of the table, the scan and sort will work out
> cheaper than the index full scan.
>
>
> I am a little surprised, though, that you don't
> have a path that is "index FAST full scan".
> This suggests that your index is actually
> bigger than your table. Maybe it's got
> lots of holes in it.
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
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).
