From oracle-l-bounce@freelists.org Tue Aug 9 01:13:33 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j796DXKA010204 for ; Tue, 9 Aug 2005 01:13:33 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j796DTIP010185 for ; Tue, 9 Aug 2005 01:13:29 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EB4D61DCE88; Tue, 9 Aug 2005 01:13:25 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10374-02; Tue, 9 Aug 2005 01:13:25 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3D78B1DCE9C; Tue, 9 Aug 2005 01:13:25 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: dba_tables.num_rows is less than dba_indexes.num_rows Date: Tue, 9 Aug 2005 16:11:16 +1000 Message-ID: <18D551B1B928FF47A65B2D91F705906A017BAC96@HSNDON-EX01.hsntech.int> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: dba_tables.num_rows is less than dba_indexes.num_rows Thread-Index: AcWcqKms+0VOfqwTQfe6N/6pWCMWZgAACD0w From: "Leng Kaing" To: "Wolfgang Breitling" Cc: X-OriginalArrivalTime: 09 Aug 2005 06:10:56.0793 (UTC) FILETIME=[1B189890:01C59CA9] X-archive-position: 23604 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Leng.Kaing@hsntech.com Precedence: normal Reply-To: Leng.Kaing@hsntech.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.1 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 So does it matter that the indexes' num_rows are bigger than that of the table? I'm concerned that some indexes will never be used as the CBO will think it's bigger than the table or some of the other indexes. -----Original Message----- From: Wolfgang Breitling [mailto:breitliw@centrexcc.com] Sent: Tuesday, 9 August 2005 4:09 PM To: Leng Kaing Cc: oracle-l@freelists.org Subject: Re: dba_tables.num_rows is less than dba_indexes.num_rows Not strange at all. As long as you are not doing a full compute, all statistics will be estimates based on sampling. Since the samples are different for the table and each of the indexes, the estimates based on those samples will (almost certainly) differ. The smaller the sample, the greater the swing in the estimates. At 11:23 PM 8/8/2005, Leng Kaing wrote: >Hi everyone, > >I'm encountering some strange problems with the >CBO in Oracle 9.2.0.6 - it's telling me that I >have more rows in the indexes than there are rows in the tables. > >I've tried all combinations of dbms_stats and >analyse and cannot understand how the CBO comes >up with such numbers. I've even done a "delete statistics" and >Re-analysed the table and indexes but it doesn't help. > >The command I used is variations of the following: > > exec > DBMS_STATS.GATHER_TABLE_STATS(ownname=>'MBS',tabname=>'READINGTOU', - > >estimate_percent=>dbms_stats.auto_sample_size,method_opt=>'FOR >COLUMNS PROCESSSTATUS',degree=>2); > Regards Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- http://www.freelists.org/webpage/oracle-l