分区表的Drop&Truncate

分区表中,Truncate或Drop分区都会导致primary key和Index无效.

 

SQL> create table test_part(
  2  a number not null,
  3  b number not null,
  4  c number not null,
  5  d varchar2(100)
  6  )
  7  partition by range(a)
  8  (
  9  partition part_1 values less than (100),
 10  partition part_2 values less than (200),
 11  partition part_3 values less than (300),
 12  partition part_4 values less than (400),
 13  partition part_5 values less than (500)
 14  )
 15  /
 
Table created
 
SQL>
SQL> alter table test_part
  2    add constraint PK_test_part primary key (A);
 
Table altered
SQL> create index ind_test_part_B on test_part(B) local;
 
Index created
SQL> create index ind_test_part_C on test_part(C);
 
Index created
 
SQL>
SQL> begin
  2  for i in 1..499 loop
  3     insert into test_part values (i,i,mod(i,2),'A');
  4  end loop;
  5  end;
  6  /
 
PL/SQL procedure successfully completed
 
SQL> commit;
 
Commit complete
 
SQL>
SQL> select index_name,status,partitioned from all_indexes where owner='LGX'
  2  and table_name='TEST_PART';
 
INDEX_NAME                     STATUS   PARTITIONED
------------------------------ -------- -----------
PK_TEST_PART                   VALID    NO
IND_TEST_PART_B                N/A      YES
IND_TEST_PART_C                VALID    NO
 
SQL>
SQL> select index_name, partition_name, status
  2    from all_ind_partitions
  3   where index_name = 'IND_TEST_PART_B'
  4   order by partition_name;
 
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_TEST_PART_B                PART_1                         USABLE
IND_TEST_PART_B                PART_2                         USABLE
IND_TEST_PART_B                PART_3                         USABLE
IND_TEST_PART_B                PART_4                         USABLE
IND_TEST_PART_B                PART_5                         USABLE
 
SQL> ALTER TABLE TEST_PART drop partition part_1;
 
Table altered
 
SQL>
SQL> select index_name,status,partitioned from all_indexes where owner='LGX'
  2  and table_name='TEST_PART';
 
INDEX_NAME                     STATUS   PARTITIONED
------------------------------ -------- -----------
PK_TEST_PART                   UNUSABLE NO
IND_TEST_PART_B                N/A      YES
IND_TEST_PART_C                UNUSABLE NO
 
SQL> ALTER TABLE TEST_PART drop partition part_2 update global indexes;
 
Table altered
 
SQL>
SQL> select index_name,status,partitioned from all_indexes where owner='LGX'
  2  and table_name='TEST_PART';
 
INDEX_NAME                     STATUS   PARTITIONED
------------------------------ -------- -----------
PK_TEST_PART                   UNUSABLE NO
IND_TEST_PART_B                N/A      YES
IND_TEST_PART_C                UNUSABLE NO
 
SQL> alter index PK_TEST_PART rebuild;
 
Index altered
 
SQL> alter index IND_TEST_PART_C rebuild;
 
Index altered
 
SQL>
SQL> select index_name,status,partitioned from all_indexes where owner='LGX'
  2  and table_name='TEST_PART';
 
INDEX_NAME                     STATUS   PARTITIONED
------------------------------ -------- -----------
PK_TEST_PART                   VALID    NO
IND_TEST_PART_B                N/A      YES
IND_TEST_PART_C                VALID    NO

 

SQL> ALTER TABLE TEST_PART truncate partition part_3;
 
Table truncated
 
SQL>
SQL> select index_name,status,partitioned from all_indexes where owner='LGX'
  2  and table_name='TEST_PART';
 
INDEX_NAME                     STATUS   PARTITIONED
------------------------------ -------- -----------
PK_TEST_PART                   UNUSABLE NO
IND_TEST_PART_B                N/A      YES
IND_TEST_PART_C                UNUSABLE NO
 
SQL> alter index PK_TEST_PART rebuild;
 
Index altered
 
SQL>
SQL> select index_name,status,partitioned from all_indexes where owner='LGX'
  2  and table_name='TEST_PART';
 
INDEX_NAME                     STATUS   PARTITIONED
------------------------------ -------- -----------
PK_TEST_PART                   VALID    NO
IND_TEST_PART_B                N/A      YES
IND_TEST_PART_C                UNUSABLE NO
 
SQL> alter index IND_TEST_PART_C rebuild;
 
Index altered
 
SQL> ALTER TABLE TEST_PART truncate partition part_4 update global indexes;
 
Table truncated
 
SQL>
SQL> select index_name,status,partitioned from all_indexes where owner='LGX'
  2  and table_name='TEST_PART';
 
INDEX_NAME                     STATUS   PARTITIONED
------------------------------ -------- -----------
PK_TEST_PART                   VALID    NO
IND_TEST_PART_B                N/A      YES
IND_TEST_PART_C                VALID    NO

 

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
/*rebuild UNUSABLE  in local index*/
alter index IND_TEST_PART_B rebuild partition PART_1;