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
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
PARTIAL is specified.
By default, any index is created as
FULL index, which decouples the index from the table indexing property.
INDEXING clause may also be specified at the partition and subpartition levels.
The following SQL DDL creates a table with these items:
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
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
CREATE INDEX ORDERS_ORDER_TOTAL_GIDX ON ORDERS (ORDER_TOTAL) GLOBAL INDEXING PARTIAL;
ORDERS_ORDER_TOTAL_GIDX index is created to index only those partitions that have
ON, and excludes the remaining partitions.
Updates to views include the following:
Table Indexing Property - The column
INDEXING is added to *
_TAB_PARTITIONS, and *
This column has one of two values
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
Partial Global Index Optimization - The column
ORPHANED_ENTRIES is added to the dictionary views
USER_IND_PARTITIONS to represent if a global index (partition) contains stale entries owing to deferred index maintenance during
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