6. Indexes on Expressions
Some DBMS support in the
CREATE INDEX ind_name ON tbl_name( column1, … ) not only columns but expressions, for example:
CREATE INDEX ind1 ON T1( LOWER(f1) )
Valentina DB does not support such syntax, but you can get the same functionality, and even better, using Table Methods (aka Calculated Fields).
So you create calculated field f1_lower=“lower(f1)” in Table T1 and define this 'virtual field' to be indexed. You will get index on disk, which contains values calculated by that expression. To use this index, specify the name of calculated field in the query:
SELECT * FROM T1 WHERE f1_lower = 'abcd'
It is clear that the advantage of Valentina's approach is that system understands which index to use, without any additional analyze. Also when you define calculated field - you can choose its type, which plays role of explicit type for calculation result.