oracle 索引 drop,关于 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的优点是：

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

在对分区表进行操作后，全局分区索引不必进行重建。

原文：http://blog.itpub.net/26506993/viewspace-1993020/