Toad World Blog

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

Sep 25, 2014 11:00:00 PM by Deiby Gomez

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.

Screen Shot 2014-09-25 at 11.25.37 PM.png-550x0

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?) ...

Screen Shot 2014-09-25 at 11.28.05 PM.png-550x0

...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>

Tags: Oracle

Deiby Gomez

Written by Deiby Gomez

Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS). Deiby was the winner of "IOUG SELECT Journal Editor’s Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle’s website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book “Oracle Database 12cR2 Multitenant - Oracle Press” and he is co-author of the book “Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press”. He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.

Certifications:

  • Oracle Database Cloud Administrator Certified Associate
  • Oracle Database Cloud Administrator Certified Professional
  • Oracle Database Cloud Service Operations Certified Associate
  • Oracle Certified Expert, Oracle Database 12c Maximum Availability Architecture
  • Oracle Certified Expert, Oracle Database 12c: RAC and Grid Infrastructure Administration
  • Oracle Database 12c Maximum Availability Architecture Certified Master
  • Oracle Database 12c: Data Guard Administrator
  • Oracle Database 12c Administrator Certified Master (OCM 12c)
  • Oracle Real Application Clusters 12c Certified Implementation Specialist
  • Oracle Database 11g Administrator Certified Master (OCM 11g)
  • Oracle Database 12c Administrator Certified Professional (OCP 12c)
  • Oracle RAC 11g and Grid Infraestructure Administrator
  • Oracle Certified Expert, Oracle Exadata X3 and X4 Administrator
  • Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert
  • Oracle Database 11g Administrator Certified Professional (OCP 11g)
  • Oracle Linux Certified Implementation Specialist
  • Oracle Database 11g Administrator Certified Associate