Re: 10g CBO and how to determine cardinality on INDEX_FFS?

"peter" <p_msantos_at_yahoo.com> wrote in message news:1132253792.850268.258580_at_g44g2000cwa.googlegroups.com...
> dear all,
> I'm trying to determine how the 10g optimizer calculates the
> cardinality and/or cost
> for an index fast full scan. I do have Jonathan Lewis's latest
> book, but I haven't
> had a chance to really dig into it, and to make matters worse his
> test are so controlled and
> as he mentioned little things can change how the optimizer
> behaves...so I thought I'd get some
> help here.
>

The cardinality is determined by applying the standard rules to the columns referenced, although there may be a sanity check that uses the distinct_keys count from the index instead of the product of the column cardinalities in some circumstances. (That's just a recent thought, isn't something I've checked, and may be version dependent).

The cost is calculated in the same way as a tablescan cost, but using the LEAF_BLOCKS statistic for the index where you would otherwise use the BLOCKS for a table.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005