Author: Tina London
Date: August 7, 1992
Перевод: Осипов Д.М.
Назначением этого документа явлется определение требований и правил разработки SQL программ в нашем подразделении, соблюдение которых позволит создавать качественные приложения. А также обратить внимание на стиль написания программ, поскольку правильный стиль облегчает процесс поддержки программ.
Разработчикам следует принимать во внимание, что в языках четвертого поколения (как и в языках третьего поколения) первый и очевидный вариант программы не обязательно является лучшим.
Особое внимание надо обратить на необходимость придерживаться определенного стиля при написании SQL операторов, помимо всего прочего это позволит программам воспользоваться преимуществом SQL кэша в Oracle V7. Седьмая версия Oracle имеет некий SQL кэш, в котором содержатся разобранные (parsed) SQL запросы. Oracle определяет находится ли запрос в кэше с помощью не чувствительного к регистру сравнения.
Следует иметь ввиду, что возможет просмотр кода, который используется для доступа к БД на сервере с помощью SQLtrace, с последующей обработкой вывода программой tkprof.
SQL операторы следует писать таким образом, чтобы облегчить процесс их чтения, понимания и исправления. Логически обособленная последовательность операторов должна находиться в отдельном файле.
SELECT
INTO
FROM
WHERE
AND/OR
GROUP BY
HAVING
CONNECT BY
FOR UPDATE OF
ORDER BY
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
SELECT ename
FROM emp
WHERE empno = '1232'
Or
SELECT ename
FROM emp
WHERE empno = :1
Поля, которые вовлекаются в операцию внешнего объединения (outer join) следует писать с правой стороны от слов WHERE или HAVING.
SELECT ename
FROM emp e,
dept d
WHERE e.empno = d.empno(+)
Синонимы для таблиц следует применять во всех запросах, в которых упоминается больше одной таблицы в предложении 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.
В целях облегчения читаемости и понимания запроса в предложении WHERE следует сначала писать условия объединяющие таблицы, а потом условия ограничивающие выборку.
Порядок перечисления таблиц в предложении 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. Поэтому последней в списке должна быть таблица, возвращающая наименьшее количество строк.
Существует очень простой путь непреднамеренно исключить использование индексов в запросах.
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'
В некоторых случаях возникает необходимость запретить СУБД использовать индексы. Для этих целей можно использовать следующие модификаторы.
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'
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.
Один из операторов, который часто игнорируется это оператор 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.
В некоторых продуктах, применяемых в нашей организации, разработчики делают объединения (joins) с системной таблицей sys_param. Это неправильный подход и он должен быть искоренен. Таблица sys_param предназначена для поддержки системы, и содержит общесистемные константы.
Правильным подходом к использованию этой таблицы является извлечение из нее необходимых значений один раз в начале программы, или, в случае выполнения регулярных циклов обновлений, в начале каждого такого цикла.
На логическом уровне любая модификация таблицы sys_param во время выполнения программы указывает на возможно пропущенные данные.
Запросы, содержащие операторы DISTINCT, UNION, MINUS, INTERSECT, ORDER BY или GROUP BY заставляют ядро Oracle выполнять ресурсоемкую операцию сортировки. Оператор DISTINCT требует выполнить одну операцию сортировки, другие операторы заставляют ядро выполнить как минимум две операции сортировки. Всегда следует искать другие пути выполнения подобных запросов. Большинство запросов содержащих UNION, MINUS и INTERSECT могут быть выполнены иным способом.
Для тестирования программ и запросов всегда следует использовать набор данных близкий к реальному как по объему, так и по значениям, указанным в 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.
Этот пример вполне доходчиво показывает, что разработчик должен знать как структуру данных, так и типичное для задачи количество строк в каждой таблице.
Избегайте применять оператор != во всех случаях, когда можно обойтись без него. Применение этого оператора отключает использование индексов, потому что ядро Oracle полагает в этом случае, что запрос извлекает почти все строки таблицы.
Для каждого запроса, даеж если он кажется простейшим, проверьте его план выполнения с помощью Oracle trace. Первым шагом в оптимизации запроса должно быть исключение FTS - полного сканирования таблицы, это может быть сделано с помощью Oracle trace. См. Приложение F.
Во всех случаях, когда это возможно, все курсоры, используемые в программе, должны быть описаны в начале программы. Хорошим стилем программирования считается сразу в начале программы объявить максимальное количество курсоров, которое будет в ней использоваться. Это может быть сделано в СУБД посредством открытия всех курсоров в начале программы, а в Pro*C и других языках оператором set MAXCURSORS.
Выполнение запроса можно разделить на три фазы. Первая фаза это компиляция или разбор (parse) запроса, вторая — подстановка значений переменных (bind), и третья выборка результатов (fetch). Следует иметь в виду, что первые две фазы относительно 'дорогие', т.е. требуют больше процессорного времени.
Что же происходит во время компиляции. Во время этой фазы обработки SQL запрос, написанный программистом, преобразуется во внутреннее представление в те коды, которые воспринимаются ядром Oracle. Oracle выполняет следующие действия:
Поскольку вышеперечисленные операции занимают определенное время и требуют ресурсов процессора, то заставлять 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 подставив новые значения переменных.
Использование индексов в запросах оправдано, если запрос извлекает меньше 15% строк из таблицы. Во всех остальных случаях полный просмотр таблицы (Full Table Scan FTS) будет работать быстрее.
Это правило называется правилом 10,15,20 процентов, потому что, в зависимости от того какую документацию по Oracle вы читаете, вы можете встретить цифры 10, 15 или 20 процентов.
Хотя применение укороченной нотации с применением символа '*' допускается ядром Oracle, такая запись считается 'плохим тоном' в программировании. Такая запись не допускается в наших проектах.
Причина по которой такая запись считается 'плохим тоном' элементарна — если таблица, в которую производится INSERT, будет модифицирована при каких-либо модификациях базы данных, то программа перестанет работать.
Одна из наиболее медленных команд в 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 секунды, давая улучшение производительности 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 также даст выигрыш в скорости выполнения.
Существует три метода, с помошью которых Oracle может найти строку в таблице:
К сожалению, мы обычно не знаем ROWID строк таблицы, поэтому самым быстрым методом доступа следует считать метод с использованием индексов.
Oracle будет использовать индексы в следующих случаях:
Oracle не будет использовать индексы в следующих случаях:
Если поле, по которому создан индекс, принимает значение 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
Когда используется предикат не равно ('!=', 'NOT =') Oracle не будет использовать индекс, однако если используются другие предикаты с приставкой NOT индексы используются, например:
'not sal > 50' => 'sal <= 50'
'not sal <= 50' => 'sal > 50'
Скорость выполнения запросов содержащих предложения 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
Когда в запросе существует два или более предиката '=', то Oracle может использовать сразу несколько индексов. Во время выполнения запроса Oracle сделает слияние (merge) индексов, и выберет те строки, на которые есть ссылки сразу во всех индексах. Например:
SELECT ename
FROM emp
WHERE deptno=20
AND job='manager'
будет выполнено слияние индексов
non unique index on job
non unique index on deptno
В тех случаях, когда в запросе используются предикаты '=' и '<'/'>', как в нижеприведенном запросе, 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
Когда есть выбор между слиянием (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 будет использовать только пять индексов для получения ссылки на строку, затем извлечет строку из БД и проверит последнее условие.
Составным называется индекс, который построен по нескольким полям таблицы. 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.
В отличие от оператора 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).
Мы рассмотрим два типа запросов, первый тип это запросы с оператором IN, второй тип — с оператором NOT IN. Начнем с запросов, содержащих оператор IN.
Ниже приведены правила по которым Oracle оптимизирует такие запросы.
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).
Выполнение всех соотнесенных подзапросов идет по похожему пути. Основной запрос и подзапрос оптимизируются отдельно. Управляющая таблица выбирается из основного запроса. Для каждой строки из управляющей таблицы выполняется подзапрос. Индексы могут использоваться и в основном запросе и в подзапросе, если они содержат предложение WHERE.
Весовые коэффиценты предикатов в запросах. Чем ниже коэффицент, тем выше скорость выполнения запроса.
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)
Структура таблиц, используемых в примерах
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)
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.
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 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.
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.
GUIDELINES FOR USING THE OUTER JOIN SYNTAX Guidelines for Using the Outer Join Syntax RDBMS RDBMS Support V6.0 October 1990
The purpose of this technical note is to provide some guidelines on how to use the outer join facility provided by ORACLE.
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.
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
For a given table, T, there may be both outer join and non-outer join predicates. Execution occurs ( conceptually ) as follows :-
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)
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.
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.
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.
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.
Десять правил создания быстро выполняющихся запросов
EXPLAIN Facility
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.
The following syntax is based on the syntax used by DB2 for their EXPLAIN facility:
EXPLAIN PLAN [SET STATEMENT_ID [=]] [INTO ] FOR
where
Core Table Format
The core table used to represent the plan information consists of the following fields:
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.
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
-----------------------------------------------------------
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.
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
HIVE: All information for read only. Please respect copyright! |
