Dmitry Yemanov
Tracker ticket: #7257
This feature allows to index only a subset of table rows defined by the search condition specified during index creation.
CREATE [UNIQUE] [{ASC[ENDING] | DESC[ENDING]}] INDEX <index_name> ON <table_name> { (<column_list>) | COMPUTED [BY] ( <value_expression> ) } WHERE <search_condition>
-- 1. CREATE INDEX IT1_COL ON T1 (COL) WHERE COL < 100; SELECT * FROM T1 WHERE COL < 100; -- PLAN (T1 INDEX (IT1_COL)) -- 2. CREATE INDEX IT1_COL2 ON T1 (COL) WHERE COL IS NOT NULL; SELECT * FROM T1 WHERE COL > 100; -- PLAN (T1 INDEX IT1_COL2) -- 3. CREATE INDEX IT1_COL3 ON T1 (COL) WHERE COL = 1 OR COL = 2; SELECT * FROM T1 WHERE COL = 2; -- PLAN (T1 INDEX IT1_COL3)
Notes:
A partial index definition may include the UNIQUE specification.In this case, every key in the index is required to be unique.This allows to enforce uniqueness across some subset of table rows.
A partial index is usable only in the following cases:
The WHERE condition includes exactly the same boolean expression as the one defined for the index;
The search condition defined for the index contains ORed boolean expressions and one of them is explicitly included in the WHERE condition;
The search condition defined for the index specifies IS NOT NULL and the WHERE condition includes an expression on the same field that is known to ignore NULLs.