# RE: Statistics Problem on partitioned table

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 2 Oct 2013 11:08:19 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DBA0BD_at_exmbx05.thus.corp>

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
```
Received on Wed Oct 02 2013 - 13:08:19 CEST

Original text of this message