Re: Statistics Problem on partitioned table
From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Wed, 2 Oct 2013 14:17:12 +0200
Message-ID: <CAJu8R6g-PMKqfo5Lf_O39GycpTrymK7C2oVvQ9BpR6rRS5Ph6Q_at_mail.gmail.com>
Mark
I haven't posted the execution plan you've requested because I did it in my first e-mail
from XXX_PER_YYY
where per_ind = 0;
Predicate Information (identified by operation id):
> Now it does leave out the table access for the non-index columns, but it
> should tell us a great deal about what the optimizer thinks is the cheapest
> way to get the relevant rowids from all the partitions and the plan should
> show what the CBO has estimated along with the actuals.
> Fragmentary revelation of the underlying facts (obscuring, for example,
> that
> a view was involved) does not make it easier for folks to help you explain
> what you're asking about.
> Please try to make it easier.
> mwf
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Mohamed Houri
> Sent: Wednesday, October 02, 2013 7:39 AM
> To: Jonathan Lewis
> Cc: ORACLE-L
> Subject: Re: Statistics Problem on partitioned table
> Jonathan,
> I am sorry I may have not been clear but there are in fact 721,699 and that
> is what I showed above
> *select per_ind, count(1) cnt from XXX_PER_YYY group by per_ind;*
> 721,699 rows .
> <snip>
Date: Wed, 2 Oct 2013 14:17:12 +0200
Message-ID: <CAJu8R6g-PMKqfo5Lf_O39GycpTrymK7C2oVvQ9BpR6rRS5Ph6Q_at_mail.gmail.com>
Mark
I haven't posted the execution plan you've requested because I did it in my first e-mail
select
a
,b ,c ,per_ind
from XXX_PER_YYY
where per_ind = 0;
| Id | Operation | Name | Starts | E-Rows |A-Rows |
| 0 | SELECT STATEMENT | | 1 | |550K | | 1 | PARTITION RANGE ALL | | 1 | 69 |550K | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| XXX_PER_YYY | 111 | 69 |550K | |* 3 | INDEX RANGE SCAN | XXX_PER_IND | 111 | 69|550K |
Predicate Information (identified by operation id):
3 - access("PER_IND"=0)
It is not exactly what you've requested but was this not sufficient?
Best regards
Mohamed
2013/10/2 Mark W. Farnham <mwf_at_rsiz.com>
> I fail to understand why the --+ gather_plan_statistics execution of the
> simple count I requested is not forthcoming.
>
> Now it does leave out the table access for the non-index columns, but it
> should tell us a great deal about what the optimizer thinks is the cheapest
> way to get the relevant rowids from all the partitions and the plan should
> show what the CBO has estimated along with the actuals.
>
> Fragmentary revelation of the underlying facts (obscuring, for example,
> that
> a view was involved) does not make it easier for folks to help you explain
> what you're asking about.
>
> Please try to make it easier.
>
> mwf
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Mohamed Houri
> Sent: Wednesday, October 02, 2013 7:39 AM
> To: Jonathan Lewis
> Cc: ORACLE-L
> Subject: Re: Statistics Problem on partitioned table
>
> Jonathan,
> I am sorry I may have not been clear but there are in fact 721,699 and that
> is what I showed above
>
> *select per_ind, count(1) cnt from XXX_PER_YYY group by per_ind;*
>
> 721,699 rows .
> <snip>
> > >
-- Bien Respectueusement Mohamed Houri -- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 02 2013 - 14:17:12 CEST