Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Problem with understanding Optimization methods.

Re: Problem with understanding Optimization methods.

From: Jonathan Lewis <>
Date: Wed, 07 Jan 2004 04:24:24 -0800
Message-ID: <>

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

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.


Jonathan Lewis

  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:

Three-day seminar:
see ____UK___November

The Co-operative Oracle Users' FAQ

> 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 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

Please see the official ORACLE-L FAQ:
Author: Jonathan Lewis

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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 Wed Jan 07 2004 - 06:24:24 CST

Original text of this message