From oracle-l-bounce@freelists.org Thu Aug 11 02:44:53 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j7B7irS7007254 for ; Thu, 11 Aug 2005 02:44:53 -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 j7B7ibIP007203 for ; Thu, 11 Aug 2005 02:44:38 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 320D41DDE8E; Thu, 11 Aug 2005 02:44:30 -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 06897-09; Thu, 11 Aug 2005 02:44:30 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9F7651DDE71; Thu, 11 Aug 2005 02:44:29 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C59E48.10917311" Subject: RE: dba_tables.num_rows is less than dba_indexes.num_rows Date: Thu, 11 Aug 2005 17:41:20 +1000 Message-ID: <18D551B1B928FF47A65B2D91F705906A017BACCB@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: AcWc6I+90E13LPUvTsqDtO6TkdvrdwBTLwGAAAOxd5AAAMmkAA== From: "Leng Kaing" To: "Christian Antognini" Cc: , "Wolfgang Breitling" X-OriginalArrivalTime: 11 Aug 2005 07:40:59.0274 (UTC) FILETIME=[040D46A0:01C59E48] X-archive-position: 23738 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.2 required=5.0 tests=AWL,BAYES_00,HTML_60_70, HTML_FONTCOLOR_UNKNOWN,HTML_FONT_BIG,HTML_MESSAGE autolearn=no version=2.63 ------_=_NextPart_001_01C59E48.10917311 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Thanks very much for the clarifications, Chris, esp. on part 2 and 3. =20 Still don't understand part 1 :-( Are you saying that num_rows is important on a table, but not on an index?=20 =20 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?=20 =20 So short of doing a compute, how do we give the CBO good stats to work with?=20 =20 =20 ---------------------------------------------- Leng Kaing Hansen Technologies 2 Frederick St; Doncaster VIC 3108 =20 Tel: +61-3-9840-3832 Fax: +61-3-9840-3102 =20 =20 ________________________________ From: Christian Antognini [mailto:Christian.Antognini@trivadis.com]=20 Sent: Thursday, 11 August 2005 5:31 PM To: Leng Kaing Cc: oracle-l@freelists.org; Wolfgang Breitling Subject: RE: dba_tables.num_rows is less than dba_indexes.num_rows =20 Hi Leng =20 >1) I always thought that NUM_ROWS is important. I know that a few weeks >ago when I noticed the differences in the num_rows in tables vs indexes, >we manually increased the table's num_rows and the query would revert to >using an index rather than a full table scan. I have yet to do a test to >see if what would happen if I set NUM_ROWS to a small number, eg. 5. =20 As you wrote you modified num_rows for the table, not for the indexes... Wolfgang wrote about the num_rows of the indexes. =20 >2) I also did "..., estimate_percent=3D>dbms_stats.auto_sample_size, >method_opt=3D>'FOR >ALL INDEXED COLUMNS', degree=3D>2, cascade=3D>true" > >Are you saying that FOR ALL INDEX COLUMNS or FOR COLUMNS only gathers >histograms for the tables, but not the stats? So do I have to do it in 2 >steps - gather stats, then gather histograms? =20 With 'FOR ALL INDEXED COLUMNS' you will have: - table statistics - column statistics and histograms only for the indexed columns - no column statistics on non-indexed columns! =20 Usually gathering statistics only for indexed columns is bad. In fact there are plenty of predicates on not-indexed columns (at least in almost all applications that I have seen so far...). =20 >3) I've never computed on any of the systems that I've worked on. And it >certainly cannot be done on the current database as it would take too >long. I always thought an estimate would be good enough. Now I'm hearing >that only a compute will do. ARGH!! We cannot afford to do this. =20 You don't need to compute in all situations. And, as you wrote, it is not always possible. Of course with estimations you could miss some information, that's all. If it does happen you have two possibilities: 1) compute 2) setting histograms manually. =20 Personally I start with small estimates and only if the statistics are not good I increase the estimate percent. Notice that "not good" means "doesn't correctly describe the data". =20 =20 Regards, Chris ------_=_NextPart_001_01C59E48.10917311 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Thanks very much for the = clarifications, Chris, esp. on part 2 and 3.

 

Still don’t understand part 1 = L Are you saying = that num_rows is important on a table, but not on an index? =

 

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? =

 

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

 

 

------------------------------------= ----------

Leng = Kaing

Hansen = Technologies

2 Frederick = St; Doncaster VIC = 3108

 

=

Tel: = +61-3-9840-3832

Fax: = +61-3-9840-3102

 

=

 

=

From: Christian Antognini [mailto:Christian.Antognini@trivadis.com]
Sent: Thursday, 11 August = 2005 5:31 PM
To: Leng Kaing
Cc: = oracle-l@freelists.org; Wolfgang Breitling
Subject: RE: = dba_tables.num_rows is less than dba_indexes.num_rows

 

Hi Leng

 

>1) I always thought that NUM_ROWS is = important. I know that a few weeks
>ago when I noticed the differences in the num_rows in tables vs = indexes,
>we manually increased the table's num_rows and the query would = revert to
>using an index rather than a full table scan. I have yet to do a = test to
>see if what would happen if I set NUM_ROWS to a small number, eg. = 5.

 

As you wrote you modified num_rows for the = table, not for the indexes... Wolfgang wrote about the num_rows of the = indexes.

 

>2)  I also did "..., estimate_percent=3D>dbms_stats.auto_sample_size,
>method_opt=3D>'FOR
>ALL INDEXED COLUMNS', degree=3D>2, cascade=3D>true"
>
>Are you saying that FOR ALL INDEX COLUMNS or FOR COLUMNS only = gathers
>histograms for the tables, but not the stats? So do I have to do it = in 2
>steps - gather stats, then gather = histograms?

 

With 'FOR ALL = INDEXED COLUMNS' you will have:

- table = statistics

- column statistics = and histograms only for the indexed columns

- no column = statistics on non-indexed columns!

 

Usually gathering = statistics only for indexed columns is bad. In fact there are plenty of predicates = on not-indexed columns (at least in almost all applications that I have = seen so far...).

 

>3) I've never computed on any of the = systems that I've worked on. And it
>certainly cannot be done on the current database as it would take = too
>long. I always thought an estimate would be good enough. Now I'm = hearing
>that only a compute will do. ARGH!! We cannot afford to do = this.

 

You don't need to = compute in all situations. And, as you wrote, it is not always possible. Of course = with estimations you could miss some information, that's all. If it does = happen you have two possibilities: 1) compute 2) setting histograms = manually.

 

Personally I start = with small estimates and only if the statistics are not good I increase the = estimate percent. Notice that "not good" means "doesn't correctly describe the data".

 

 

Regards,

Chris

------_=_NextPart_001_01C59E48.10917311-- -- http://www.freelists.org/webpage/oracle-l