Cardinality Issues
- From: Rajiv Iyer <raju.rgi@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Aug 2013 17:13:52 +0530
Hi All.
I have a problem with a SQL which is not giving a good cardinality
estimate. The original SQL is pretty complex with many joins but I have
managed to replicate the issue with a simple test case. It's as follows:
---Create tables BILLING & LOCATION
create table BILLING as
with VW as
(
select rownum id from dual connect by level <= 1000
)
select
rownum BILL_ID,
'Y' ACTIVE,
case
when rownum <= 50000 then
996
else
case when MOD(rownum,50000)=0 then
990
when MOD(rownum,5000)=0 then
991
when MOD(rownum,500)=0 then
992
when MOD(rownum,50)=0 then
993
when MOD(rownum,5)=0 then
994
else
995
end
end location_id
from VW, VW
where rownum <= 100000;
create table LOCATION as
select location_id, 'X-'||rownum LOCATION_NAME
from
(
select distinct LOCATION_ID from BILLING
) order by location_id;
---gather stats
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'BILLING');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'LOCATION');
--Visualize the data
SELECT a.LOCATION_ID,b.location_name,COUNT(*) FROM BILLING A, LOCATION B
WHERE A.LOCATION_ID = B.LOCATION_ID
GROUP BY A.LOCATION_ID,B.LOCATION_NAME
ORDER BY 3;
LOCATION_ID LOCATION_NAME COUNT(*)
----------- ------------- ----------
990 X-4 1
991 X-3 9
992 X-2 90
993 X-1 900
994 X-6 9000
995 X-5 40000
996 X-7 50000
So we know that BILLING table has 9 records for location 'X-3'. But the
estimated rows in the explain plan is not in tune with this. It shows a
value of 100K
EXPLAIN PLAN FOR
select * from BILLING a, LOCATION B
where a.location_id = b.location_id and b.location_name = 'X-3';
Plan hash value: 725447293
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14286 | 265K| 70 (5)|
00:00:01 |
|* 1 | HASH JOIN | | 14286 | 265K| 70 (5)|
00:00:01 |
|* 2 | TABLE ACCESS FULL| LOCATION | 1 | 8 | 3 (0)|
00:00:01 |
| 3 | TABLE ACCESS FULL| BILLING | 100K| 1074K| 65 (2)|
00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."LOCATION_ID"="B"."LOCATION_ID")
2 - filter("B"."LOCATION_NAME"='X-3')
This is a problem when there are many joins in the query and we don't get a
good plan.
I have considered adding hints to get us the desired plan. But I would like
to know if there are any alternatives.
Thanks,
Rajiv
--
//www.freelists.org/webpage/oracle-l
Other related posts:
