Re: Statistics Problem on partitioned table

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Wed, 2 Oct 2013 10:58:34 +0200
Message-ID: <CAJu8R6jmaeC=Cp9A3SkVSUg7KzfJNDE0udwoHMkpkAHh66VMpQ_at_mail.gmail.com>

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>

>
>
> 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 - 10:58:34 CEST