Gathering Optimizer Statistics for Partitioned Tables with Index Partitions whose Status is UNUSABLE

Gathering Optimizer Statistics for Partitioned Tables with Index Partitions whose Status is UNUSABLE 

Below explanation is for  collecting statistics on a partitioned table with an index partition whose value for the STATUS column of DBA_IND_PARTITIONS is UNUSABLE using the DBMS_STATS.GATHER_TABLE_STATS procedure.

When statistics are collected using the DBMS_STATS.GATHER_TABLE_STATS procedure for a partitioned table with an index partition whose value for the STATUS column of DBA_IND_PARTITIONS is UNUSABLE, GATHER_TABLE_STATS procedure will collect statistics for both the index partition and the partitioned table.
Thus, the values in the LAST_ANALYZED columns of DBA_TAB_PARTITIONS and DBA_IND_PARTITIONS will be updated.
However, the value of the STATUS column in DBA_IND_PARTITIONS for the index partition remains UNUSABLE.

The following example shows the partitioned table PART_TAB with the global partitioned index PART_TAB_GLOBAL_IDX and the local partitioned index PART_TAB_LOCAL_IDX.
Each partitioned index contains partitions with status UNUSABLE.
Gather statistics for partition P1 in partitioned table PART_TAB:

SQL >  select substr(TABLE_NAME,1,20) table_name, substr(partition_name,1,20)
  part_name , LAST_ANALYZED from dba_tab_partitions
  where TABLE_NAME = '&TableName' order by part_name;

TABLE_NAME           PART_NAME       LAST_ANALYZED
-------------------- --------------- -------------------
PART_TAB             P1              2022/11/01 15:00:00
PART_TAB             P2              2022/11/01 15:00:00
PART_TAB             P3              2022/11/01 15:00:00
PART_TAB             P4              2022/11/01 15:00:00

SQL> select substr(index_name,1,20) index_name, substr(partition_name,1,20)
   part_name , status, LAST_ANALYZED from dba_ind_partitions
  where index_name like 'PART_TAB%' order by part_name;

INDEX_NAME           PART_NAME       STATUS   LAST_ANALYZED
-------------------- --------------- -------- -------------------
PART_TAB_GLOBAL_IDX  G1_IDX          USABLE   2022/11/01 15:00:00
PART_TAB_GLOBAL_IDX  G2_IDX          UNUSABLE 2022/11/01 15:00:00
PART_TAB_LOCAL_IDX   P1_IDX          UNUSABLE 2022/11/01 15:00:00
PART_TAB_LOCAL_IDX   P2_IDX          USABLE   2022/11/01 15:00:00
PART_TAB_LOCAL_IDX   P3_IDX          USABLE   2022/11/01 15:00:00
PART_TAB_LOCAL_IDX   P4_IDX          USABLE   2022/11/01 15:00:00


SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => '<OWNERNAME>', tabname => 'PART_TAB', partname => 'P1', cascade => TRUE);

PL/SQL procedure successfully completed.


SQL> select substr(TABLE_NAME,1,20) table_name, substr(partition_name,1,20)
  part_name , LAST_ANALYZED from dba_tab_partitions
 where TABLE_NAME = 'PART_TAB' order by part_name;

TABLE_NAME           PART_NAME       LAST_ANALYZED
-------------------- --------------- -------------------
PART_TAB             P1              2022/11/01 15:30:00　<--------(★)
PART_TAB             P2              2022/11/01 15:00:00
PART_TAB             P3              2022/11/01 15:00:00
PART_TAB             P4              2022/11/01 15:00:00

SQL> select substr(index_name,1,20) index_name, substr(partition_name,1,20)
 part_name , status, LAST_ANALYZED from dba_ind_partitions
 where index_name like 'PART_TAB%' order by part_name;

INDEX_NAME           PART_NAME       STATUS   LAST_ANALYZED
-------------------- --------------- -------- -------------------
PART_TAB_GLOBAL_IDX  G1_IDX          USABLE   2022/11/01 15:30:00
PART_TAB_GLOBAL_IDX  G2_IDX          UNUSABLE 2022/11/01 15:30:00　<--------(★)
PART_TAB_LOCAL_IDX   P1_IDX          UNUSABLE 2022/11/01 15:30:00　<--------(★)
PART_TAB_LOCAL_IDX   P2_IDX          USABLE   2022/11/01 15:00:00
PART_TAB_LOCAL_IDX   P3_IDX          USABLE   2022/11/01 15:00:00
PART_TAB_LOCAL_IDX   P4_IDX          USABLE   2022/11/01 15:00:00

If you do not want to collect statistics for index partitions, but only for partitioned tables, specify the parameter "cascade => FALSE" in the DBMS_STATS.GATHER_TABLE_STATS procedure.