Dropping an Empty Partition Without “Update Indexes” Necessary

“Update Indexes” option can be omitted when dropping an empty 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.