Обычно указатель SQL ссылается на колонки таблицы. Но индекс может также быть сформирован в выражениях, включающих столбцы таблицы.

Как пример, рассмотрите следующую таблицу, которая отслеживает изменения суммы в долларах на различных счетах:

CREATE TABLE account_change(chng_id INTEGER PRIMARY KEY,
                            acct_no INTEGER REFERENCES account,
                            location INTEGER REFERENCES locations,
                            amt INTEGER,  -- in cents
                            authority TEXT, comment TEXT);
CREATE INDEX acctchng_magnitude ON account_change(acct_no, abs(amt));

Каждый вход в таблице account_change делает запись депозита или отказа на счет. У депозитов есть положительное "amt" и у отказов есть отрицательное "amt".

Индекс acctchng_magnitude по номеру счета ("acct_no") и на абсолютном значении суммы. Этот индекс позволяет делать эффективные запросы по величине изменения счета. Например, чтобы перечислить все изменения номера счета $xyz, которые составляют больше 100.00$, можно сказать:

SELECT * FROM account_change WHERE acct_no=$xyz AND abs(amt)>=10000;

Или, чтобы перечислить все изменения одного конкретного счета ($xyz) в порядке уменьшающейся величины, можно написать:

SELECT * FROM account_change WHERE acct_no=$xyz ORDER BY abs(amt) DESC;

Оба вышеупомянутых запроса в качестве примера хорошо работали бы без индекса acctchng_magnitude. Индекс acctchng_magnitude просто помогает работать быстрее, особенно на базах данных, где есть много записей в таблице для каждого счета.

1. Как использовать индексы по выражениям

Используйте CREATE INDEX, чтобы создать новый индекс по одному или более выражениям точно так же, как создаете индекс на колонках. Единственная разница в том, что выражения перечисляются как элементы, которые будут внесены в указатель, а не имена столбцов.

Планировщик запросов SQLite рассмотрит использование индекса по выражению, когда выражение, которое внесено в указатель, появится в операторе Where или в пункте ORDER BY запроса ТОЧНО как это написано в CREATE INDEX. Планировщик вопроса не делает алгебры. Чтобы соответствовать ограничениям оператора Where и условиям ORDER BY к индексам, SQLite требует, чтобы выражения были теми же самыми, за исключением незначительных синтаксических различий, таких как изменения пробела. Таким образом, если вы имеете:

CREATE TABLE t2(x,y,z);
CREATE INDEX t2xy ON t2(x+y);

И затем вы управляете запросом:

SELECT * FROM t2 WHERE y+x=22;

Тогда индекс не будет использоваться, потому что выражение в CREATE INDEX (x+y) не является тем же самым выражением, как это появляется в запросе (y+x). Эти два выражения могли бы быть математически эквивалентными, но планировщик запросов SQLite настаивает, что они должны быть тем же самым, не просто эквивалентными. Рассмотрите переписывание запроса таким образом:

SELECT * FROM t2 WHERE x+y=22;

Этот второй запрос будет, вероятно, использовать индекс, потому что теперь выражение в операторе Where (x+y) точно соответствует выражению в индексе.

2. Ограничения

Есть определенные разумные ограничения на выражения, которые появляются в CREATE INDEX:

1. Выражения в CREATE INDEX могут обратиться только к колонкам внесенной в указатель таблицы, но не к колонкам в других таблицах.

2. Выражения в CREATE INDEX могут содержать вызовы функции, но только функций, вывод которых всегда определяется полностью ее входными параметрами (детерминированные функции). Очевидно, такие функции как random(), не будут работать хорошо в индексе. Но также и такие функции, как sqlite_version(), хотя они постоянные через любое соединение с базой данных, но они не постоянные через жизнь всю основного файла базы данных, а следовательно не могут использоваться в CREATE INDEX.

   Обратите внимание на то, что определенные применением функции SQL по умолчанию считают недетерминированными и их нельзя использовать в CREATE INDEX, если флаг SQLITE_DETERMINISTIC не используется, когда функция зарегистрирована.

3. Выражения в CREATE INDEX не могут использовать подзапросы.

4. Выражения могут использоваться только в CREATE INDEX, но не в UNIQUE или ограничениях PRIMARY KEY в CREATE TABLE.

3. Совместимость

Способность внести выражения в указатель была добавлена к SQLite с version 3.9.0 (2015-10-14). База данных, которая использует индекс по выражениям, не будет применима более ранними версиями SQLite.