Oracle分區表刪除分區資料時導致索引失效解決

Oracle分區表刪除分區資料時導致索引失效解決

一、描述

      今天有個小任務就是要刪除些資料，哈哈，先自己小開心一下。因為要刪除的資料表是我之前轉換成的分區表。這個分區表是按照裡面有個建立時間欄位來分區的，1個季度為1個分區。所以我現在要將2017年7月1日之前的資料刪除（資料量約1000萬），可以直接刪除表分區資料就好。如果要是用delete去刪除這麼多的資料，我還要寫預存程序，分批提交的這樣做。就是這樣的一簡單的truncate partition 引發了後繼的業務故障。最終查詢到該表的索引失效，重建立後恢複。真是汗！
 
二、實驗

1.建立環境

SQL> create table TEST_PARTAS (id number(11), ACCOUNT_ID number(11) ,CTIME date)

  2 partition by range (CTIME)
  3 interval( NUMTOYMINTERVAL(3,'month'))
  4 (partition P0 values less than (TO_DATE('2016-01-01','yyyy-mm-dd')),
  5 partition p1 values less than (to_date('2017-01-01','yyyy-mm-dd')));

Table created.

SQL> insert into TEST_PARTAS select t.id,t.account_id,t.create_time from act_test t;
3483178 rows created.

SQL> commit;
Commit complete.

SQL> EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS('SAM','TEST_PARTAS');
PL/SQL procedure successfully completed.

2.檢查分區表及資料

SQL> select count(*) from TEST_PARTAS;

  COUNT(*)
----------
  3483178

SQL> set lines 120 pages 200;
SQL> set long 9999999
SQL> col table_name for a15
SQL> col PARTITION_NAME for a10

SQL> select t.table_name,t.partition_name,t.num_rows,t.blocks,t.interval,t.high_value from USER_TAB_PARTITIONS t;

TABLE_NAME PARTITION_ NUM_ROWS BLOCKS INT HIGH_VALUE
--------------- ---------- ---------- ---------- --- --------------------------------------------------
TEST_PARTAS P0 2182116 6046 NO TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                                    MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS P1 616290 36506 NO TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                                    MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS SYS_P1611 44829 4030 YES TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:

                                                    MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS SYS_P1612 21706 3022 YES TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:

                                                    MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS SYS_P1613 172525 3022 YES TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:

                                                    MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS SYS_P1614 442435 2014 YES TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                                    MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS SYS_P1615 3277 238 YES TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:

                                                    MI:SS', 'NLS_CALENDAR=GREGORIAN')

7 rows selected.

3.建立主鍵和索引

SQL> alter table TEST_PARTAS add constraint pk_id primary key(ID);

Table altered.

SQL> CREATE INDEX IND_ACCOUNT_ID ON TEST_PARTAS (ACCOUNT_ID);
Index created.

4.檢查索引狀態，目前狀態可用

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_ID TEST_PARTAS VALID
IND_ACCOUNT_ID TEST_PARTAS VALID

5.用truncate 刪除p0分區資料，不加update index參數

SQL> alter table test_partas truncate partition p0;

Table truncated.

6.檢查索引狀態，狀態不可用

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_ID TEST_PARTAS UNUSABLE
IND_ACCOUNT_ID TEST_PARTAS UNUSABLE

7.重建立索引，要加online ，盡量減小對業務的衝擊

SQL> alter index PK_ID rebuild online;

Index altered.

SQL> alter index IND_ACCOUNT_ID rebuild online;

Index altered.

8.檢查索引狀態，此時索引恢複正常可用狀態

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_ID TEST_PARTAS VALID
IND_ACCOUNT_ID TEST_PARTAS VALID

9.用truncate 刪除p1分區資料，增加update index參數

SQL> alter table test_partas truncate partition p1 update indexes;

Table truncated.

10.檢查索引狀態，此時索引正常可用狀態

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_ID TEST_PARTAS VALID
IND_ACCOUNT_ID TEST_PARTAS VALID

三、擴充
      通過這個問題，我們再擴充一下，如果drop分區會不會同樣影響索引，答案是肯定的，刪除分區，索引仍然失效。

SQL> alter table test_partas drop partition SYS_P1611;

Table altered.

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_ID TEST_PARTAS UNUSABLE
IND_ACCOUNT_ID TEST_PARTAS UNUSABLE

四、總結
      一個小小的失誤，帶來了大大的問題，還好這次操作，影響的不是核心業務表。通過失誤，也讓我看到了自己對知識點掌握上的不足。以後的路還很遠，振作起來，努力學習吧。讓自己在後面的DB生涯中，少範錯誤，多多提高效率。

https://www.bkjia.com/topicnews.aspx?tid=12

本文永久更新連結地址：https://www.bkjia.com/Linux/2018-02/150984.htm