RE: Cardinality Issues
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Aug 2013 13:51:12 +0000
> 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
But you only know that AFTER you've worked out the answer.
When you ask the question you know only that the billing.location_ID has seven
distinct values and you're going to count the rows for one of them; but you
have to go to the location table to find out which one.
This is a standard problem for the optimizer - it has to work out part of the
result set before it can work out how big the whole result set is.
I don't think it's not documented, so you'll have to chase it up with Oracle
Support but there is a "precompute_subquery" that you could use if you rewrite
your query to use an IN subquery: https/forums.oracle.com/thread/2524169
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Rajiv Iyer [raju.rgi@xxxxxxxxx]
Sent: 29 August 2013 12:43
To: oracle-l@xxxxxxxxxxxxx
Subject: Cardinality Issues
Hi
All.-------------------------------------------------------------------------------
| 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')--
//www.freelists.org/webpage/oracle-l
Other related posts:
