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@xxxxxxxxxxxxxxxxxx> > > 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@xxxxxxxxx] > 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 > > > > @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@xxxxxxxxxxxxxxxxxx<mailto: > jonathan@xxxxxxxxxxxxxxxxxx>> > > > 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-- > //www.freelists.org/webpage/oracle-l > > > > > > -- > Bien Respectueusement > Mohamed Houri > > -- > //www.freelists.org/webpage/oracle-l > > > -- Bien Respectueusement Mohamed Houri -- //www.freelists.org/webpage/oracle-l