TRUNCATE PARTITION by example

Example to Truncate a partition with indexes.
When doing Truncate to a partition, must do it with UPDATE GLOBAL INDEXES;

Code example:

SQL> SELECT index_name, status, global_stats 

FROM USER_INDEXES 

WHERE table_name = 'SFI_CUSTOMER_PROFILE';

INDEX_NAME                         STATUS   GLOBAL_STATS
---------------------------------- -------- ------------
SCP_MSISDN_IDX                     VALID    YES
SFI_CUSTOMER_PROFILE_PK            VALID    YES

Code example without UPDATE GLOBAL INDEXES:

ALTER TABLE SFI_CUSTOMER_PROFILE TRUNCATE PARTITION AFFILIATE_82;

SQL> SELECT index_name, status, global_stats 

FROM USER_INDEXES 

WHERE table_name = 'SFI_CUSTOMER_PROFILE';

 
INDEX_NAME                         STATUS   GLOBAL_STATS
---------------------------------- -------- ------------
SCP_MSISDN_IDX                     UNUSABLE YES
SFI_CUSTOMER_PROFILE_PK            UNUSABLE YES
 
ALTER INDEX SCP_MSISDN_IDX REBUILD ONLINE;
ALTER INDEX SFI_CUSTOMER_PROFILE_PK REBUILD ONLINE;

SQL> SELECT index_name, status, global_stats 

FROM USER_INDEXES 

WHERE table_name = 'SFI_CUSTOMER_PROFILE';

 

INDEX_NAME                         STATUS   GLOBAL_STATS
---------------------------------- -------- ------------
SCP_MSISDN_IDX                     VALID    YES
SFI_CUSTOMER_PROFILE_PK            VALID    YES

Code example with UPDATE GLOBAL INDEXES:
ALTER TABLE SFI_CUSTOMER_PROFILE TRUNCATE PARTITION AFFILIATE_82 UPDATE GLOBAL INDEXES;
 
Table truncated
 

SQL> SELECT index_name, status, global_stats 

FROM USER_INDEXES 

WHERE table_name = 'SFI_CUSTOMER_PROFILE';

 

INDEX_NAME                         STATUS   GLOBAL_STATS
---------------------------------- -------- ------------
SCP_MSISDN_IDX                     VALID    YES
SFI_CUSTOMER_PROFILE_PK            VALID    YES