# Re: Statistics Problem on partitioned table

Date: Wed, 2 Oct 2013 13:39:15 +0200

Message-ID: <CAJu8R6j27Ha+VcHBU5X5qj9qz2fut90AUBf=iykcmQYjjU3Ddg_at_mail.gmail.com>

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

** PER_IND CNT
**

- ----------

14820567 2

14820568 2

14823592 2

14888565 2

14332136 2

13565375 2

13617240 2

13546549 92

13546573 92

13546630 92

13546881 92

13546890 92

13546911 92

13546914 92

.

And so on until I arrived at the end (721,699)

13831389 130

13831395 130

13831404 130

13831451 130

0 6119655 ----> this is my predicate

It is when I count how many distinct CNT I have that I found 59 rows (2, 92, 130, .......6119655) . Thats what I meant by 59 rows

Best regards

2013/10/2 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>

*>*

*> Mohamed,*

*>*

*>*

*>*

*> There's still the puzzle that you now show 56 distinct values, but the*

*> stats show 721,599 distinct keys.*

*>*

*> We need to work out how this discrepancy could have appeared (it would*

*> explain your plan, of course).*

*>*

*>*

*>*

*> Regards*

*>*

*> Jonathan Lewis*

*>*

*>*

*>*

*>*

*>*

*> ________________________________*

*> From: Mohamed Houri [mohamed.houri_at_gmail.com]*

*> Sent: 02 October 2013 09:58*

*> To: Jonathan Lewis*

*> Cc: ORACLE-L*

*> Subject: Re: Statistics Problem on partitioned table*

*>*

*>*

*> Jonathan*

*>*

*> > How come there is only one distinct value of per_ind (first post), but*

*> the number of distinct keys in the index on per_ind is over 700,000 ?*

*>*

*> Again nicely spotted. Here below is the correct figure*

*>*

*> select per_ind, count(1) cnt from XXX_PER_YYY group by per_ind;*

*>*

*> 721,699 rows
*

*>*

*> I managed to put the result in a test table so that I can check how much*

*> count I have for each per_ind and so on*

*>*

*> select distinct cnt from mho_test order by cnt asc;*

*>*

*> 2*

*>*

*> 4*

*>*

*> 6*

*>*

*> 8*

*>*

*> 10*

*>*

*> 12*

*>*

*> 14*

*>*

*>
*

*>*

*> 6119655 ---> this the count for per_ind = 0 (at the moment the query was*

*> issued)*

*>*

*> 56 rows*

*>*

*>*

*> The old figure (that have prompted your question) was against a view*

*> XXX_PER_YYY_VW (which is select * from XXX_PER_YYY where per_ind = 0).*

*>*

*>*

*> There 4 columns on the XXX_PER_YYY table and they are all not null;*

*>*

*> SQL> select count(1)*

*>*

*> from*

*>*

*> (*

*>*

*> select table_name, partition_name, global_stats, last_analyzed,*

*> num_rows*

*>*

*> from all_tab_partitions*

*>*

*> where table_name='XXX_PAR_YYY*

*>*

*> )*

*>*

*> where num_rows = 0;*

*>*

*>*

*>*

*> COUNT(1)*

*>*

*> ----------*

*>*

*> 758*

*>*

*>*

*>*

*> 785 empty partitions over 1493 partitions.*

*>*

*>*

*>*

*> SQL> select table_name, global_stats, last_analyzed, num_rows*

*>*

*> 2 from all_tables*

*>*

*> 3 where table_name='XXX_PAR_YYY'*

*>*

*> 4 order by 1, 2, 4 desc nulls last;*

*>*

*>*

*>*

*> TABLE_NAME GLO LAST_ANALYZED NUM_ROWS*

*>*

*> ------------------------------ --- ----------------- ----------*

*>*

*> 'XXX_PAR_YYY' YES 20131002 03:40:33 49916324*

*>*

*>*

*>*

*>*

*>*

*> SQL> select leaf_blocks, distinct_keys, clustering_factor,*

*> num_rows,partitioned*

*>*

*> 2 from all_indexes where index_name = ' XXX_PER_IND';*

*>*

*>*

*>*

*> LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS PAR*

*>*

*> ----------- ------------- ----------------- ---------- ---*

*>*

*> 120037 721,699 205043 49916324 YES*

*>*

*>*

*>*

*> I hope that I gave you sufficient information to suggest a way to have the*

*> statistics collected so that the CBO will do good estimations and hence*

*> generate an optimal plan*

*>*

*>*

*>*

*> _at_Mark*

*>*

*> select count(*) from XXX_PER_YYY where per_ind = 0; ---> 6,110,510*

*>*

*>*

*>*

*> I made an effort to make myself clear while obfuscating the table and*

*> index information*

*>*

*> Best Regards*

*>*

*> Mohamed*

*>*

*>*

*> 2013/10/1 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:*

*> jonathan_at_jlcomp.demon.co.uk>>*

*>*

*>*

*> Mohamed,*

*>*

*> How come there is only one distinct value of per_ind (first post), but the*

*> number of distinct keys in the index on per_ind is over 700,000 ?*

*>*

*> Regards*

*> Jonathan Lewis*

*>*

*>*

*>*

*>*

*> --------------------------------*

*>*

*>*

*>*

*> select leaf_blocks, distinct_keys, clustering_factor,*

*> num_rows,partitioned*

*>*

*> from all_indexes where index_name = 'XXX_PER_IND';*

*>*

*> leaf_blocks distinct_keys clustering_factor num_rows partitioned*

*>*

*> 119369 721701 204870 49754928 YES*

*>*

*>*

*>*

*> There is only one distinct value of per_ind (per_ind =0)*

*>*

*> select per_ind, count(1) from XXX_PER_YYY group by per_ind;*

*>*

*> *per_ind cout(1)**

*>*

*>*

*> 0 6,118,184--*

*> http://www.freelists.org/webpage/oracle-l*

*>*

*>*

*>*

*>*

*>*

*> --*

*> Bien Respectueusement*

*> Mohamed Houri*

*>*

*> --*

*> http://www.freelists.org/webpage/oracle-l*

*>*

*>*

*>*

-- Bien Respectueusement Mohamed Houri -- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 02 2013 - 13:39:15 CEST