Рекомендации для разработчика SQL

Источник: oracloid

1.0 Введение

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

Разработчикам следует принимать во внимание, что в языках четвертого поколения (как и в языках третьего поколения) первый и очевидный
вариант программы не обязательно является лучшим.

Особое внимание надо обратить на необходимость придерживаться определенного стиля при написании SQL операторов, помимо всего прочего это позволит программам воспользоваться преимуществом SQL кэша в Oracle V7. Седьмая версия Oracle имеет некий SQL кэш, в котором содержатся разобранные (parsed) SQL запросы. Oracle определяет находится ли запрос в кэше с помощью не чувствительного к регистру сравнения.

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

2.0 Стиль написания SQL операторов

2.1 Основные положения

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

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

2.2 Ключевые слова SQL

Ключевые слова Oracle следует писать ПРОПИСНЫМИ БУКВАМИ

Перечисленные ниже ключевые слова следует писать с новой строки:

          SELECT
          INTO
          FROM
          WHERE
          AND/OR
          GROUP BY
          HAVING
          CONNECT BY
          FOR UPDATE OF
          ORDER BY

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

          SELECT    sal,
                    Job,
                    ename,
                    dept
          FROM      emp
          WHERE     sal > any
                    (SELECT   sal
                    FROM emp
                    WHERE deptno = 30)
          ORDER BY  sal;

Строки, которые не начинаются с приведенных выше ключевых слов следует начинать с позиции второго слова в предыдущей строке, например:
```
          SELECT    ename,
                    dept
          FROM      emp
```

2.3 Константы и переменные

Константы, переменные и т.п. следует располагать с правой стороны от слов
WHERE или HAVING.

          SELECT    ename
          FROM      emp
          WHERE     empno = '1232'          Or
          SELECT    ename
          FROM      emp
          WHERE     empno = :1

2.4 Внешние объединения (outer joins)

Поля, которые вовлекаются в операцию внешнего объединения (outer join) следует писать с правой стороны от слов WHERE или HAVING.

          SELECT    ename
          FROM      emp e,
                    dept d
          WHERE     e.empno = d.empno(+)

2.5 Синонимы таблиц (aliases)

Синонимы для таблиц следует применять во всех запросах, в которых упоминается больше одной таблицы в предложении FROM. Использование синонимов (alias) в таких запросах ускоряет операцию разбора SQL оператора ядром Oracle, поскольку уменьшает рекурсию запросов.

          SELECT    count(*)
          FROM      oe o,
                    oe_link l,
                    oe_link_name n
          WHERE     o.oe = l.oe
          AND       l.name = n.name

Отметьте, что синонимы o, l, n используются в предложении WHERE.

2.6 Порядок условий в предложении WHERE

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

3.0 Запросы к БД

3.1 Порядок перечисления таблиц в предложении FROM

Порядок перечисления таблиц в предложении FROM имеет значение для оптимизатора, когда оптимизатор выбирает способ выполнения запроса. Оптимизатор работает следующим образом. Он просматривает предложения WHERE и присваивает таблицам определенный вес, основываясь на типе предиката, т.е. field = 'const' или field = field(+). Затем он выбирает таблицу с наименьшим весом и делает ее управляющей (driving) таблицей. Однако, здесь есть один тонкий момент, если несколько таблиц получают одинаковый вес и он является
наименьшим, то оптимизатор выбирает в качестве управляющей ту таблицу, которая стоит последней в предложении FROM.

Список весовых коээфициентов предикатов приведен в приложении A.

Рассмотрим следущий пример.

          Indexes :          unique on oe(id)
          unique on oe_link(oe)
          unique on oe_link_name(name)
          non unique on oe(oe)
        -------------------------------------
          SELECT    count(*)
          FROM      oe_link l,
                    oe_link_name n,
                    oe o
          WHERE     o.oe = l.oe
          AND       l.name = n.name
          Time 621.21 secs
          SELECT    count(*)
          FROM      oe o,
                    oe_link l,
                    oe_link_name n
          WHERE     o.oe = l.oe
          AND       l.name = n.name
          Time 197.05 secs

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

3.2 Непреднамеренное запрещение индексов

Существует очень простой путь непреднамеренно исключить использование индексов в запросах.

On table oe, which is described in the В таблице OE, которая
описана в приложении С, поле id имеет тип NUMBER.

          SELECT    id,
                    oe
          FROM      oe
          WHERE     to_char(id) = 1232          Time 97 secs.
          в то время, как
          SELECT    id,
                    oe
          FROM      oe
          WHERE     id = 1232
          Time .4 secs.

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

В следующем примере индексы также не используются.

          SELECT    id,
                    oe
          FROM      oe
          WHERE     id+1 = 1233

Использование типа данных DATE в предложении WHERE часто приводит к проблемам. Например, выбрать записи сделанные в определенный день можно следующими тремя способами.

          SELECT    *
          FROM      oe
          WHERE     trunc(timestamp) = '26-MAR-91'          SELECT    *
          FROM      oe
          WHERE     timestamp between '26-mar-91'
          AND       '27-mar-91'
          SELECT    *
          FROM      oe
          WHERE     timestamp >= to_date('26-mar-91:00:00',
                    'dd-mon-yy:hh24:mi')
          AND       timestamp < to_date('27-mar-91:00:00',
                    'dd-mon-yy:hh24:mi')

Первый запрос будет выполняться 240 секунд.

Выполнение второго запроса займет всего 1.05 секунды, однако он имеет некоторую особенность. В результаты выборки попадут строки, введенные в полночь 27 марта.

Третий запрос будет выполняться всего 0.5 секунды и он не будет выбирать записи созданные в полночь.

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

Пример a) следует переписать как пример b)

          a)          SELECT    *
          FROM      job
          WHERE     db_id//job_no = 'AZ0100201'
          b)
          SELECT    *
          FROM      job
          WHERE     db_id = 'AZ'
          AND       job_no  = '0100201'

Разработчикам следует принимать во внимание, что при разборе SQL оператора Oracle неявно выполняет преобразование типа данных. Oracle пожет выбрать преобразование типа данных как для поля, так и для константы. Если Oracle решит преобразовать тип данных поля, то он не будет использовать индекс по этому полю. В нижеприведенной таблице приведены правила, по которым Oracle выбирает что преобразовывать.

Mixed Common Function
datatype Unit chosen
-------------- ----------- --------

Char with number number to_number
Char with rowid rowid to_rowid
Char with date date to_date

Следующий пример показывает как эти правила работают на практике.

          SELECT    deptno
          FROM      dept
          WHERE     deptno = 1324          indexes
          non unique index on deptno

Этот SELECT не будет использовать индексы, потому что поле deptno будет преобразовываться ядром Oracle к типу NUMBER.

А эти SELECT'ы будет использовать индекс.

          SELECT    deptno
          FROM      dept
          WHERE     deptno = to_char(1324)          SELECT    deptno
          FROM      dept
          WHERE     deptno = '1324'

3.3 Намеренное запрещение использования индексов

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

Datatype Inhibit expression
-------- ------------------

Char char//''
number number+0
date add_months(date,0)

Применение nvl(column,0) возможно со всеми типами данных, однако, в некоторых случаях это может привести к выполнению множества ненужных математических операций, см. следующий пример.

          SELECT    deptno
          FROM      dept
          WHERE     nvl(deptno,0) = '1234'

3.4 Различные варианты написания запросов

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

Продемонстрируем это на следующем примере, пусть нам надо найти человека, который не имеет задания. В БД имеется 99 человек и 9900
заданий.

В обеих таблицах созданы индексы по полю name.

Первый вариант

          SELECT    p.name
          FROM      people p,
                    job j
          WHERE     p.name = j.name(+)
          AND       j.name is null          fred the 27
          1 record selected.
          Time 51.40 secs.

Не очень хорошее время выполнения.

Второй вариант

          SELECT    name
          FROM      people
          WHERE     name not in
                    (SELECT   name
                     FROM     job)          fred the 27
          1 record selected.
          Time 6.11 secs

Гораздо лучший результат, время выполнения уменьшено почти на порядок! Мы могли бы почивать на лаврах, пить чай задрав ноги вверх, однако, если мы подправим подзапрос...

          SELECT    name
          FROM      people p
          where     not name in
                    (SELECT   name
                    FROM      job j
                       WHERE     p.name = j.name)          fred the 27
          1 record selected.
          Time 1.08 secs.

:-)

3.5 Использование оператора EXISTS

Один из операторов, который часто игнорируется это оператор EXISTS. Он может быть иногда полезен для вовлечения внешних ключей (foreign keys). В следующем примере мы проверяем имет ли 'fred the 45' какое-либо задание.

Первый вариант

          SELECT    distinct 'x'
          FROM      job
          WHERE     name = 'fred the 45'          1 record selected.
          Time  0.45 secs.

Во втором варианте мы используем оператор EXISTS и получаем уменьшение времени обработки почти в два раза.

          SELECT    'x'
          FROM      dual
          WHERE     exists
                    ( SELECT  'x'
                    FROM      job
                    where     name = 'fred the 45')          1 record selected.
          Time 0.26 secs.

Причина ускорения обработки состоит в том, что ядро Oracle знает, что можно остановиться после того, как найдено хотя бы одно совпадение
name = 'fred the 45'. Поэтому Oracle не будет просматривать всю таблицу целиком.

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

Запросы о 'fred the 34' и 'fred the 9999' дают следующие результаты.

          SELECT    distinct 'x'
          FROM      job
          WHERE     name ='fred the 34'          Time 6.65 secs.
          SELECT    'x'
          FROM      dual
          WHERE     exists
                    (SELECT   'x'
                    FROM      job
                    WHERE     name = 'fred the 34')
          Time 0.28 secs.
          SELECT    'x'
          FROM      dual
          WHERE     exists
                    (SELECT 'x'
                    FROM job
                    WHERE name = 'fred the 9999')
          Time 8.28 secs.

Это происходит из-за того, что данные в таблице хранятся по порядку, хотя в общем случае это не так. В данном случае запись 'fred the 1'
находится в первом блоке таблицы, а запись 'fred the 9999' в последнем блоке.

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

3.6 Не делайте ненужных объединений (joins)

В некоторых продуктах, применяемых в нашей организации, разработчики делают объединения (joins) с системной таблицей sys_param. Это
неправильный подход и он должен быть искоренен. Таблица sys_param предназначена для поддержки системы, и содержит общесистемные константы.

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

На логическом уровне любая модификация таблицы sys_param во время выполнения программы указывает на возможно пропущенные данные.

3.7 Ресурсоемкие операции

Запросы, содержащие операторы DISTINCT, UNION, MINUS, INTERSECT, ORDER BY или GROUP BY заставляют ядро Oracle выполнять ресурсоемкую
операцию сортировки. Оператор DISTINCT требует выполнить одну операцию сортировки, другие операторы заставляют ядро выполнить как минимум две операции сортировки. Всегда следует искать другие пути выполнения подобных запросов. Большинство запросов содержащих UNION, MINUS и INTERSECT могут быть выполнены иным способом.

3.8 Используйте для тестов реальные данные

Для тестирования программ и запросов всегда следует использовать набор данных близкий к реальному как по объему, так и по значениям,
указанным в ER диаграмме.Ниже приведенные выкладки сделаны на базе примера, рассмотренного в разделе 3.4. Теперь в таблицу people мы
поместим 999 записей, а в таблицу job 9990 записей. Другими словами мы увеличили на порядок количество людей.

          SELECT    p.name
          FROM      people p,
                    job j
          WHERE     p.name = j.name(+)
          AND       j.name is null          fred the 218
          Time 23.20 secs.
          SELECT    name
          FROM      people
          WHERE     not name in
                    (SELECT name
                    FROM job)
          fred the 218
          Time 193.46 secs.
          SELECT    name
          FROM      people p
          WHERE     not name in
                    (SELECT   name
                    FROM      job j
                    WHERE     p.name = j.name)
          fred the 218
          Time  8.66 secs.

Как вы уже заметили, время выполнения запроса, использующего внешнее объединение (outer join), много меньше, чем время выполнения запроса, использующего подзапрос. Т.е. эти результаты полностью противоположны результатам полученным в 3.4.

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

3.9 Применение оператора !

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

3.10 Использование Oracle trace

Для каждого запроса, даеж если он кажется простейшим, проверьте его план выполнения с помощью Oracle trace. Первым шагом в оптимизации запроса должно быть исключение FTS - полного сканирования таблицы, это может быть сделано с помощью Oracle trace. См. Приложение F.

Примечание:: Для первоначальной оптимизации запросов рекомендуется использовать команду
EXPLAIN PLAN. (Осипов Д.М.)

3.11 Управление курсорами Oraclе

Во всех случаях, когда это возможно, все курсоры, используемые в программе, должны быть описаны в начале программы. Хорошим стилем программирования считается сразу в начале программы объявить максимальное количество курсоров, которое будет в ней использоваться. Это может быть сделано в СУБД посредством открытия всех курсоров в начале программы, а в Pro*C и других языках оператором set MAXCURSORS.

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

Что же происходит во время компиляции. Во время этой фазы обработки SQL запрос, написанный программистом, преобразуется во внутреннее
представление в те коды, которые воспринимаются ядром Oracle. Oracle выполняет следующие действия:

просматривает словарь данных и выполняет проверку
прав доступа. Для этого Oracle генерирует несколько SQL запросов, которые
называются рекурсивными запросами (recursive queries), с помощью который
проверяет имеет ли данный пользователь право доступа к указанному столбцу и
т.д.
оптимизирует запрос. Здесь выполняется та
оптимизация, которая вызвана обращением к представлениям (view) и выполнением
подзапросов.

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

Вторая фаза, фаза подстановки (bind), выполняется после компиляции запроса. Во время этой фазы выполняется подстановка значения
переменных в запрос. Например, при обработке запроса

         SELECT  'x'
         FROM    person 
         WHERE   name = :1

значение некоей переменной 1 будет подставлено в запрос по адресу, зарезервированному указателем :1.

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

Шаг 1

При запуске программы выполнить определение и компиляцию запроса с помощью вызова процедур типа sql_set-up, open_cursor и т.п.

Шаг 2

Выполнить запрос передав ему значения соответствующих переменных с помощью вызова процедур типа sql_execute, ora_bind и т.п.

Шаг 3

Извлечь и обработать строки, возвращаемые запросом, с помощью вызова процедур типа sql_fetch, ora_fetch и т.п.

Чтобы выполнить запрос еще раз - вернутся к шагу 2 подставив новые значения переменных.

3.12 Правило 10,15,20 процентов

Использование индексов в запросах оправдано, если запрос извлекает меньше 15% строк из таблицы. Во всех остальных случаях полный просмотр таблицы (Full Table Scan FTS) будет работать быстрее.

Это правило называется правилом 10,15,20 процентов, потому что, в зависимости от того какую документацию по Oracle вы читаете, вы можете встретить цифры 10, 15 или 20 процентов.

4.0 Оператор INSERT

4.1 Применение * в операторе INSERT

Хотя применение укороченной нотации с применением символа '*' допускается ядром Oracle, такая запись считается 'плохим тоном' в
программировании. Такая запись не допускается в наших проектах.

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

5.0 Оператор UPDATE

5.1 Согласованные изменения

Одна из наиболее медленных команд в SQL это команда UPDATE. Это является следствием того, что большинство согласованных изменений в таблицах требуют полного просмотра таблиц (FTS). В результате этого эти операции являются ресурсоемкими и очень медленными когда таблицы слишком большие.

Приведенный ниже оператор UPDATE является типичным примером согласованного изменения.

  UPDATE target_table
  SET    target_field = (
                         SELECT source_information
                         FROM   source_table
                         WHERE  source_table.key = target_table.key
                         )
  WHERE  EXISTS (
                 SELECT 'x'
                 FROM   source_table
                 WHERE  source_table.key = target_table.key
                )

Проблема низкой производительности возникает потому, что в запросе не используется информация из source_table для оганичения количества просматриваемых в target_table строк. Если какое-либо условие в предложении WHERE оператора UPDATE не отсечет большинство строк из target_table, то этот оператор UPDATE потребует огромного количества процессорного времени.

Приведенная ниже PL/SQL процедура решает эту проблему и эффективно использует индекс по полю key, для того чтобы вовлечь в процесс
изменения ограниченное количество строк из target_table.

   Declare
     CURSOR source IS
        SELECT *
        FROM   source_table;
   Begin
     FOR row IN source LOOP
       UPDATE target_table
       SET    target_field = row.source_information
       WHERE  key = row.key;
     END LOOP;
   Exception
     WHEN OTHERS THEN
     Null;
   End;

Эта PL/SQL процедура просматривает в цикле записи в source_table и изменяет соответствующую строку в target_table, в том случае если
она существует. По сути, процедура переключает полный просмотр (FTS) с таблицы target_table на source_table и эффективно использует индекс таблицы target_table.

Время выполнения вышеприведенного оператора UPDATE, который исправляет 9 записей в таблице имеющей 8000 записей составляет 19.4 секунды. Те же самые действия, выполняемые с помощью процедуры на PL/SQL занимают 1.12 cекунды, давая улучшение производительности 94%.

PL/SQL скрипт однозначно будет выигрывать в производительности у оператора UPDATE когда таблица source_table меньше, чем
таблица target_table. Причем, чем больше таблица target_table по сравнению с source_table, тем больше будет выигрыш. Если же существует индекс для таблицы target_table, который значительно ускоряет поиск по ключу (WHERE key = row.key), то для приведенной выше PL/SQL процедуры размер таблицы target_table почти не оказывает влияния на время выполнения, имеет значение только количество изменяемых строк.

В большинстве случаев, замена предложения EXISTS на предложение IN в операторе UPDATE также даст выигрыш в скорости выполнения.

6.0 Оптимизация запросов

6.1 Доступ к таблицам

Существует три метода, с помошью которых Oracle может найти строку в таблице:

Просмотр всех строк в таблице по порядку и проверка каждой строки на соответствие конкретному условию. Этот метод называется полное сканирование таблицы (full table scan, FTS), он является самым медленным и наименее эффективным.
Использование индекса для поиска строки.
Использование идентификатора строки (ROWID) для прямого доступа к строке. Это наиболее эффективный метод доступа. Однако, этот метод можно использовать только в пределах одной транзакци, потому что ROWID может измениться после завершения транзакции, и в этом случае рекомендуется использовать механизм первичных ключей.

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

Oracle будет использовать индексы в следующих случаях:

Если поле, по которому существует индекс, упомянуто в предложении WHERE.
Если поле, по которому существует индекс, не модифицируется в запросе какой-либо функцией, математическим оператором или и тем и другим сразу. Исключение составляют следующие случаи MIN(column), MIN(column+constant) MAX(column), MAX(column+constant).

Oracle не будет использовать индексы в следующих случаях:

Если в запросе нет предложения WHERE.
Если поле, по которому существует индекс, модифицируется в запросе каким-либо образом.
Если выполняется поиск для значений NULL или NOT NULL.

6.2 Индексы и NULL значения

Если поле, по которому создан индекс, принимает значение NULL в какой-либо строке таблицы, то указатель на эту строку не включается в индекс. По этой причине в приведенном ниже запросе индекс по полю COMM использоваться не будет.

          SELECT    *
          FROM      emp
          WHERE     comm is NULL

При выполнении показанного ниже запроса Oracle полагает, что для большинства записей в таблице EMP поле COMM имеет значения NOT NULL, и поэтому не будет использовать индекс, а выполнит полный просмотр таблицы (FTS).

          SELECT    *
          FROM      emp
          WHERE     comm is not NULL

Однако, FTS может нам не подойти, если мы знаем, что таких записей мало. Следующий запрос переписан таким образом, чтобы заставить Oracle использовать индекс по полю COMM.

          SELECT    *
          FROM      emp
          WHERE     comm > -0.01

6.3 Индексы и предикаты 'NOT ='

Когда используется предикат не равно ('!=', 'NOT =') Oracle не будет использовать индекс, однако если используются другие предикаты с
приставкой NOT индексы используются, например:

          'not sal > 50' => 'sal <= 50'
          'not sal <= 50' => 'sal > 50'

6.4 Предложение GROUP BY

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

          SELECT    job,
                    avg(sal)
          FROM      emp
          GROUP BY  job
          HAVING    job = 'president'
          OR        job = 'manager'          SELECT    job,
                    avg(sal)
          FROM      emp
          WHERE     job = 'president'
          OR        job = 'manager'
          GROUP BY  job

6.5 Использование нескольких индексов

Когда в запросе существует два или более предиката '=', то Oracle может использовать сразу несколько индексов. Во время выполнения запроса Oracle сделает слияние (merge) индексов, и выберет те строки, на которые есть ссылки сразу во всех индексах. Например:

          SELECT    ename
          FROM      emp
          WHERE     deptno=20
          AND       job='manager'          будет выполнено слияние индексов
          non unique index on job
          non unique index on deptno

6.6 Когда не выполняется слияние индексов

В тех случаях, когда в запросе используются предикаты '=' и '<'/'>', как в нижеприведенном запросе, Oracle не может выполнить слияния (merge) индексов. Здесь будет использоваться индекс по полю job для того, чтобы получить строки с job = 'manager', а затем для этих строк будет выполнена проверка на deptno > 10.

          SELECT    *
          FROM      emp
          WHERE     job='manager'
          AND       deptno > 10          индексы:
          non unique index on job
          non unique index on deptno

Когда нельзя отдать явного предпочтения какому-либо индексу, как в нижеприведенном запросе, Oracle будет использовать только один из индексов, поскольку выполнять их слияние не эффективно. Заметьте, что повторное сканирование (empno > 1) будет выполнено для каждой строки прошедшей первую проверку (sal > 1).

Так как оба индекса non unique, Oracle выберет тот индекс, на который наткнется раньше в таблице DC_INDEXES в кэше. А поскольку разработчики не знают, что содержится в кэше, выбор индекса не предсказуем.

          SELECT    ename
          FROM      emp
          WHERE     sal > 1
          AND       empno > 1          индексы:
          non unique index on empno
          non unique index on sal

6.7 Подавление слияния индексов

Когда есть выбор между слиянием (merge) unique индекса с non unique индексом ядро Oracle всегда использует unique индекс и избегает выполнять слияние индексов. Причина этого состоит в том, что unique индекс возвращает ссылку только на одну строку. Рассмотрим следующий пример.

          SELECT    ename
          FROM      emp
          WHERE     sal = 3000
          AND       empno = 7902          индексы:
          unique index on empno
          non unique index on sal

Здесь будет использоваться только unique index on empno, затем, если запись найдена, будет выполнена проверка на sal = 3000.

При выполнении SQL оператора Oracle может выполнить слияние не более 5-ти индексов. Остальные предикаты будут проверяться без использования индексов.

          SELECT    *
          FROM      emp
          WHERE     empno =7844
          AND       job = 'salesman'
          AND       deptno = 30
          AND       sal = 1500
          AND       comm = 0
          AND       ename = 'turner'          индексы:
          non unique index on empno
          non unique index on job
          non unique index on deptno
          non unique index on sal
          non unique index on comm
          non unique index on ename

Because all the predicates score equally, according to APPENDIX A, only five of the above indexes will be used. Rows that are returned will be checked by the kernel to see if the last value is correct. Поскольку, согласно приложению А вес всех предикатов одинаковый, Oracle выполнит слияние индексов. Согласно упомянутым выше правилам, Oracle будет использовать только пять индексов для получения ссылки на строку, затем извлечет строку из БД и проверит последнее условие.

6.8 Составные индексы

Составным называется индекс, который построен по нескольким полям таблицы. Oracle может использовать составной индекс, если в предложении WHERE указано первое из полей, по которым построен индекс.

In the following examples assume a concatenated index on job and deptno. В следующем примере мы подразумеваем, что существует составной
индекс ... index on emp (job, deptno)

          SELECT    *
          FROM      emp
          WHERE     job = 'president'
          AND       deptno = 10

Индекс будет использоваться.

          SELECT    *
          FROM      emp
          WHERE     deptno = 10
          AND       job = 'president'

Индекс будет использоваться.

          SELECT    *
          FROM      emp
          WHERE     deptno = 10

Индекс не будет использоваться, потому что в WHERE нет ссылки на job - первое поле в составном индексе.

          SELECT    *
          FROM      emp
          WHERE     job = 'analyst'

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

          SELECT    *
          FROM      emp
          WHERE     job != 'clerk'
          AND       deptno = 10

Индекс не будет использоваться, потому что оператор '!=' отключает использование индекса. См. раздел 3.9.

6.9 Оптимизация запросов с OR

В отличие от оператора AND, который требует чтобы строка удовлетворяла обоим условиям, оператор OR требует, чтобы строкаудовлетворяла хотябы одному условию. Рассмотрим следующий пример.

          SELECT    ename,
                    sal,
                    job
          FROM      emp
          WHERE     sal = 3000
          OR        job = 'clerk'          индекс:
          non unique on job

Если здесь использовать индекс по полю job, то мы получим только те записи в которых job = 'clerk', и пропустим те записи, в которых job != 'clerk', но sal = 3000, что не соответствует условиям запроса.

Использование составного индекса по полям (job, sal) или (sal, job) также невозможно, поскольку значения полей должны проверяться независимо друг от друга.

Если у нас есть составной индекс по (sal, job), то ядро Oracle может выполнить оптимизацию запроса по полю job. Оптимизации поможет
также наличие простого индекса по полю sal.

Если же существуют два индекса по обоим полям, связанным оператором OR, то запрос будет концептуально пересмотрен и выполнен как объединение двух SQL операторов. Рассмотрим это подробнее.

          SELECT    ename,
                    sal,
                    job
          FROM      emp
          WHERE     sal = 3000
          OR        job = 'clerk'          индексы:
          non unique on job
          non unique on sal

Становится:

          SELECT    ename,
                    sal,
                    job
          FROM      emp
          WHERE     job = 'clerk'          UNION
          SELECT    ename,
                    sal,
                    job
          FROM      emp
          WHERE     sal = 3000
          AND       job != 'clerk'

Заметьте, что ядро Oracle использовало последний предикат в предложении WHERE для создания первого запроса с единственным условием, и затем использовало тот же предикат, но уже с оператором '!=' во втором запросе с двумя условиями. Если мы перепишем запрос следующим образом:

          SELECT    ename,
                    sal,
                    job
          FROM      emp
          WHERE     job = 'clerk'
          OR        sal = 3000          индексы:
          non unique on job
          non unique on sal

Становится:

          SELECT    ename,
                    sal,
                    job
          FROM      emp
          WHERE     sal = 3000          UNION
          SELECT    ename,
                    sal,
                    job
          FROM      emp
          WHERE     job = 'clerk'
          AND       sal != 3000

Из этого ледует, что наиболее выгодно первым в WHERE помещать предикат связанный с тем условием, которое возвратит наибольшее количество строк, а последним - предикат возвращающий наименьшее количество строк. Тем самым мы мнимизируем количество проверок на '!=' во втором SELECT'е.

Однако, такая оптимизация запросов с OR не может быть выполнена для SQL запросов, содержащих предложение CONNECT BY или внешние
объединения (outer joins).

6.10 Не соотнесенные подзапросы

Мы рассмотрим два типа запросов, первый тип это запросы с оператором IN, второй тип - с оператором NOT IN. Начнем с запросов, содержащих оператор IN.

Ниже приведены правила по которым Oracle оптимизирует такие запросы.

Основной запрос и подзапрос оптимизируются независимо друг от друга.
Правила для оптимизации основного запроса и подзапроса одинаковы, например, в приведенным ниже запросе Oracle не будет оптимизировать подзапрос, так как в нем нет предложения WHERE.
В качестве управляющей таблицы будет выступать таблица из подзапроса. В примере это будет таблица job.
Подзапрос преобразуется в объединение (join) следующим образом. Строки, возвращаемые подзапросом, сортируются и очищаются от
дубликатов, (для доступа к полученной в результате выполнения подзапроса таблице используется FTS), и таблица из основного запроса объединяется (joined) с результатами выполнения подзапроса используя в качестве ключа колонку, упомянутую в предложении WHERE в основном запросе.

          SELECT    distinct name
          FROM      men
          WHERE     pin in
                    (SELECT pin
                    FROM job)          индексы:

дает следующий explain plan.

          SORT(UNIQUE)
            MERGE JOIN
              TABLE ACCESS (FULL) OF 'MEN'
            SORT(JOIN)
              TABLE ACCESS (FULL) OF 'JOB'          Execute time 4759

Операция SORT(UNIQUE) обусловлена наличием DISTINCT в запросе.

Если же существуют индексы мы получим

          SELECT    distinct name
          FROM      men
          WHERE     pin in
                    (SELECT pin
                    FROM job )          индексы:
          unique on job(jobno)
          non unique on job(pin)

дает следующий explain plan.

          SORT(UNIQUE)
            NESTED LOOPS
              TABLE ACCESS (FULL) OF 'MEN'
              INDEX(RANGE SCAN) OF 'JOB_1' (NON_UNIQUE)

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

SQL оператор концептуально выполняется как

          SELECT distinct pin 
          FROM            job 
          ORDER BY        pin

и эти отсортированные строки затем объединяются со строками из таблицы men с использованием индекса ... index on job(pin).

Применять оператор NOT IN не рекомендуется. Разработчикам следует преобразовывать такие конструкции используя внешние объединения (outer joins). Однако, следует иметь ввиду, что внешние объединения (outer joins) являются расширением Oracle и могут не поддерживаться в других СУБД. Рассмотрим следующий пример.

          SELECT    *
          FROM      dept
          WHERE     deptno not in
                    (SELECT   deptno
                    FROM      emp)

Он может быть переписан с использованием внешнего объединения (outer join).

          SELECT    d.*
          FROM      dept d,emp e
          WHERE     d.deptno = e.deptno(+)
          AND       e.rowid is NULL

Такая нотация основана на том, что каждая строка имеет уникальный ROWID, который никогда не может быть NULL. Следует отметить также, что NOT IN является эквивалентом NOT EXISTS. Поэтому любой запрос с NOT EXISTS может быть переписан с использованием внешнего объединения (outer join).

6.11 Соотнесенные подзапросы

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

ПРИЛОЖЕНИЕ A

Весовые коэффиценты предикатов в запросах. Чем ниже коэффицент, тем выше скорость выполнения запроса.

  Rank     Path
  ======   ============================================
   1       ROWID = constant
   2       Unique indexed column = constant
   3       entire unique concatenated index = constant
   4       entire cluster key = corresponding
           cluster  key in another  table  in
           the same cluster
   5       entire cluster key = constant
   6       entire   non-unique   concatenated 
           index = constant
   7       non-unique single column index merge
   8       most leading concatenated index = constant
   9       indexed  column BETWEEN low value AND 
           high value, or indexed column LIKE 'C%'
           (bounded range)
  10       sort/merge (joins only)
  11       MAX or MIN of single indexed column
  12       ORDER BY entire index
  13       full table scans
  14       unindexed column = constant, or 
           column IS NULL, or  column  LIKE '%C%'
           (full table scan)

ПРИЛОЖЕНИЕ B

Структура таблиц, используемых в примерах

table oe (id number(6,0),
          oe number(1,0),
          timestamp(date))
Поле id содержит уникальные значения начиная с 1.
Поле oe содержит 0 если id четное и 1 если id нечетное.
unique index on id
nonunique index on oe
100,000 строк в oe
table oe_link (oe   number(1),
               name char(10))
with 2 records
table oe_link_name (name char(10),
                    age  number(3))
With 1 record
people (name, sex)
job (job_id, name)

ПРИЛОЖЕНИЕ C

Update Performance RDBMS
John R. Pack VAX/VMS
July 17, 1990 V6.0.30.4
(Revised 19-Sep-90)

Using PL/SQL to Enhance
Update Performance

One of the slowest commands in SQL is the UPDATE. Most often, when a client complains about performance, a large, correlated update (or,worse,
an uncorrelated update) is at the root of the problem.Often, even after creating the optimal indexes and fine tuning the SQL statement itself, the update is still hogging enormous CPU resources and is still the bottleneck in the user's production scheme.

The Correlated Update

This is largely due to the fact that most correlated updates require a full table scan. This results in very slow performance when the table is extremely large.

The following update statement is typical of correlated updates:

     Update Target_Table
     Set Target_Field = (Select Source_Information
                         From Source_Table
                         Where Source_Table.Key =
                         Target_Table.Key)
     Where exists (Select 'x'
                   From Source_Table
                   Where Source_Table.Key =
                   Target_Table.Key)

Performance problems arise because there is no method of eliminating rows in the Target_Table based on information in the Source_Table.
If other conditions in the Update's Where clause do not disqualify most of the rows in the Target_Table, this update will require substantial processing time.

In addition, this type of query suffers because an index would not help the Target_Table access time. In fact, the only index which will
improve the performance of the preceding update is an index on the Source_Table Key field. If the Source_Table is large, this is very useful.

Nevertheless, the best method of updating the table would be able to access just the correct rows in the Target_Table using an effective
index. This method is now available using PL/SQL and an index on the Target_Table Key field.

The PL/SQL Update

The following PL/SQL code effectively uses an index on the Key field to access only the appropriate records in the Target_Table:

             Declare
               Cursor Source is
               Select *
               From Source_Table;
               Begin
               For Row in Source Loop
               Update Target_Table
               Set Target_Field = Row.Source_Information
               Where Key = Row.Key;
               End Loop;
               Exception
               When OTHERS Then
               Null;
             End;

This PL/SQL script loops through each of the records in the Source_Table and updates the appropriate row in the Target_Table, if any. Essentially, this transfers the full table scan to the Source_Table and allows the index on the Target_Table to be used effectively.

Performance Gain

Running a typical correlated update on an 8,000 row table to update 9 records required 19.4 CPU seconds. The same update using the PL/SQL script executed in 1.12 CPU seconds - a 94% performance improvement.

The PL/SQL script will outperform the correlated update whenever the Source_Table is smaller than the Target_Table. The larger the
Target_Table compared to the Source_Table, the more substantial the performance gain. With an effective index, the size of the Target_Table is no longer a factor in the time required to update the table; the number of records being updated determines the performance.

Replacing the EXISTS subquery with IN subquery will give same improvement in most cases.

ПРИЛОЖЕНИЕ D

GUIDELINES FOR USING THE OUTER JOIN SYNTAX

Guidelines for Using the Outer Join Syntax RDBMS
RDBMS Support V6.0
October 1990

1 INTRODUCTION

The purpose of this technical note is to provide some guidelines on how to use the outer join facility provided by ORACLE.

1.1 Outer Join Semantics - Definitions

The following terms, used to describe the operation on outer joins, are defined :-

'outer-join column' - a column reference followed by
the symbol (+), e.g. EMPNO(+)
and DEPT.DEPTNO(+) are outer
join columns

'simple predicate' - a logical expression containing
no AND's, OR's, or NOT's (
usually a simple relation such
as A = B )

'outer join predicate'- a simple predicate containing
one or more outer join columns.

2 OUTER JOIN SYNTAX - RULES

An outer join predicate may only contain outer join columns from one table ( in other words, all outer join columns in a single outer join predicate must
belong to the same table). This means, for example, that the following statement is illegal :-

  EMP.EMPNO(+) = DEPT.DEPTNO(+)  -  outer join columns from
                                    two tables

Also, if a column in a predicate is an outer join column, then all columns from the same table must be outer join columns in that predicate. This means,
for example, that the following statement is illegal :-

   EMP.SAL + EMP.COMM(+) = SALGRADE.HIGH  - mixed columns
                                            from one table

In a predicate, the table referenced with a (+) is directly 'outer joined' to all other tables in the predicate. It is indirectly 'outer joined' to any tables to which these other tables are themselves 'outer joined'. A predicate may not be directly or indirectly 'outer joined' to itself. This means, for example, that the following combination of predicates is illegal :-

        EMP.EMPNO(+)   = PERS.EMPNO
   AND  PERS.DEPTNO(+) = DEPT.DEPTNO
   AND  DEPT.JOB(+)    = EMP.JOB       - circular outer
                                         join relationship

3 OUTER JOIN EXECUTION

For a given table, T, there may be both outer join and non-outer join predicates. Execution occurs ( conceptually ) as follows :-

The result of joining all tables mentioned in table T's outer join predicates is formed (by recursive application of this algorithm ).
For each row of the result, a set of composite rows is formed, each consisting of the original row in the result joined to a row in table T for which the composite row satisfies all of table T's outer join predicates.
If a set of composite rows is the null set, a composite row is created consisting of the original row in the result joined to a row similar to those
in table T, but with all values set to null.
Rows that do not pass the non-outer join predicates are removed.

This may be summarised as follows. Outer join predicates ( those with (+) after a column of table T ), are evaluated BEFORE table T is augmented with a null row. The null row is added only if there are NO rows in table T that satisfy the outer join predicates. Non-outer join predicates are evaluated AFTER table T is augmented with a null row (if needed)

4 OUTER JOIN - RECOMMENDATIONS

Certain types of outer joins in complicated logical expressions may not be well formed. In general, outer join columns in predicates that are branches of an OR should be avoided. Inconsistencies between the branches of the OR can result in an ambiguous query, and this may not be detected. It is best to confine outer join columns to the top level of the 'where' clause, or to nested AND's only.

5 OUTER JOIN - ILLUSTRATIVE EXAMPLES

5.1 Simple Outer Join

   SELECT ENAME, LOC
   FROM   DEPT, EMP
   WHERE  DEPT.DEPTNO = EMP.DEPTNO(+)

The predicate is evaluated BEFORE null augmentation. If there is a DEPT row for which there are no EMP rows, then a null EMP row is concatenated to the DEPT row.

5.2 Outer Join With Simple Post-Join Predicates

   SELECT ENAME, LOC
   FROM   DEPT, EMP
   WHERE  DEPT.DEPTNO = EMP.DEPTNO(+)
   AND    EMP.DEPTNO IS NULL

The second simple predicate is evaluated AFTER null augmentation, since there is no (+), removing rows which were not the result of null augmentation and hence leaving only DEPT rows for which there was no corresponding EMP row.

5.3 Outer Join With Additional Pre-Join Predicates

   SELECT ENAME, LOC
   FROM   DEPT, EMP
   WHERE  DEPT.DEPTNO = EMP.DEPTNO(+)
   AND    'CLERK'     = EMP.JOB(+)
   AND    EMP.DEPTNO IS NULL

The predicate on EMP.JOB is evaluated at the same time as the one on EMP.DEPTNO - before null augmentation. As a result, a null row is augmented to any DEPT row for which there are no corresponding clerks's in the EMP table. Therefore, this query displays departments containing no clerks.

Note that it the (+) were omitted from the EMP.JOB predicate, no rows would be returned. In this case, both the EMP.JOB and EMP.DEPTNO IS NULL predicates are evaluated AFTER the outer join, and there can be no rows for which both are true.

ПРИЛОЖЕНИЕ E

Десять правил создания быстро выполняющихся запросов

НЕ СЛЕДУЕТ НЕЧАЯННО ВЫКЛЮЧАТЬ ИСПОЛЬЗОВАНИЕ ИНДЕКСОВ ПОСРЕДСТВОМ МОДИФИКАЦИИ ПОЛЯ В ПРЕДЛОЖЕНИИ WHERE.
В ПРЕДЛОЖЕНИИ FROM ПОСЛЕДНЕЙ СЛЕДУЕТ ПОМЕЩАТЬ ТАБЛИЦУ, ИЗ КОТОРОЙ ИЗВЛЕКАЕТСЯ НАИМЕНЬШЕЕ КОЛИЧЕСТВО СТРОК.
НЕОБХОДИМО ИССЛЕДОВАТЬ НЕСКОЛЬКО ВАРИАНТОВ НАПИСАНИЯ ОДНОГО И ТОГО ЖЕ ЗАПРОСА.
СЛЕДУЕТ ИСПОЛЬЗОВАТЬ ПЕРАТОР EXISTS ТАМ ГДЕ ЭТО ВОЗМОЖНО.
НЕ НАДО ПРОСИТЬ ЯДРО СУБД ДЕЛАТЬ БОЛЬШЕ, ЧЕМ НУЖНО.
НЕ СЛЕДУЕТ ЗАСТАВЛЯТЬ СУБД ЧАСТО ВЫПОЛНЯТЬ КОМПИЛЯЦИЮ ЗАПРОСОВ. СЛЕДУЕТ ОТДАВАТЬ ПРЕДПОЧТЕНИЕ КОНСТРУКЦИИ BIND.
ТЫ ДОЛЖЕН ЗНАТЬ ОБЪЕМ ДАННЫХ И ЗНАЧЕНИЯ ПОЛЕЙ И ИСПОЛЬЗОВАТЬ В ТЕСТАХ РЕАЛИСТИЧЕСКИЙ НАБОР ДАННЫХ.
ПРИ НАПИСАНИИ ЗАПРОСОВ СЛЕДУЕТ ДАВАТЬ СИНОНИМЫ ТАБЛИЦАМ.
НЕ ПРИМЕНЯЙТЕ ОПЕРАТОР != ЕСЛИ МОЖНО ОБОЙТИСЬ БЕЗ нЕГО
.
ИСПОЛЬЗУЙТЕ ORACLE TRACE FACILITY ДЛЯ НАБЛЮДЕНИЯ ЗА ХОДОМ ВЫПОЛНЕНИЯ ВАШИХ ЗАПРОСОВ

ПРИЛОЖЕНИЕ F

                         EXPLAIN Facility

MOTIVATION

There is a need for users to be able to determine the steps the system takes in performing various operations on a database. The EXPLAIN facility provides users with a convenient mechanism for getting this information. The facility stores this information in a standard database table that can be manipulated with standard SQL statements.

SYNTAX

The following syntax is based on the syntax used by DB2 for their EXPLAIN
facility:

        EXPLAIN PLAN [SET STATEMENT_ID [=] ]
        [INTO ]
        FOR

where

STATEMENT_ID: a unique optional identifier for the statement;
INTO: - allows user to save the results of the analysis in the specified table.
The table must conform to the format for the table used to store the analysis
( see TABLE FORMATS section for a description of the table format ). If this
clause is not specified, the system will then attempt to store the information
in a table named .PLAN_TABLE . If the explicit or implicit table
does not exist the EXPLAIN command will fail.
: - an insert, delete, update, or query statement;

TABLE FORMATS

Core Table Format

The core table used to represent the plan information consists of the
following fields:

STATEMENT_ID: - An identifier associated with the statement. If not set by the user, the
identifier will be NULL. Note that a user may identify a statement by the
timestamp field.
TIMESTAMP: - The date and time when the statement was analysed.
REMARKS: - Any comment the user wishes to associate with this step of the analysis.
OPERATION: - the name of the operation being performed. The following table provides
a listing of the operations described by the facility.

 Operation       Description
 ---------------------------------------------------------------
 And-Equal       A retrieval utilising intersection of
                   rowids from index searches
 Connect by      A retrieval that is based on a tree walk
 Concatenation   A retrieval from a group of tables.  It is
                 essentially a UNION ALL operation of the
                 sources.  Used for OR operations.
 Counting        A node that is used to count the number of
                   rows returned from a table.  Used for queries
                   that use the ROWNUM meta-column.
 Filter          A restriction of the rows returned from a table
 First Row       A retrieval of only the first row
 For Update      A retrieval that is used for updating
 Index           A retrieval from an index
 Intersection    A retrieval of rows common to two tables
 Merge Join      A join utilising merge scans
 Minus           A retrieval of rows in Source 1 table but not in
                   Source 2 table
 Nested  Loops   A join utilising  nested  loops.  Each value 
                   in the first subnode is looked up in the 
                   second subnode.  This is often used when 
                   one table in a join is indexed and 
                   the other is not.
 Project         A retrieval of a subset of columns from a table
 Remote          A retrieval from a database other than the 
                   current database
 Sequence        An operation involving a sequence table
 Sort            A retrieval of rows ordered on some column 
                   or group of columns
 Table           A retrieval from a base table
 Union           A retrieval of unique rows from two tables
 View            A retrieval from a virtual table
 ----------------------------------------------------------------

Note that the operation shown when counting the number of rows returned by a query (i.e. select count(*)) is SORT. This is due to the way that COUNT is implemented internally. The table will not really be sorted.

OPTIONS

- an option that modifies the operation, e.g., OUTER option on join
operations, rationale for sorting, type of index scan, type of filter, etc.
The following table provides a list of the options for the operations that
have options.

  OPERATION    OPTIONS      DESCRIPTION
  ----------------------------------------------------------
  Index        UNIQUE KEY  Unique key lookup on index
               RANGE       Index range scan
  Merge Join   OUTER       Join is an outer join
  Nested Loops OUTER       Join is an outer join
  Sort         DISTINCT    Sort is to produce distinct values
               GROUP BY    Sort is for grouping operation
               JOIN        Sort is for merge join
               ORDER BY    Sort is for order by
  Table        BY ROWID    Table scan is by rowid
               FULL        Sequential table scan
               CLUSTER     Table scan by cluster key
  -----------------------------------------------------------

OBJECT_NODE

- the name of the node that owns the database object.

OBJECT_OWNER

- the name of the schema the owns the database object.

OBJECT_NAME

- the name of the database object.

OBJECT_TYPE

- a modifier that provides descriptive information about the database
object, e.g., NON-UNIQUE for indexes, etc.

OBJECT_INSTANCE

- a number corresponding to the ordinal position of the object as it
appears in the original query. The numbering proceeds from left to right,
outer to inner with respect to the original query text. Note that at this
level, view expansion will result in rather interesting object instance
numbers. We will be addressing this issue fully in future releases.

SEARCH_COLUMNS

- the number of leading columns used when searching an index.

ID

- a number assigned to this operation in the tree. Corresponds to a
preorder traversal of the row source tree.

PARENT_ID

- the number assigned to the previous operation that receives information
from this operation. This field combined with the ID field allows users to do
a treewalk of the specified plan with the CONNECT BY statement.

POSITION

- the position this database object occupies for the previous operation.

OTHER

- other information that is specific to the row source that a user may nd useful. For example, the select statement to a remote node, etc.

Sample Table Definition

create table PLAN_TABLE (
        statement_id    char(30),
        timestamp       date,
        remarks         char(80),
        operation       char(30),
        options         char(30),
        object_node     char(30),
        object_owner    char(30),
        object_name     char(30),
        object_instance numeric,
        object_type     char(30),
        search_columns  numeric,
        id              numeric,
        parent_id       numeric,
        position        numeric,
        other           long);

An SQL script to create this table resides in file xplainpl.sql in the same directory containing the file catalog.sql. This table must reside in the current
schema unless you use the optional INTO clause of the EXPLAIN command.

EXAMPLES

Suppose we issue the following statements:

        EXPLAIN PLAN
        SET STATEMENT_ID = 'query1'
        INTO QUERY_PLANS
        FOR SELECT * 
            FROM  T1,T2,T3 
            WHERE T1.F1 = T2.F1 AND T2.F2 = T3.F2;        SELECT OPERATION, OPTIONS, OBJECT_NAME, ID,
          PARENT_ID, POSITION
          FROM QUERY_PLANS
          WHERE STATEMENT_ID = 'query1'
          ORDER BY ID;

The following output would be created:

OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION
---------------------------------------------------------------
MERGE JOIN 1
MERGE JOIN 2 1 1
SORT JOIN 3 2 1
TABLE ACCESS FULL T1 4 3 1
SORT JOIN 5 2 2
TABLE ACCESS FULL T2 6 5 1
SORT JOIN 7 1 1
TABLE ACCESS FULL T3 8 7 1

8 RECORDS selected

Suppose that an index is created on field F1 on table T1 and the following statements are issued:

        EXPLAIN PLAN
        SET STATEMENT_ID = 'query2'
        INTO QUERY_PLANS
        FOR SELECT * FROM T1 WHERE F1 > 1;        SELECT OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, 
          SEARCH_COLUMNS, ID, PREVIOUS_ID
          FROM QUERY_PLANS WHERE
          STATEMENT_ID='query2'
          ORDER BY ID;

The following output is produced:

OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE SEARCH_COLUMNS ID POS
-----------------------------------------------------------------
TABLE SCAN BY ROWID T1 1
INDEX SCAN RANGE IT1 NON-UNIQUE 1 2 1

2 RECORDS selected

With the same conditions as before, suppose we issue the following which demonstrates an index only retrieval:

        EXPLAIN PLAN
        SET STATEMENT_ID = 'query3'
        INTO QUERY_PLANS
        FOR SELECT F1 FROM T1 WHERE F1 > 1;        SELECT OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, 
          SEARCH_COLUMNS, ID
          FROM QUERY_PLANS WHERE
          STATEMENT_ID='query3';

The following output is produced:

OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE SEARCH_COLUMNS ID
------------------------------------------------------------------
INDEX SCAN RANGE IT1 NON-UNIQUE 1

1 RECORDS selected

The next example illustrates the output if a grouping operation is specified in the statement:

        EXPLAIN PLAN
        SET STATEMENT_ID = 'query4'
        INTO QUERY_PLANS
        FOR SELECT AVG(F1),F2 FROM T1 GROUP BY F2;        SELECT OPERATION, OPTIONS, OBJECT_NAME, ID,
          PARENT_ID
          FROM QUERY_PLANS WHERE STATEMENT_ID = 'query4'
          ORDER BY ID;
OPERATION     OPTIONS    OBJECT_NAME       ID     PARENT_ID
-----------------------------------------------------------
SORT          GROUP BY                     1
TABLE SCAN    FULL      T1                 2        1
2 RECORDS selected

The next example illustrates the ouptut if DISTINCT is specified in the statement:

        EXPLAIN PLAN
        SET STATEMENT_ID = 'query5'
        INTO QUERY_PLANS
        FOR SELECT DISTINCT F1 FROM T1;        SELECT OPERATION, OPTIONS, OBJECT_NAME, ID,
          PREVIOUS_ID
          FROM QUERY_PLANS WHERE STATEMENT_ID = 'query5'
          ORDER BY ID;
OPERATION     OPTIONS    OBJECT_NAME           ID   PREVIOUS_ID
--------------------------------------------------------------
SORT          DISTINCT                         1
TABLE SCAN    FULL      T1                     2         1
2 RECORDS selected

The next example illustrates the output if a subquery is specified in the statement:

        EXPLAIN PLAN
        SET STATEMENT_ID = 'query6'
        INTO QUERY_PLANS
        FOR 
             SELECT * FROM T1 
             WHERE F1 < ( SELECT F2 FROM T2 WHERE F1=3);        SELECT OPERATION, OPTIONS, OBJECT_NAME, ID,
          PARENT_ID, POSITION
          FROM QUERY_PLANS WHERE STATEMENT_ID = 'query6'
          ORDER BY ID;
OPERATION     OPTIONS  OBJECT_NAME    ID   PARENT_ID  POSITION
--------------------------------------------------------------
FILTER          OUT                   1
TABLE SCAN      FULL  T1              2     1          1
TABLE SCAN      FULL  T2              3     1          2
3 RECORDS selected

The final example displays a complex query whose output is sent to the default plan table. ( It is assumed that this table has been created before
issuing the statement.)

    EXPLAIN PLAN
    SET STATEMENT_ID = 'query7'
    FOR SELECT * FROM T1,T2 WHERE T1.F1 = T2.F1 UNION
        SELECT * FROM T2,T3 WHERE T2.F1 = T3.F1;    SELECT OPERATION, OPTIONS, OBJECT_NAME, ID, 
           PARENT_ID, 
           POSITION
      FROM PLAN_TABLE WHERE STATEMENT_ID = 'query7'
      ORDER BY ID;
The following output is produced:
OPERATION  OPTIONS     OBJECT_NAME  ID   PARENT_ID  POSITION
------------------------------------------------------------
PROJECTION                          1
UNION                               2      1          1
SORT        DISTINCT                3      2          1
NEST LOOP                           4      3          1
TABLE SCAN  BY ROWID   T1           5      4          1
INDEX SCAN  RANGE      IT1          6      5          1
TABLE SCAN  FULL       T2           7      4          2
SORT        DISTINCT                8      2          2
MERGE JOIN                          9      8          1
SORT        JOIN                    10     9          1
TABLE SCAN  FULL       T2           11     10         1
SORT        JOIN                    12     9          2
TABLE SCAN  FULL       T3           13     12         1
13 RECORDS selected

The following example is based on the previous query. It illustrates the use of the treewalking capability in Oracle's version of SQL.

        SELECT LPAD(' ',2*LEVEL)//OPERATION, 
               OPTIONS, OBJECT_NAME
        FROM PLAN_TABLE WHERE STATEMENT_ID='query7'
        CONNECT BY PRIOR ID = PARENT_ID
           and STATEMENT_ID = 'query7'
        START WITH ID = 1
           and STATEMENT_ID = 'query7'
        ORDER BY ID;LPAD(' ',2*LEVEL)//OPERATION    OPTIONS   OBJECT_NAME
----------------------------------------------------------
PROJECTION
  UNION
    SORT                      DISTINCT
      NEST LOOP
        TABLE SCAN            BY ROWID      T1
            INDEX SCAN        RANGE         IT1
        TABLE SCAN            FULL          T2
    SORT                      DISTINCT
      MERGE JOIN
        SORT                  JOIN
          TABLE SCAN          FULL          T2
        SORT                  JOIN
          TABLE SCAN          FULL          T3
13 RECORDS selected

Ссылки по теме