Отключение индексов

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

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

Можно ли отключить индекс в запросе? Мы говорили, что индексы не всегда оказывают положительное влияние на производительность запросов. Давайте рассмотрим крайний случай. Представьте, что вся таблица EMPLOYEE, изображенная на рис. 6.4, читается с использованием индекса.

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

Должен существовать момент, когда более эффективным становится сканирование таблицы, а не использование индекса. Чтобы его установить, определите количество индексов и элементов данных в блоке и число уровней в индексе, а затем сравните количество логических операций ввода-вывода для каждого метода. Существует и общее эмпирическое правило — если ожидается, что число возвращаемых строк превысит 15—20% от числа строк в таблице, то, вероятно, лучше использовать полное сканирование таблицы. (Оптимизатор по стоимости, кажется, применяет в качестве точки отключения индексов значение 5%, что, конечно, очень мало.)

Как явно отключить индекс в запросе? В Oracle версии 6 это делалось путем определенной модификации индексированного столбца. Вот несколько примеров:

SELECT *
FROM employee emp
/* конкатенировать пустую строку с символьным столбцом */
WHERE emp.ename || '' = 'SMITH';

SELECT *
FROM employee emp
/* использовать функцию, которая не влияет на смысл */
WHERE UPPER (emp. ename) = 'SMITHS';

SELECT *
FROM employee emp
/* добавить нуль в числовые столбцы */
WHERE emp.empno + 0 = 1234;

Из-за методов упорядочивания индекса и проведения поиска в нем всякая трансформация, влияющая на значение столбца и, следовательно, на его положение в индексе, приводит к тому, что индекс для поиска данного элемента использоваться не будет.

В Oracle7 индекс отключается через подсказку оптимизатору. Этот метод имеет ряд существенных преимуществ:

• он более понятен и очевиден для читающего SQL-запрос;

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

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

SELECT /*+ FULL(EMP) */
FROM employees emp
WHERE emp.ename = 'SMITH';

Еще одно преимущество данного метода состоит в том, что оптимизатору можно указать, какие индексы использовать при наличии выбора и что оптимизировать — пропускную способность (ALL ROWS) или время реакции (FIRST ROWS).

Хочется верить, что когда оптимизатор по стоимости версии 7.3 окончательно "созреет", отключение индексов станет забытым искусством.

Дата добавления: 2015-08-09