| 1 |
------------------ |
| 2 |
Expression indices |
| 3 |
------------------ |
| 4 |
|
| 5 |
Function: |
| 6 |
Allow to index arbitrary expressions applied to the row values, hence allowing |
| 7 |
indexed access paths to be used for expression-based predicates. |
| 8 |
|
| 9 |
Author: |
| 10 |
Oleg Loa <[email protected]> |
| 11 |
Dmitry Yemanov <[email protected]> |
| 12 |
|
| 13 |
Syntax rules: |
| 14 |
CREATE [UNIQUE] [{ASC[ENDING] | DESC[ENDING]}] INDEX <index_name> ON <table_name> |
| 15 |
COMPUTED [BY] ( <value_expression> ) |
| 16 |
|
| 17 |
Scope: |
| 18 |
DSQL (DDL) |
| 19 |
|
| 20 |
Example(s): |
| 21 |
1. CREATE INDEX IDX1 ON T1 COMPUTED BY ( UPPER(COL1 COLLATE PXW_CYRL) ); |
| 22 |
SELECT * FROM T1 WHERE UPPER(COL1 COLLATE PXW_CYRL) = '<capital cyrillic letters sequence>' |
| 23 |
-- PLAN (T1 INDEX (IDX1)) |
| 24 |
2. CREATE INDEX IDX2 ON T2 COMPUTED BY ( EXTRACT(YEAR FROM COL2) || EXTRACT(MONTH FROM COL2) ); |
| 25 |
SELECT * FROM T2 ORDER BY EXTRACT(YEAR FROM COL2) || EXTRACT(MONTH FROM COL2) |
| 26 |
-- PLAN (T2 ORDER IDX2) |
| 27 |
|
| 28 |
Note(s): |
| 29 |
1. An expression used in the index declaration must match a predicate expression precisely |
| 30 |
to allow the engine to choose an indexed access path. Otherwise, the given index won't be used |
| 31 |
for a retrieval/sorting. |
| 32 |
2. Expression indices have exactly the same features and limitations as usual indices, with the |
| 33 |
only exception that, by definition, they cannot be composite (multi-segment). |