incorrect cardinality estimates when using "having 1=1"
- From: "Vlado Barun" <vlado@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 6 Jun 2005 12:00:45 -0400
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@xxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » incorrect cardinality estimates when using "having 1=1"
