Самоучитель по SQL-сервер в Linux
         

CREATE INDEX

Создает индекс для таблицы.

Синтаксис

CREATE [ UNIQUE ] INDEX индекс ON таблица

             [ USING тип ] ( поле [ класс ] [, ...] )

CREATE [ UNIQUE ] INDEX индекс ON таблица

            [ USING тип ] ( функция ( поле [....])[ класс ] )

Параметры

  • UNIQUE. Необязательное ключевое слово UNIQUE. При его присутствии база данных автоматически проверяет наличие повторяющихся значений в поле (или группе полей), для которых создается индекс. Проверка происходит как при создании индекса, так и при каждом включении данных в таблицу. В дальнейшем PostgreSQL выдает ошибку при выполнении команд INSERT или UPDATE, в результате которых в индексе появляются повторяющиеся значения и команда завершается неудачей.


  • индекс. Имя создаваемого индекса.
  • таблица. Имя таблицы, в которой создается индекс.
  • тип. Алгоритм, используемый при построении индекса. Допустимы три значения:
    • btree — реализация на базе В-деревьев Лемана-Яо с высокой степенью параллельности;
    • rtree — реализация па базе стандартных R-деревьев с использованием квадратичного разбиения по алгоритму Гуттмана;
    • hash — реализация на базе алгоритмов линейного хэширования.
  • поле. Поле (или разделенный запятыми список полей), по которому строится индекс.
  • класс. Необязательный операторный класс. Для большинства пользователей этот параметр не важен.
  • функция. Имя функции, вызываемой для заданных полей (вместо прямого индексирования данных). Указанная функция должна возвращать отдельное значение (не группу значений!), по которому и строится индекс.

Результаты

  • CREATE. Сообщение возвращается при успешном создании индекса.
  • ERROR: Cannot create Index: 'индекс' already exists. Ошибка— индекс с указанным именем уже существует.
  • ERROR: DefineIndex: attribute "поле" not found. Ошибка — заданное иоле не существует в индексируемой таблице.
  • ERROR: DefineIndex: relation "таблица" not found. Ошибка — заданная таблица не существует в подключенной базе данных.

Описание

Команда CREATE INDEX строит оптимизирующий индекс по значениям одного или нескольких полей заданной таблицы. Следует помнить, что индексы предназначены для повышения эффективности и быстродействия операций с базами данных, однако в некоторых таблицах затраты на сопровождение индекса перевешивают выгоду от его использования. В этом случае индекс снижает общее быстродействие.

Построение индексов по полям

При создании «традиционного» индекса указывается список из одного или нескольких полей таблицы. Эта разновидность индексов используется запросами, напрямую работающими с индексируемыми полями в секции WHERE. Учтите, что индексы типов rtree и hash индексируют только одно поле, а индекс типа btree позволяет индексировать до 16 полей.

Функциональный индекс

К другой категории относятся функциональные индексы. Функциональный индекс строится по возвращаемому значению функции, примененной к одному пли нескольким полям. Такие индексы обычно используются запросами, часто вызывающими одну и ту же функцию в секции WHERE.

Например, если в секции WHERE некоторого запроса часто вызывается функция upper(last_name), этот запрос можно оптимизировать построением функционального индекса по результату функции upper(last_name).

Операторы и операторные классы

Оптимизатор запросов PostgreSQL при сравнении использует разные индексы для разных операторов. Тип индекса выбирается по списку, приведенному в табл. 14.1.

Таблица 14.1. Соответствие «оператор-индекс»

Индекс Оператор

В-дерево

<, <=, >=, >

R-дерево

«, &<, &>, », @, — , &&

Хэш

=

Для каждого индексируемого поля можно задать операторный класс, который должен использоваться при индексации. Данная возможность существует только потому, что в некоторых обстоятельствах имеется несколько вариантов осмысленного упорядочения данных. Впрочем, типа индексации, принятого по умолчанию, обычно бывает достаточно, если только вы не занимаетесь разработкой собственных нестандартных типов и операторов.

Примеры

Следующая команда строит уникальный индекс по полю Id таблицы employees:

booktown=# CREATE UNIQUE INDEX employee_id_idx

booktown-# ON employees (id);

CREATE

В другом фрагменте по полю last_name таблицы employees строится функциональный индекс с использованием функции upper():

booktown=# CREATE INDEX employee_upper_name_idx

booktown-# ON employees (upper(last_name));

CREATE

Содержание раздела