Switch to: V10V9V8V7V6V5

7. Partial Indexes

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate.

Difference from Other DBMS

Some other DBMS can support this feature via ''CREATE INDEX … WHERE …” command.

In Valentina DB you can get the same effect using Table Methods aka Calculated Fields. You should specify expression in such way, that excluded values become NULLs. Since Valentina does not put NULLs into indexes, you get index only on NOT NULL values.

Having indexed calculated field, you can easily mention it in the SELECT queries, to do indexed search only for that values.

Example

Let you want index only values in range 1 .. 100.

Make indexed calculated field f1_1_100 with expression

  "if( 1<=f1 and f1 <= 100, f1, NULL )"

See Table Methods for details how to create them in API or SQL ways.

Now you can use it in searches.

SELECT *
FROM T1
WHERE f1_1_100 = 56

See also

* Valentina SQL Expressions - list of all possible functions and operators that can be used in expression.

* Table Methods