Re: Cardinality Issues
Date: Thu, 29 Aug 2013 19:40:49 +0530
Message-ID: <CADxvSwORk16hk-G6OGf+RoZrLF2XA2VNVjrQrimadmgyjxX6uA_at_mail.gmail.com>
Thanks Jonathan.
It sure looks like a promising workaround. I was able to get a much better cardinality using /*+ precompute_subquery */ and histogram on location_id column in BILLING table. I will try and implement this in my original report query.
EXPLAIN PLAN FOR
select * from billing where location_id in (select /*+ precompute_subquery
*/ location_id from location where location_name = 'X-3');
Plan hash value: 2877307530
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 34 | 374 | 66 (4)| 00:00:01 | |* 1 | TABLE ACCESS FULL| BILLING | 34 | 374 | 66 (4)| 00:00:01 | -----------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter("LOCATION_ID"=991)
Thanks All.
Rajiv
On Thu, Aug 29, 2013 at 7:21 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:
> > 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 |
>> 00:00:01 |
> -------------------------------------------------------------------------------
> | 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)|
> | 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
> > >
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 29 2013 - 16:10:49 CEST