Skip to content

script to analyze partition tables data distribution

script to analyze partition tables data distribution published on Комментариев к записи script to analyze partition tables data distribution нет
select table_owner||'.'||table_name,partition_name,100* (round (num_rows/ case when
        sum(num_rows) over( partition by table_name) >0 then sum(num_rows) over( partition by table_name)
        else 1
        end        
        ,3)) as pct_of_rows
        ,num_rows, sum(num_rows) over( partition by table_name)  total_row_cnt
        from dba_tab_partitions
    where table_name in 
    ( select table_name from DBA_PART_TABLES dpt where dpt.owner not like 'SYS%' and dpt.interval is null  and dpt.partitioning_type like '%RANGE%' ) 
order by 1,3 desc nulls last;

TABLE_NAME                               | PARTITION_NAME                 | PCT_OF_ROWS |   NUM_ROWS | TOTAL_ROW_CNT
---------------------------------------- | ------------------------------ | ----------- | ---------- | -------------
XXXX.OPN_HIS                             | P_MAX                          |         100 |   63308846 |      63309145
XXXX.OPN_HIS                             | P_2012_09                      |           0 |          2 |      63309145 
XXXX.OPN_HIS                             | P_2012_10                      |           0 |        297 |      63309145
XXXX.OPN_HIS                             | P_2012_08                      | <NULL>      | <NULL>     |      63309145

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика