RE: dba_tables.num_rows is less than dba_indexes.num_rows

Lex de Haan wrote:

> I am too lazy to test it myself, but *in theory* the number
> of index entries could be used in a join situation where one
> of the participating tables is not accessed itself --
> because the index contains all necessary information -

Exactly. Works like this in practise in Oracle. E.g. when doing a SELECT COUNT on column(s) uniquely indexed, the unique index (e.g. PK) is a prime candidate to doing a full index scan (or even a parallel index scan) as the index contains an entry per row and is (usually) a significantly smaller data volume to plough through than a full table scan. (still does not beat PQ on bitmap indexes though when doing a select count).

The CBO is never stupid. Pretty weird at on occasion? Yep. But never just plain stupid. :-)

--
Billy

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This e-mail and its contents are subject to the Telkom SA Limited
e-mail legal notice available at
http://www.telkom.co.za/TelkomEMailLegalNotice.PDF
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--
http://www.freelists.org/webpage/oracle-l