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