Debian Codegraph @

doc/sql.extensions/README.expression_indices

raw
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).