Dropping an Empty Partition Without “Update Indexes” Necessary

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 below example shows when we drop an empty partition,  the ” Update Indexes” clause is not required.

1) Create partition table :

SQL>CREATE TABLE JAMESTESTCREATE TABLE JAMESTEST( 
  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 JAMESTEST values (i, 'JAMES'||to_char(i) ); 
  end loop; 
  commit;
end;

PL/SQL procedure successfully completed.

3)Create global index:

SQL> create index id_idx on JAMESTEST (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 JAMESTEST ;
  COUNT(*)
----------      
1800

SQL>  select count(*) from JAMESTEST partition ( p1);
  COUNT(*)
----------       
999
SQL>  select count(*) from JAMESTEST partition ( p2);
  COUNT(*)
----------       
801

SQL>  delete from JAMESTEST 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 JAMESTEST 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
Advertisements
This entry was posted in Partitioning and tagged , , , . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s