правилам. Пока Oracle не решит вложить средства в переработку этого кода или не наделит оптимизатор по стоимости способностью генерировать эффективные пути доступа, оптимизатор по правилам, скорее всего, останется в строю.

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

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

Оптимизатор по стоимости работает путем оценки стоимости всех обоснованных вариантов оптимизации запроса и выбора варианта с минимальной стоимостью. Стоимость в данном контексте есть оценка числа посещений блоков базы данных и числа операций сетевого ввода-вывода. Использование центрального процессора при этом не учитывается. Расчет производится на основании статистической информации о таблице, хранящейся в словаре данных, которая вычисляется или оценивается по случайной выборке с помощью команды ANALYZE. Эта информация отражает хранение и распределение данных в разрезе таблиц, кластеров и индексов. Собирается следующая статистическая информация:

О таблице:

Общее количество строк Количество блоков со строками Количество пустых блоков

Средний объем свободного пространства на блок Количество связанных блоков Средняя длина строки

О столбце:

Количество различных значений Наименьшее значение в столбце Наибольшее значение в столбце

Об индексе:

Глубина (количество ветвей плюс лист)

Количество блоков-листьев

Количество различных ключевых значений

Среднее количество блоков-листьев индекса на ключ Среднее количество блоков данных на ключ

Количество логических блочных операций ввода-вывода при чтении всей таблицы с использованием индекса

(Только в версии 7.3) 75-точечная гистограмма, на которой отражаются индексные ключи; по этим данным степень избирательности любой индексной операции можно оценить с точностью до 2,5%

Даже при наличии столь обширной статистической информации оптимизатору по стоимости приходится делать ряд серьезных предположений относительно ее применимости и надежности (помните: есть ложь, гнусная ложь и статистика!).

Первое предположение состоит в том, что эта статистика точно отражает реальность. Эта информация не собирается автоматически, а вычисляется SOL-командой ANALYZE. Регулярный запуск этой команды является обязанностью администратора базы данных. Это означает, гто данная информация, будут сохраненной в словаре данных, подвержена старению и со временем становится менее надежной (вероятно, это грустное отражение нас самих). В худшем (и невероятном) случае может оказаться, что мы вычислили статистическую информацию, полностью удалили данные из таблицы и загрузили в нее новые данные. Это даст нам статистическую информацию, абсолютно не связанную с данными! Опыт показал, что даже таблицы, подверженные интенсивному обновлению, не проявляют тенденции к сильному изменению своей общей формы - поэтому это не очень ужасный кошмар. Тем не менее, следует планировать регулярное обновление статистической информации.

Еще одно предположение, которое неявно делает оптимизатор по стоимости (в версиях до 7.3), состоит в том, что значения в индексе распределены равномерно. Другими словами, если в таблице 500 тысяч строк и индексированный столбец может иметь два значения (например, Y и N), то оптимизатор по стоимости предполагает, что каждое значение встречается 250 тысяч раз, и вообще не будет использовать индекс. Если же N встречается только 100 тысяч раз, то, естественно, более эффективно выбирать эти значения при помощи индекса. На устранение этого недостатка направлены гистограммы в версии 7.3.

Итак, подытожим наши впечатления от оптимизатора по стоимости.

В версиях до 7.2 его вообще не следует использовать.

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

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

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

Проблема связанной переменной

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

SELECT c.cu3t#

, c.cust name FROM custs с WHERE с. country cocle = : country;

SELECT c.cu3t#

, c.cu3t name FROM report clriver r , custs с WHERE r.reportt = :report num AND country cocle LIKE r.param;

Если эта таблица находится в базе данных какой-нибудь американской корпорации, то может случиться, что из 50000 клиентов 48000 имеют код страны US и лишь 300 клиентов - код страны FR (Франция). Если построен индекс по столбцу COUNTRY CODE, то весьма разумно использовать его в запросе, когда связанная переменная имеет значение, отличное от US, так как мы знаем, что при этом будет извлекаться fienee 4% строк таблицы. Но поскольку решение об оптимизации принимается до того, как станет известно значение связанной переменной, то знание распределения значений в данном случае совершенно бесполезно.

Проблема становится более сложной для решения, если значение выбирается из другой таблицы, так как в этом случае нам известно только то, что в таблице REPORT DRrVER для любого номера oтгeтa может быть несколько строк. Действительно, интеллектуальный оптимизатор посмотрел бы, является ли столбец REPORT* первичным ключом таблицы REPORT DRIVER, чтобы определить возможность налишя нескольких значений. Однако в любом случае к тому моменту, когда оптимизатор определит, что в столбце CUSTS ищется значение US, процесс обработки запроса зайдет довольно Далеко. Для всех версий Oracle? эта проблема разрешается просто - значения связанных переменных никогда не используются в решениях об оптимизации. Вместо этого оптимизатор делает интеллектуальное предположение о вероятных свойствах этого значения.