RE: dba_tables.num_rows is less than dba_indexes.num_rows

  • From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
  • To: <Christian.Antognini@xxxxxxxxxxxx>, "'Leng Kaing'" <Leng.Kaing@xxxxxxxxxxx>
  • Date: Thu, 11 Aug 2005 11:44:45 +0200

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Christian Antognini
Sent: Thursday, August 11, 2005 10:21
To: Leng Kaing
Cc: oracle-l@xxxxxxxxxxxxx; 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
--
//www.freelists.org/webpage/oracle-l
BEGIN:VCARD
VERSION:2.1
N:de Haan;Lex
FN:Lex de Haan
ORG:Natural Join B.V.
TEL;WORK;VOICE:+31.30.2515022
TEL;HOME;VOICE:+31.30.2518795
TEL;CELL;VOICE:+31.62.2955714
TEL;WORK;FAX:+31.30.2523366
ADR;WORK:;;Pieter Breughelstraat 10;Utrecht;;3583 SK;Netherlands
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Pieter Breughelstraat 10=0D=0AUtrecht 3583 
SK=0D=0ANetherlands
URL;WORK:http://www.naturaljoin.nl
EMAIL;PREF;INTERNET:lex.de.haan@xxxxxxxxxxxxxx
REV:20040224T160439Z
END:VCARD

Other related posts: