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
Make DBA Life Easy 