The Search For Intelligent Life in the Cost-Based Optimizer
by Tim Gorman

Как работает Оптимизатор по Правилам

Однако давайте начнем с начала...

Оптимизатор по правилам (RBO) имеет только небольшой объем информации, используемый при выборе плана исполнения для оператора SQL:

• Сам текст оператора SQL
• Элементарная информация об объектах, расположенных в части FROM оператора SQL, таких как таблицы, кластеры и представления, и типы данных столбцов, указанных в других частях оператора
• Элементарная информация об индексах, ассоциированных с таблицами, на которые ссылается SQL оператор
• Информация словаря данных доступна только для локальной базы данных. Если Вы ссылаетесь на удаленную базу данных, ее словарь не доступен для RBO...

Для того чтобы определить план исполнения, RBO сначала просматривает часть оператора WHERE снизу вверх, отделяя каждый предикат от другого. Он накладывает ранг на каждый предикат, используя 15 методов доступа, упорядоченных по весьма сомнительному критерию:

1. Извлечение одной строки с помощью ROWID
2. Извлечение одной строки через кластерное соединение (cluster join)
3. Извлечение одной строки через хэш-кластер с помощью уникального кластерного ключа
4. Извлечение одной строки с помощью уникального индекса
5. Доступ через кластерное соединение
6. Доступ по ключу хэш-кластера
7. Доступ по ключу индексного кластера
8. Доступ по составному ключу
9. Доступ по неуникальному одностолбцовому индексу
10. Доступ через ограниченный диапазонный поиск по индексным столбцам
11. Доступ через неограниченный диапазонный поиск по индексным столбцам
12. Доступ через 'sort-merge' соединение (sort-merge join)
13. Поиск MAX или MIN значения по индексному столбцу
14. Операция ORDER BY по индексным столбцам
15. Полное табличное сканирование

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

Кроме того, отметьте преобладание в списке ранжирования табличных кластеров обоих типов: индексных и хэш-кластеров. Фактически, из первых семи правил в списке пять относятся к кластерам. За более чем 10 лет разработки приложений под Oracle я только два или три раза использовал преимущества табличных кластеров и только однажды в реальном промышленном приложении. Каждый раз, когда они рассматривались для применения, их недостатки перевешивали их преимущества. Сколько раз Вы использовали кластеры в вашей карьере?

Таким образом, только десять из пятнадцати правил уместны для обычных приложений. Десять. Не слишком богатая палитра выбора для такой сложной темы как выполнение SQL в Oracle8.

Для иллюстрации того, как может быть абсурден RBO, давайте рассмотрим пример следующего SQL оператора:

  SELECT COUNT(*)
  FROM  A, B, C
  WHERE A.STATUS = B.STATUS
  AND   A.B_ID = B.ID
  AND   B.STATUS = 'OPEN'
  AND   B.ID = C.B_ID
  AND   C.STATUS = 'OPEN';

Это простое соединение трех таблиц. Несколько замечаний:

• В таблице B по полю ID определен уникальный (UNIQUE) индекс (первичный ключ)
• По полю STATUS таблиц A, B определены неуникальные (NONUNIQUE) индексы. На таблице C такого индекса нет
• Существует неуникальный (NONUNIQUE) индекс по полю B_ID таблицы C, но нет (как кто-то мог ожидать) индекса на аналогичном поле таблицы А. Это специальное упущение, чтобы проиллюстрировать суть...
• В таблицу B (родительская сущность) была загружено 100 строк, в дочерние таблицы (A и C) - по 1000 строк (10 строк на каждую строку в таблице B)
• Все поля STATUS были заполнены одинаковым значением 'OPEN'