关于 Oracle 分区索引的正确 DROP 和 TRUNCATE 方法

--查看分区索引的大小

SQL> select segment_name, trunc(sum(bytes) / 1024 / 1024 / 1024, 2) 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.75
IDX_ACCTFLOW_TYPECODEDATE       24.53
IDX_ACCTFLOW_MAPPING       33.19
IDX_ACCTFLOW_CUSTOMTRANSTYPE       33.44
PK_CUSTOMACCOUNTFLOW       33.19

SQL> select index_name, index_type, status
  from user_indexes s
 where index_name in ('IDX_ACCTFLOW_CUSTOMTRANSTYPE',
                      'IDX_ACCTFLOW_CUSTCODE',
                      'IDX_ACCTFLOW_MAPPING',
                      'IDX_ACCTFLOW_TYPECODEDATE',
                      'PK_CUSTOMACCOUNTFLOW');  2    3    4    5    6    7  

INDEX_NAME       INDEX_TYPE   STATUS
------------------------------ --------------------------- --------
IDX_ACCTFLOW_CUSTCODE       FUNCTION-BASED NORMAL   N/A
IDX_ACCTFLOW_CUSTOMTRANSTYPE   NORMAL   VALID
IDX_ACCTFLOW_MAPPING       NORMAL   VALID
IDX_ACCTFLOW_TYPECODEDATE      NORMAL   VALID
PK_CUSTOMACCOUNTFLOW       NORMAL   VALID

--备份出要进行操作的分区

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 的优点是：

在对分区表进行操作的时候，索引仍是在线和可用的，这个操作不会影响到应用程序。
在对分区表进行操作后，全局分区索引不必进行重建。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-1993020/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-1993020/