Partial Indexing in Oracle 12c

There are lots of really nice capabilities introduced in the 12c database that significantly improves the manageability of partitioned environments.

One of those new capabilities is the ability to create a partial index. In fact, in Oracle 12c we can create an index on a specific partition and of course this subject will true about indexes that will create by INDEXING PARTIAL expression. This property will decrease space utilization, Create index performance improvement and also, increase the maintainperformance of the index and will increase the speed of DML on partition tables.

For using this facility when we create a table we can use INDEXING expression and use  INDEXING OFF for a partition that prevents to create an index on a specific partition.

Indexing property is in the table and partition level is by default on.

create table vahid.raptor(id number(9), create_date date,DEC VARCHAR2(4000)) INDEXING OFF

    PARTITION BY RANGE (create_date)

    INTERVAL(NUMTOYMINTERVAL(12, 'MONTH'))

    (

    partition p_1 VALUES LESS THAN(to_date('2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INDEXING OFF,

    partition p_2 VALUES LESS THAN(to_date('2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INDEXING OFF,

    partition p_3 VALUES LESS THAN(to_date('2020-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INDEXING ON

    );

We can change property of partition indexing after create table:

   SQL> alter table vahid.raptor modify partition p_2 indexing on;

    SQL> alter table vahid.raptor modify partition p_2 indexing off;

Now I want to insert rows in the raptor and I will check index status as well as query execution status on the table.

   begin

    for i in 1.. 10000 loop

  insert into vahid.raptor values(1,TO_DATE('2000-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',15,'Raptor-F35'));

  insert into vahid.raptor values(i,TO_DATE('2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',15,'Raptor-F35'));

  insert into vahid.raptor values(i,TO_DATE('2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',15,'Raptor-F35'));

  insert into vahid.raptor values(i,TO_DATE('2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',15,'Raptor-F35'));

  insert into vahid.raptor values(i,TO_DATE('2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',100,'Raptor-F35'));

    end loop;

    commit;

   end;

gather table statistics and check the size of each segment

   begin

      dbms_stats.gather_table_stats(ownname =>'VAHID' ,tabname => 'RAPTOR' ,degree => 8,cascade => true);

    end;

Check indexing status:

select d.table_name,d.partition_name,d.indexing from dba_tab_partitions d where d.table_name='RAPTOR';

For viewing indexing property of a table using the below query:

  select table_name, def_indexing from dba_part_tables where table_name='RAPTOR';

As well as we can change above property by:

SQL> alter table vahid.RAPTOR modify default attributes indexing ON;

Let me create partial and full index on CREATE_DATE and check it.

SQL> set timing on;

SQL>   CREATE INDEX IDX_01 ON vahid.RAPTOR(CREATE_DATE) LOCAL INDEXING PARTIAL;

Index created.

Elapsed: 00:00:00.27

Check index partition status:

    select index_name, partition_name, status from dba_ind_partitions  where index_name='IDX_01' order by partition_position;

As you can see in the above grid just index IDX_01 is usable on partition p_3.

Check index space utilization:

    select p.segment_name,p.partition_name,p.bytes/1024/1024 SIZE_MB from dba_segments p where p.segment_name='IDX_01';

   

Now we will check the execution plan of query and the point is full table scan on p_1 and p_2

SELECT COUNT(create_date) FROM vahid.raptor WHERE 

    create_date BETWEEN TO_DATE('02/01/2002','MM/DD/YYYY') AND TO_DATE('02/01/2010','MM/DD/YYYY') ;

Also, check the query on partition p_3 and we will understand a big difference in the plan

   SELECT COUNT(create_date) FROM vahid.raptor WHERE 

    create_date BETWEEN TO_DATE('02/01/2019','MM/DD/YYYY') AND TO_DATE('03/01/2019','MM/DD/YYYY') ;

The point: we can rebuild unusable partitions manually:

SQL> ALTER INDEX IDX_01 REBUILD PARTITION p_1;

    select index_name, partition_name, status from dba_ind_partitions  where index_name='IDX_01' and partition_name='P_1' order by partition_position;

As we should know when I change INDEXING from ON to OFF the index will unusable.

FULL INDEXING:

At first, I dropped IDX_01 and recreate it.

SQL> drop index IDX_01;

SQL>    CREATE INDEX  IDX_01 ON vahid.raptor(create_date) LOCAL INDEXING FULL;

Index created.

Elapsed: 00:00:00.54

The time of index creation as you see increased and also the index space utilization will increase.