TwelveC

Let us assume you have a table with ten columns and four B*Tree indexes. If you analyze the space usage; chances are very high that size of all indexes together is nearly equal to more than the size of the table.  If you end up creating more indexes to support complex business requirements, the size of all indexes together could be 2-4 times(or more) the size of tables. So what is the big deal, space is cheap.  Really?  This is what you get to read but when your database is in 10s or 100s of terabytes, every byte matters. So how would you feel if you were told that there is an option in Oracle 12c that would let you create partial index on partitioned tables to save storage and improve on performance.

Let us explore more on this topic.

We know that the main purpose of index is to provide fast access to selective data and that the purpose of index is defeated when you retrieve large amounts of data(subjective to size of table).  Moreover for some applications, you need indexes only for the current or recent months whereas for some applications, index is not required for current or recent months. For reporting or data warehouse databases, full table scans in parallel are generally many times faster compared to sequential index range scans.  But when you create a global or local Index on partitioned table, index is created on all of the data or partitions. Prior to Oracle 12c , you could not create indexes on selective partitions; Indexes always meant on all of the data.  However with Oracle 12c, you can create partial indexes that  contains index data from selective partitions only.

So how would you implement partial indexes?

1. Enable or disable the indexing property at table level or partition/subpartiton level using INDEXING clause.
2. Create indexes as FULL or PARTIAL. When you specify PARTIAL, only partitions that have the indexing property turned on  will be part of index.

Example

In the below example, I am disabling the index property of the table and  enabling the indexing property at partition level. Now when I create partial indexes, only those partitions  with indexing property turned on are part of index.

SQL>  CREATE TABLE TRANSACTION(
2    TRANSACTION_ID           NUMBER(12)    PRIMARY KEY,
3    OPERATION_ID             NUMBER(3)     NOT NULL,
4    TRANSCTION_AMT           NUMBER(9,2)   NOT NULL,
5    TRANSACTION_DATE         DATE,
6    TRANSACTION_TYPE         VARCHAR2(2))
7  INDEXING OFF
8  PARTITION BY RANGE (TRANSACTION_DATE)
9  (
10     PARTITION TRANSACTIONDATE_P1 VALUES LESS THAN (TO_DATE(’01-MAR-2014′,’DD-MON-YYYY’))      INDEXING OFF,
11     PARTITION TRANSACTIONDATE_P2 VALUES LESS THAN (TO_DATE(’01-APR-2014′,’DD-MON-YYYY’))      INDEXING OFF,
12     PARTITION TRANSACTIONDATE_P3 VALUES LESS THAN (TO_DATE(’01-MAY-2014′,’DD-MON-YYYY’))      INDEXING OFF,
13     PARTITION TRANSACTIONDATE_P4 VALUES LESS THAN (TO_DATE(’01-JUN-2014′,’DD-MON-YYYY’))         INDEXING ON,
14     PARTITION TRANSACTIONDATE_P5 VALUES LESS THAN (TO_DATE(’01-JUL-2014′,’DD-MON-YYYY’))         INDEXING ON
15 ) ;

Table created.

In the example below,Only data from partitions TRANSACTIONDATE_P4 and TRANSACTIONDATE_P5 are part of index.

SQL> CREATE INDEX   IDX_TRANS_OID  ON TRANSCTION(OPERATION_ID)
2  GLOBAL INDEXING PARTIAL;

Index created.

In this example, all partitions are part of index.

SQL> CREATE INDEX   IDX_TRANS_TYPE ON TRANSCTION(TRANSACTION_TYPE)
2  GLOBAL INDEXING FULL;

Index created.

In the example below,Only data from partitions TRANSACTIONDATE_P4 and TRANSACTIONDATE_P5 are part of index.

SQL> CREATE INDEX  IDX_TRANS_DATE ON TRANSACTION(TRANSACTION_DATE)
2  LOCAL INDEXING PARTIAL;

Index created.

 You can enable or disable the indexing property of the table or partition or subpartition using the ALTER TABLE command;  

SQL> alter table transaction modify partition TRANSACTIONDATE_P3 indexing on ;

Table altered.