8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Partial Indexes for Partitioned Tables in Oracle Database 12c Release 1

Oracle 12c allows the creation of global and local indexes on a subset of the partitions of a partitioned table.

Table : INDEXING [ON | OFF] Clause
Index : INDEXING [FULL| PARTIAL] Clause
Modified Views

Table: INDEXING [ON | OFF] Clause

The decision about which partitions are indexed is made using the INDEXING [ON | OFF] clause associated with each partition, with the default being INDEXING ON.

We create a partitioned table with three partitions. The first two partitions have indexing turned on. One by default, and one explicitly. The third partition has indexing turned off.

-- Create and populate the partitioned table.
drop table t1 purge;

create table t1 (
  id            number,
   description   varchar2(50),
   created_date  date
)
partition by range (created_date) (
  partition part_2014 values less than (date '2015-01-01'),
  partition part_2015 values less than (date '2016-01-01') indexing on,
  partition part_2016 values less than (date '2017-01-01') indexing off
);


insert into t1 values (1, 't1 one', date '2014-07-01');
insert into t1 values (2, 't1 two', date '2015-07-01');
insert into t1 values (3, 't1 three', date '2016-07-01');
commit;

The INDEXING column of the USER_TAB_PARTITIONS view shows us the indexing status of each partition. Only the 2016 partition has INDEXING turned off.

column table_name format a20
column partition_name format a20

select table_name,
       partition_name,
       indexing
from   user_tab_partitions
where  table_name = 'T1'
order by 1,2;

TABLE_NAME           PARTITION_NAME       INDE
-------------------- -------------------- ----
T1                   PART_2014            ON
T1                   PART_2015            ON
T1                   PART_2016            OFF

SQL>

We change the indexing clause for a partition using the ALTER TABLE ... MODIFY PARTITION command. We turn indexing off for the 2014 and 2015 partitions, and turn indexing on for the 2016 partition.

alter table t1 modify partition part_2014 indexing off;
alter table t1 modify partition part_2015 indexing off;
alter table t1 modify partition part_2016 indexing on;

We can see the changes reflected in the output from the USER_TAB_PARTITIONS view. Indexing is off for the 2014 and 2015 partitions. Indexing is on for the 2016 partition.

select table_name,
       partition_name,
       indexing
from   user_tab_partitions
where  table_name = 'T1'
order by 1,2;

TABLE_NAME           PARTITION_NAME       INDE
-------------------- -------------------- ----
T1                   PART_2014            OFF
T1                   PART_2015            OFF
T1                   PART_2016            ON

SQL>

Index: INDEXING [FULL | PARTIAL] Clause

Even with the partition indexing settings in place, by default indexes are created as INDEXING FULL, so the partition setting are ignored.

We create a local index on the CREATED_DATE column. If we don't specify the INDEXING clause during index creation, the default action is INDEXING FULL.

drop index t1_local_partial_idx;

-- create index t1_local_partial_idx on t1(created_date) local indexing full;
create index t1_local_partial_idx on t1(created_date) local;

Checking the STATUS column of the USER_IND_PARTITIONS view we see all index partitions are usable. By default index creation ignores the INDEXING setting of the table partitions.

column index_name format a25

select index_name,
       partition_name,
       status
from   user_ind_partitions
where  index_name = 'T1_LOCAL_PARTIAL_IDX'
order by 1,2;

INDEX_NAME                PARTITION_NAME       STATUS
------------------------- -------------------- --------
T1_LOCAL_PARTIAL_IDX      PART_2014            USABLE
T1_LOCAL_PARTIAL_IDX      PART_2015            USABLE
T1_LOCAL_PARTIAL_IDX      PART_2016            USABLE

SQL>

Checking the INDEXING column of the USER_INDEXES view we see the index is marked as indexing FULL.

column indexing format a8

select index_name,
       indexing
from   user_indexes
where index_name = 'T1_LOCAL_PARTIAL_IDX'
order by 1;

INDEX_NAME                INDEXING
------------------------- --------
T1_LOCAL_PARTIAL_IDX      FULL

SQL>

We drop the index and create it again, this time using the INDEXING PARTIAL clause.

drop index t1_local_partial_idx;
create index t1_local_partial_idx on t1(created_date) local indexing partial;

Now we see the table partitions marked as INDEXING OFF are not indexed, and have index partitions marked as unusable. The 2016 table partition was marked as INDEXING ON, so it is indexed and we see a usable index partition.

select index_name,
       partition_name,
       status
from   user_ind_partitions
where  index_name = 'T1_LOCAL_PARTIAL_IDX'
order by 1,2;

INDEX_NAME                PARTITION_NAME       STATUS
------------------------- -------------------- --------
T1_LOCAL_PARTIAL_IDX      PART_2014            UNUSABLE
T1_LOCAL_PARTIAL_IDX      PART_2015            UNUSABLE
T1_LOCAL_PARTIAL_IDX      PART_2016            USABLE

SQL>

Checking the INDEXING column of the USER_INDEXES view we see the index is marked as indexing PARTIAL.

column indexing format a8

select index_name,
       indexing
from   user_indexes
where  index_name = 'T1_LOCAL_PARTIAL_IDX'
order by 1;

INDEX_NAME                INDEXING
------------------------- --------
T1_LOCAL_PARTIAL_IDX      PARTIAL

SQL>

Global indexes can also be created as partial indexes, with only the flagged partitions included in the index. We create a partial global index. Notice the use of the indexing partial clause. The resulting global index is marked as INDEXING PARTIAL.

create index t1_global_partial_idx on t1(description) global indexing partial;


-- Check the indexing status of the indexes.
select index_name,
       indexing
from   user_indexes
where  index_name like 'T1%'
order by 1;

INDEX_NAME                INDEXING
------------------------- --------
T1_GLOBAL_PARTIAL_IDX     PARTIAL
T1_LOCAL_PARTIAL_IDX      PARTIAL

SQL>

Modified Views

The {DBA|ALL|USER}_PART_TABLES, {DBA|ALL|USER}_TAB_PARTITIONS and {DBA|ALL|USER}_TAB_SUBPARTITIONS views have been modified to include an INDEXING column, which indicates if indexing is ON or OFF for the partition.

The {DBA|ALL|USER}_INDEXES view has been modified to include an INDEXING column, which indicates if the index is FULL or PARTIAL.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.