转载

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

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

正文到此结束