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

Myth丶恋晨 2024-04-17 23:50 12阅读 0赞

**--查看分区索引的大小**   
  
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/