Oracle Database 12c offers several enhancements for partitioning. In a previous blog posting, I talked about reference partitioning enhancements. This post will present other enhancements in relation to general partitioning.

Asynchronous index maintenance

With Oracle database, a global index becomes UNUSABLE when dropping or truncating the partition on which this index points, until the clause UPDATE GLOBAL INDEXES is used.

With Oracle 11g, droping or truncating a partition involves the index maintenance, consisting on the deletion of orphaned entries. With Oracle 12c, the index maintenance can be deferred, automatically or manually.

To demonstrate this new feature, I created on an Oracle 11g R2 and an Oracle 12c databases a table TB_SALARIES in a schema MSC, containing a list of salaries with their name, first name and hire date. The table uses range partitioning by year, based on the hire date.

SQL> CREATE TABLE TB_SALARIES(
 SAL_ID NUMBER NOT NULL,
 SAL_NAME VARCHAR2(20) NOT NULL,
 SAL_FNAME VARCHAR2(20) NOT NULL,
 HIRE_DATE DATE NOT NULL,
 CONSTRAINT PK_SALARY PRIMARY KEY (SAL_ID) ENABLE,
 CONSTRAINT UNQ_NAME UNIQUE (SAL_NAME, SAL_FNAME) USING INDEX GLOBAL)
 PARTITION BY RANGE (HIRE_DATE)(
 PARTITION P_2010_31_12 VALUES LESS THAN (to_date('31.12.2010','dd.mm.yyyy')) NOCOMPRESS,
 PARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy')) NOCOMPRESS,
 PARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')) NOCOMPRESS
 );

 

By creating a primary key and unique constraints, we automatically generate two global indexes, which we can see in the USER_INDEXES view:

SQL> select index_name, status from user_indexes;
 INDEX_NAME                     STATUS
 ------------------------------ --------
 UNQ_NAME                       VALID
 PK_SALARY                      VALID

 

Then I inserted 990000 rows in the partition P_2012_31_12.

SQL> INSERT INTO TB_SALARIES (
 SELECT LEVEL sal_id,
 DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (10, 20)) sal_name,
 DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (10, 20)) sal_fname,
 to_date('01.03.2012','dd.mm.yyyy') hire_date
 FROM DUAL CONNECT BY LEVEL
SQL> commit;

I also inserted one line in P_2011_31_12 partition, for later purposes.

SQL> INSERT INTO TB_SALARIES VALUES (12345, 'Scott', 'TIGER', to_date('01.03.2011','dd.mm.yyyy'));
 SQL> commit;

Oracle 11g

Let’s see the size on the disk of both indexes. We will use it later for comparison:

SQL> SELECT sum(bytes)/1024/1024 MB,segment_name
 FROM user_extents
 WHERE segment_type = 'INDEX'
 GROUP BY segment_name;
MB         SEGMENT_NAME
 ---------- --------------------------------------------------------
 19         PK_SALARY
 72         UNQ_NAME

Now, I will drop the partition P_2012_31_12, containing the 990 000 rows:

SQL> ALTER TABLE MSC.TB_SALARIES DROP PARTITION P_2012_31_12 UPDATE GLOBAL INDEXES;

The UPDATE INDEXES clause does not rebuild the index, since the size of the index on the disk has not changed:

SQL> SELECT sum(bytes)/1024/1024 MB,segment_name
 FROM user_extents
 WHERE segment_type = 'INDEX'
 GROUP BY segment_name;
MB         SEGMENT_NAME
 ---------- --------------------------------------------------------
 19         PK_SALARY
 72         UNQ_NAME

However, indexes are in a VALID state so the optimizer will not require the parameter skip_unusable_indexes to be set to TRUE. Remember that this parameter says to the optimizer to skip indexes if they are in an UNUSABLE state, to avoid blocking DMLs.

SQL> select index_name, status from user_indexes;
 INDEX_NAME                     STATUS
 ------------------------------ --------
 PK_SALARY                      VALID
 UNQ_NAME                       VALID

The drop partition operation took about one minute, since the index cleanup was performed immediately because of the UPDATE GLOBAL INDEX clause. We can see it by using the tkprof utility with the trace file containing the statement:

As oracle OS user:

$ tkprof /u00/app/oracle/diag/rdbms/msc_site1/MSC/trace/MSC_ora_9887.trc /tmp/report.txt
 $ cat /tmp/report.txt

screen_11g

This extract confirms the duration of one minute, and shows high usage for CPU and I/O disk.

Oracle 12c

Here, I performed the same drop partition operation on the 12c database:

SQL> ALTER TABLE MSC.TB_SALARIES DROP PARTITION P_2012_31_12 UPDATE GLOBAL INDEXES;

The drop partition operation was performed immediately, I did not have wait. With the trace file and tkprof utility, we can observe that the elapsed time for the whole statements is below 1 second, compared to the 69 seconds in 11g.

We can also see that the CPU usage and disk access are much lower compared to the 11g database.

See below the extract of the tkprof report file:

screen_12c

The reason of those figures is that even if the UPDATE GLOBAL INDEX clause is used, the index cleanup is not performed just after the drop partition operation is realized. Indeed, with Oracle 12c, the index maintenance is performed asynchronously by two ways:

  • The job PMO_DEFERRED_GIDX_MAINT_JOB, scheduled everyday at 02:00 AM per default, performs all index cleanups periodically. It can be run manually with the dbms_scheduler package.
SQL> select job_name, next_run_date from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';
 JOB_NAME                       NEXT_RUN_DATE
 ------------------------------ ------------------------------------------
 PMO_DEFERRED_GIDX_MAINT_JOB    16-JUL-13 02.00.00.800000 AM EUROPE/VIENNA
  • The DBMS_PART.CLEANUP_GIDX procedure, to be run manually, performs the index cleanups for a given table in a given schema.

To demonstrate that index cleanup is performed asynchronously, we can select the new column ORPHANED_ENTRIES of the USER_INDEXES view:

SQL> select INDEX_NAME, STATUS, ORPHANED_ENTRIES   from user_indexes;
 INDEX_NAME                               STATUS   ORP
 ---------------------------------------- -------- ---
 PK_SALARY                                VALID    YES
 UNQ_NAME                                 VALID    YES

At this time, we can see some orphaned entries. It means that some index entries are pointing to deleted rows. This is due to the drop of the partition.

Now, I run (as SYS user) the index cleanup manually for indexes of the table TB_SALARIES in schema MSC, in order to delete orphaned entries:

SQL> exec DBMS_PART.CLEANUP_GIDX('MSC','TB_SALARIES');

(Note that we can also run the job PMO_DEFERRED_GIDX_MAINT_JOB or wait its automatic execution during the maintenance window.)

After about one minute, the execution is finished, and we can select the ORPHANED_ENTRIES again from USER_INDEXES. Note that the duration of the statement corresponds to the duration of the DROP PARTITION statement observed in oracle 11g…

SQL> select INDEX_NAME, STATUS, ORPHANED_ENTRIES   from user_indexes;
 INDEX_NAME                               STATUS   ORP
 ---------------------------------------- -------- ---
 PK_SALARY                                VALID    NO
 UNQ_NAME                                 VALID    NO

As we can see, orphaned entries were deleted from both indexes. However, there is still no rebuilding of the indexes, since the size of the indexes on the disk has not changed:

SQL> SELECT sum(bytes)/1024/1024 MB,segment_name
 FROM user_extents
 WHERE segment_type = 'INDEX'
 GROUP BY segment_name;
MB         SEGMENT_NAME
 ---------- --------------------------------------------------------
 19         PK_SALARY
 72         UNQ_NAME


A rebuild definitively frees up the space used by orphaned entries.

SQL> alter index pk_salary rebuild;
 SQL> alter index unq_name rebuild;
SQL> SELECT sum(bytes)/1024 KB,segment_name
 FROM user_extents
 WHERE segment_type = 'INDEX'
 GROUP BY segment_name;
       KB  SEGMENT_NAME
 ---------- ----------------------------------------
 64  UNQ_NAME
 64  PK_SALARY

This size corresponds to the remaining entry in the P_2011_31_12 partition, where 64 KB is the size of the smaller extent.

To conclude about index maintenance, this new feature allows to reduce the workload produced during the partition maintenance in a partitioned database. Index maintenance causes high workload and can be performed during the night, when the database is not highly solicited.

Multiple partition maintenance

Oracle 12c allows to manage multiple partitions at the same time. This is not the case with Oracle 11g, where partitions must be added, deleted or truncated one by one.

To demonstrate this new feature, I have created the following table on an Oracle 11g and an Oracle 12c database.

SQL> CREATE table TB_CUSTOMER (
 CUST_ID number primary key,
 CUST_NAME varchar(25),CUST_DATE date)
 PARTITION BY RANGE (CUST_DATE)(
 PARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy'))
 );

Oracle 11g

This is what happens if we try to add two partitions at the same time with Oracle 11g:

SQL> alter table tb_customer add
 PARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')),
 PARTITION P_2013_31_12 VALUES LESS THAN (to_date('31.12.2013','dd.mm.yyyy'));
 *
 ERROR at line 4:
 ORA-14043: only one partition may be added

Oracle 12c

And now, we can see that there is no error with Oracle 12c:

SQL> alter table tb_customer add
 PARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')),
 PARTITION P_2013_31_12 VALUES LESS THAN (to_date('31.12.2013','dd.mm.yyyy'))
 ;
 Table altered.

This is not a big feature, but it will simplify partitioning maintenance inside a database.

Partial indexes

Partial indexes represent the ability to index only some of the partitions of a table.

There are two restrictions for partial index usage:

  • Unique indexes are not supported. It concerns both explicitly (CREATE UNIQUE INDEX) and implicitly (unique constraint) created indexes.
  • Non-partitioned tables are not supported.

To demonstrate this feature, I use the table TB_SALARIES created previously, but I don’t recreate the unique constraint since it is not supported:

SQL> connect msc/msc
 SQL>CREATE TABLE TB_SALARIES(
 SAL_ID NUMBER NOT NULL,
 SAL_NAME VARCHAR2(20) NOT NULL,
 SAL_FNAME VARCHAR2(20) NOT NULL,
 HIRE_DATE DATE NOT NULL,
 CONSTRAINT PK_SALARY PRIMARY KEY (SAL_ID) ENABLE)
 PARTITION BY RANGE (HIRE_DATE)(
 PARTITION P_2010_31_12 VALUES LESS THAN (to_date('31.12.2010','dd.mm.yyyy')),
 PARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy')),
 PARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy'))
 );

To know if indexing is enabled for a given partition, we can get the value of the INDEXING column in *_TAB_PARTITIONS view. This is a new column introduced in Oracle 12c, which also concerns *_PART_TABLES and *_TAB_SUBPARTITIONS views.

SQL> select table_name, partition_name, indexing from user_tab_partitions;
 TABLE_NAME                     PARTITION_NAME                 INDE
 ------------------------------ ------------------------------ ----
 TB_SALARIES                    P_2010_31_12                   ON
 TB_SALARIES                    P_2011_31_12                   ON
 TB_SALARIES                    P_2012_31_12                   ON

Here, we can see that indexing has been enabled per default. To specify the indexing mode at table creation time for a given partition, append the INDEXING OFF clause as follow:

SQL> CREATE TABLE TB_SALARIES(
 ...
 PARTITION P_2010_31_12 VALUES LESS THAN (to_date('31.12.2010','dd.mm.yyyy')) INDEXING OFF,
 ...
 );

You can also alter an existing partition by using the following syntax:

SQL> alter table TB_SALARIES modify partition P_2010_31_12 INDEXING OFF;
 Table altered.

From now on, we assume that the partition P_2010_31_12 of the table TB_SALARIES has a disabled indexing.

SQL> select table_name, partition_name, indexing from user_tab_partitions;
 TABLE_NAME                     PARTITION_NAME                 INDE
 ------------------------------ ------------------------------ ----
 TB_SALARIES                    P_2010_31_12                   OFF
 TB_SALARIES                    P_2011_31_12                   ON
 TB_SALARIES                    P_2012_31_12                   ON

The next step is to create an index. We use the INDEXING clause in the CREATE INDEX statement to define the indexing type.

Example to create a local partial index:

SQL> create index sal_index on tb_salaries(sal_name) local indexing partial;

We can check the indexing mode of an index with the new column INDEXING in the *_INDEXES view:

SQL> select index_name, indexing from user_indexes;
 INDEX_NAME                     INDEXIN
 ------------------------------ -------
 SAL_INDEX                      PARTIAL
 PK_SALARY                      FULL

Now we are going to examine what happens when we create an index. The behavior is a little different between local and global indexes.

Local index

A local index makes one table partition correspond to one index partition. In the case of a partial index, Oracle will generate an USABLE index partition for each table partition having indexing enabled, and UNUSABLE index partitions for table partitions having a disabled indexing:

SQL> create index sal_index on tb_salaries(sal_name) local indexing partial;
 Index created.
SQL> select index_name, partition_name, status from user_ind_partitions;
 INDEX_NAME                     PARTITION_NAME                 STATUS
 ------------------------------ ------------------------------ --------
 SAL_INDEX                      P_2012_31_12                   USABLE
 SAL_INDEX                      P_2011_31_12                   USABLE
 SAL_INDEX                      P_2010_31_12                   UNUSABLE

In the above output, we can see that Oracle has created an UNUSABLE index partition for the table partition P_2010_31_12.

This is the case when we create a new partial index. But what happens if a partial index already exists and we change the indexing mode of a partially indexed table? Let’s test this case:

SQL> alter table tb_salaries modify partition P_2010_31_12 indexing on;
 Table altered.
SQL> select index_name, partition_name, status from user_ind_partitions;
 INDEX_NAME                     PARTITION_NAME                 STATUS
 ------------------------------ ------------------------------ --------
 SAL_INDEX                      P_2012_31_12                   USABLE
 SAL_INDEX                      P_2011_31_12                   USABLE
 SAL_INDEX                      P_2010_31_12                   USABLE

It is interessant to see that the index partition status for P_2010_31_12 has automatically become USABLE. This means that you can enable or disable indexing on table partitions without having to consider existing indexes.

Global index

A global index makes one single index correspond to multiple table partitions. In the case of a partial index, Oracle will generate an index containing only data of the partitions having the INDEXING enabled.

To demonstrate this, we just have to populate each partition of the table TB_SALARIES and display the execution plan where we select data of an indexed partition and a non-indexed partition.

Let’s start by inserting a row for each partition:

SQL> insert into tb_salaries values (1,'Scott','Tiger',to_date('28.03.2010','dd.mm.yyyy'));
 SQL> insert into tb_salaries values (2,'hr','hr',to_date('28.03.2011','dd.mm.yyyy'));
 SQL> insert into tb_salaries values (3,'oe','oe',to_date('28.03.2012','dd.mm.yyyy'));

Now we have to create a global index on the HIRE_DATE column:

SQL> create index sal_index on tb_salaries(hire_date) indexing partial;
 Index created.

The package dbms_xplan will help show the partial indexing.

In the following example, information contained in both P_2010_31_12 and P_2011_31_12 partitions are displayed. Oracle should use the index since P_2011_31_12 indexing is enabled.

SQL> explain plan for select * from tb_salaries where hire_date < to_date('31.12.2011','dd.mm.yyyy');
 Explained.
partial_index_plan1

The plan indeed shows that the index was used to search records (step 4). We can also see a full table access (step 6), which corresponds to the access to data of the non-indexed partition.

To be sure that no index will be used for this specific partition, we have to limit the next statement to records contained in the partition having a disabled indexing:

SQL> explain plan for select * from tb_salaries where hire_date < to_date('31.12.2010','dd.mm.yyyy');
 Explained.

partial_index_plan2

This time, the plan shows that no indexes were used to search records in the table TB_SALARIES. This is normal, because the partition containing the hire_date < 31/12/2010 has a disabled indexing.

To finish, let’s see what happens on an existing global partial index when we change the indexing status of the partition.

SQL> alter table tb_salaries modify partition P_2010_31_12 indexing on;
 Table altered.
SQL> explain plan for select * from tb_salaries where hire_date < to_date('31.12.2010','dd.mm.yyyy');
 Explained.

partial_index_plan3

As for the local partial index, the index has been adapted to now index columns contained in the newly indexed partition.

ONLINE move partition

Oracle 11g

In Oracle 11g, it was already possible to perform DML operation when moving a partition. But the DML stayed pending until the move was finished, causing a relative waiting time depending on the move operation duration.

Move a partition also made indexes UNUSABLE in Oracle 11g, if the moved partition was not empty. So, even if a DML was finally executed after moving the partition, the error “ORA-01502 index ‘%s.%s’ or partition of such index is in unusable state” was displayed in many cases and the statement was rolled back.

To demonstrate the case, I have created a table TB_CUSTOMER with three partitions and a global index:

SQL> create table TB_CUSTOMER (
 CUST_ID number primary key,
 CUST_NAME varchar(25),
 CUST_DATE date)
 PARTITION BY RANGE (CUST_DATE)(
 PARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy')) NOCOMPRESS,
 PARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')) NOCOMPRESS,
 PARTITION P_2013_31_12 VALUES LESS THAN (to_date('31.12.2013','dd.mm.yyyy')) NOCOMPRESS);
SQL> alter table tb_customer add constraint unq_name unique (cust_name) using index global;

Then I have inserted several rows in the partition P_2012_31_12:

SQL> insert into TB_CUSTOMER values (1,'OE',to_date('11.03.2012','dd.mm.yyyy'));
 SQL> insert into TB_CUSTOMER values (2,'HR',to_date('26.07.2012','dd.mm.yyyy'));

And to finish I moved the partition P_2012_31_12 on a different tablespace:

SQL> alter table tb_customer move partition P_2012_31_12 tablespace users2;
 Table altered.

Now if I try to insert a new row in the partition P_2012_31_12, an error is raised due to the state of the index corresponding to the partition:

SQL> insert into TB_CUSTOMER values (1,'TOTO',to_date('11.03.2012','dd.mm.yyyy'));
 insert into TB_CUSTOMER values (1,'TOTO',to_date('11.03.2012','dd.mm.yyyy'))
 *
 ERROR at line 1:ORA-01502: index 'MSC.SYS_C009612' or partition of such index is in unusable State

As the error says, indexes have become UNUSABLE:

SQL> select index_name, status from user_indexes;
INDEX_NAME                     STATUS
 ------------------------------ --------
 SYS_C009612                    UNUSABLE
 UNQ_NAME                       UNUSABLE


Oracle 12c

Oracle 12c offers the new clause ONLINE for move operation on partitions and sub partitions. This clause allows DML during the move operation, and global indexes remain in a valid state after a move partition operation. So not only DML are immediately executed, but also the partition move operation is completely transparent for users.

Note that if a transaction is active on the concerned partition before the move partition statement is ordered, two scenarios may occur:

  • Without ONLINE clause, the error “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired” is displayed. The transaction must be finished (commit or rollback) and the move statement must be re executed. This is common to Oracle 11g and Oracle 12c releases.
  • With ONLINE clause, the move operation waits the transaction to be finished (commit or rollback) and is then executed.

Here is a short example, with the same TB_CUSTOMER table.

Without ONLINE clause

I move the partition to the tablespace USERS2. We assume that no transactions are running:

SQL> alter tb_customer move partition P_2012_31_12 tablespace users2;
 Table altered.

 

Then we can see that the status for both indexes is UNUSABLE:

SQL> select index_name, status from user_indexes;
 INDEX_NAME                     STATUS
 ------------------------------ --------
 SYS_C009612                    UNUSABLE
 UNQ_NAME                       UNUSABLE
 A manual rebuild is required:
SQL> alter index unq_name rebuild;
 SQL> alter index SYS_C009612 rebuild;
SQL> select index_name, status from user_indexes;
INDEX_NAME                     STATUS
 ------------------------------ --------
 SYS_C009612                    VALID
 UNQ_NAME                       VALID

Global indexes have became UNUSABLE after moving the partition, requiring a manual rebuild.

With ONLINE clause

Now I use the ONLINE clause and I move back the partition to the tablespace USERS.

SQL> select index_name, status from user_indexes;
INDEX_NAME                     STATUS
 ------------------------------ --------
 SYS_C009612                    VALID
 UNQ_NAME                       VALID
SQL> alter table tb_customer move partition P_2013_31_12 tablespace users online;
 Table altered.SQL> select index_name, status from user_indexes;INDEX_NAME                     STATUS
 ------------------------------ --------
 SYS_C009612                    VALID
 UNQ_NAME                       VALID

Global indexes remained in a VALID state after moving the partition. The partition can still be used in DML statements.