You can create local and global indexes on a subset of the partitions of a table, enabling more flexibility in index creation. This feature is supported using a default table indexing property. When a table is created or altered, a default indexing property can be specified for the table or its partitions. The table indexing property is only considered for partial indexes.
When an index is created as PARTIAL on a table:
Local indexes: An index partition is created usable if indexing is turned on for the table partition, and unusable otherwise. You can override this behavior by specifying USABLE/UNUSABLE at the index or index partition level.
Global indexes: Includes only those partitions for which indexing is turned on, and exclude the others.
This feature is not supported for unique indexes, or for indexes used for enforcing unique constraints. FULL is the default if neither FULL nor PARTIAL is specified.
By default, any index is created as FULL index, which decouples the index from the table indexing property.
The INDEXING clause may also be specified at the partition and subpartition levels.
The following SQL DDL creates a table with these items:
Partitions ORD_P1 and ORD_P3 are included in all partial global indexes
Local index partitions (for indexes created PARTIAL) corresponding to the above two table partitions are created usable by default.
Other partitions are excluded from all partial global indexes, and created unusable in local indexes (for indexes created PARTIAL).
CREATE TABLE orders (
order_id NUMBER(12),
order_date DATE CONSTRAINT order_date_nn NOT NULL,
order_mode VARCHAR2(8),
customer_id NUMBER(6) CONSTRAINT order_customer_id_nn NOT NULL,
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT order_mode_lov CHECK (order_mode in ('direct','online')),
CONSTRAINT order_total_min CHECK (order_total >= 0))
INDEXING OFF
PARTITION BY RANGE (ORDER_DATE)
(PARTITION ord_p1 VALUES LESS THAN (TO_DATE('01-MAR-1999','DD-MON-YYYY'))
INDEXING ON,
PARTITION ord_p2 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY'))
INDEXING OFF,
PARTITION ord_p3 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY'))
INDEXING ON,
PARTITION ord_p4 VALUES LESS THAN (TO_DATE('01-MAR-2000','DD-MON-YYYY')),
PARTITION ord_p5 VALUES LESS THAN (TO_DATE('01-MAR-2010','DD-MON-YYYY')));
A local or global partial index, can be created to follow the table indexing properties of the previous SQL example by specification of the INDEXING PARTIAL clause.
CREATE INDEX ORDERS_ORDER_TOTAL_GIDX ON ORDERS (ORDER_TOTAL) GLOBAL INDEXING PARTIAL;
The ORDERS_ORDER_TOTAL_GIDX index is created to index only those partitions that have INDEXING ON, and excludes the remaining partitions.
Updates to views include the following:
Table Indexing Property - The column INDEXING is added to *_PART_TABLES, *_TAB_PARTITIONS, and *_TAB_SUBPARTITIONS views.
This column has one of two values ON or OFF, specifying indexing on or indexing off.
Partial Global Indexes as an Index Level Property - A new column INDEXING is added to the USER_INDEXES view. This column can be set to FULL or PARTIAL.
Partial Global Index Optimization - The column ORPHANED_ENTRIES is added to the dictionary views USER_INDEXES and USER_IND_PARTITIONS to represent if a global index (partition) contains stale entries owing to deferred index maintenance during DROP/TRUNCATE PARTITION, or MODIFY PARTITION INDEXING OFF. The column can have one of three values:
YES => the index (partition) contains orphaned entries
NO => the index (partition) does not contain any orphaned entries
Oracle Database Reference for information about the database views