關於 Oracle 分割槽索引的正確 DROP 和 TRUNCATE 方法

--備份出要進行操作的分割槽

create table CUSTOMACCOUNTFLOW_bk as select * from ELMP_TRANS_CUSTOMACCOUNTFLOW partition(SYS_P623);

insert into CUSTOMACCOUNTFLOW_bk select * from ELMP_TRANS_CUSTOMACCOUNTFLOW partition(SYS_P603);

SQL> select count(*) from CUSTOMACCOUNTFLOW_bk;

  COUNT(*)
----------
    291862

Elapsed: 00:00:00.07
SQL> select segment_name, trunc(sum(bytes) / 1024 / 1024 / 1024, 4) GB
  from user_segments
 where segment_name in ('IDX_ACCTFLOW_CUSTOMTRANSTYPE',
                        'IDX_ACCTFLOW_CUSTCODE',
                        'IDX_ACCTFLOW_MAPPING',
                  2    3    4    5    6          'IDX_ACCTFLOW_TYPECODEDATE',
                        'PK_CUSTOMACCOUNTFLOW')
 group by segment_name;  7    8  

SEGMENT_NAME  GB
--------------------------------------------------------------------------------- ----------
IDX_ACCTFLOW_CUSTCODE     31.7566
IDX_ACCTFLOW_TYPECODEDATE     24.5361
IDX_ACCTFLOW_MAPPING     33.1943
IDX_ACCTFLOW_CUSTOMTRANSTYPE     33.4472
PK_CUSTOMACCOUNTFLOW     33.1923

--TRUNCATE 掉一個分割槽

SQL> set timing on
SQL> alter table ELMP_TRANS_CUSTOMACCOUNTFLOW truncate partition SYS_P623 update indexes;

Table truncated.

Elapsed: 00:00:04.60
SQL> select segment_name, trunc(sum(bytes) / 1024 / 1024 / 1024, 4) GB
  from user_segments
 where segment_name in ('IDX_ACCTFLOW_CUSTOMTRANSTYPE',
                        'IDX_ACCTFLOW_CUSTCODE',
                        'IDX_ACCTFLOW_MAPPING',
                  2    3    4    5    6          'IDX_ACCTFLOW_TYPECODEDATE',
                        'PK_CUSTOMACCOUNTFLOW')
 group by segment_name;  7    8  

SEGMENT_NAME  GB
--------------------------------------------------------------------------------- ----------
IDX_ACCTFLOW_CUSTCODE     31.7547
IDX_ACCTFLOW_TYPECODEDATE     24.5361
IDX_ACCTFLOW_MAPPING     33.1943
IDX_ACCTFLOW_CUSTOMTRANSTYPE     33.4472
PK_CUSTOMACCOUNTFLOW     33.1923

--DROP 掉一個分割槽

Elapsed: 00:00:00.02
SQL> alter table ELMP_TRANS_CUSTOMACCOUNTFLOW drop partition SYS_P603 update indexes;

Table altered.

Elapsed: 00:00:20.92
SQL> select segment_name, trunc(sum(bytes) / 1024 / 1024 / 1024, 4) GB
  from user_segments
 where segment_name in ('IDX_ACCTFLOW_CUSTOMTRANSTYPE',
                        'IDX_ACCTFLOW_CUSTCODE',
                        'IDX_ACCTFLOW_MAPPING',
                        'IDX_ACCTFLOW_TYPECODEDATE',
                        'PK_CUSTOMACCOUNTFLOW')
 group by segment_name;  2    3    4    5    6    7    8  

SEGMENT_NAME  GB
--------------------------------------------------------------------------------- ----------
IDX_ACCTFLOW_CUSTCODE     31.7381
IDX_ACCTFLOW_TYPECODEDATE     24.5361
IDX_ACCTFLOW_MAPPING     33.1943
IDX_ACCTFLOW_CUSTOMTRANSTYPE     33.4472
PK_CUSTOMACCOUNTFLOW     33.1923

Elapsed: 00:00:00.03

加上 UPDATE INDEXES 的優點是：

在對分割槽表進行操作的時候，索引仍是線上和可用的，這個操作不會影響到應用程式。
在對分割槽表進行操作後，全域性分割槽索引不必進行重建。