Oracle Database 12c: What's New in Partitioning?

Introduction

Partitioning addresses key issues in supporting very large tables and indexes by decomposing them into smaller and more manageable pieces called partitions, which are entirely transparent to an application. New in Oracle Database 12c are:
1. Interval Reference Partitioning
2. Cascading TRUNCATE and EXCHANGE Operations
3. Moving Partitions Online
4. Maintaining Multiple Partitions
5. Maintaining Global Indexes Asynchronously
6. Using Partial Indexes

Hardware and Software Requirements

The following is a list of hardware and software requirements:

Oracle Database 12c

Linux operating system (especially for topic 3 where you run a Linux script)

Prerequisites

Before starting this tutorial, you should:

Have access to Oracle Database 12c with a sample ORCL database, the SYS user with SYSDBA privilege and OS authentication (so that you can execute the sqlplus / as sysdba command.)

This example uses the USERS tablespace, included in Oracle Database 12c. The Sample Schemas installation guide is part of the database documentation set, and is also available online at:http://otn.oracle.com.>

Have downloaded and unzipped the 12c_parti.zip file (which is in the files subdirectory of this tutorial) into a working directory.

Navigate to your working directory and execute all files from that location.

Execute the tutorial setup which creates the PART12c user.

Assumption

This tutorial assumes that when you begin executing steps for a topic, you complete the entire topic before going to another one. If you wish to restart a topic, you must first execute the cleanup task.
You can also re-execute the Tutorial Setup which drops the PART12C user and its objects and then recreates it.

What You Already Know

Range partitioning is a convenient method for partitioning historical data. The boundaries of range partitions define the ordering of the partitions in the tables or indexes.
Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition.
Range or interval partitioning is often used to organize data by time intervals on a column of type DATE.

Tutorial Setup

Create the PART12C user who executes all other tutorial topics.

First, set your orcl environment variables.

To start a SQLPlus session, enter: sqlplus / as sysdba.

Optionally, to display the file that you are about to execute, enter !cat setup12c.sql.

Tip: It is always a good idea to first view a file before executing it. To avoid cluttering this tutorial, this optional step is not repeated.

To create the PART12c user, enter: @setup12c.sql.

Note: All Oracle passwords in this tutorial are: oracle_4U.

To exit SQLPlus, enter: exit.

Now, that you created the PART12C user with access to the USERS tablespace, you can begin to explore each of the new partitioning features of Oracle Database 12c.

1. Interval Reference Partitioning

Interval Reference Partitioning enables reference-partitioned table to use interval partitioning as a top partitioning strategy which provides better partitioning modeling.
Interval partitioned tables can be used as parent tables for reference partitioning. Partitions in the reference partitioned table corresponding to interval partitions in the parent table are created upon insert into the reference partitioned table. Your first set of test tables:

To explore interval reference partitioning:

In this tutorial section, you learned about interval reference partitioning:

How to create tables that use parent tables for reference partitioning
How partitions are automatically created when you insert data
That only the necessary partitions are created
That a child partition inherits its name from its direct parent
How to rename a partition
That renaming a partition does not affect other (dependent) partitions

2. Cascading TRUNCATE and EXCHANGE Operations

You can simplify application development for reference and interval-reference partitioned tables by enabling the inheritance of the partition maintenance operation from the parent to the child tables with the CASCADE option for TRUNCATE PARTITION and EXCHANGE PARTITION operations. The cascade options are off by default so they do not affect compatibility. Your test tables are very similar to the first set:

Ensure that your environment variable are set and log in to SQL*Plus as the PART12C user with the oracle_4U password. (Review the Tutorial Setup, if you are not sure about the commands.)

To create the test tables (as shown in the graphic above), execute the t2create.sql script.
Note: You can execute a SQL script without specifying the extension because .sql is the default.

SQL> @t2create
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> set echo off
SQL> 
SQL> REM    Create the INTREF_P parent table
SQL> CREATE TABLE intRef_p
  2     (pkcol number not null,
  3      col2  varchar2(200),
  4      CONSTRAINT pk_intref PRIMARY KEY (pkcol))
  5      PARTITION by range (pkcol) interval (10)
  6     (PARTITION p1 VALUES less than (10));

Table created.

SQL> 
SQL> REM    Create the INTREF_C1 child table
SQL> CREATE TABLE intRef_c1
  2     (pkcol number not null,
  3      col2  varchar2(200),
  4      fkcol number not null,
  5      CONSTRAINT pk_c1 PRIMARY KEY (pkcol),
  6      CONSTRAINT fk_c1 FOREIGN KEY (fkcol)
  7      REFERENCES intRef_p(pkcol) ON DELETE CASCADE)
  8      PARTITION by reference (fk_c1);

Table created.

SQL> 
SQL> REM    Create the INTREF_C2 child table
SQL> CREATE TABLE intRef_c2
  2     (pkcol number PRIMARY KEY not null,
  3      col2  varchar2(200),
  4      fkcol number not null,
  5      CONSTRAINT fk_c2 FOREIGN KEY (fkcol)
  6      REFERENCES intRef_p(pkcol) ON DELETE CASCADE)
  7      PARTITION by reference (fk_c2);

Table created.

SQL> 
SQL> REM    Create the INTREF_GC1 grandchild table
SQL> CREATE TABLE intRef_gc1
  2     (col1  number not null,
  3      col2  varchar2(200),
  4      fkcol number not null,
  5      CONSTRAINT fk_gc1 FOREIGN KEY (fkcol)
  6      REFERENCES intRef_c1(pkcol) ON DELETE CASCADE)
  7      PARTITION by reference (fk_gc1);

Table created.

SQL> 
SQL> REM    Create the INTREF_GC2 grandchild table
SQL> CREATE TABLE intRef_gc2
  2     (col1  number not null,
  3      col2  varchar2(200),
  4      fkcol number not null,
  5      CONSTRAINT fk_gc2 FOREIGN KEY (fkcol)
  6      REFERENCES intRef_c2(pkcol) ON DELETE CASCADE)
  7      PARTITION by reference (fk_gc2);

Table created.

SQL> 
SQL> REM  End of file
SQL>

Insert data so that you have two sets of partitions. You will drop one of them later. Execute the t2insert.sql script.

SQL> @t2insert
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM    Prepare two sets of partitions with data
SQL> REM    ... we want to drop one set of them later.
SQL> set echo on
SQL> INSERT INTO intRef_p
  2     VALUES (999,' data for truncate - p');

1 row created.

SQL> commit;

Commit complete.

SQL> INSERT INTO intRef_c1
  2     VALUES (1999,' data for truncate - c1',999);

1 row created.

SQL> INSERT INTO intRef_c2
  2     VALUES (2999,' data for truncate - c2',999);

1 row created.

SQL> commit;

Commit complete.

SQL> INSERT INTO intRef_gc1
  2     VALUES (1999,' data for truncate - gc1',1999);

1 row created.

SQL> INSERT INTO intRef_gc2
  2     VALUES (2999,' data for truncate - gc2',2999);

1 row created.

SQL> commit;

Commit complete.

SQL> INSERT INTO intRef_p
  2     VALUES (333,' data for truncate - p');

1 row created.

SQL> commit;

Commit complete.

SQL> INSERT INTO intRef_c1
  2     VALUES (1333,' data for truncate - c1',333);

1 row created.

SQL> INSERT INTO intRef_c2
  2     VALUES (2333,' data for truncate - c2',333);

1 row created.

SQL> commit;

Commit complete.

SQL> INSERT INTO intRef_gc1
  2     VALUES (1333,' data for truncate - gc1',1333);

1 row created.

SQL> INSERT INTO intRef_gc2
  2     VALUES (2333,' data for truncate - gc1',2333);

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> REM   End of file.
SQL>

To view all data created so far, execute the t2selstar.sql script.

SQL> @t2selstar
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   View all data
SQL> col COL2 format a30
SQL> set echo on
SQL> SELECT * from intRef_p;

     PKCOL COL2
---------- ------------------------------
       333  data for truncate - p
       999  data for truncate - p

2 rows selected.

SQL> SELECT * from intRef_c1;

     PKCOL COL2                                FKCOL
---------- ------------------------------ ----------
      1333  data for truncate - c1               333
      1999  data for truncate - c1               999

2 rows selected.

SQL> SELECT * from intRef_c2;

     PKCOL COL2                               FKCOL
---------- ------------------------------ ----------
      2333  data for truncate - c2               333
      2999  data for truncate - c2               999

2 rows selected.

SQL> SELECT * from intRef_gc1;

      COL1 COL2                               FKCOL
---------- ------------------------------ ----------
      1333  data for truncate - gc1             1333
      1999  data for truncate - gc1             1999
 
2 rows selected.

SQL> SELECT * from intRef_gc2;

      COL1 COL2                               FKCOL
---------- ------------------------------ ----------
      2333  data for truncate - gc1             2333
      2999  data for truncate - gc2             2999

2 rows selected.

SQL> 
SQL> REM   End of file.
SQL>

To view the data for primary key 999, joined by parent - child - grandchild, execute the t2select.sql script.

SQL> @t2select
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   View data joined by parent - child - grandchild
SQL> col parent     format a30
SQL> col child1     format a30
SQL> col child2     format a30
SQL> col grandchild1    format a30
SQL> col grandchild2    format a30
SQL> 
SQL> SELECT p.pkcol, p.col2 parent, c1.col2 child1, c2.col2 child2, gc1.col2 grandchild1, gc2.col2 grandchild2
  2  FROM intRef_p p, intRef_c1 c1, intRef_c2 c2, intRef_gc1 gc1, intRef_gc2 gc2
  3  where p.pkcol  = c1.fkcol
  4    and p.pkcol  = c2.fkcol
  5    and c1.pkcol = gc1.fkcol
  6    and c2.pkcol = gc2.fkcol
  7    and p.pkcol=999;

     PKCOL PARENT             CHILD1             CHILD2             GRANDCHILD1            GRANDCHILD2
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
       999  data for truncate - p      data for truncate - c1     data for truncate - c2     data for truncate - gc1    data for truncate - gc2

1 row selected.

SQL> 
SQL> REM   End of file.
SQL>

Use the cascading TRUNCATE functionality by executing:
ALTER TABLE intRef_p TRUNCATE PARTITION for (999) cascade update indexes;
```
SQL> ALTER TABLE intRef_p 
   TRUNCATE PARTITION for (999) cascade update indexes;
  2  
Table truncated.

SQL> 
```

To view all data in the reference partitioned tables, execute the t2selstar.sql script.

SQL> @t2selstar
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   View all data
SQL> set echo on
SQL> SELECT * from intRef_p;

     PKCOL COL2
---------- ------------------------------
       333  data for truncate - p

1 row selected.

SQL> SELECT * from intRef_c1;

     PKCOL COL2                    FKCOL
---------- ------------------------------ ----------
      1333  data for truncate - c1       333

1 row selected.

SQL> SELECT * from intRef_c2;

     PKCOL COL2                    FKCOL
---------- ------------------------------ ----------
      2333  data for truncate - c2       333

1 row selected.

SQL> SELECT * from intRef_gc1;

      COL1 COL2                    FKCOL
---------- ------------------------------ ----------
      1333  data for truncate - gc1     1333

1 row selected.

SQL> SELECT * from intRef_gc2;

      COL1 COL2                    FKCOL
---------- ------------------------------ ----------
      2333  data for truncate - gc1     2333

1 row selected.

SQL> 
SQL> REM   End of file.
SQL>

Note: All data that were referencing the primary key 999 have been truncated.

Execute the t2select.sql script again that joins by parent - child - grandchild.

SQL> @t2select
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   View data joined by parent - child - grandchild
SQL> col parent     format a30
SQL> col child1     format a30
SQL> col child2     format a30
SQL> col grandchild1    format a30
SQL> col grandchild2    format a30
SQL> 
SQL> SELECT p.pkcol, p.col2 parent, c1.col2 child1, c2.col2 child2, gc1.col2 grandchild1, gc2.col2 grandchild2
  2  FROM intRef_p p, intRef_c1 c1, intRef_c2 c2, intRef_gc1 gc1, intRef_gc2 gc2
  3  where p.pkcol  = c1.fkcol
  4    and p.pkcol  = c2.fkcol
  5    and c1.pkcol = gc1.fkcol
  6    and c2.pkcol = gc2.fkcol
  7    and p.pkcol=999;

no rows selected

SQL> 
SQL> REM   End of file.
SQL>

Note the same result: All data that were referencing the primary key 999 have been truncated.

To drop your test tables, execute the t2drop.sql script.

SQL> @t2drop
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> set echo on
SQL> 
SQL> REM To cleanup and to allow for re-execution of this topic,
SQL> REM   tables must be dropped.
SQL> 
SQL> DROP TABLE intRef_gc1 PURGE;

Table dropped.

SQL> DROP TABLE intRef_gc2 PURGE;

Table dropped.

SQL> DROP TABLE intRef_c1 PURGE;

Table dropped.

SQL> DROP TABLE intRef_c2 PURGE;

Table dropped.

SQL> DROP TABLE intRef_p PURGE;

Table dropped.

SQL> 
SQL> PROMPT   End of file.
End of file.
SQL>

To create new test tables (a parent with one child and one grandchild), execute the t2create1.sql script.

SQL> @t2create1
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> set echo off
SQL> 
SQL> REM    Create the INTREF_P parent table
SQL> CREATE TABLE intRef_p
  2     (pkcol number not null,
  3      col2  varchar2(200),
  4      CONSTRAINT pk_intref PRIMARY KEY (pkcol))
  5      PARTITION by range (pkcol) interval (10)
  6     (PARTITION p1 VALUES less than (10));

Table created.

SQL> 
SQL> REM    Create the INTREF_C1 child table
SQL> CREATE TABLE intRef_c1
  2     (pkcol number not null,
  3      col2  varchar2(200),
  4      fkcol number not null,
  5      CONSTRAINT pk_c1 PRIMARY KEY (pkcol),
  6      CONSTRAINT fk_c1 FOREIGN KEY (fkcol)
  7      REFERENCES intRef_p(pkcol) ON DELETE CASCADE)
  8      PARTITION by reference (fk_c1);

Table created.

SQL> 
SQL> REM    Create the INTREF_GC1 grandchild table
SQL> CREATE TABLE intRef_gc1
  2     (col1  number not null,
  3      col2  varchar2(200),
  4      fkcol number not null,
  5      CONSTRAINT fk_gc1 FOREIGN KEY (fkcol)
  6      REFERENCES intRef_c1(pkcol) ON DELETE CASCADE)
  7      PARTITION by reference (fk_gc1);

Table created.

SQL>

To insert data into your new test tables, execute the t2insbefore.sql script.

SQL> @t2insbefore
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Prepare two sets of partitions with data
SQL> set echo on
SQL> INSERT INTO intRef_p
  2     VALUES (999,' p999 - partitioned data BEFORE exchange - p');

1 row created.

SQL> INSERT INTO intRef_c1
  2     VALUES (1999,' p999 - partitioned data BEFORE exchange - c1',999);

1 row created.

SQL> INSERT INTO intRef_gc1
  2     VALUES (1999,' p999 - partitioned data BEFORE exchange - gc1',1999);

1 row created.

SQL> 
SQL> INSERT INTO intRef_p
  2     VALUES (333,' p333 - partitioned data BEFORE exchange - p');

1 row created.

SQL> INSERT INTO intRef_c1
  2     VALUES (1333,' p333 - partitioned data BEFORE exchange - c1',333);

1 row created.

SQL> INSERT INTO intRef_gc1
  2     VALUES (1333,' p333 - partitioned data BEFORE exchange - gc1',1333);

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> REM   End of file.
SQL>

To view all data for one branch of parent, child, and grandchild, execute the t2selstar1.sql script.

SQL> @t2selstar1
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> col col2 format a48
SQL> REM   View all data for one branch of parent, child, and grandchild
SQL> set echo on
SQL> SELECT * from intRef_p;

     PKCOL COL2
---------- ------------------------------------------------
       333  p333 - partitioned data BEFORE exchange - p
       999  p999 - partitioned data BEFORE exchange - p

2 rows selected.

SQL> SELECT * from intRef_c1;

     PKCOL COL2                          FKCOL
---------- ------------------------------------------------ ----------
      1333  p333 - partitioned data BEFORE exchange - c1       333
      1999  p999 - partitioned data BEFORE exchange - c1       999

2 rows selected.

SQL> SELECT * from intRef_gc1;

      COL1 COL2                          FKCOL
---------- ------------------------------------------------ ----------
      1333  p333 - partitioned data BEFORE exchange - gc1     1333
      1999  p999 - partitioned data BEFORE exchange - gc1     1999

2 rows selected.

SQL> 
SQL> REM   End of file.
SQL>

To test the EXCHANGE functionality you need to create standalone nonpartitioned tables with the same PK-FK equivalent table construct than the reference partitioned table. Execute the t2createX.sql script to create these tables.

SQL> @t2createX
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Create standalone nonpartitioned tables with
SQL> REM     the same PK-FK equivalent table construct
SQL> REM     than the reference partitioned table for EXCHANGE
SQL> 
SQL> set echo on
SQL> CREATE TABLE XintRef_p
  2     (pkcol number not null,
  3      col2 varchar2(200),
  4     CONSTRAINT xpk_intref PRIMARY KEY (pkcol));

Table created.

SQL> 
SQL> CREATE TABLE XintRef_c1
  2     (pkcol number not null,
  3      col2 varchar2(200),
  4      fkcol number not null,
  5      CONSTRAINT xpk_c1 PRIMARY KEY (pkcol),
  6      CONSTRAINT xfk_c1 FOREIGN KEY (fkcol)
  7      REFERENCES XintRef_p(pkcol) ON DELETE CASCADE);

Table created.

SQL> 
SQL> CREATE TABLE XintRef_gc1
  2     (col1 number not null,
  3      col2 varchar2(200),
  4      fkcol number not null,
  5      CONSTRAINT xfk_gc1 FOREIGN KEY (fkcol)
  6      REFERENCES XintRef_c1(pkcol) ON DELETE CASCADE);

Table created.

SQL> 
SQL> REM   End of file.
SQL>

To insert data into your new test tables, execute the t2insertX.sql script.

SQL> @t2insertX
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Create data for EXCHANGE
SQL> set echo on
SQL> INSERT INTO XintRef_p
  2     VALUES (333,' p333 - nonpartitioned data - p');

1 row created.

SQL> INSERT INTO XintRef_c1
  2     VALUES (1333,' p333 - nonpartitioned data - c1',333);

1 row created.

SQL> INSERT INTO XintRef_gc1
  2     VALUES (1333,' p333 - nonpartitioned data - gc1',1333);

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> REM   End of file.
SQL>

To view your new test data, execute the t2selstarX.sql script.

SQL> @t2selstarX
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   View data in standalone tables before exchange
SQL> 
SQL> set echo on
SQL> SELECT * from XintRef_p;

     PKCOL COL2
---------- ------------------------------------------------
       333  p333 - nonpartitioned data - p

1 row selected.

SQL> SELECT * from XintRef_c1;

     PKCOL COL2                          FKCOL
---------- ------------------------------------------------ ----------
      1333  p333 - nonpartitioned data - c1            333

1 row selected.

SQL> SELECT * from XintRef_gc1;

      COL1 COL2                          FKCOL
---------- ------------------------------------------------ ----------
      1333  p333 - nonpartitioned data - gc1              1333

1 row selected.

SQL> 
SQL> PROMPT  End of file.
End of file.
SQL>

Execute a cascading EXCHANGE for the data that reference the primary key 333.
ALTER TABLE intRef_p EXCHANGE PARTITION for (333) with table XintRef_p CASCADE UPDATE indexes;
```
SQL> ALTER TABLE intRef_p 
   EXCHANGE PARTITION for (333) with table XintRef_p 
   CASCADE UPDATE indexes;  2    3  

Table altered.

SQL> 
```

View the standalone tables after the cascading exchange to confirm that the whole dependent table tree has been exchanged. Execute the t2selstarX.sql script.

SQL> @t2selstarX
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   View data in standalone tables before exchange
SQL> 
SQL> set echo on
SQL> SELECT * from XintRef_p;

     PKCOL COL2
---------- ------------------------------------------------
       333  p333 - partitioned data BEFORE exchange - p

1 row selected.

SQL> SELECT * from XintRef_c1;

     PKCOL COL2                          FKCOL
---------- ------------------------------------------------ ----------
      1333  p333 - partitioned data BEFORE exchange - c1       333

1 row selected.

SQL> SELECT * from XintRef_gc1;

      COL1 COL2                          FKCOL
---------- ------------------------------------------------ ----------
      1333  p333 - partitioned data BEFORE exchange - gc1     1333

1 row selected.

SQL> 
SQL> PROMPT  End of file.
End of file.
SQL>

To confirm the exchange, compare this output with the output in step 15. You see the difference in data.

To view the reference-partitioned tables after the cascading exchange, execute the t2selstar1.sql script.

SQL> @t2selstar1
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> col col2 format a48
SQL> REM   View all data for one branch of parent, child, and grandchild
SQL> set echo on
SQL> SELECT * from intRef_p;

     PKCOL COL2
---------- ------------------------------------------------
       333  p333 - nonpartitioned data - p
       999  p999 - partitioned data BEFORE exchange - p

2 rows selected.

SQL> SELECT * from intRef_c1;

     PKCOL COL2                          FKCOL
---------- ------------------------------------------------ ----------
      1333  p333 - nonpartitioned data - c1            333
      1999  p999 - partitioned data BEFORE exchange - c1       999

2 rows selected.

SQL> SELECT * from intRef_gc1;

      COL1 COL2                          FKCOL
---------- ------------------------------------------------ ----------
      1333  p333 - nonpartitioned data - gc1              1333
      1999  p999 - partitioned data BEFORE exchange - gc1     1999

2 rows selected.

SQL> 
SQL> REM   End of file.
SQL>

To remove all test data, execute the t2drop2.sql script.

SQL> @t2drop2
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Drop tables used to show the exchange command
SQL> 
SQL> set echo on
SQL> DROP TABLE intRef_gc1 PURGE;

Table dropped.

SQL> DROP TABLE intRef_c1 PURGE;

Table dropped.

SQL> DROP TABLE intRef_p PURGE;

Table dropped.

SQL> DROP TABLE XintRef_gc1 purge;

Table dropped.

SQL> DROP TABLE XintRef_c1 purge;

Table dropped.

SQL> DROP TABLE XintRef_p purge;

Table dropped.

SQL> 
SQL> PROMPT  End of file.
End of file.
SQL>

In this tutorial section, you learned about reference and interval-reference partitioned tables:

How to simplify application development by enabling the inheritance of the partition maintenance operations from the parent to the child tables
How to use the CASCADE option for TRUNCATE PARTITION and EXCHANGE PARTITION operations.
That the cascade options are off by default so they do not affect compatibility.

3. Moving Partitions Online

Starting with Oracle Database 12c, the ALTER TABLE ... MOVE PARTITION operation functions as a non-blocking online DDL command, while DML operations continue to execute uninterrupted on the partition that is being moved.
Additionally, global indexes are maintained when a partition is moved, so that a manual index rebuild is no longer required. The online partitioning movement removes the read-only state for the actual MOVE PARTITION command.

This image provides a summary of the index types. You will learn more about them in this and the following tutorial sections.

Ensure that your environment variable are set and log in to SQL*Plus as the PART12C user with the oracle_4U password. (Review the Tutorial Setup, if you are not sure about the commands.)

To create a test table with a global and a local index, execute the t3create.sql script.
Note: You can execute a SQL script without specifying the extension because .sql is the default.
Review the output and press ENTER or RETURN when prompted.

SQL> @t3create
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Create a fairly large test table.
SQL> set pause off
SQL> set echo on
SQL> CREATE TABLE pt (col1, col2, col3, col4)
  2  PARTITION by range (col1) interval (150000)
  3  (PARTITION pdml values less than (500000),
  4   PARTITION px values less than (600000),
  5   PARTITION py values less than (700000))
  6  as
  7  SELECT rownum, rownum*mod(rownum,10), 1, 'aaaaaaaaaaaaaaaaaaaaaa'     FROM dual connect by level < 1000000;

Table created.

SQL> PAUSE  Press RETURN ...
Press RETURN ...

SQL> 
SQL> REM   Create a global and a local index on the table.
SQL> CREATE INDEX i1_pt_global on pt (col2);

Index created.

SQL> CREATE INDEX i1_pt_local on pt (col1) local;

Index created.

SQL> 
SQL> set pause off
SQL> REM   End of file.
SQL>

To gain an overview over your just created data, count the rows in the PT table and the partition for 5000. Execute the t3count.sql script.

SQL> @t3count
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Count the rows of the table and the partition to be moved.
SQL> 
SQL> set echo on
SQL> SELECT count(*) from pt;

  COUNT(*)
----------
    999999

1 row selected.

SQL> SELECT count(*) from pt PARTITION for (5000);

  COUNT(*)
----------
    499999

1 row selected.

SQL> 
SQL> REM   End of file.
SQL>

As you see, there are almost one million rows in the PT table and half a million in the partition.

To view the status of the global and local indexes execute the t3ind.sql script.

SQL> @t3ind
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Display status for global and local index
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col index_name format a30
SQL> set echo on
SQL> SELECT index_name, status
  2    FROM user_indexes
  3    WHERE index_name like 'I1_PT_G%';

INDEX_NAME             STATUS
------------------------------ --------
I1_PT_GLOBAL               VALID

1 row selected.

SQL> SELECT index_name, status
  2    FROM user_ind_partitions
  3    WHERE index_name like 'I1_PT_L%';

INDEX_NAME             STATUS
------------------------------ --------
I1_PT_LOCAL            USABLE
I1_PT_LOCAL            USABLE
I1_PT_LOCAL            USABLE
I1_PT_LOCAL            USABLE
I1_PT_LOCAL            USABLE

5 rows selected.

SQL> 
SQL> REM   End of file.
SQL>

As expected, the global index is VALID and the local ones USABLE.

Execute the t3col3.sql script to display and count the values in the COL3 column.

SQL> @t3col3
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Query COL3
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> set echo on
SQL> SELECT col3, count(*)
  2    FROM pt group by col3;

      COL3   COUNT(*)
---------- ----------
     1     999999

1 row selected.

SQL> 
SQL> REM   End of file.

Note: The only column value is "1."

In a different terminal window, execute the tail t3list.log command.

$ tail t3list.log
-- DISCLAIMER:
-- This script is provided for educational purposes only. It is 
-- NOT supported by Oracle World Wide Technical Support.
-- The script has been tested and appears to work as intended.
-- You should always run new scripts initially 
-- on a test instance.

REM   This is a sample output log for the demonstration of
REM       MOVING PARTITIONS ONLINE
$

Optionally in your SQL*Plus session, review the script that you will execute with the following command:

!cat
onlinePartMove.sh

SQL> !cat onlinePartMove.sh
#!/bin/sh
#  -- DISCLAIMER:
#  -- This script is provided for educational purposes only. It is
#  -- NOT supported by Oracle World Wide Technical Support.
#  -- The script has been tested and appears to work as intended.
#  -- You should always run new scripts on a test instance initially.
#
j=1
x=444

do_dml()  
{
sqlplus /nolog << EOF
  connect part12c/oracle_4U
  REM select 'Update starts at '|| to_char(sysdate,'hh24:mi:ss') from dual;
  UPDATE pt set col3=2 where col1= $j;
  REM select 'Updated record '||$j from dual;
  commit;
  end;
/
  select 'Update ends at ' ||to_char(sysdate,'hh24:mi:ss') from dual;
  exit;
EOF
}

do_online_move()
{
sqlplus /nolog << EOF
  connect part12c/oracle_4U
  set echo on
  SELECT 'Online move starts at ' ||to_char(sysdate,'hh24:mi:ss') from dual;
  ALTER TABLE pt move partition for (1000) online update indexes;
  SELECT 'Online move ends at ' ||to_char(sysdate,'hh24:mi:ss') from dual;
  set echo off
  exit;
EOF
}

# main

echo "Let the fun start" >> t3list.log

echo "Will update " $x "records, starting date and time: " date
while [ $j -le $x ]
do
do_dml $j > /dev/null
echo "Updated record " $j "at: " date >> t3list.log
((j++))
done &

sleep 2

do_online_move
wait

SQL>

Note: The script uses the variables j and x. You can adjust the value of x, if the execution of the script is too fast or too slow in your environment.
The variable j is updated in a loop.
While the value of j is less than x, DML operations are performed and a row is written to the t3list.log file.

In your SQL*Plus session execute this script. (As you know, Linux is very case-sensitive, so you must execute the command exactly as indicated.)

!
./onlinePartMove.sh

SQL> ! ./onlinePartMove.sh
Will update  444 records, starting date and time: 
Thu Sep 12 20:09:37 UTC 2013

SQL*Plus: Release 12.1.0.1.0 Production on Thu Sep 12 20:09:39 2013

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

SQL> Connected.
SQL> SQL> 
'ONLINEMOVESTARTSAT'||TO_CHAR(
------------------------------
Online move starts at 20:09:40

SQL> 
Table altered.

SQL> 
'ONLINEMOVEENDSAT'||TO_CHAR(
----------------------------
Online move ends at 20:09:43

SQL> SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Note the time of the online move, in this example, from 9 minutes 40 seconds to 9 minutes 43 seconds.

At any time in the second terminal window, you can re-excute the tail t3list.log command. You see the values changing as long as the onlinePartMove.sh script is executing. Below is a sample output. (Hundreds of rows have been removed to avoid cluttering this tutorial.) You can also use any editor in your environment to see the entire file.

$ tail t3list.log
-- DISCLAIMER:
-- This script is provided for educational purposes only. It is 
-- NOT supported by Oracle World Wide Technical Support.
-- The script has been tested and appears to work as intended.
-- You should always run new scripts initially 
-- on a test instance.

REM   This is a sample output log for the demonstration of
REM       MOVING PARTITIONS ONLINE
Let the fun start
Updated record  1 at: 
Thu Sep 12 20:09:38 UTC 2013
Updated record  2 at: 
Thu Sep 12 20:09:38 UTC 2013
. . .
Updated record  64 at: 
Thu Sep 12 20:09:41 UTC 2013
Updated record  65 at: 
Thu Sep 12 20:09:41 UTC 2013
. . .
Updated record  443 at: 
Thu Sep 12 20:10:00 UTC 2013
Updated record  444 at: 
Thu Sep 12 20:10:00 UTC 2013

Note the updates in the middle section that occured while the partitions are moving online.

Execute the t3col3.sql script to display and count the values in the COL3 column.

SQL> @t3col3
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Query COL3
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> set echo on
SQL> SELECT col3, count(*)
  2    FROM pt group by col3;

      COL3   COUNT(*)
---------- ----------
     1     999555
     2    444

2 rows selected.

SQL> 
SQL> REM   End of file.

Note that the COL3 column has been updated as often as specified by the value x, that is, 444 times in this example.

To view the status of the global and local indexes execute the t3ind.sql script.

SQL> @t3ind
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Display status for global and local index
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col index_name format a30
SQL> set echo on
SQL> SELECT index_name, status
  2    FROM user_indexes
  3    WHERE index_name like 'I1_PT_G%';

INDEX_NAME             STATUS
------------------------------ --------
I1_PT_GLOBAL               VALID

1 row selected.

SQL> SELECT index_name, status
  2    FROM user_ind_partitions
  3    WHERE index_name like 'I1_PT_L%';

INDEX_NAME             STATUS
------------------------------ --------
I1_PT_LOCAL            USABLE
I1_PT_LOCAL            USABLE
I1_PT_LOCAL            USABLE
I1_PT_LOCAL            USABLE
I1_PT_LOCAL            USABLE

5 rows selected.

SQL> 
SQL> REM   End of file.
SQL>

As expected, the global index is VALID and the local ones USABLE.

To remove your test data, execute the t3drop.sql script.

SQL> @t3drop
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Drop test table.
SQL> 
SQL> set echo on
SQL> DROP TABLE pt purge;

Table dropped.

SQL> 
SQL> REM   End of file.
SQL>

In this tutorial section you learned:

How to move a partition online and simultaneously updating the same rows
That the online move is a non-blocking online DDL command, that is, the DML operations continue to execute uninterrupted on the partition that is being moved
That global indexes are maintained when a partition is moved, so that a manual index rebuild is no longer required

4. Maintaining Multiple Partitions

Multipartition maintenance operations enable adding multiple partitions to a table, dropping multiple partitions, merging multiple partitions into one partition, splitting of a single partition into multiple partitions, and truncating multiple partitions using a single SQL data definition language (DDL) statement. For a summary of valid maintenance operations on partitions, subpartitions, and indexes, see the Oracle Database VLDB and Partitioning Guide.

Ensure that your environment variable are set and log in to SQL*Plus as the PART12C user with the oracle_4U password. (Review the Tutorial Setup, if you are not sure about the commands.)

To create a test table with multiple partitions, execute the t4create.sql script.

SQL> @t4create
SQL> CREATE TABLE pt (c1, c2, c3)
  2     PARTITION by range (c1) interval (10)
  3     (PARTITION p1 values less than (11))
  4  as
  5  SELECT rownum, rownum*mod(rownum,10), 'aaaa'
  6     FROM dual connect by level < 95;

Table created.

SQL> 
SQL> REM   End of file.
SQL>

To view the existing number of rows for some of the partitions, execute the t4selstar.sql script.

SQL> @t4selstar
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Count the rows in several partitions.
SQL> 
SQL> set echo on
SQL> SELECT count(*) FROM pt PARTITION for (5);

  COUNT(*)
----------
    10

1 row selected.

SQL> SELECT count(*) FROM pt PARTITION for (55);

  COUNT(*)
----------
    10

1 row selected.

SQL> SELECT count(*) FROM pt PARTITION for (95);

  COUNT(*)
----------
     4

1 row selected.

SQL> SELECT count(*) FROM pt PARTITION for (105);

  COUNT(*)
----------
     0

1 row selected.

SQL> REM   End of file.
SQL>

To view the partitions of the PT table, execute the t4show.sql script.

SQL> @t4show
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Show the partitions of the PT table.
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col partitioned    format a12
SQL> col table_name format a10
SQL> col partition_name format a20
SQL> col high_value format a12
SQL> col index_name format a20
SQL> set pause off
SQL> 
SQL> set echo on
SQL> SELECT  table_name, partition_name, high_value
  2     FROM  user_tab_partitions
  3     WHERE table_name = 'PT'
  4     ORDER BY table_name, partition_position;

TABLE_NAME PARTITION_NAME   HIGH_VALUE
---------- -------------------- ------------
PT     P1           11
PT     SYS_P618     21
PT     SYS_P619     31
PT     SYS_P620     41
PT     SYS_P621     51
PT     SYS_P622     61
PT     SYS_P623     71
PT     SYS_P624     81
PT     SYS_P625     91
PT     SYS_P626     101

10 rows selected.

SQL>

Merge the partitions for 5, 15, and 25 into p30 in one operation. List the partitions to be merged explicitly and in the correct order.
Remember, a range partitioned table has its partitions in sequential order. The upper bound of one partition defines the lower bound of the following partition.
ALTER TABLE pt MERGE PARTITIONS for (5), for (15), for (25) INTO PARTITION p30;
```
SQL> ALTER TABLE pt
  MERGE PARTITIONS for (5), for (15), for (25)
  INTO PARTITION p30;   2    3  

Table altered.

SQL> 
```

To verify that you merged multiple partitions, execute the t4show.sql script.

SQL> @t4show
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Show the partitions of the PT table.
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col partitioned    format a12
SQL> col table_name format a10
SQL> col partition_name format a20
SQL> col high_value format a12
SQL> col index_name format a20
SQL> set pause off
SQL> 
SQL> set echo on
SQL> SELECT  table_name, partition_name, high_value
  2     FROM  user_tab_partitions
  3     WHERE table_name = 'PT'
  4     ORDER BY table_name, partition_position;

TABLE_NAME PARTITION_NAME   HIGH_VALUE
---------- -------------------- ------------
PT     P30          31
PT     SYS_P620     41
PT     SYS_P621     51
PT     SYS_P622     61
PT     SYS_P623     71
PT     SYS_P624     81
PT     SYS_P625     91
PT     SYS_P626     101

8 rows selected.

SQL>

Success. If you compare this output with that of step 4, you see that three partitions are merged into one.

Split the me@t4showrged partition again into the same partitions you had originally. Use P10, P20, and P30 as partition names.
ALTER TABLE pt SPLIT PARTITION p30 INTO (PARTITION p10 values less than (10), PARTITION p20 values less than (20), PARTITION p30);
```
SQL> ALTER TABLE pt SPLIT PARTITION p30 INTO 
  (PARTITION p10 values less than (10), 
   PARTITION p20 values less than (20), 
   PARTITION p30);  2    3    4  

Table altered.

SQL> 
```

To verify your operation, execute the t4show.sql script.

SQL> @t4show
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Show the partitions of the PT table.
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col partitioned    format a12
SQL> col table_name format a10
SQL> col partition_name format a20
SQL> col high_value format a12
SQL> col index_name format a20
SQL> set pause off
SQL> 
SQL> set echo on
SQL> SELECT  table_name, partition_name, high_value
  2     FROM  user_tab_partitions
  3     WHERE table_name = 'PT'
  4     ORDER BY table_name, partition_position;

TABLE_NAME PARTITION_NAME   HIGH_VALUE
---------- -------------------- ------------
PT     P10          10
PT     P20          20
PT     P30          31
PT     SYS_P620     41
PT     SYS_P621     51
PT     SYS_P622     61
PT     SYS_P623     71
PT     SYS_P624     81
PT     SYS_P625     91
PT     SYS_P626     101

10 rows selected.

SQL>

To rename all partitions for readability, execute the t4rename.sql script.

SQL> @t4rename
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Rename all partitions for pt, just for your ease of reading
SQL> 
SQL> set echo on
SQL> ALTER TABLE pt rename PARTITION for (5)  to part10;

Table altered.

SQL> ALTER TABLE pt rename PARTITION for (15) to part20;

Table altered.

SQL> ALTER TABLE pt rename PARTITION for (25) to part30;

Table altered.

SQL> ALTER TABLE pt rename PARTITION for (35) to part40;

Table altered.

SQL> ALTER TABLE pt rename PARTITION for (45) to part50;

Table altered.

SQL> ALTER TABLE pt rename PARTITION for (55) to part60;

Table altered.

SQL> ALTER TABLE pt rename PARTITION for (65) to part70;

Table altered.

SQL> ALTER TABLE pt rename PARTITION for (75) to part80;

Table altered.

SQL> ALTER TABLE pt rename PARTITION for (85) to part90;

Table altered.

SQL> ALTER TABLE pt rename PARTITION for (95) to part100;

Table altered.

SQL> REM   End of file.
SQL>

To review the new partition names, execute the t4show.sql script.

SQL> @t4show
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Show the partitions of the PT table.
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col partitioned    format a12
SQL> col table_name format a10
SQL> col partition_name format a20
SQL> col high_value format a12
SQL> col index_name format a20
SQL> set pause off
SQL> 
SQL> set echo on
SQL> SELECT  table_name, partition_name, high_value
  2     FROM  user_tab_partitions
  3     WHERE table_name = 'PT'
  4     ORDER BY table_name, partition_position;

TABLE_NAME PARTITION_NAME   HIGH_VALUE
---------- -------------------- ------------
PT     PART10       10
PT     PART20       20
PT     PART30       31
PT     PART40       41
PT     PART50       51
PT     PART60       61
PT     PART70       71
PT     PART80       81
PT     PART90       91
PT     PART100      101

10 rows selected.

SQL>

As mentioned above, the merging of multiple partitions by explicitly specifying them requires a sorted order for range and interval.
To produce a merge error, execute the t4merge_error.sql script.

SQL> @t4merge_error
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Attempt merging partitions, expect an error.
SQL> 
SQL> set echo on
SQL> ALTER TABLE pt MERGE PARTITIONS part10, part30, part20
  2    INTO PARTITION p30;
ALTER TABLE pt MERGE PARTITIONS part10, part30, part20
                                        *
ERROR at line 1:
ORA-14274: partitions being merged are not adjacent


SQL> REM   End of file.
SQL>

Note that the PART30 partition is not in the correct sequential order and produces the ORA-14274 error.

With the syntax new to Oracle Database 12c, perform a multi-partition merge operation. Either execute the t4merge_ok.sql script or enter the following command:

ALTER
TABLE pt
  MERGE PARTITIONS part10
to part30
  INTO PARTITION part30;

SQL> @t4merge_ok
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Correctly merge partitions.
SQL> 
SQL> set echo on
SQL> ALTER TABLE pt
  2    MERGE PARTITIONS part10 to part30
  3    INTO PARTITION part30;

Table altered.

SQL> REM   End of file.
SQL>

To view your current partitions, execute the t4show.sql script.

SQL> @t4show
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Show the partitions of the PT table.
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col partitioned    format a12
SQL> col table_name format a10
SQL> col partition_name format a20
SQL> col high_value format a12
SQL> col index_name format a20
SQL> set pause off
SQL> 
SQL> set echo on
SQL> SELECT  table_name, partition_name, high_value
  2     FROM  user_tab_partitions
  3     WHERE table_name = 'PT'
  4     ORDER BY table_name, partition_position;

TABLE_NAME PARTITION_NAME   HIGH_VALUE
---------- -------------------- ------------
PT     PART30       31
PT     PART40       41
PT     PART50       51
PT     PART60       61
PT     PART70       71
PT     PART80       81
PT     PART90       91
PT     PART100      101

8 rows selected.

SQL>

To perform a multi-spit operation, execute the t4split.sql script. With this step your prepare partitions to be dropped.

SQL> @t4split
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Split partitions.
SQL> 
SQL> set echo on
SQL> ALTER TABLE pt
  2    SPLIT PARTITION for (5) INTO
  3    (PARTITION pa values less than (1),
  4     PARTITION pb values less than (2),
  5     PARTITION pc values less than (3),
  6     PARTITION pd values less than (4),
  7     PARTITION pe);

Table altered.

SQL> REM   End of file.

To view your current partitions, execute the t4show.sql script.

SQL> @t4show
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Show the partitions of the PT table.
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col partitioned    format a12
SQL> col table_name format a10
SQL> col partition_name format a20
SQL> col high_value format a12
SQL> col index_name format a20
SQL> set pause off
SQL> 
SQL> set echo on
SQL> SELECT  table_name, partition_name, high_value
  2     FROM  user_tab_partitions
  3     WHERE table_name = 'PT'
  4     ORDER BY table_name, partition_position;

TABLE_NAME PARTITION_NAME   HIGH_VALUE
---------- -------------------- ------------
PT     PA           1
PT     PB           2
PT     PC           3
PT     PD           4
PT     PE           31
PT     PART40       41
PT     PART50       51
PT     PART60       61
PT     PART70       71
PT     PART80       81
PT     PART90       91
PT     PART100      101

12 rows selected.

SQL>

To drop multiple partitions in one operation, execute the following command.
Note that partitions do not have to be specified in sequential order.
ALTER TABLE pt DROP partitions for (0), for (2), for (1);
```
SQL> ALTER TABLE pt 
  DROP partitions for (0), for (2), for (1);   2  

Table altered.

SQL> 
```
FAQ Notes:
- Dropped partitions are NOT in the recycle bin.
- Dropped tables are in the recycle bin depending on your recycle bin settings.
- Purged tables are never in the recycle bin.

To verify the operation and view your current partitions, execute the t4show.sql script.

SQL> @t4show 
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Show the partitions of the PT table.
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col partitioned    format a12
SQL> col table_name format a10
SQL> col partition_name format a20
SQL> col high_value format a12
SQL> col index_name format a20
SQL> set pause off
SQL> 
SQL> set echo on
SQL> SELECT  table_name, partition_name, high_value
  2     FROM  user_tab_partitions
  3     WHERE table_name = 'PT'
  4     ORDER BY table_name, partition_position;

TABLE_NAME PARTITION_NAME   HIGH_VALUE
---------- -------------------- ------------
PT     PD           4
PT     PE           31
PT     PART40       41
PT     PART50       51
PT     PART60       61
PT     PART70       71
PT     PART80       81
PT     PART90       91
PT     PART100      101

9 rows selected.

SQL>

Note: The PART100 partition has the high value of 101.

To understand the fast-split operation, first review the header file and header block for the PART100 partition. To view information in DBA_SEGMENTS, execute the t4seg100.sql script.

SQL> @t4seg100
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   View header_file and Header_block number in DBA_SEGMENTS.
SQL> col segment_name      format a15
SQL> col partition_name    format a15
SQL> col header_file       format 9999999999
SQL> col header_block      format 9999999999
SQL> set echo on
SQL> SELECT segment_name, partition_name, header_file, header_block
  2    FROM dba_segments
  3    WHERE segment_name='PT' and partition_name='PART100';

SEGMENT_NAME    PARTITION_NAME  HEADER_FILE HEADER_BLOCK
--------------- --------------- ----------- ------------
PT      PART100           6     9489

1 row selected.


SQL>

To perform the fast-split operation and create the PART95, PART96, PART97, and PART 101 partitions, execute the t4fastsplit.sql script.

SQL> @t4fastsplit
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Perform fast split.
SQL> 
SQL> set echo on
SQL> ALTER TABLE pt
  2    SPLIT PARTITION part100 INTO
  3    (PARTITION part95 values less than (95),
  4     PARTITION part96 values less than (96),
  5     PARTITION part97 values less than (97),
  6     PARTITION part101);

Table altered.

SQL> REM   End of file
SQL>

To view the segment information, for example for the PART95 partion, execute the t4seg95.sql script.

SQL> @t4seg95
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   View header_file and Header_block number in DBA_SEGMENTS.
SQL> col segment_name      format a15
SQL> col partition_name    format a15
SQL> col header_file       format 9999999999
SQL> col header_block      format 9999999999
SQL> set echo on
SQL> SELECT segment_name, partition_name, header_file, header_block
  2    FROM dba_segments
  3    WHERE segment_name='PT' and partition_name='PART95';

SEGMENT_NAME    PARTITION_NAME  HEADER_FILE HEADER_BLOCK
--------------- --------------- ----------- ------------
PT      PART95            6     9489

1 row selected.

SQL> 
SQL>

To verify the split and view your current partitions, execute the t4show.sql script.

SQL> @t4show
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Show the partitions of the PT table.
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col partitioned    format a12
SQL> col table_name format a10
SQL> col partition_name format a20
SQL> col high_value format a12
SQL> col index_name format a20
SQL> set pause off
SQL> 
SQL> set echo on
SQL> SELECT  table_name, partition_name, high_value
  2     FROM  user_tab_partitions
  3     WHERE table_name = 'PT'
  4     ORDER BY table_name, partition_position;

TABLE_NAME PARTITION_NAME   HIGH_VALUE
---------- -------------------- ------------
PT     PD           4
PT     PE           31
PT     PART40       41
PT     PART50       51
PT     PART60       61
PT     PART70       71
PT     PART80       81
PT     PART90       91
PT     PART95       95
PT     PART96       96
PT     PART97       97
PT     PART101      101

12 rows selected.

SQL>

Because you cannot explicitly add partitions to an interval-partitioned table, alter the table to be partionted by range. To modify the PT table, execute the following command:
ALTER TABLE pt set interval();
```
SQL> ALTER TABLE pt set interval();

Table altered.

SQL> 
```

Alter the table and add three partitions: PN1, PN2, and PN3. To modify the PT table, execute the t4alter.sql script.

SQL> @t4alter
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Add multiple partitions to your altered table.
SQL> 
SQL> set echo on
SQL> ALTER TABLE pt ADD
  2    PARTITION pn1 values less than (102),
  3    PARTITION pn2 values less than (104),
  4    PARTITION pn3 values less than (200) compress for oltp;

Table altered.

SQL> REM   End of file
SQL>

To view your current partitions, execute the t4show.sql script.

SQL> @t4show
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Show the partitions of the PT table.
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col partitioned    format a12
SQL> col table_name format a10
SQL> col partition_name format a20
SQL> col high_value format a12
SQL> col index_name format a20
SQL> set pause off
SQL> 
SQL> set echo on
SQL> SELECT  table_name, partition_name, high_value
  2     FROM  user_tab_partitions
  3     WHERE table_name = 'PT'
  4     ORDER BY table_name, partition_position;

TABLE_NAME PARTITION_NAME   HIGH_VALUE
---------- -------------------- ------------
PT     PD           4
PT     PE           31
PT     PART40       41
PT     PART50       51
PT     PART60       61
PT     PART70       71
PT     PART80       81
PT     PART90       91
PT     PART95       95
PT     PART96       96
PT     PART97       97
PT     PART101      101
PT     PN1          102
PT     PN2          104
PT     PN3          200

15 rows selected.

SQL>

To cleanup the test data for this topic, execute the t4drop.sql script.

SQL> @t4drop
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   Cleanup test data.
SQL> 
SQL> set echo on
SQL> DROP TABLE pt purge;

Table dropped.

SQL> REM   End of file
SQL>

In this tutorial section you learned:

How to perform multipartition maintenance operations by using a single SQL command
How to merge multiple partitions into one partition
How to split a single partition into multiple partitions
That merging multiple partitions of range and interval-partitioned tables requires the partitions to be specified in sequential order
How to drop mulitple partions from a table
How to add multiple partitions to a range-partitioned table

5. Maintaining Global Indexes Asynchronously

The maintenance of global indexes can be decoupled from the DROP and TRUNCATE PARTITION operation and delayed to off-peak time without making the global index unusable. The advantage is that the original DROP and TRUNCATE PARTITION operation can proceed faster because the index maintenance occurs later.

Ensure that your environment variable are set and log in to SQL*Plus as the PART12C user with the oracle_4U password. (Review the Tutorial Setup, if you are not sure about the commands.)

To create the PT test table which will be used to demonstrate Oracle Database 12c funtionality, execute the t5create12.sql script. (Note: SQL*Plus uses the default .sql suffix for executing a script file.)

SQL> @t5create12
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> REM   Create test table
SQL> 
SQL> set echo on
SQL> CREATE TABLE pt (col1, col2, col3)
  2   PARTITION by range (col1) interval (100000)
  3  (PARTITION p0 values less than (1),
  4   PARTITION p1 values less than (10),
  5   PARTITION px values less than (99000),
  6   PARTITION py values less than (100000))
  7  as
  8  SELECT rownum, rownum*mod(rownum,10), 'aaaaaaaaaaaaaaaaaaaaaa'
  9    FROM dual connect by level < 100000;

Table created.

SQL> REM   End of file
SQL>

To insert rows into the PT table, execute the t5ins12.sql script.

SQL> @t5ins12
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> REM   Insert rows into test table
SQL> set echo on
SQL> INSERT /*+ append nologging */ into pt SELECT * FROM pt;

99999 rows created.

SQL> COMMIT;

Commit complete.

SQL> INSERT /*+ append nologging */ into pt SELECT * FROM pt;

199998 rows created.

SQL> COMMIT;

Commit complete.

SQL> INSERT /*+ append nologging */ into pt SELECT * FROM pt;

399996 rows created.

SQL> COMMIT;

Commit complete.

SQL> INSERT /*+ append nologging */ into pt SELECT * FROM pt;

799992 rows created.

SQL> COMMIT;

Commit complete.

SQL> REM   End of file
SQL>

To create a global index on the COL2 column of the PT table, execute the following command:
CREATE INDEX i1_pt on pt (col2);
```
SQL> CREATE INDEX i1_pt on pt (col2);

Index created.

SQL>
```

For comparison purpose create and use the PT_11G table which has the same structure and volume. To create the 11g-style test table, execute the t5create11.sql script.

SQL> @t5create11
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> REM   Create 11g-style test table
SQL> set echo on
SQL> CREATE TABLE pt_11g (col1, col2, col3)
  2   PARTITION by range (col1) interval (100000)
  3  (PARTITION p0 values less than (1),
  4   PARTITION p1 values less than (10),
  5   PARTITION px values less than (99000),
  6   PARTITION py values less than (100000))
  7  as
  8   SELECT * FROM pt;

Table created.

SQL> REM   End of file
SQL>

To create an index on the PT_11G table, execute the t5index11.sql script.

SQL> @t5index11
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> REM   Create index on 11g-style test table
SQL> set echo on
SQL> CREATE INDEX i1_pt_11g on pt_11g (col2);

Index created.

SQL> REM   End of file
SQL>

To count the number of rows in your test tables and partitions, execute the t5selstar.sql script.

SQL> @t5selstar
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> REM   Count test tables and partitions
SQL> set echo on
SQL> SELECT count(*) FROM pt;

  COUNT(*)
----------
   1599984

1 row selected.

SQL> SELECT count(*) FROM pt_11g;

  COUNT(*)
----------
   1599984

1 row selected.

SQL> SELECT count(*) FROM pt PARTITION for (9999);

  COUNT(*)
----------
   1583840

1 row selected.

SQL> SELECT count(*) FROM pt_11g PARTITION for (9999);

  COUNT(*)
----------
   1583840

1 row selected.

SQL> REM   End of file
SQL>

Note: Both test partitions to be dropped have about one and a half million rows.The test tables have slightly more.

To display the index status, execute the t5orphan.sql script.

SQL> @t5orphan
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> REM   Show index status
SQL> set echo on
SQL> SELECT index_name, status, orphaned_entries
  2    FROM user_indexes
  3    WHERE index_name like 'I1_PT%';

INDEX_NAME             STATUS   ORPHANED_ENTRIES
------------------------------ -------- --------------------
I1_PT_11G              VALID    NO
I1_PT                  VALID    NO

2 rows selected.

SQL> REM   End of file
SQL>

Note: There are currently no orphaned entries.

To demonstrate the 11g behavior of dropping a partition, execute the t5alter11.sql script.

SQL> @t5alter11
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> REM   Change to 11g-style behavior
SQL> set echo off

Session altered.

SQL> REM  End of file
SQL>

Time and compare the DROP PARTITION operation for both the 11g and the 12c operations. To drop an 11g-style partition, execute the t5drop11.sql script.

SQL> @t5drop11
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> REM
SQL> set timing on
SQL> set echo on
SQL> ALTER TABLE pt_11g
  2   DROP PARTITION for (9999) update indexes;

Table altered.

Elapsed: 00:00:04.91
SQL> set timing off
SQL>

To count the remaining rows in the PT_11G table, execute the t5sel11.sql script.

SQL> @t5sel11
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> REM
SQL> set echo on
SQL> SELECT count(*) FROM pt_11g;

  COUNT(*)
----------
     16144

1 row selected.

SQL>

To demonstrate the 12c behavior of dropping a partition, first execute the t5alter12.sql script.

SQL> @t5alter12
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> REM   Change to 12c-style behavior
SQL> set echo off

Session altered.

SQL> REM   End of file
SQL>

To drop an 12c-style partition, execute the t5drop12.sql script.

SQL> @t5drop12
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> 
SQL> set timing on
SQL> set echo on
SQL> ALTER TABLE pt
  2   DROP PARTITION for (9999) update indexes;

Table altered.

Elapsed: 00:00:00.02
SQL> set timing off
SQL>

Compare the timing of this step with the output of step 10. Although your own values may be different, they should show that the dropping a partition in Oracle Database 12c is notably faster than in an 11g-style operation. The DROP operation is faster because it is a metadata-only operation.

To count the remaining rows in the PT table, execute the t5sel12.sql script.

SQL> @t5sel12
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> 
SQL> set echo on
SQL> SELECT count(*) FROM pt;

  COUNT(*)
----------
     16144

1 row selected.

SQL>

To display the index status, execute the t5orphan.sql script.

SQL> @t5orphan
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> REM   Show index status
SQL> set echo on
SQL> SELECT index_name, status, orphaned_entries
  2    FROM user_indexes
  3    WHERE index_name like 'I1_PT%';

INDEX_NAME             STATUS   ORPHANED_ENTRIES
------------------------------ -------- --------------------
I1_PT_11G              VALID    NO
I1_PT                  VALID    YES

2 rows selected.

SQL> REM   End of file
SQL>

Note that the I1_PT index has orphaned entries. This is normal for an 12c-style index. In the next step you verify that this index is really usable.

To verify that both indexes are usable, execute the t5use.sql script.

SQL> @t5use
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> 
SQL> set echo on
SQL> PROMPT  Demonstrate that 11g index can be used
Demonstrate that 11g index can be used
SQL> SELECT count(*) FROM pt_11g WHERE col2=5;

  COUNT(*)
----------
     0

1 row selected.

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dgks77hgns2ra, child number 0
-------------------------------------
SELECT count(*) FROM pt_11g WHERE col2=5

Plan hash value: 1826084569

-------------------------------------------------------------------------------
| Id  | Operation     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     5 |        |          |
|*  2 |   INDEX RANGE SCAN| I1_PT_11G |    19 |    95 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL2"=5)


19 rows selected.

SQL> 
SQL> PROMPT  Demonstrate that 12c index can be used
Demonstrate that 12c index can be used
SQL> SELECT count(*) FROM pt WHERE col2=5;

  COUNT(*)
----------
     0

1 row selected.

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4x9wgf6m2m139, child number 0
-------------------------------------
SELECT count(*) FROM pt WHERE col2=5

Plan hash value: 3227303916

---------------------------------------------------------------------------
| Id  | Operation     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |   |   |   | 3 (100)|      |
|   1 |  SORT AGGREGATE   |   | 1 | 5 |        |      |
|*  2 |   INDEX RANGE SCAN| I1_PT | 1 | 5 | 3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL2"=5)
       filter(TBL$OR$IDX$PART$NUM(,0,8,0,"PT".ROWID)=1)


20 rows selected.

SQL> set timing on
SQL>

Indexes are automatically maintained by a background scheduler job in non-peak times because it can be a time-consuming operation. So this step is not part of a regular DBA task. You learn about it in this tutorial just in case you need to perform manual index maintenance.
By executing step 13, you created an artificially high percentage of orphaned entries. If you have a high number of orphaned entries, you could use the INDEX ONLINE REBUILD functionality (which should be faster for your test case).
But for your learning purposes, you will cleanup the orphaned entries with the following command:
ALTER INDEX I1_PT COALESCE CLEANUP;
```
SQL> ALTER INDEX I1_PT COALESCE CLEANUP; 

Index altered.

Elapsed: 00:00:20.70

SQL> 
```
As an alternative, you could use exec dbms_part.cleanup_gidx() . This package uses the same code path as the ALTER INDEX...COALESCE CLEANUP command, so you can expect that it will take the same amount of time.

To drop your test tables, execute the t5drop.sql script.

SQL> @t5drop
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For ASYNCHRONOUS GLOBAL INDEX MAINTENANCE
SQL> REM   Drop test tables
SQL> set timing off
SQL> set echo on
SQL> DROP TABLE pt_11g purge;

Table dropped.

SQL> DROP TABLE pt purge;

Table dropped.

SQL> 
SQL> REM   End of file
SQL>

In this tutorial section you learned:

That with Oracle Database 12c the maintenance of global indexes is decoupled and delayed from the DROP and TRUNCATE PARTITION operations
That these operations are faster because they are metadata-only operations
That a scheduler job automatically maintains indexes during non-peak times
How to manually perform an index cleanup operation, if needed.

6. Using Partial Indexes

To provide more flexibility with index creation, you can create local and global indexes on a subset of the table partitions. This operation is supported by using a default table indexing property. When a table is created or altered, a default indexing property can be specified for the table or its partitions.

Ensure that your environment variable are set and log in to SQL*Plus as the PART12C user with the oracle_4U password. (Review the Tutorial Setup, if you are not sure about the commands.)

Create a test table with partial indexing for some partitions and not for others. To create the PT test table, execute the t6create.sql script. (Note: SQL*Plus uses the default .sql suffix for executing a script file.)

SQL> @t6create
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> set timing off
SQL> set lines 400 pages 1000
SQL> set echo on
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> 
SQL> CREATE TABLE pt (col1, col2, col3, col4)
  2   INDEXING OFF
  3  PARTITION by range (col1)
  4   interval (1000)
  5  (PARTITION p100 values less than (101) indexing on,
  6   PARTITION p200 values less than (201) indexing on,
  7   PARTITION p300 values less than (301) indexing on)
  8  as
  9    SELECT round(rownum/100),
 10    dbms_random.value(1,1000),
 11    'aaaaaa',
 12    'bbb'||rownum
 13    FROM dual connect by level < 55000;

Table created.

SQL> REM   End of file
SQL>

The table and partiton metadata will be applied to all indexes that will be defined as partial indexes (this is not the default).

Create a local index on the COL4 column. This will be the default full index. Create also a local index on the COL1 column as a partial index. For learning purposes, it is recommended to enter the following commands:

CREATE
INDEX i_l_partpt on pt(col1) LOCAL INDEXING PARTIAL;
CREATE INDEX i_l_pt on pt(col4) LOCAL;

But because this is not a typing exercise, you could alternatively use the t6create_ind.sql file to create these indexes.

SQL> @t6create_ind 
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> 
SQL> CREATE INDEX i_l_partpt on pt(col1) LOCAL INDEXING PARTIAL;

Index created.

SQL> CREATE INDEX i_l_pt on pt(col4) LOCAL;

Index created.

SQL> 
SQL> REM   End of file
SQL>

Create a full global index on the COL3 column and a partial global index on the COL2 column. To create these indexes, execute the t6create_g.sql script.

SQL> @t6create_g
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> 
SQL> CREATE INDEX i_g_partpt on pt(col2) INDEXING PARTIAL;

Index created.

SQL> CREATE INDEX i_g_pt on pt(col3);

Index created.

SQL> 
SQL> REM   End of file
SQL>

To view the partitions and their INDEXING attributes, execute the t6show_parti.sql script.

SQL> @t6show_parti
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> 
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col partition_name format a15
SQL> col high_value format a15
SQL> col indexing   format a10
SQL> set echo on
SQL> 
SQL> SELECT partition_name, high_value, indexing
  2    FROM user_tab_partitions
  3    WHERE table_name='PT'
  4    ORDER BY partition_position;

PARTITION_NAME  HIGH_VALUE  INDEXING
--------------- --------------- ----------
P100        101     ON
P200        201     ON
P300        301     ON
SYS_P685    1301        OFF

4 rows selected.

SQL> 
SQL> REM   End of file
SQL>

To view the index status and to confirm that there are currently no orphaned entires, execute the t6show_ind.sql script.

SQL> @t6show_ind
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> 
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col index_name format a15
SQL> col status     format a10
SQL> col indexing   format a10
SQL> set echo on
SQL> 
SQL> SELECT index_name, indexing, status, orphaned_entries
  2    FROM user_indexes
  3    WHERE index_name in
  4    ('I_G_PARTPT','I_G_PT','I_L_PARTPT','I_L_PT');

INDEX_NAME  INDEXING   STATUS     ORPHANED_ENTRIES
--------------- ---------- ---------- --------------------
I_G_PARTPT  PARTIAL    VALID      NO
I_G_PT      FULL       VALID      NO
I_L_PARTPT  PARTIAL    N/A        NO
I_L_PT      FULL       N/A        NO

4 rows selected.

SQL> 
SQL> REM   End of file
SQL>

Partial indexing for local indexes is implemented through the UNUSABLE state of a partition, more precisely: by using the UNUSABLE value as displayed in the STATUS column of the USER_IND_PARTITIONS view.
Note that you can explicitly set an index partition to this state, but unlike the partial indexing state (as defined on the partition level), the UNUSABLE state by itself is not preserved for the partition-index maintenance operations.

To view the status of the index partitions, execute the t6show_ind_parti.sql script.

SQL> @t6show_ind_parti
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> 
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> col index_name format a15
SQL> col partition_name format a15
SQL> col status     format a10
SQL> col indexing   format a10
SQL> set echo on
SQL> 
SQL> SELECT index_name, partition_name, status, null
  2    FROM user_ind_partitions
  3    WHERE index_name in ('I_L_PARTPT','I_L_PT');

INDEX_NAME  PARTITION_NAME  STATUS     N
--------------- --------------- ---------- -
I_L_PT      SYS_P687    USABLE
I_L_PT      P300        USABLE
I_L_PT      P200        USABLE
I_L_PT      P100        USABLE
I_L_PARTPT  SYS_P686    UNUSABLE
I_L_PARTPT  P300        USABLE
I_L_PARTPT  P200        USABLE
I_L_PARTPT  P100        USABLE

8 rows selected.

SQL> 
SQL> REM   End of file
SQL>

To create statistics for the optimizer, execute the t6stats.sql script.

SQL> @t6stats
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> 
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> set echo on
SQL> 
SQL> exec dbms_stats.gather_table_stats('PART12C','PT');

PL/SQL procedure successfully completed.

SQL> 
SQL> REM   End of file
SQL>

Modify the table to be a range-partitioned table. (For background info, see MyOracle Support note 14558315.) To alter the table, execute the t6alter.sql script.

SQL> @t6alter
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> 
SQL> ALTER TABLE pt set interval ();

Table altered.

SQL> 
SQL> REM   End of file
SQL>

Demonstrate a query that benefits from the partial global index and only uses a scan for the missing information, touching the partitions which are not part of the partial global index.To show the execution plan (the COL2 column is used in a WHERE clause), execute the t6xplan1.sql script.

SQL> @t6xplan1
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> 
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> set echo on
SQL> EXPLAIN PLAN for SELECT count(*) FROM pt WHERE col2 = 3;

Explained.

SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3689440839

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |        |     1 |    22 |    54   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                   |        |     1 |    22 |        |      |       |       |
|   2 |   VIEW                        | VW_TE_2    |     2 |       |    54   (0)| 00:00:01 |       |       |
|   3 |    UNION-ALL                      |        |       |       |        |      |       |       |
|*  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PT     |     1 |    26 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  5 |      INDEX RANGE SCAN                 | I_G_PARTPT |     1 |       |     1   (0)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE SINGLE            |        |     1 |    26 |    51   (0)| 00:00:01 |     4 |     4 |
|*  7 |      TABLE ACCESS FULL                | PT     |     1 |    26 |    51   (0)| 00:00:01 |     4 |     4 |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("PT"."COL1"<301)
   5 - access("COL2"=3)
   7 - filter("COL2"=3)

21 rows selected.

SQL> 
SQL> REM   End of file
SQL>

Demonstrate a query that uses the partial local index. To show the execution plan (the COL1 column is used in a WHERE clause), execute the t6xplan2.sql script.

SQL> @t6xplan2
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> 
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> set echo on
SQL> EXPLAIN PLAN for SELECT count(*) FROM pt WHERE col1 <50;

Explained.

SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1756007265

------------------------------------------------------------------------------------------------------
| Id  | Operation       | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     1 |     3 |     7   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE     |        |     1 |     3 |        |      |       |       |
|   2 |   PARTITION RANGE SINGLE|        |  5025 | 15075 |     7   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX FAST FULL SCAN | I_L_PARTPT |  5025 | 15075 |     7   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("COL1"<50)

15 rows selected.

SQL> 
SQL> REM   End of file
SQL>

This simple example does not need to access any partitions which are not indexed.

To view the index status for the P300 partition, execute the t6union300.sql script.

SQL> @t6union300
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> col index_name format a15
SQL> col partitiom_name format a15
SQL> col indexing   format a15
SQL> col status     format a10
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> set echo on
SQL> 
SQL> SELECT index_name, partition_name, status, null as "ORPHANS"
  2    FROM user_ind_partitions
  3    WHERE index_name in ('I_L_PARTPT','I_L_PT')
  4    and partition_name='P300'
  5  UNION ALL
  6    SELECT index_name, indexing, status, orphaned_entries
  7     FROM  user_indexes
  8     WHERE index_name in ('I_G_PARTPT','I_G_PT');

INDEX_NAME  PARTITION_NAME  STATUS     ORP
--------------- --------------- ---------- ---
I_L_PARTPT  P300        USABLE
I_L_PT      P300        USABLE
I_G_PT      FULL        VALID      NO
I_G_PARTPT  PARTIAL     VALID      NO

4 rows selected.

SQL> REM   End of file
SQL>

Note the local index status is USABLE, the global one is VALID.

For your learning purposes, create orphaned entries (by disabling indexing for a partition) and then check the index status. To modify the partition, execute the t6off.sql script.

SQL> @t6off
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> 
SQL> ALTER TABLE pt modify partition p300 INDEXING OFF;

Table altered.

SQL> 
SQL> REM   End of file
SQL>

To view the current index status for the P300 partition, execute the t6union300.sql script again.

SQL> @t6union300
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> col index_name format a15
SQL> col partitiom_name format a15
SQL> col indexing   format a15
SQL> col status     format a10
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> set echo on
SQL> 
SQL> SELECT index_name, partition_name, status, null as "ORPHANS"
  2    FROM user_ind_partitions
  3    WHERE index_name in ('I_L_PARTPT','I_L_PT')
  4    and partition_name='P300'
  5  UNION ALL
  6    SELECT index_name, indexing, status, orphaned_entries
  7     FROM  user_indexes
  8     WHERE index_name in ('I_G_PARTPT','I_G_PT');

INDEX_NAME  PARTITION_NAME  STATUS     ORP
--------------- --------------- ---------- ---
I_L_PARTPT  P300        UNUSABLE
I_L_PT      P300        USABLE
I_G_PT      FULL        VALID      NO
I_G_PARTPT  PARTIAL     VALID      YES

4 rows selected.

SQL> REM   End of file
SQL>

Note the index status for relevant indexes and partitions shows:

There is no change for full indexes.
The local partition for the partial index is now UNUSABLE.
The partial global index has orphaned entries.
The orphaned entries do not have any impact on the validity of an index.

To cleanup orphaned entries, execute the t6ind_cleanup.sql script.

SQL> @t6ind_cleanup
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> 
SQL> ALTER INDEX i_g_partpt coalesce cleanup;

Index altered.

SQL> REM   End of file
SQL>

To verify the result of this command, execute the t6union300.sql script again.

SQL> @t6union300
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> col index_name format a15
SQL> col partitiom_name format a15
SQL> col indexing   format a15
SQL> col status     format a10
SQL> set lines 400 pages 1000
SQL> set feedback 1
SQL> set echo on
SQL> 
SQL> SELECT index_name, partition_name, status, null as "ORPHANS"
  2    FROM user_ind_partitions
  3    WHERE index_name in ('I_L_PARTPT','I_L_PT')
  4    and partition_name='P300'
  5  UNION ALL
  6    SELECT index_name, indexing, status, orphaned_entries
  7     FROM  user_indexes
  8     WHERE index_name in ('I_G_PARTPT','I_G_PT');

INDEX_NAME  PARTITION_NAME  STATUS     ORP
--------------- --------------- ---------- ---
I_L_PARTPT  P300        UNUSABLE
I_L_PT      P300        USABLE
I_G_PT      FULL        VALID      NO
I_G_PARTPT  PARTIAL     VALID      NO

4 rows selected.

SQL> REM   End of file
SQL>

Note: There are no longer orphaned entries.

To cleanup your test data, execute the t6drop.sql script.

SQL> @t6drop
SQL> -- DISCLAIMER:
SQL> -- This script is provided for educational purposes only. It is
SQL> -- NOT supported by Oracle World Wide Technical Support.
SQL> -- The script has been tested and appears to work as intended.
SQL> -- You should always run new scripts initially
SQL> -- on a test instance.
SQL> 
SQL> REM   For PARTIAL INDEXING
SQL> 
SQL> set echo on
SQL> DROP TABLE pt purge;

Table dropped.

SQL> 
SQL> REM   End of file
SQL>

In this tutorial section you learned:

How to create local and global indexes on a subset of the table partitions
That this operation is supported by using a default table indexing property
That a default indexing property can be specified for the table or its partitions
How to 'artificially' create orphaned entries for your learning purpose.
That orphaned entries do not have any impact on the validity of an index

Summary

In this tutorial, you learned about what is new in Oracle Database 12c partitioning:
1. Interval Reference Partitioning

How to create tables that use parent tables for reference partitioning

How partitions are automatically created when you insert data

That only the necessary partitions are created

That a child partition inherits its name from its direct parent

How to rename a partition

That renaming a partition does not affect other (dependent) partitions

2. Cascading TRUNCATE and EXCHANGE Operations

How to simplify application development by enabling the inheritance of the partition maintenance operations from the parent to the child tables

How to use the CASCADE option for TRUNCATE PARTITION and EXCHANGE PARTITION operations.

That the cascade options are off by default so they do not affect compatibility

3. Moving Partitions Online

How to move a partition online and simultaneously updating the same rows

That the online move is a non-blocking online DDL command, that is, the DML operations continue to execute uninterrupted on the partition that is being moved

That global indexes are maintained when a partition is moved, so that a manual index rebuild is no longer required

4. Maintaining Multiple Partitions

How to perform multipartition maintenance operations by using a single SQL command

How to merge multiple partitions into one partition

How to split a single partition into multiple partitions

That merging multiple partitions of range and interval-partitioned tables requires the partitions to be specified in sequential order

How to drop mulitple partions from a table

How to add multiple partitions to a range-partitioned table

5. Maintaining Global Indexes Asynchronously

That with Oracle Database 12c the maintenance of global indexes is decoupled and delayed from the DROP and TRUNCATE PARTITION operations

That these operations are faster because they are metadata-only operations

That a scheduler job automatically maintains indexes during non-peak times

How to manually perform an index cleanup operation, if needed.

6. Using Partial Indexes

How to create local and global indexes on a subset of the table partitions

That this operation is supported by using a default table indexing property

That a default indexing property can be specified for the table or its partitions

How to 'artificially' create orphaned entries for your learning purposes

That orphaned entries do not have any impact on the validity of an index

Oracle DBA Information at one place

Search This Blog

Oracle Database 12c: What's New in Partitioning?

Oracle Database 12c: What's New in Partitioning?

Introduction

Hardware and Software Requirements

The following is a list of hardware and software requirements:

Oracle Database 12c

Linux operating system (especially for topic 3 where you run a Linux script)

Prerequisites

Assumption

What You Already Know

Tutorial Setup

1. Interval Reference Partitioning

2. Cascading TRUNCATE and EXCHANGE Operations

3. Moving Partitions Online

4. Maintaining Multiple Partitions

5. Maintaining Global Indexes Asynchronously

6. Using Partial Indexes

Summary

Comments

Post a Comment

Oracle Database 12c: What's New in Partitioning?

Oracle Database 12c: What's New in Partitioning?

Introduction

Hardware and Software Requirements

The following is a list of hardware and software requirements: Oracle Database 12c Linux operating system (especially for topic 3 where you run a Linux script)

Prerequisites

Assumption

What You Already Know

Tutorial Setup

1. Interval Reference Partitioning

2. Cascading TRUNCATE and EXCHANGE Operations

3. Moving Partitions Online

4. Maintaining Multiple Partitions

5. Maintaining Global Indexes Asynchronously

6. Using Partial Indexes

Summary

Comments

Post a Comment

The following is a list of hardware and software requirements:

Oracle Database 12c

Linux operating system (especially for topic 3 where you run a Linux script)