Expression Indexes

O. Loa, D. Yemanov, A. Karyakin

Arbitrary expressions applied to values in a row in dynamic DDL can now be indexed, allowing indexed access paths to be available for search predicates that are based on expressions.

Syntax Pattern

   CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX <index name>
    ON <table name>
    COMPUTED BY ( <value expression> )

Examples

   CREATE INDEX IDX1 ON T1
     COMPUTED BY ( UPPER(COL1 COLLATE PXW_CYRL) );
   COMMIT;
   /**/
   SELECT * FROM T1
     WHERE UPPER(COL1 COLLATE PXW_CYRL) = 'ÔÛÂÀ'
   -- PLAN (T1 INDEX (IDX1))

   CREATE INDEX IDX2 ON T2
     COMPUTED BY ( EXTRACT(YEAR FROM COL2) || EXTRACT(MONTH FROM COL2) );
   COMMIT;
   /**/
   SELECT * FROM T2
     ORDER BY EXTRACT(YEAR FROM COL2) || EXTRACT(MONTH FROM COL2)
   -- PLAN (T2 ORDER IDX2)

Note

The expression used in the predicate must match exactly the expression used in the index declaration, in order to allow the engine to choose an indexed access path. The given index will not be available for any retrieval or sorting operation if the expressions do not match.
Expression indices have exactly the same features and limitations as regular indices, except that, by definition, they cannot be composite (multi-segment).