RE: Statistics Problem on partitioned table
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 2 Oct 2013 11:08:19 +0000
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
Other related posts:
