From oracle-l-bounce@freelists.org Mon Jun 6 12:05:50 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j56H5o3U015583 for ; Mon, 6 Jun 2005 12:05:50 -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 j56H5oNi015579 for ; Mon, 6 Jun 2005 12:05:50 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 976951BBD2C; Mon, 6 Jun 2005 11:02:40 -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 13948-02; Mon, 6 Jun 2005 11:02:40 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1B0EC1BA64A; Mon, 6 Jun 2005 11:02:40 -0500 (EST) Message-Id: <200506061600.j56G0p638971@cadre5.com> From: "Vlado Barun" To: Subject: incorrect cardinality estimates when using "having 1=1" Date: Mon, 6 Jun 2005 12:00:45 -0400 MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcVqsOYaZnsBHr8jR5GUd98WM401nQ== X-archive-position: 20689 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: vlado@cadre5.com Precedence: normal Reply-To: vlado@cadre5.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.63 The cardinality estimate for a query with a "having 1=1" clause differs significantly from the same query without the "having 1=1". Following is a reproducible test case. test_db> create table t as select * from dba_objects; Table created. test_db> exec dbms_stats.gather_table_stats('DBAVLADO', 'T'); PL/SQL procedure successfully completed. test_db> explain plan for select owner, count(*) from t group by owner; Explained. test_db> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 25 | 150 | 20 (10)| | 1 | SORT GROUP BY | | 25 | 150 | 20 (10)| | 2 | TABLE ACCESS FULL | T | 7710 | 46260 | 19 (6)| ------------------------------------------------------------------------- 8 rows selected. test_db> explain plan for select owner, count(*) from t group by owner having 1=1; Explained. test_db> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 20 (10)| |* 1 | FILTER | | | | | | 2 | SORT GROUP BY | | 1 | 6 | 20 (10)| | 3 | TABLE ACCESS FULL | T | 7710 | 46260 | 19 (6)| ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(1=1) 14 rows selected. test_db> test_db> select count(distinct owner) from t; COUNT(DISTINCTOWNER) -------------------- 25 test_db> In summary, without the having 1=1 Oracle estimates 25 rows, which is correct. With the "having 1=1", Oracle estimates 1 row. The developer is using "having 1=1" to simplify adding additional constraints to the having clause, if needed. Any idea why the difference and possible ways to solve this (without removing the having 1=1)? Vlado Barun, M.Sc. Senior Data Architect, Cadre5 www.cadre5.com e-mail: vlado@cadre5.com -- http://www.freelists.org/webpage/oracle-l