How do we create an index expression-based?
Expression-based indexes are indexes created using a combination of columns and SQL expressions. Expression-based indexes can speed up queries in which filtering happens based on expressions rather than columns. Once an expression-based index has been created on a table, eligible queries using that expression can make use of the index, which is then reflected in the EXPLAIN plan of the query.
- Example: (NuoDB 4.0.2-4)
This example shows how to create an index on an expression and then use the same expression in a SELECT statement.
CREATE TABLE shoes(shoe_num INTEGER, color STRING, shoe_type INTEGER);
CREATE INDEX sh_concat ON shoes(shoe_num || color || shoe_type);
INSERT INTO shoes VALUES (37,'blue',5);
INSERT INTO shoes VALUES (39,'blue',4);
INSERT INTO shoes VALUES (37,'black',4);
EXPLAIN (analyze ON) SELECT * FROM shoes WHERE (shoe_num || color || shoe_type) LIKE '37%';
Boolean sieve (micros: 28, rows out: 2, rows in: 2, out/in: 1.0000)
(SHOE_NUM || COLOR || SHOE_TYPE)[precomputed]
Index Scan SHOES (micros: 57 (f/o=22/35), rows out: 2, batches: 1)
Bitmap Index Scan SH_CONCAT [cost: 6.0, interval sel: 66.666%, predicate sel: 66.666%, keys: 2, rows: 2]
The expression on which the index is based must be deterministic.
It is not possible to use non-deterministic functions, such as RAND(), date, and time processing functions which output depends on the current time zone.
More information can be found here