incorrect cardinality estimates when using "having 1=1"

From: Vlado Barun <vlado_at_cadre5.com>
Date: Mon, 6 Jun 2005 12:00:45 -0400
Message-Id: <200506061600.j56G0p638971@cadre5.com>

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_at_cadre5.com

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 06 2005 - 12:05:50 CDT