{"id":3211,"date":"2013-09-13T01:47:00","date_gmt":"2013-09-12T23:47:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/"},"modified":"2013-09-13T01:47:00","modified_gmt":"2013-09-12T23:47:00","slug":"oracle-12c-partitioning-enhancements-part-ii-other-improvements-1","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/","title":{"rendered":"Oracle 12c: Partitioning enhancements Part II – Other improvements"},"content":{"rendered":"

\"\"<\/p>\n

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.<\/p>\n

Asynchronous index maintenance<\/h3>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

SQL> CREATE TABLE TB_SALARIES(\n SAL_ID NUMBER NOT NULL,\n SAL_NAME VARCHAR2(20) NOT NULL,\n SAL_FNAME VARCHAR2(20) NOT NULL,\n HIRE_DATE DATE NOT NULL,\n CONSTRAINT PK_SALARY PRIMARY KEY (SAL_ID) ENABLE,\n CONSTRAINT UNQ_NAME UNIQUE (SAL_NAME, SAL_FNAME) USING INDEX GLOBAL)\n PARTITION BY RANGE (HIRE_DATE)(\n PARTITION P_2010_31_12 VALUES LESS THAN (to_date('31.12.2010','dd.mm.yyyy')) NOCOMPRESS,\n PARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy')) NOCOMPRESS,\n PARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')) NOCOMPRESS\n );<\/pre>\n
 <\/p>\n
By creating a primary key and unique constraints, we automatically generate two global indexes, which we can see in the USER_INDEXES view:<\/p>\n
SQL> select index_name, status from user_indexes;\n INDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STATUS\n ------------------------------ --------\n UNQ_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID\n PK_SALARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID<\/pre>\n
 <\/p>\n
Then I inserted 990000 rows in the partition P_2012_31_12.<\/p>\n
SQL> INSERT INTO TB_SALARIES (\n SELECT LEVEL sal_id,\n DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (10, 20)) sal_name,\n DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (10, 20)) sal_fname,\n to_date('01.03.2012','dd.mm.yyyy') hire_date\n FROM DUAL\u00a0CONNECT BY LEVEL\nSQL> commit;<\/pre>\n
I also inserted one line in P_2011_31_12 partition, for later purposes.<\/p>\n
SQL> INSERT INTO TB_SALARIES VALUES (12345, 'Scott', 'TIGER', to_date('01.03.2011','dd.mm.yyyy'));\n SQL> commit;<\/pre>\n
Oracle 11g<\/h4>\n
Let\u2019s see the size on the disk of both indexes. We will use it later for comparison:<\/p>\n
SQL> SELECT sum(bytes)\/1024\/1024 MB,segment_name\n FROM user_extents\n WHERE segment_type = 'INDEX'\n GROUP BY segment_name;\nMB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SEGMENT_NAME\n ---------- --------------------------------------------------------\n 19\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PK_SALARY\n 72\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNQ_NAME<\/pre>\n
Now, I will drop the partition P_2012_31_12, containing the 990 000 rows:<\/p>\n
SQL> ALTER TABLE MSC.TB_SALARIES DROP PARTITION P_2012_31_12 UPDATE GLOBAL INDEXES;<\/pre>\n
The UPDATE INDEXES clause does not rebuild the index, since the size of the index on the disk has not changed:<\/p>\n
SQL> SELECT sum(bytes)\/1024\/1024 MB,segment_name\n FROM user_extents\n WHERE segment_type = 'INDEX'\n GROUP BY segment_name;\nMB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SEGMENT_NAME\n ---------- --------------------------------------------------------\n 19\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PK_SALARY\n 72\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNQ_NAME<\/pre>\n
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.<\/p>\n
SQL> select index_name, status from user_indexes;\n INDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STATUS\n ------------------------------ --------\n PK_SALARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID\n UNQ_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID<\/pre>\n
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:<\/p>\n
As oracle OS user:<\/p>\n
$ tkprof \/u00\/app\/oracle\/diag\/rdbms\/msc_site1\/MSC\/trace\/MSC_ora_9887.trc \/tmp\/report.txt\n $ cat \/tmp\/report.txt<\/pre>\n
\"screen_11g\"<\/p>\n
This extract confirms the duration of one minute, and shows high usage for CPU and I\/O disk.<\/p>\n
Oracle 12c<\/h4>\n
Here, I performed the same drop partition operation on the 12c database:<\/p>\n
SQL> ALTER TABLE MSC.TB_SALARIES DROP PARTITION P_2012_31_12 UPDATE GLOBAL INDEXES;<\/pre>\n
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.<\/p>\n
We can also see that the CPU usage and disk access are much lower compared to the 11g database.<\/p>\n
See below the extract of the tkprof report file:<\/p>\n
\"screen_12c\"<\/p>\n
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:<\/p>\n
    \n
  • 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.<\/li>\n<\/ul>\n
    SQL> select job_name, next_run_date from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';\n JOB_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NEXT_RUN_DATE\n ------------------------------ ------------------------------------------\n PMO_DEFERRED_GIDX_MAINT_JOB\u00a0\u00a0\u00a0 16-JUL-13 02.00.00.800000 AM EUROPE\/VIENNA<\/pre>\n
      \n
    • The DBMS_PART.CLEANUP_GIDX procedure, to be run manually, performs the index cleanups for a given table in a given schema.<\/li>\n<\/ul>\n
      To demonstrate that index cleanup is performed asynchronously, we can select the new column ORPHANED_ENTRIES of the USER_INDEXES view:<\/p>\n
      SQL> select INDEX_NAME, STATUS, ORPHANED_ENTRIES\u00a0\u00a0 from user_indexes;\n INDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STATUS\u00a0\u00a0 ORP\n ---------------------------------------- -------- ---\n PK_SALARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID\u00a0\u00a0\u00a0 YES\n UNQ_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID\u00a0\u00a0\u00a0 YES<\/pre>\n
      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.<\/p>\n
      Now, I run (as SYS user) the index cleanup\u00a0manually for indexes of the table TB_SALARIES in schema MSC, in order to delete orphaned entries:<\/p>\n
      SQL> exec DBMS_PART.CLEANUP_GIDX('MSC','TB_SALARIES');<\/pre>\n
      (Note that we can also run the job PMO_DEFERRED_GIDX_MAINT_JOB or wait its automatic execution during the maintenance window.)<\/p>\n
      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…<\/p>\n
      SQL> select INDEX_NAME, STATUS, ORPHANED_ENTRIES\u00a0\u00a0 from user_indexes;\n INDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STATUS\u00a0\u00a0 ORP\n ---------------------------------------- -------- ---\n PK_SALARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID\u00a0\u00a0\u00a0 NO\n UNQ_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID\u00a0\u00a0\u00a0 NO<\/pre>\n
      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:<\/p>\n
      SQL> SELECT sum(bytes)\/1024\/1024 MB,segment_name\n FROM user_extents\n WHERE segment_type = 'INDEX'\n GROUP BY segment_name;\nMB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SEGMENT_NAME\n ---------- --------------------------------------------------------\n 19\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PK_SALARY\n 72\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNQ_NAME<\/pre>\n

      \n<\/span><\/p>\n
      A rebuild definitively frees up the space used by orphaned entries.<\/p>\n
      SQL> alter index pk_salary rebuild;\n SQL> alter index unq_name rebuild;\nSQL> SELECT sum(bytes)\/1024 KB,segment_name\n FROM user_extents\n WHERE segment_type = 'INDEX'\n GROUP BY segment_name;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 KB\u00a0 SEGMENT_NAME\n ---------- ----------------------------------------\n 64\u00a0 UNQ_NAME\n 64\u00a0 PK_SALARY<\/pre>\n
      This size corresponds to the remaining entry in the P_2011_31_12 partition, where 64 KB is the size of the smaller extent.<\/p>\n
      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.<\/p>\n
      Multiple partition maintenance<\/h3>\n
      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.<\/p>\n
      To demonstrate this new feature, I have created the following table on an Oracle 11g and an Oracle 12c database.<\/p>\n
      SQL> CREATE table TB_CUSTOMER (\n CUST_ID number primary key,\n CUST_NAME varchar(25),CUST_DATE date)\n PARTITION BY RANGE (CUST_DATE)(\n PARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy'))\n );<\/pre>\n
      Oracle 11g<\/h4>\n
      This is what happens if we try to add two partitions at the same time with Oracle 11g:<\/p>\n
      SQL> alter table tb_customer add\n PARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')),\n PARTITION P_2013_31_12 VALUES LESS THAN (to_date('31.12.2013','dd.mm.yyyy'));\n *\n ERROR at line 4:\n ORA-14043: only one partition may be added<\/pre>\n
      Oracle 12c<\/h4>\n
      And now, we can see that there is no error with Oracle 12c:<\/p>\n
      SQL> alter table tb_customer add\n PARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')),\n PARTITION P_2013_31_12 VALUES LESS THAN (to_date('31.12.2013','dd.mm.yyyy'))\n ;\n Table altered.<\/pre>\n
      This is not a big feature, but it will simplify partitioning maintenance inside a database.<\/p>\n
      Partial indexes<\/h3>\n
      Partial indexes represent the ability to index only some of the partitions of a table.<\/p>\n
      There are two restrictions for partial index usage:<\/p>\n
        \n
      • Unique indexes are not supported. It concerns both explicitly (CREATE UNIQUE INDEX) and implicitly (unique constraint) created indexes.<\/li>\n
      • Non-partitioned tables are not supported.<\/li>\n<\/ul>\n
        To demonstrate this feature, I use the table TB_SALARIES created previously, but I don\u2019t recreate the unique constraint since it is not supported:<\/p>\n
        SQL> connect msc\/msc\n SQL>CREATE TABLE TB_SALARIES(\n SAL_ID NUMBER NOT NULL,\n SAL_NAME VARCHAR2(20) NOT NULL,\n SAL_FNAME VARCHAR2(20) NOT NULL,\n HIRE_DATE DATE NOT NULL,\n CONSTRAINT PK_SALARY PRIMARY KEY (SAL_ID) ENABLE)\n PARTITION BY RANGE (HIRE_DATE)(\n PARTITION P_2010_31_12 VALUES LESS THAN (to_date('31.12.2010','dd.mm.yyyy')),\n PARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy')),\n PARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy'))\n );<\/pre>\n
        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,\u00a0which also concerns *_PART_TABLES and *_TAB_SUBPARTITIONS views.<\/p>\n
        SQL> select table_name, partition_name, indexing from user_tab_partitions;\n TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PARTITION_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INDE\n ------------------------------ ------------------------------ ----\n TB_SALARIES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2010_31_12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON\n TB_SALARIES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2011_31_12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON\n TB_SALARIES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2012_31_12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/pre>\n
        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:<\/p>\n
        SQL> CREATE TABLE TB_SALARIES(\n ...\n PARTITION P_2010_31_12 VALUES LESS THAN (to_date('31.12.2010','dd.mm.yyyy')) INDEXING OFF,\n ...\n );<\/pre>\n
        You can also alter an existing partition by using the following syntax:<\/p>\n
        SQL> alter table TB_SALARIES modify partition P_2010_31_12 INDEXING OFF;\n Table altered.<\/pre>\n
        From now on, we assume that the partition P_2010_31_12 of the table TB_SALARIES has a\u00a0disabled indexing.<\/p>\n
        SQL> select table_name, partition_name, indexing from user_tab_partitions;\n TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PARTITION_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INDE\n ------------------------------ ------------------------------ ----\n TB_SALARIES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2010_31_12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OFF\n TB_SALARIES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2011_31_12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON\n TB_SALARIES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2012_31_12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/pre>\n
        The next step is to create an index. We use the INDEXING clause in the CREATE INDEX statement to define the indexing type.<\/p>\n
        Example to create a local partial index:<\/p>\n
        SQL> create index sal_index on tb_salaries(sal_name) local indexing partial;<\/pre>\n
        We can check the indexing mode of an index with the new column INDEXING in the *_INDEXES view:<\/p>\n
        SQL> select index_name, indexing from user_indexes;\n INDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INDEXIN\n ------------------------------ -------\n SAL_INDEX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PARTIAL\n PK_SALARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FULL<\/pre>\n
        Now we are going to examine what happens when we create an index. The behavior is a little different between local and global indexes.<\/p>\n
        Local index<\/h4>\n
        A local index makes one table partition\u00a0correspond 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:<\/p>\n
        SQL> create index sal_index on tb_salaries(sal_name) local indexing partial;\n Index created.\nSQL> select index_name, partition_name, status from user_ind_partitions;\n INDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PARTITION_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STATUS\n ------------------------------ ------------------------------ --------\n SAL_INDEX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2012_31_12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 USABLE\n SAL_INDEX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2011_31_12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 USABLE\n SAL_INDEX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2010_31_12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNUSABLE<\/pre>\n
        In the above output, we can see that Oracle has created an UNUSABLE index partition for the table partition P_2010_31_12.<\/p>\n
        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:<\/p>\n
        SQL> alter table tb_salaries modify partition P_2010_31_12 indexing on;\n Table altered.\nSQL> select index_name, partition_name, status from user_ind_partitions;\n INDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PARTITION_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STATUS\n ------------------------------ ------------------------------ --------\n SAL_INDEX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2012_31_12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 USABLE\n SAL_INDEX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2011_31_12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 USABLE\n SAL_INDEX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2010_31_12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 USABLE<\/pre>\n
        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.<\/p>\n
        Global index<\/p>\n
        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.<\/p>\n
        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.<\/p>\n
        Let’s start by inserting a row for each partition:<\/p>\n
        SQL> insert into tb_salaries values (1,'Scott','Tiger',to_date('28.03.2010','dd.mm.yyyy'));\n SQL> insert into tb_salaries values (2,'hr','hr',to_date('28.03.2011','dd.mm.yyyy'));\n SQL> insert into tb_salaries values (3,'oe','oe',to_date('28.03.2012','dd.mm.yyyy'));<\/pre>\n
        Now we have to create a global index on the HIRE_DATE column:<\/p>\n
        SQL> create index sal_index on tb_salaries(hire_date) indexing partial;\n Index created.<\/pre>\n
        The package dbms_xplan will help show the partial indexing.<\/p>\n
        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.<\/p>\n
        SQL> explain plan for select * from tb_salaries where hire_date < to_date('31.12.2011','dd.mm.yyyy');\n Explained.\n\"partial_index_plan1\"<\/pre>\n
        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.<\/p>\n
        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:<\/p>\n
        SQL> explain plan for select * from tb_salaries where hire_date < to_date('31.12.2010','dd.mm.yyyy');\n Explained.<\/pre>\n
        \"partial_index_plan2\"<\/p>\n
        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.<\/p>\n
        To finish, let’s see what happens on an existing global partial index when we change the indexing status of the partition.<\/p>\n
        SQL> alter table tb_salaries modify partition P_2010_31_12 indexing on;\n Table altered.\nSQL> explain plan for select * from tb_salaries where hire_date < to_date('31.12.2010','dd.mm.yyyy');\n Explained.<\/pre>\n
        \"partial_index_plan3\"<\/p>\n
        As for the local partial index, the index has been adapted to now index columns contained in the newly indexed partition.<\/p>\n
        ONLINE move partition<\/h3>\n
        Oracle 11g<\/h4>\n
        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.<\/p>\n
        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.<\/p>\n
        To demonstrate the case, I have created a table TB_CUSTOMER with three partitions and a global index:<\/p>\n
        SQL> create table TB_CUSTOMER (\n CUST_ID number primary key,\n CUST_NAME varchar(25),\n CUST_DATE date)\n PARTITION BY RANGE (CUST_DATE)(\n PARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy')) NOCOMPRESS,\n PARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')) NOCOMPRESS,\n PARTITION P_2013_31_12 VALUES LESS THAN (to_date('31.12.2013','dd.mm.yyyy')) NOCOMPRESS);\nSQL> alter table tb_customer add constraint unq_name unique (cust_name) using index global;<\/pre>\n
        Then I have inserted several rows in the partition P_2012_31_12:<\/p>\n
        SQL> insert into TB_CUSTOMER values (1,'OE',to_date('11.03.2012','dd.mm.yyyy'));\n SQL> insert into TB_CUSTOMER values (2,'HR',to_date('26.07.2012','dd.mm.yyyy'));<\/pre>\n
        And to finish I moved the partition P_2012_31_12 on a different tablespace:<\/p>\n
        SQL> alter table tb_customer move partition P_2012_31_12 tablespace users2;\n Table altered.<\/pre>\n
        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:<\/p>\n
        SQL> insert into TB_CUSTOMER values (1,'TOTO',to_date('11.03.2012','dd.mm.yyyy'));\n insert into TB_CUSTOMER values (1,'TOTO',to_date('11.03.2012','dd.mm.yyyy'))\n *\n ERROR at line 1:ORA-01502: index 'MSC.SYS_C009612' or partition of such index is in unusable State<\/pre>\n
        As the error says, indexes have become UNUSABLE:<\/p>\n
        SQL> select index_name, status from user_indexes;\nINDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STATUS\n ------------------------------ --------\n SYS_C009612\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNUSABLE\n UNQ_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNUSABLE<\/pre>\n

        \n<\/strong><\/p>\n
        Oracle 12c<\/h4>\n
        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.<\/p>\n
        Note that if a transaction is active on the concerned partition before the move partition statement is ordered, two scenarios may occur:<\/p>\n
          \n
        • Without ONLINE clause, the error \u201cORA-00054: resource busy and acquire with NOWAIT specified or timeout expired\u201d 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.<\/li>\n<\/ul>\n
            \n
          • With ONLINE clause, the move operation waits the transaction to be finished (commit or rollback) and is then executed.<\/li>\n<\/ul>\n
            Here is a short example, with the same TB_CUSTOMER table.<\/p>\n
            Without ONLINE clause<\/p>\n
            I move the partition to the tablespace USERS2. We assume that no transactions are running:<\/p>\n
            SQL> alter tb_customer move partition P_2012_31_12 tablespace users2;\n Table altered.<\/pre>\n
             <\/p>\n
            Then we can see that the status for both indexes is UNUSABLE:<\/p>\n
            SQL> select index_name, status from user_indexes;\n INDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STATUS\n ------------------------------ --------\n SYS_C009612\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNUSABLE\n UNQ_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNUSABLE\n A manual rebuild is required:\nSQL> alter index unq_name rebuild;\n SQL> alter index SYS_C009612 rebuild;\nSQL> select index_name, status from user_indexes;\nINDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STATUS\n ------------------------------ --------\n SYS_C009612\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID\n UNQ_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID<\/pre>\n
            Global indexes have became UNUSABLE after moving the partition, requiring a manual rebuild.<\/p>\n
            With ONLINE clause<\/h4>\n
            Now I use the ONLINE clause and I move back the partition to the tablespace USERS.<\/p>\n
            SQL> select index_name, status from user_indexes;\nINDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STATUS\n ------------------------------ --------\n SYS_C009612\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID\n UNQ_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID\nSQL> alter table tb_customer move partition P_2013_31_12 tablespace users online;\n Table altered.SQL> select index_name, status from user_indexes;INDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STATUS\n ------------------------------ --------\n SYS_C009612\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID\n UNQ_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALID<\/pre>\n
            Global indexes remained in a VALID state after moving the partition. The partition can still be used in DML statements.<\/p>\n","protected":false},"excerpt":{"rendered":"
            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 […]<\/p>\n","protected":false},"author":27,"featured_media":2734,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[17,209,366],"type_dbi":[],"class_list":["post-3211","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-management","tag-oracle-11g","tag-oracle-12c","tag-partitioning"],"acf":[],"yoast_head":"\nOracle 12c: Partitioning enhancements Part II - Other improvements - dbi Blog<\/title>\n<meta name=\"description\" content=\"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.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12c: Partitioning enhancements Part II - Other improvements\" \/>\n<meta property=\"og:description\" content=\"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.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2013-09-12T23:47:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_12c_Plug.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"200\" \/>\n\t<meta property=\"og:image:height\" content=\"209\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"16 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 12c: Partitioning enhancements Part II – Other improvements\",\"datePublished\":\"2013-09-12T23:47:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\\\/\"},\"wordCount\":1922,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/2e1ax_default_entry_Oracle_12c_Plug.jpg\",\"keywords\":[\"Oracle 11g\",\"Oracle 12c\",\"Partitioning\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\\\/\",\"name\":\"Oracle 12c: Partitioning enhancements Part II - Other improvements - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/2e1ax_default_entry_Oracle_12c_Plug.jpg\",\"datePublished\":\"2013-09-12T23:47:00+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"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.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/2e1ax_default_entry_Oracle_12c_Plug.jpg\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/2e1ax_default_entry_Oracle_12c_Plug.jpg\",\"width\":200,\"height\":209},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12c: Partitioning enhancements Part II – Other improvements\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle 12c: Partitioning enhancements Part II - Other improvements - dbi Blog","description":"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.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12c: Partitioning enhancements Part II - Other improvements","og_description":"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.","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/","og_site_name":"dbi Blog","article_published_time":"2013-09-12T23:47:00+00:00","og_image":[{"width":200,"height":209,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_12c_Plug.jpg","type":"image\/jpeg"}],"author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"16 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 12c: Partitioning enhancements Part II – Other improvements","datePublished":"2013-09-12T23:47:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/"},"wordCount":1922,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_12c_Plug.jpg","keywords":["Oracle 11g","Oracle 12c","Partitioning"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/","name":"Oracle 12c: Partitioning enhancements Part II - Other improvements - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_12c_Plug.jpg","datePublished":"2013-09-12T23:47:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"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.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_12c_Plug.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_12c_Plug.jpg","width":200,"height":209},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-ii-other-improvements-1\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12c: Partitioning enhancements Part II – Other improvements"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3211","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=3211"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3211\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/2734"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=3211"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=3211"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=3211"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=3211"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}