Re: Statistics Problem on partitioned table

From: Mohamed Houri <mohamed.houri_at_gmail.com>
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) . That’s 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-l

Received on Wed Oct 02 2013 - 13:39:15 CEST