Oracle Global,Partition and Sub-partition Statistics

Here is an example of how to collect partitioned table stats. Specifically for Non-LIST sub-partition tables, whose sub-partition stats will not be collected by default. here is the rule :

        When the AUTO option is specified for “granularity”, Oracle collects global, partition-level, and sub-partition level statistics if sub-partition method is LIST. For other partitioned tables, only the global and partition level statistics are generated. 

1)Create a sample range type sub-partition table.

CREATE TABLE STUDENTS
(
ENROLLMENT_DATE DATE NOT NULL,
DEPARTMENT_ID NUMBER(2) NOT NULL,
STUDENT_ID NUMBER(6)
)
PARTITION BY RANGE (ENROLLMENT_DATE)
SUBPARTITION BY RANGE (DEPARTMENT_ID)
SUBPARTITION TEMPLATE
(SUBPARTITION SP_1 VALUES LESS THAN (10),
SUBPARTITION SP_2 VALUES LESS THAN (20),
SUBPARTITION SP_MAX VALUES LESS THAN (MAXVALUE)
)
(
PARTITION P_2016 VALUES LESS THAN (TO_DATE('2017-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION P_2017 VALUES LESS THAN (TO_DATE('2018-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
);
Table created.

insert into students values (TO_DATE('2016-07-01 10:20:08', 'YYYY-MM-DD HH24:MI:SS'), 8,1);
insert into students values (TO_DATE('2016-07-01 10:20:08', 'YYYY-MM-DD HH24:MI:SS'), 12,2);
insert into students values (TO_DATE('2016-07-01 10:20:08', 'YYYY-MM-DD HH24:MI:SS'), 25,3);

insert into students values (TO_DATE('2017-02-09 18:34:43', 'YYYY-MM-DD HH24:MI:SS'), 7,4);
insert into students values (TO_DATE('2017-02-09 18:34:43', 'YYYY-MM-DD HH24:MI:SS'), 13,5);
insert into students values (TO_DATE('2017-02-09 18:34:43', 'YYYY-MM-DD HH24:MI:SS'), 23,6);

insert into students values (TO_DATE('2018-08-03 02:30:45', 'YYYY-MM-DD HH24:MI:SS'), 3,7);
insert into students values (TO_DATE('2018-08-03 02:30:45', 'YYYY-MM-DD HH24:MI:SS'), 19,8);
insert into students values (TO_DATE('2018-08-03 02:30:45', 'YYYY-MM-DD HH24:MI:SS'), 28,9);

2) Check table, partition and sub-partition without stats:

SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED 
from user_tab_partitions where table_name='STUDENTS' ;

TABLE_NAME PARTITION_NAME     NUM_ROWS   LAST_ANALYZED
---------- -----------------  ---------- -------------
STUDENTS   P_2016
STUDENTS   P_2017
STUDENTS   P_MAX

SQL> select TABLE_NAME,NUM_ROWS,LAST_ANALYZED 
     from user_tables 
    where table_name='STUDENTS';

TABLE_NAME  NUM_ROWS   LAST_ANALYZED
----------- ---------- ---------------
STUDENTS

SQL> select TABLE_NAME,PARTITION_NAME,subpartition_name,NUM_ROWS,
            LAST_ANALYZED 
      from user_tab_subpartitions
     where table_name='STUDENTS' 
     order by PARTITION_NAME,LAST_ANALYZED;

TABLE_NAME PARTITIO SUBPARTITION_NAME NUM_ROWS LAST_ANAL
---------- -------- ----------------- -------- ----------
STUDENTS   P_2016   P_2016_SP_1
STUDENTS   P_2016   P_2016_SP_2
STUDENTS   P_2016   P_2016_SP_MAX
STUDENTS   P_2017   P_2017_SP_1
STUDENTS   P_2017   P_2017_SP_2
STUDENTS   P_2017   P_2017_SP_MAX
STUDENTS   P_MAX    P_MAX_SP_1
STUDENTS   P_MAX    P_MAX_SP_2
STUDENTS   P_MAX    P_MAX_SP_MAX

9 rows selected.

3)Sub-partition stats are NOT collected by default.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'TETSUSER',
                                        TabName =>'STUDENTS');

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,NUM_ROWS,LAST_ANALYZED
      from user_tables 
     where table_name='STUDENTS'; 

TABLE_NAME   NUM_ROWS   LAST_ANAL
------------ ---------- ---------
STUDENTS     9          04-JAN-19

SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED
from user_tab_partitions where table_name='STUDENTS' ;

TABLE_NAME PARTITION_NAME NUM_ROWS   LAST_ANAL
---------- -------------- ---------- ---------
STUDENTS   P_2016         3          04-JAN-19
STUDENTS   P_2017         3          04-JAN-19
STUDENTS   P_MAX          3          04-JAN-19

SQL> select TABLE_NAME,PARTITION_NAME,subpartition_name,
            NUM_ROWS,LAST_ANALYZED
      from user_tab_subpartitions
     where table_name='STUDENTS'
  order by PARTITION_NAME,LAST_ANALYZED;

TABLE_NAME    PARTITION_NA SUBPARTITION_NAME NUM_ROWS   LAST_ANAL
------------- ------------ ----------------- ---------- ---------
STUDENTS      P_2016       P_2016_SP_1
STUDENTS      P_2016       P_2016_SP_2
STUDENTS      P_2016       P_2016_SP_MAX
STUDENTS      P_2017       P_2017_SP_1
STUDENTS      P_2017       P_2017_SP_2
STUDENTS      P_2017       P_2017_SP_MAX
STUDENTS      P_MAX        P_MAX_SP_1
STUDENTS      P_MAX        P_MAX_SP_2
STUDENTS      P_MAX        P_MAX_SP_MAX

9 rows selected.

4) With PartName option, all Global and Partition stats are collected, but not for Sub-partitions.

SQL> exec dbms_stats.delete_table_stats(ownname => 'TETSUSER',
                                        tabname => 'STUDENTS');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'TETSUSER',
                        TabName =>'STUDENTS',PartName=>'P_2016');

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,NUM_ROWS,LAST_ANALYZED
     from user_tables where table_name='STUDENTS'; 

TABLE_NAME    NUM_ROWS    LAST_ANAL
------------- ---------- ---------
STUDENTS      9           07-JAN-19

SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED
     from user_tab_partitions where table_name='STUDENTS'  ;

TABLE_NAME    PARTITION_NA NUM_ROWS   LAST_ANAL
------------- ------------ ---------- ---------
STUDENTS      P_2016       3          07-JAN-19
STUDENTS      P_2017       3          07-JAN-19
STUDENTS      P_MAX        3          07-JAN-19

SQL> select TABLE_NAME,PARTITION_NAME,subpartition_name,
            NUM_ROWS, LAST_ANALYZED
      from user_tab_subpartitions
     where table_name='STUDENTS' 
  order by PARTITION_NAME,LAST_ANALYZED;

TABLE_NAME    PARTITION_NA SUBPARTITION_NAME NUM_ROWS  LAST_ANAL
------------- ------------ ------------------ -------- ---------
STUDENTS      P_2016       P_2016_SP_1
STUDENTS      P_2016       P_2016_SP_2
STUDENTS      P_2016       P_2016_SP_MAX
STUDENTS      P_2017       P_2017_SP_1
STUDENTS      P_2017       P_2017_SP_2
STUDENTS      P_2017       P_2017_SP_MAX
STUDENTS      P_MAX        P_MAX_SP_1
STUDENTS      P_MAX        P_MAX_SP_2
STUDENTS      P_MAX        P_MAX_SP_MAX

9 rows selected.

5) With Granularity=>’ALL’,  all Global, Partition and Sun-partition stats are collected.

SQL> exec dbms_stats.delete_table_stats(ownname => 'TESTUSER',
tabname => 'STUDENTS');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'TESTUSER',
TabName =>'STUDENTS', Granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,NUM_ROWS,LAST_ANALYZED
from user_tables where table_name='STUDENTS'; 

TABLE_NAME    NUM_ROWS   LAST_ANAL
------------- ---------- ---------
STUDENTS      9          07-JAN-19

SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED
from user_tab_partitions where table_name='STUDENTS' ;

TABLE_NAME    PARTITION_NA NUM_ROWS   LAST_ANAL
------------- ------------ ---------- ---------
STUDENTS      P_2016       3          07-JAN-19
STUDENTS      P_2017       3          07-JAN-19
STUDENTS      P_MAX        3          07-JAN-19

SQL>select TABLE_NAME,PARTITION_NAME,subpartition_name,
           NUM_ROWS,LAST_ANALYZED
     from user_tab_subpartitions
    where table_name='STUDENTS' 
 order by PARTITION_NAME,LAST_ANALYZED;

TABLE_NAME    PARTITION_NA SUBPARTITION_NAME NUM_ROWS  LAST_ANAL
------------- ------------ ----------------- --------- ---------
STUDENTS      P_2016       P_2016_SP_1        1        07-JAN-19
STUDENTS      P_2016       P_2016_SP_2        1        07-JAN-19
STUDENTS      P_2016       P_2016_SP_MAX      1        07-JAN-19
STUDENTS      P_2017       P_2017_SP_1        1        07-JAN-19
STUDENTS      P_2017       P_2017_SP_2        1        07-JAN-19
STUDENTS      P_2017       P_2017_SP_MAX      1        07-JAN-19
STUDENTS      P_MAX        P_MAX_SP_1         1        07-JAN-19
STUDENTS      P_MAX        P_MAX_SP_2         1        07-JAN-19
STUDENTS      P_MAX        P_MAX_SP_MAX       1        07-JAN-19

9 rows selected.

How to Find Interval Partition Name by Date Column Value

Since  HIGH_VALUE  in DBA_TAB_PARTITION view is LONG type, so it is difficult to get the system generated interval partition name straight away. The following will lead you how to get it easily.

SQL> desc dba_tab_partitions
Name              Null?    Type
----------------- -------- ------------
TABLE_OWNER                VARCHAR2(30)
TABLE_NAME                 VARCHAR2(30)
COMPOSITE                  VARCHAR2(3)
PARTITION_NAME             VARCHAR2(30)
SUBPARTITION_COUNT         NUMBER
HIGH_VALUE                 LONG
...
.

Subscribe to get access

Read more of this content when you subscribe today.

How to Check and Modify Default Attributes of Tables and Indexes Partitions

Sometimes the default attributes require to be modified for future partitions, or sub-partitions, specially in the situation when tables and indexes are refreshed from different environment.

Let’s check how to modify default attributes of tables, indexes, table partitions and index partitions ,etc.

Subscribe to get access

Read more of this content when you subscribe today.

Dropping an Empty Partition Without “Update Indexes” Necessary

“Update Indexes” option can be omitted when dropping an empty partition.

As we know  the ‘update indexes’ clause is used  to drop the local indexes and update the global index  when we drop a partition. So the indexes will still be VALID”

The following example shows when we drop an empty partition,  the ” Update Indexes” clause is not required.

1) Create partition table
SQL>CREATE TABLE TEST_TBL
    ( 
       id number, 
       name varchar2(80)
    )
    PARTITION BY RANGE ( id )
     ( PARTITION p1 VALUES LESS THAN (1000), 
       PARTITION p2 VALUES LESS THAN (2000)
     );

Table created.
2) Insert records into partition
SQL>begin 
  for i in 1..1800 loop 
    insert into TEST_TBL values (i, 'TEST'||to_char(i) ); 
  end loop; 
  commit;
end;

PL/SQL procedure successfully completed.

3)Create global index:

SQL> create index id_idx on TEST_TBL (id) global;
Index created.

SQL>  select index_name, index_type, status 
       from all_indexes
       where index_name='ID_IDX';

INDEX_NAME    INDEX_TYPE  STATUS
------------- ----------- --------
ID_IDX        NORMAL      VALID
4) Empty partition p2 by deleting all records in p2 partition
SQL> select count(*) from TEST_TBL ;

COUNT(*)
----------      
1800

SQL>  select count(*) from TEST_TBL partition ( p1);

COUNT(*)
----------       
999

SQL>  select count(*) from TEST_TBL partition ( p2);

COUNT(*)
----------       
801

SQL>  delete from TEST_TBL where ID >= 1000;
801 rows deleted.

SQL>commit;
Commit complete
5) Drop the empty partition p2, and confirm the global index is still valid
SQL>alter table TEST_TBL drop partition p2;
Table altered.

SQL> select index_name, index_type, status 
      from all_indexes 
     where index_name='ID_IDX';

INDEX_NAME           INDEX_TYPE                  STATUS
-------------------- --------------------------- --------
ID_IDX               NORMAL                      VALID

Sample script to drop interval partitions

SITUATION

The big benefit of using interval partition is automatic partition creation when new records are inserted. But we need purge the history partitions by developing in-house scripts.

SQL> desc user_tab_partitions;

Name Null? Type
----------------------- -------- --------------
TABLE_NAME VARCHAR2(30)
COMPOSITE VARCHAR2(3)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_COUNT NUMBER
HIGH_VALUE LONG
HIGH_VALUE_LENGTH NUMBER
PARTITION_POSITION NUMBER

SOLUTION

Here is a basic sample script for dropping partitions more than 20 days old.

SQL> set serveroutput on
SQL> declare
dt date;
begin
for x in (select table_name,partition_name, high_value
from user_tab_partitions where INTERVAL='YES' )
loop
execute immediate 'select '||x.high_value||' from dual' into dt;
if dt < sysdate - 20
then
dbms_output.put_line('to drop partition: '||x.table_name||'.'||x.partition_name);
execute immediate 'alter table '||x.table_name||' drop partition '|| x.partition_name||' update indexes ';
end if;
end loop;
end;
/

to drop partition: TBL_TEST1.SYS_P277
to drop partition: TBL_TEST1.SYS_P221
to drop partition: TBL_TEST2.SYS_P231
to drop partition: TBL_TEST2.SYS_P281
to drop partition: TBL_TEST3.SYS_P226
to drop partition: TBL_TEST3..SYS_P242

PL/SQL procedure successfully completed.