RE: dba_tables.num_rows is less than dba_indexes.num

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Thu, 11 Aug 2005 11:44:45 +0200
Message-Id: <20050811094449.83C301DDDB8@turing.freelists.org>

Hi Christian,

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 -- to estimate the join cardinality. another *theoretic* possibility would be an index join operation: single table query, two separate indexes covering all select clause expressions.

But still, I agree this index cardinality statistic is not of much value, and typically redundant.
Setting up a test would be fun, though...

kind regards,

Lex.

Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christian Antognini
Sent: Thursday, August 11, 2005 10:21
To: Leng Kaing
Cc: oracle-l_at_freelists.org; Wolfgang Breitling Subject: RE: dba_tables.num_rows is less than dba_indexes.num_rows

Leng

>Still don't understand part 1 :-( Are you saying that num_rows is
>important on a table, but not on an index?

num_rows on a table is definitively used by the CBO to estimate costs.

Neither Wolfgang nor I know a situation where the num_rows of an index is used by the CBO. If you, or somebody else, know such a situation, please, share it! (Of course we would like to see a reproducible test case...)

>If I manually updated the table's num_rows to be bigger than then the
>indexes' num_rows, and saw that the CBO is now favouring an index look
>up rather a full table scan, surely it would prove that num_rows is
>important for both tables and indexes?

No. It only proves that table's num_rows is important.

>So short of doing a compute, how do we give the CBO good stats to work
>with?

As I wrote it depends... and if you need histograms in some situation even a compute is not enough!

HTH,
Chris

-- http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 11 2005 - 04:46:58 CDT