Re: Problem with understanding Optimization methods.

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: <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: 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).