Oracle SQL. 100 шагов от новичка до профессионала. 20 дней новых знаний и практики

Шаг 10. Индексы

Введение

Индексы – это специальные ссылочные массивы в базах данных. Назначение индексов – ускорение поиска данных, процессов сортировки данных. Обычно индексы увеличивают производительность запросов к базе данных.

Теория и практика

Индексы работают по принципу b-tree, то есть сбалансированной древовидной структуры.

Для примера: у нас в одной из колонок таблицы есть уникальные значения от 1 до 500 000.

Нам необходимо найти значение 255 000. По ссылочному индексу мы определяем, больше или меньше искомое значение 250 000, то есть половины всех значений, далее больше или меньше искомое значение 260 000, далее мы переходим к нашему значению 255 000.

Мы не просматривали каждую запись таблицы, а нашли наше значение за несколько итераций.

Индексы создаются для определенной колонки (колонок) таблицы.

Процесс пересоздания индексов может занимать значительное время, это необходимо учитывать при операциях вставки и обновления, удаления данных.

Синтаксис

CREATE INDEX IDx_NAME ON TABLE_NAME (column_NAME);

Idx_NAME – наименование индекса;

TABLE_NAME – наименование таблицы, где создается индекс;

column_NAME – наименование колонки, для которой создается индекс.

Пример создания индекса для колонки MARK таблицы AUTO:

CREATE INDEX IDx_AUTO_MARK ON AUTO (MARK);

Реверсивный индекс

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

Синтаксис

CREATE INDEX IDx_NAME ON TABLE_NAME (column_NAME) REVERSE;

Пример: создание реверсивного индекса для колонки MARK таблицы AUTO.

CREATE INDEX reg_DATE ON AUTO (reg_num) REVERSE;

Удаление индекса

Для удаления индекса используется команда

DROP INDEX IDx_NAME;

Индексы создаются для определенной колонки таблицы.

Процесс пересоздания индексов может занимать значительное время, это необходимо учитывать при операциях вставки и обновления, удаления данных.

Важные замечания

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

Индексы рекомендуется создавать на колонках, которые используются в операциях объединения.

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

При наименовании индексов следует придерживаться следующего правила: IDx_имя таблицы_имена_колонок.

Вопросы учеников

Если таблица небольшая, в ней не более 200 записей например, нужен ли в такой таблице индекс?

Нет, индексы для такой таблицы, скорее всего, не понадобятся.

Какие типы индексов существуют в различных СУБД?

Существует множество разных типов индексов, но более подробно мы разберем эту тему в следующих шагах.

В моей базе данных фильтр (WHERE) данных, поиск данных всегда осуществляется одновременно по определенному набору колонок. Какие индексы следует использовать в этом случае?

В этом случае создается композитный индекс.

Синтаксис:

CREATE INDEX IDx_NAME ON TABLE_NAME (column_NAME1, column_NAMEn) REVERSE;

Контрольные вопросы и задания для самостоятельного выполнения

1. В таблице есть ограничение уникальности, имеет ли смыcл создавать на этой колонке индекс?
2. В некоторой таблице постоянно обновляются записи, следует ли использовать индексы в этой таблице?
3. Создайте индекс на колонку COLOR в таблице AUTO.
4. Создайте реверсивный индекс для колонки YEAROLD в таблице MAN.

День третий

Шаг 11. Простые запросы

Введение

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