Tag: alter table drop partition

As we know  the ‘update indexes’ clause is used  to drop the local indexes and update the global index  when we drop a partition. So the indexes will still be VALID”

The following example shows when we drop an empty partition,  the ” Update Indexes” clause is not required.

1) Create partition table

SQL>CREATE TABLE TEST_TBL
    ( 
       id number, 
       name varchar2(80)
    )
    PARTITION BY RANGE ( id )
     ( PARTITION p1 VALUES LESS THAN (1000), 
       PARTITION p2 VALUES LESS THAN (2000)
     );

Table created.

2) Insert records into partition

SQL>begin 
  for i in 1..1800 loop 
    insert into TEST_TBL values (i, 'TEST'||to_char(i) ); 
  end loop; 
  commit;
end;

PL/SQL procedure successfully completed.

3)Create global index:

SQL> create index id_idx on TEST_TBL (id) global;
Index created.

SQL>  select index_name, index_type, status 
       from all_indexes
       where index_name='ID_IDX';

INDEX_NAME    INDEX_TYPE  STATUS
------------- ----------- --------
ID_IDX        NORMAL      VALID

4) Empty partition p2 by deleting all records in p2 partition

SQL> select count(*) from TEST_TBL ;

COUNT(*)
----------      
1800

SQL>  select count(*) from TEST_TBL partition ( p1);

COUNT(*)
----------       
999

SQL>  select count(*) from TEST_TBL partition ( p2);

COUNT(*)
----------       
801

SQL>  delete from TEST_TBL where ID >= 1000;
801 rows deleted.

SQL>commit;
Commit complete

5) Drop the empty partition p2, and confirm the global index is still valid

SQL>alter table TEST_TBL drop partition p2;
Table altered.

SQL> select index_name, index_type, status 
      from all_indexes 
     where index_name='ID_IDX';

INDEX_NAME           INDEX_TYPE                  STATUS
-------------------- --------------------------- --------
ID_IDX               NORMAL                      VALID