The trick of dropping a table partition without impact the Global Index

Some days ago Michael Abbey invited me to a dinner (René Antunez Oracle ACE, also was there) in his apartment in Ottawa. He is such an admirable person, he is also an Oracle ACE and he likes to share his knowledge, he was talking us a lot of stories about Oracle, how was Oracle many years ago. When I born he already knew Oracle!! so just imagine!

Well, I thought that we were going to request some pizza, but no, he made the dinner and actually he is a very good chef! We had some wine and the funniest thing I remember was when he asked me "would you like to try a weird beer? I said yes" and he gave me a Corona beer LOL, that was funny because Corona is not weird :D.

After to talk about Oracle, his life, Pythian, Collaborate, OTN, OOW, and his books, by the way he gave me a copy of his new book as a gift (did I say that "El señor Abbey" is an admirable person?) …

…he started to talk some technical things. He was talking about some issues with partitioning and he told us the following:

El señor abbey: do you know that when you drop a partition of a table and you have a Global Index the whole Global Index becomes unusable?

René and me: Yes.

El señor abbey: Well, there is a trick here, if you delete all the rows in the table partition and after you drop the partition the Global index doesn’t become unusable.

I liked that trick, everyday we learn something new! that's why I really like to meet new people, new Oracle friends! that's why I am part of an Oracle community and that's why I like to go to other countries, I am always trying to learn something new! something that the books don't have.

Well, after to play for a while Zelda the link between worlds I decided to try the Abbey’s trick in order to share my findings with you.

Note: This trick also works in 11g (I confirmed it) and I believe in 10g, because it is a conceptual trick not a software trick.

[oracle@db12102 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 25 15:21:50 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>
SQL> create table dgomez(
id number,
b varchar2(20))
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
); 2 3 4 5 6 7 8 9 10

Table created.

SQL> create unique index gindex on dgomez(id) 
GLOBAL PARTITION BY RANGE (id)
( PARTITION g1 VALUES LESS THAN (11),
PARTITION g2 VALUES LESS THAN (21),
PARTITION g3 VALUES LESS THAN (MAXVALUE)); 2 3 4 5

Index created.

SQL> alter table dgomez add constraint c1 primary key (id);

Table altered.

SQL> select p.index_name, p.partition_name,p.status from dba_ind_partitions p, dba_indexes i 
where i.index_name=p.index_name 
and table_name='DGOMEZ'; 2 3

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
GINDEX G3 USABLE
GINDEX G2 USABLE
GINDEX G1 USABLE

SQL> insert into dgomez values (1,'d');

1 row created.

SQL> insert into dgomez values (6,'e');

1 row created.

SQL> insert into dgomez values (11,'i');

1 row created.

SQL> insert into dgomez values (16,'b');

1 row created.

SQL> insert into dgomez values (21,'y');

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SYS','DGOMEZ');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows from dba_tab_partitions where table_name='DGOMEZ';

PARTITION_ NUM_ROWS
---------- ----------
P0 1
P1 1
P2 1
P3 1
P4 1

SQL> alter table dgomez drop partition p0;

Table altered.

SQL> select p.index_name, p.partition_name,p.status from dba_ind_partitions p, dba_indexes i 
where i.index_name=p.index_name 
and table_name='DGOMEZ'; 2 3

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
GINDEX G3 UNUSABLE
GINDEX G2 UNUSABLE
GINDEX G1 UNUSABLE

SQL>

As you can see all the partitions of the Global Index became unusable. Now let's see what happen when first we delete all the rows in the table partition before to execute the "drop partition" sentence.

SQL> drop table dgomez purge;

Table dropped.

SQL> create table dgomez(
id number,
b varchar2(20))
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
); 2 3 4 5 6 7 8 9 10

Table created.

SQL> create unique index gindex on dgomez(id) 
GLOBAL PARTITION BY RANGE (id)
( PARTITION g1 VALUES LESS THAN (11),
PARTITION g2 VALUES LESS THAN (21),
PARTITION g3 VALUES LESS THAN (MAXVALUE)); 2 3 4 5

Index created.

SQL> alter table dgomez add constraint c1 primary key (id);

Table altered.

SQL> select p.index_name, p.partition_name,p.status from dba_ind_partitions p, dba_indexes i 
where i.index_name=p.index_name 
and table_name='DGOMEZ'; 2 3

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
GINDEX G3 USABLE
GINDEX G2 USABLE
GINDEX G1 USABLE

SQL> insert into dgomez values (1,'d');

1 row created.

SQL> insert into dgomez values (6,'e');

1 row created.

SQL> insert into dgomez values (11,'i');

1 row created.

SQL> insert into dgomez values (16,'b');

1 row created.

SQL> insert into dgomez values (21,'y');

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SYS','DGOMEZ');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows from dba_tab_partitions where table_name='DGOMEZ';

PARTITION_ NUM_ROWS
---------- ----------
P0 1
P1 1
P2 1
P3 1
P4 1

SQL> delete from dgomez partition(p0);<------- This is the trick

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter table dgomez drop partition p0;

Table altered.

SQL> select p.index_name, p.partition_name,p.status from dba_ind_partitions p, dba_indexes i 
where i.index_name=p.index_name 
and table_name='DGOMEZ'; 2 3

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
GINDEX G3 USABLE
GINDEX G2 USABLE
GINDEX G1 USABLE

SQL>