Cardinality Issues

From: Rajiv Iyer <raju.rgi_at_gmail.com>
Date: Thu, 29 Aug 2013 17:13:52 +0530
Message-ID: <CADxvSwOtQTn=QbVnZDEEu=cBQv0rBma+mhsg8amDAWAuy0JA8w_at_mail.gmail.com>

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Aug 29 2013 - 13:43:52 CEST