Рекомендации разработчику Oracle Опции

[SuperMax]

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

Источник: oracloid

1.0 Введение

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

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

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

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

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

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

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

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

1. Ключевые слова Oracle следует писать ПРОПИСНЫМИ БУКВАМИ
2. Перечисленные ниже ключевые слова следует писать с новой строки:
   SELECT
   INTO
   FROM
   WHERE
   AND/OR
   GROUP BY
   HAVING
   CONNECT BY
   FOR UPDATE OF
   ORDER BY
3. Все ключевые слова в пределах одного SQL оператора следует писать с одной и той же позиции. При использовании вложенных операторов SELECT их следует сдвигать внутрь предложения на позицию второго слова в предыдущей строке, например:
   SELECT sal,
   Job,
   ename,
   dept
   FROM emp
   WHERE sal > any
   (SELECT sal
   FROM emp
   WHERE deptno = 30)
   ORDER BY sal;
4. Строки, которые не начинаются с приведенных выше ключевых слов следует начинать с позиции второго слова в предыдущей строке, например:
   SELECT ename,
   dept
   FROM emp

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

1. Константы, переменные и т.п. следует располагать с правой стороны от слов
   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 следует сначала писать условия объединяющие таблицы, а потом
условия ограничивающие выборку.




[url=""]3.0 Запросы к БД
[/url]

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) следует переписать как пример

a) SELECT *
FROM job
WHERE db_id//job_no = 'AZ0100201'



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 могут быть выполнены иным способом.



[url=""]3.8 Используйте для тестов реальные данные
[/url]
Для тестирования программ и запросов всегда следует использовать набор данных близкий к реальному как по объему, так и по значениям,
указанным в 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 выполняет следующие действия:

1. просматривает словарь данных и выполняет проверку
   прав доступа. Для этого Oracle генерирует несколько SQL запросов, которые
   называются рекурсивными запросами (recursive queries), с помощью который
   проверяет имеет ли данный пользователь право доступа к указанному столбцу и
   т.д.
2. оптимизирует запрос. Здесь выполняется та
   оптимизация, которая вызвана обращением к представлениям (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, будет модифицирована при каких-либо модификациях базы данных, то программа перестанет работать.



[url=""]5.0 Оператор UPDATE
[/url]

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 также даст выигрыш в скорости выполнения.[url=""]
[/url]


[url=""]6.0 Оптимизация запросов
[/url]

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

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

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



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

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

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

1. Если в запросе нет предложения WHERE.
2. Если поле, по которому существует индекс, модифицируется в запросе каким-либо образом.
3. Если выполняется поиск для значений 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'[url=""]
[/url] [url=""]6.4 Предложение GROUP BY
[/url]
Скорость выполнения запросов содержащих предложения 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 будет использовать только пять индексов для получения ссылки на строку, затем извлечет строку из БД и проверит последнее условие.

[url=""]

[/url]
[url=""]6.8 Составные индексы
[/url]
Составным называется индекс, который построен по нескольким полям таблицы. 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.


[url=""]

[/url]
[url=""]6.9 Оптимизация запросов с OR
[/url]
В отличие от оператора 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 оптимизирует такие запросы.

1. Основной запрос и подзапрос оптимизируются независимо друг от друга.
2. Правила для оптимизации основного запроса и подзапроса одинаковы, например, в приведенным ниже запросе Oracle не будет оптимизировать подзапрос, так как в нем нет предложения WHERE.
3. В качестве управляющей таблицы будет выступать таблица из подзапроса. В примере это будет таблица job.
4. Подзапрос преобразуется в объединение (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.

[url=""]


ПРИЛОЖЕНИЕ A
[/url]Весовые коэффиценты предикатов в запросах. Чем ниже коэффицент, тем выше скорость выполнения запроса.

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)

[url=""]
[/url]


ПРИЛОЖЕНИЕ 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.

[url=""]


ПРИЛОЖЕНИЕ D
[/url]


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 :-

1. The result of joining all tables mentioned in table T's outer join predicates is formed (by recursive application of this algorithm ).
2. 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.
3. 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.
4. 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.

[url=""]


ПРИЛОЖЕНИЕ E
[/url]


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

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

[url=""]
[/url]


ПРИЛОЖЕНИЕ 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 18 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 12 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 11 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