Предположим, имеется таблица следующего вида:

CREATE TABLE test1 (
    id integer,
    content varchar
);

и приложение выполняет множество запросов типа:

SELECT content FROM test1 WHERE id = константа;

Без предварительной подготовки системе пришлось бы сканировать всю таблицу test1 таблицу, строку за строкой, чтобы найти все совпадающие записи. Если в таблице содержится много строк, test1 а запрос возвращает лишь малую их часть (одну или ни одной), такой метод поиска очевидно неэффективен. Но если системе было указано поддерживать индекс для столбца id столбца планировщик может использовать более эффективный метод поиска соответствующих строк. Например, может потребоваться пройти всего на несколько уровней вглубь дерева поиска.

Похожий подход используется в большинстве научно-популярных книг: термины и понятия, которые читатели ищут чаще всего, собраны в алфавитном указателе в конце книги. Заинтересованный читатель может относительно быстро просмотреть указатель и перейти к нужным страницам вместо того, чтобы читать всю книгу целиком в поисках необходимого материала. Подобно тому как автор должен предугадать темы, которые читатели, скорее всего, будут искать, задача разработчика базы данных — определить, какие индексы окажутся полезными.

Для создания индекса по id столбцу, как упоминалось ранее, можно использовать следующую команду:

CREATE INDEX test1_id_index ON test1 (id);

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

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

После создания индекса дальнейшее вмешательство не требуется: система будет автоматически обновлять индекс при изменении таблицы и использовать его в запросах, если сочтет это более эффективным, чем последовательное сканирование таблицы. Однако может потребоваться регулярный запуск ANALYZE команду регулярно для обновления статистики, чтобы планировщик запросов мог принимать обоснованные решения. См. Глава 2.11 для получения информации о том, как проверить, используется ли индекс, а также когда и почему планировщик может принять решение не использовать индекс.

Индексы также могут повысить производительность выполнения команд UPDATE и DELETE командами, содержащими условия поиска. Кроме того, индексы могут применяться для оптимизации поиска в операциях соединения. Таким образом, индекс, определенный для столбца, входящего в условие соединения, может также значительно ускорить выполнение запросов с соединениями.

В целом, Digital Q.DataBase индексы могут использоваться для оптимизации запросов, содержащих одно или несколько WHERE или JOIN предложений вида

индексируемый-столбец индексируемый-оператор значение-для-сравнения

В данном случае индексируемый-столбец представляет собой любой столбец или выражение, на основе которого был определен индекс. индексируемый-оператор — это оператор, входящий в класс операторов для индексируемого столбца. (Более подробная информация об этом приведена ниже.) И значение-для-сравнения может быть любым выражением, которое не является изменчивым (volatile) и не ссылается на таблицу индекса.

В некоторых случаях планировщик запросов может извлечь индексируемое условие такой формы из другой SQL-конструкции. Простой пример: если исходное условие имело вид

значение-для-сравнения оператор индексируемый-столбец

то его можно привести к индексируемому виду, если исходный оператор имеет коммутативный оператор, входящий в класс операторов индекса.

Создание индекса для большой таблицы может занять много времени. По умолчанию Digital Q.DataBase допускает чтение (SELECT инструкции) выполняться в таблице параллельно с созданием индекса, однако операции записи (INSERT, UPDATE, DELETE) блокируются до завершения построения индекса. В промышленных средах это часто недопустимо. Существует возможность разрешить запись параллельно с созданием индекса, но необходимо учитывать ряд нюансов — подробности см. в разделе Building Indexes Concurrently.

После создания индекса системе необходимо обеспечивать его синхронизацию с таблицей. Это увеличивает накладные расходы при выполнении операций манипулирования данными. Индексы также могут препятствовать созданию heap-only кортежей. Следовательно, индексы, которые используются редко или не используются вовсе, необходимо удалять.