RE: Cardinality Issues
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 29 Aug 2013 13:51:12 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90141FD42_at_exmbx06.thus.corp>
> 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_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Rajiv Iyer [raju.rgi_at_gmail.com] Sent: 29 August 2013 12:43
To: oracle-l_at_freelists.org
Subject: Cardinality Issues
| 3 | TABLE ACCESS FULL| BILLING | 100K| 1074K| 65 (2)| 00:00:01 |
Predicate Information (identified by operation id):
Date: Thu, 29 Aug 2013 13:51:12 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90141FD42_at_exmbx06.thus.corp>
> 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_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Rajiv Iyer [raju.rgi_at_gmail.com] Sent: 29 August 2013 12:43
To: oracle-l_at_freelists.org
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')-- http://www.freelists.org/webpage/oracle-l Received on Thu Aug 29 2013 - 15:51:12 CEST