Как ускорить order by oracle

Обновлено: 25.01.2025

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

И такое будет продолжаться до тех пор, пока приложение не будет подвергнуто жестокому анализу на предмет повышения производительности. Один из способов повысить скорость работы приложения - оптимизация SQL-запросов. Этот способ хорош тем, что не надо лезть в дебри оптимизации SQL-сервера. Проще не допускать появления неэффективных SQL-запросов. Но если такое уже случилось, ищи выходы из сложившихся неприятных ситуаций.

Общая оптимизация

Каждая SQL-операция имеет так называемый "коэффициент полезности" – уровень эффективности данной операции. Чем больше балл, тем "полезней" операция, а значит, SQL-запрос выполняется быстрее.

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

Примеры

Чтобы лучше понять таблицы, рассмотрим пример расчета рейтинга запроса.

… WHERE smallint_column = 12345

5 баллов за поле слева (smallint_column), 2 балла за точный цифровой операнд(smallint_column), 10 баллов за операцию сравнения (=) и 10 баллов за значение справа (12345). Итого получили 27 баллов. Теперь рассмотрим более сложный пример:

. WHERE char_column >= varchar_column || "x"

5 баллов за поле слева (char_column), 0 баллов за символьный операнд (char_column), 5 баллов за операцию больше или равно (>=) , 3 балла за логическое выражение (varchar_column || "x") , 0 баллов за символьный операнд (varchar_column). В итоге получим 13 баллов.

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

Основной закон при оптимизации запросов - закон преобразования. Неважно, как мы представляем условие, главное чтобы результат остался прежним. И снова рассмотрим пример. Есть запрос: . WHERE column1 < column2 AND column2 = column3 AND column1 = 5 . Используя перестановку, получаешь запрос: …WHERE 5 < column2 AND column2 = column3 AND column1 = 5 . Результат запроса будет один и тот же, а продуктивность разной, потому что использование точного значения (5) влияет на производительность.

Если ты изучал С или С++, то знаешь, что выражение x=1+1-1-1 во время компиляции станет x=0. Удивительно, что лишь некоторые БД способны выполнять такие операции. При выполнении запроса БД будет выполнять операции сложения и вычитания и тратить твое драгоценное время. Поэтому всегда лучше сразу рассчитывать такие выражения там, где это возможно. Не … WHERE a - 3 = 5 , а … WHERE a = 8 .

Еще одна возможность оптимизировать запрос - придерживаться общей идеи составления условий в SQL. Другими словами, условие должно иметь вид: <колонка> <операция> <выражение>. Например, запрос ". WHERE column1 - 3 = -column2" лучше привести к виду: . WHERE column1 = -column2 + 3 .

И эти приемы оптимизации работают практически всегда и везде.

Оптимизируем условия

Теперь настало время произвести оптимизацию самих условных операторов SQL. Большинство запросов используют директиву SQL WHERE, поэтому, оптимизируя условия, можно добиться значительной производительности запросов. При этом почему-то лишь небольшая часть приложений для БД используют оптимизацию условий.

AND

Очевидно, что в серии из нескольких операторов AND условия должны располагаться в порядке возрастания вероятности истинности данного условия. Это делается для того, чтобы при проверке условий БД не проверяла остальную часть условия. Эти рекомендации не относится к БД Oracle, где условия начинают проверяться с конца. Соответственно, их порядок должен быть обратным – по убыванию вероятности истинности.

OR

Ситуация с данным оператором прямо противоположна ситуации с AND. Условия должны располагаться в порядке убывания вероятности истинности. Фирма Microsoft настойчиво рекомендует использовать данный метод при построении запросов, хотя многие даже не знают об этом или, по крайней мере, не обращают на него внимание. Но опять-таки это не относится к БД Oracle, где условия должны располагаться по возрастанию вероятности истинности.

Еще одним условием для оптимизации можно считать тот факт, что если одинаковые колонки располагаются рядом, запрос выполняется быстрее. Например, запрос ".. WHERE column1 = 1 OR column2 = 3 OR column1 = 2" будет выполняться медленней, чем запрос "WHERE column1 = 1 OR column1 = 2 OR column2 = 3" . Даже если вероятность истинности условия column2 = 3 выше, чем column1 = 2.

Еще в школе мне рассказывали про распределительный закон. Он гласит, что A AND (B OR C) - то же самое, что и (A AND B) OR (A AND C ). Опытным путем было установлено, что запрос вида ". WHERE column1 = 1 AND (column2 = "A" OR column2 = "B")" выполняется несколько быстрее, чем ". WHERE (column1 = 1 AND column2 = "A") OR (column1 = 1 AND column2 = "B")" . Некоторые БД сами умеют оптимизировать запросы такого типа, но лучше перестраховаться.

NOT

Эту операцию всегда следует приводить к более "читабельному" виду (в разумных пределах, конечно). Так, запрос ". WHERE NOT (column1 > 5)" преобразуется в ". WHERE column1 <= 5" . Более сложные условия можно преобразовать используя правило де Моргана, которое ты тоже должен был изучить в школе. Согласно этому правилу NOT(A AND B) = (NOT A) OR (NOT B) и NOT(A OR B) = (NOT A) AND (NOT B) . Например, условие ". WHERE NOT (column1 > 5 OR column2 = 7)" преобразуется в более простую форму: . WHERE column1 <= 5 AND column2 <> 7 .

IN

Многие наивно полагают, что запрос ". WHERE column1 = 5 OR column1 = 6" равносилен запросу ". WHERE column1 IN (5, 6)" . На самом деле это не так. Операция IN работает гораздо быстрее, чем серия OR. Поэтому всегда следует заменять OR на IN, где это возможно, несмотря на то, что некоторые БД сами производят эту оптимизацию. Там, где используется серия последовательных чисел, IN следует поменять на BETWEEN. Например, ". WHERE column1 IN (1, 3, 4, 5)" оптимизируется к виду: …WHERE column1 BETWEEN 1 AND 5 AND column1 <> 2 . И этот запрос действительно быстрее.

LIKE

CASE

Сама эта функция может использоваться для повышения скорости работы запроса, когда в нем есть более одного вызова медленной функции в условии. Например, чтобы избежать повторного вызова slow_function() в запросе ". WHERE slow_function(column1) = 3 OR slow_function(column1) = 5" , нужно использовать CASE:

. WHERE 1 = CASE slow_function(column1)

WHEN 3 THEN 1

WHEN 5 THEN 1

END

Сортировка

ORDER BY используется для сортировки, которая, как известно, занимает время. Чем больше объем данных, тем больше времени займет сортировка, поэтому нужно обязательно ее оптимизировать. На скорость сортировки в запросах влияет три фактора:

Самой ресурсоемкой сортировкой является сортировка строк. Несмотря на то, что текстовые поля имеют фиксированную длину, длина содержимого этих полей может быть различной (в пределах размера поля). Поэтому неудивительно, что сортировка колонки VARCHAR(100) будет медленней, чем сортировка колонки VARCHAR(10) (даже если данные будут одинаковые). А происходит это из-за того, что при сортировке сама база данных выделяет память для своих операций в соответствии с максимальным размером поля независимо от содержимого. Поэтому при объявлении полей всегда следует использовать размер, который нужен, и не выделять лишние байты про запас.

На компьютерах с ОС Windows поля типа INTEGER занимают 32 бита, а поля типа SMALLINT – 16 бит. Логично предположить, что сортировка полей типа SMALLINT должна происходить быстрее. На самом деле сортировка INTEGER происходит быстрее, чем SMALLINT. Также сортировка INTEGER происходит быстрее, чем CHAR.

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

Группирование

Операция GROUP BY используется для определения подмножества в результате запроса, а также для применения к этому подмножеству агрегатных функций. Рассмотрим несколько наиболее эффективных методов оптимизации операции группирования.

Первое, что следует помнить, - нужно использовать как можно меньше колонок для группировки. Также следует избегать лишних условий. Например, в запросе SELECT secondary_key_column, primary_key_column, COUNT(*) FROM Table1 GROUP BY secondary_key_column, primary_key_column колонка secondary_key_column совершенно не нужна. Причина простая: secondary_key_column является уникальным полем, оно может не иметь значений NULL, а значит, некоторые данные могут просто потеряться. Но если убрать secondary_key_column из секции GROUP BY, некоторые БД могут выдать ошибку о том, что невозможно указывать это поле, если оно не объявлено в секции GROUP BY. Для решения этой проблемы можно написать запрос в таком виде: SELECT MIN(secondary_key_column), primary_key_column, COUNT(*) FROM Table1 GROUP BY primary_key_column . Этот запрос быстрее и "правильнее" с точки зрения конструирования запросов.

В большинстве БД операции WHERE и HAVING не равноценны и выполняются не одинаково. Это значит, что следующие два запроса логически одинаковы, но выполняются с разной скоростью:

SELECT column1 FROM Table1 WHERE column2 = 5 GROUP BY column1 HAVING column1 > 6

SELECT column1 FROM Table1 WHERE column2 = 5 AND column1 > 6 GROUP BY column1

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

Если требуется группирование, но без использования агрегатных функций ( COUNT(), MIN(), MAX и т.д.), разумно использовать DISTINCT. Так, вместо SELECT column1 FROM Table1 GROUP BY column1 лучше использовать SELECT DISTINCT column1 FROM Table1 .

При использовании MIN() и MAX() учитываем, что эти функции лучше работают по отдельности. Это значит, что их лучше использовать в раздельных запросах или в запросах с использованием UNION.

При использовании функции SUM() большей производительности можно добиться используя SUM(x + y) , а не SUM(x) + SUM(y) . Для вычитания лучше противоположное: SUM(x) – SUM(y) быстрее, чем SUM(x – y).

Соединения таблиц (JOINS)

Вот где сложно что-то сказать про оптимизацию, так это при использовании JOIN . Дело в том, что скорость выполнения таких операций во многом зависит от организации самой таблицы: использование foreign-key, primary-key, количество вложенных соединений и т.д. Иногда лучшей производительности можно добиться используя вложенные циклы непосредственно в программе. Иногда быстрее работают JOINs. Однозначного совета по тому, как использовать разные способы соединения таблиц, не существует. Все зависит от конкретного случая и архитектуры БД.

Подзапросы (SUBQUERIES)

Раньше далеко не все БД могли похвастаться поддержкой подзапросов, а сейчас практически любая современная БД это умеет. Даже MySQL, которая несколько лет воплощала подзапросы в жизнь, наконец разжилась их поддержкой. Основная проблема при оптимизации подзапросов - не оптимизация непосредственно самого кода запроса, а выбор правильного способа для реализации запроса. Задачи, для которых используются подзапросы, также могут решаться с помощью вложенных циклов или JOIN’ов. Когда используешь JOIN, даешь возможность БД выбрать механизм, которым будет производиться соединение таблиц. Если же используешь подзапросы, то явно указываешь на использование вложенных циклов.

Ниже аргументы в пользу того или иного способа. Выбирай сам в зависимости от ситуации.

Если запрос содержит условие WHERE, встроенный оптимизатор БД будет оптимизировать запрос в целом, в то время как в случае использования подзапросов запросы будут оптимизироваться отдельно.
Некоторые БД более эффективно работают с JOINs, нежели с подзапросами (например, Oracle).
После JOIN’а информация окажется в общем "списке", что нельзя сказать про подзапросы.

Подзапросы допускают более свободные условия.
Подзапросы могут содержать GROUP BY, HAVING, что намного сложнее реализовать в JOIN’ах.
Подзапросы могут использоваться при UPDATE, что невозможно при использовании JOIN’ов.
В последнее время оптимизация подзапросов самими БД (их встроенным оптимизатором) заметно улучшилась.

Основное преимущество JOIN’ов в том, что не надо указывать БД то, каким именно способом производить операцию. А основное преимущество подзапросов в том, что цикл подзапроса может иметь несколько итераций (повторений), что, в свою очередь, может существенно увеличить производительность.

Заключение

В этой статье показаны самые распространенные способы увеличения производительности SQL-запросов. Тем не менее, чтобы оптимизировать запросы, есть еще очень много разных уловок и трюков. Оптимизация запросов больше похожа на искусство, чем на науку. У каждой базы данных свои встроенные оптимизаторы, которые могут помочь в этом нелегком деле, но всю работу за тебя никто не сделает. Как говорил старенький преподаватель по физике: "Чтобы решать задачи, их нужно решать".

Не рекомендуется использовать ORDER BY в связке с такими операциями, как DISTINCT или GROUP B Y, потому что данные операторы могут создавать побочные эффекты для сортировки. Как следствие, ты можешь получить неправильно отсортированный набор данных, который может оказаться критическим в некоторых ситуациях. Такое следствие не относится к оптимизации, но забывать о нем не стоит.

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

У любой SQL-операции есть "коэффициент полезности". Чем выше коэффициент, тем "полезней" операция: запрос выполняется быстрее.

В отличие от компиляторов, не все БД умеют упрощать выражения типа x=1+1-1-1 до x=0. Следовательно, они тратят драгоценное время на выполнение пустых операций. Оптимизируй их заранее.

При использовании функции SUM() можно добиться большей производительности с помощью SUM(x + y) , а не SUM(x) + SUM(y) .

Но если функции SUM() требуются для вычитания, используй противоположное: SUM(x) – SUM(y). SUM(x – y) работает медленнее.

У каждой БД есть свои встроенные оптимизаторы, но они далеки от совершенства. Поэтому оптимизируй заранее.

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

1. Ни каких подзапросов, только JOIN
Как я уже писал ранее, если выборка 1 к 1 или надо что-то просуммировать, то ни каких подзапросов, только join.
Стоит заметить, что в большинстве случаев оптимизатор сможет развернуть подзапрос в join, но это может случиться не всегда.

2. Выбор IN или EXISTS ?
На самом деле это сложный выбор и правильное решение можно получить только опытным путем.
Я дам только несколько советов:
* Если в основной выборке много строк, а в подзапросе мало, то ваш выбор IN. Т.к. в этом случае запрос в in выполнится один раз и сразу ограничит большую основную таблицу.
* Если в подзапросе сложный запрос, а в основной выборке относительно мало строк, то ваш выбор EXISTS. В этом случае сложный запрос выполнится не так часто.
* Если и там и там сложно, то это повод изменить логику на джойны.

3. Не забывайте про индексы
Совет для совсем новичков: вешайте индексы на столбцы по которым джойните таблицы.

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

5. По возможности не используйте WITH в oracle.
Значительно облегчает жизнь, если запрос в with необходимо использовать несколько раз ( с хинтом materialize ) в основной выборке или если число строк в подзапросе не значительно.
Во всех других случаях необходимо использовать прямые подзапросы в from или взаранее подготовленную таблицу с нужными индексами и данными из WITH.
Причина плохой работы WITH в том, что при его джойне не используются ни какие индексы и если данных в нем много, то все встанет. Вторая причина в том, что оптимизатору сложно определить сколько данных нам вернет with и оптимизатор не может построить правильный план запроса.
В большинстве случаев WITH без +materialize все равно будет развернут в основной запрос.

6. Не делайте километровых запросов
Часто в web обратная проблема - это много мелких запросов в цикле и их советуют объединить в один большой. Но тут есть свои ограничения, если у вас запрос множество раз обернутый в from, то внутреннюю(ие) части надо вынести в отдельную выборку, заполнить временную таблицу, навесить индексы, а потом использовать ее в основной выборке. Скорость работы будет значительно выше (в первую очередь из-за сложности построения оптимального плана на большом числе сочетаний таблиц)

7. Используйте KEEP взамен корреляционных подзапросов.
В ORACLE есть очень полезные аналитические функции, которые упростят ваши запросы. Один из них - это KEEP.
KEEP позволит сделать вам сортировку или группировку основной выборки без дополнительно запроса.
Пример: отобрать контрагента для номенклатуры, который раньше остальных был к ней подвязан. У одной номенклатуры может быть несколько поставщиков.
При обычном бы подходе пришлось бы делать корреляционный подзапрос для каждой номенклатуры с выбором минимальной даты.
Но не злоупотребляйте большим числом аналитических функций, особенно если они имеют разные сортировки. Каждая разная сортировка - это новое сканирование окна.

8. Гуляние по выборке вверх-вниз
Менее популярная функция, но не менее полезная. Позволяет смещать текущую строку выборки на N элементов вверх или вниз. Бывает полезно, если необходимо сравнить показатели рядом стоящих строк.
Следующий пример отбирает продажи департаментов отсортированных по дате. К основной выборке добавляются столбцы со следующим и предыдущим значением выручки. Второй параметр - это на сколько строк сместиться, третьи - параметр по-умолчанию, если данные соседа не нашлись. При обычном подходе бы пришлось это делать через логику приложения.

9. Direct Path Read
Установка этой настройки (настройкой или параллельным запросом) - чтение данных напрямую в PGA, минуя буферный кэш. Что укоряет последующие этапы запроса, т.к. не используется UNDO и защелки совместного доступа.

10. Direct IO
Использование прямой записи/чтения с диска без использования буфера файловой системы (файловая система конкретно для СУБД).
* В случае чтения преимущество в использовании буферного кэша БД, замен кэша ФС (кэш бд лучше заточен на работу с sql)
* В случае записи, прямая запись гарантирует, что данные не потеряются в буфере ФС в случае выключения электричества (для redolog всегда использует fsync, в не зависимости от типа ФС)

Частенько, в логи медленных запросов MySQL мне вываливалась масса запросов подобных этому:

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

Creating tmp table 0.000052
executing 0.000002
Copying to tmp table 1.779980

Дабы лучше понять, что за шляпа происходит - я воспроизвёл примерные действия сервера, при вышеозначенном запросе:

SELECT user_id,
FROM tmp_table
ORDER BY group_leader DESC , user_regdate ASC
LIMIT 20 ;
--

Возникла масса вопросов:
- Почему такая фака происходит?
- Можно ли предотвратить создание временных таблиц для запросов ORDER BY?
- Если нельзя, то как можно максимально ускорить подобные запросы? Тут интересуют способы максимально облегчить для сервера выполнение таких запросов, за счёт уменьшения каких-либо издержек на создание временных таблиц, копирование в них временных данных и пр.
- Какие ещё можно покрутить параметры, кроме тех что я уже крутил, согласно документации? А крутил я - max_length_for_sort_data = 4096 и sort_buffer_size = 128M (Такие значения сейчас стоят)

Отредактированно Бананище (18.01.2012 06:00:43)

EXPLAIN не выявил проблем с индексами

А по запросу не скажешь.
Хорошо оптимизированный ORDER BY - это когда все указанные в нем колонки в индексе, и этот индекс используется.
У Вас же в ORDER BY используются колонки из разных таблиц, вряд ли MySQL сумеет не полезть за ними в данные (отчего все и станет медленно).
Для верности покажите EXPLAIN и структуру таблиц.

Попробуйте добавить индексы
phpbb_user_group (group_id, user_pending, group_leader)
phpbb_users (user_id, user_regdate)

Второй индекс, возможно, прийдется FORCEить, потому что есть еще
одно условие в WHERE (которое, впрочем, сформирует RANGE).

Попробовал, не помогло, однако идея форсить индексы - оказалась хороша.
Решил сидеть до конца. Перекурив мануалы - впёр индексы по полям
user_id, user_type, user_regdate таблицы phpbb_users
и group_id, user_id, group_leader, user_pending таблицы phpbb_user_group

И скорость выполнения запроса увеличилась в два раза и стала 0.9 секунд (Тестил с SQL_NO_CACHE).
Что-ж, на данный момент считаю это прорывом. Буду рыть дальше .

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

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

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

Синтаксический анализ SQL-запросов

Этап синтаксического анализа (parsing) главным образом состоит в выполнении проверки синтаксиса и семантики SQL-операторов. В конце этого этапа создается дерево синтаксического разбора (parse tree), отражающее структуру запроса.

В частности, во время этого этапа SQL-оператор преобразуется в запрос реляционной алгебры, который подвергается анализу для выяснения того, является ли он корректным с синтаксической точки зрения. Далее этот запрос подвергается проверке на предмет корректности с семантической точки зрения, во время которой с помощью словаря данных проверяется, чтобы все упоминаемые в запросе таблицы и отдельные столбцы, равно как и все необходимые объектные привилегии, действительно существовали. Вдобавок проверяются типы столбцов для получения уверенности в том, что данные соответствуют определениям столбцов. Потом оператор нормализуется для того, чтобы его можно было обработать более эффективным образом. В случае если запрос сформулирован неправильно, он отклоняется. После того, как дерево синтаксического разбора проходит все синтаксические и семантические проверки, оно признается действительным и отправляется на этап генерации логического плана запроса. Все эти операции происходят в области SGA, представляющей библиотечный кэш части.

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

На этапе оптимизации Oracle применяет свой оптимизатор, который называется оптимизатором по стоимости ( Cost-Base Optimizer — CBO), для выбора наилучшего метода доступа для извлечения данных из присутствующих в запросе таблиц и индексов. За счет использования предоставляемых статистических данных и любых указываемых в SQL-запросах подсказок, CBO генерирует для SQL-оператора оптимальный план выполнения.

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

Этап перезаписи запроса

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

Этап генерации плана выполнения

На этом этапе Oracle преобразует логический план в физический план запроса. Для обработки запроса оптимизатору может быть доступно на выбор сразу несколько алгоритмов. Он выбирает самый эффективный из этих алгоритмов для ответа на запрос и определяет наиболее эффективный способ для реализации операций. Помимо принятия решения о том, какие операционные шаги лучше всего выполнять, он еще также определяет порядок, в котором необходимо выполнять эти шаги. Например, решив, что нужно выполнять операцию соединения между таблицей A и таблицей B, оптимизатор далее будет определять, какого типа должно быть это соединение, и в каком порядке его лучше выполнять.

В общем, при генерации физического плана или плана выполнения запроса оптимизатор принимает во внимание следующие факторы:

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

Оптимизатор может генерировать несколько действительных физических планов запроса, которые являются потенциальными планами выполнения. Затем оптимизатор делает выбор между ними путем оценки стоимости каждого возможного физического плана на основании доступных ему статистических данных по таблицам и индексам и выбора того плана, подсчитанная стоимость которого оказывается наименьшей. Этот процесс оценки стоимости возможных физических планов запроса называется оптимизацией запроса по стоимости (cost-based optimization). Стоимость выполнения плана напрямую зависит от того, сколько ресурсов (ввода-вывода, памяти и ЦП) для него требуется. Потом оптимизатор передает выбранный самый низкий по стоимости физический план запроса механизму выполнения запросов Oracle. В следующем разделе рассматривается простой пример, чтобы можно было лучше разобраться в том, что собой представляет процесс оптимизации процесса по стоимости.

Пример оптимизации запроса по стоимости

Давайте предположим, что требуется выполнить показанный ниже запрос, предусматривающий поиск информации обо всех руководителях (supervisor), которые работают в Далласе (Dallas):

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

Для произведения вычислений по стоимости давайте исходить из следующих предположений:

считывать и записывать данные можно только по одной строке за раз (в реальности операции ввода-вывода выполняются обычно на уровне блоков, а не на уровне строк);
база данных записывает каждый промежуточный шаг на диск (опять-таки, в реальном мире такого может и не быть);
с таблицами не ассоциированы никакие индексы;
в таблице employee содержится 2000 строк;
в таблице dept содержится 40 строк и руководителей тоже 40 (по одному на каждое отделение);
в Далласе всего функционирует десять отделений.

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

Запрос 1: декартово соединение

В случае применения этого запроса сначала получается декартово произведение таблиц employee и dept, а затем проверяться, какие из строк в нем удовлетворяют требованию:

Общая стоимость выполнения этого запроса будет выглядеть так:

декартово произведение таблиц employee и dept потребует считывания обеих таблиц, т.е. 2000 + 40 = 2040 операций чтения;
создание декартова произведения — 2000 * 40 = 80000 операций записи;
считывание результата декартова произведения для его сравнения с условием выбора строк — 2000 * 40 = 80000 операций чтения;
итого общая стоимость ввода-вывода составит: 2040 + 80000 + 80000 = 162040.

Запрос 2: соединение двух таблиц

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

Общая стоимость выполнения этого запроса будет выглядеть так:

соединение таблиц employee и dep сначала потребует считывания всех строк из обеих таблиц, т.е. 2000 + 40 = 2.040 операций чтения;
создание соединения таблиц employee и dep — 2000 операций записи;
считывание результатов соединения будет стоить 2000 операций чтения;
итого общая стоимость ввода-вывода составит: 2040 + 2000 + 2000 = 6040.

Запрос 3: соединение сокращенных связей

Третий запрос тоже подразумевает выполнение соединения таблиц employee и dept, но с соединением не всех, а только выборочных строк из этих двух таблиц. В случае его применения необходимые данные будут извлекаться так, как описано далее. Сначала будет осуществляться считывание таблицы employee для получения всех строк со значением SUPERVISOR. Затем будет выполняться считывание таблицы dept для извлечения всех строк со значением DALLAS. И, наконец, напоследок будет осуществляться соединение тех строк, которые были извлечены из таблиц employee и dept.

Общая стоимость выполнения этого запроса будет выглядеть так:

считывание таблицы employee для извлечения строк со значением SUPERVISOR будет стоить 2000 операций чтения;
запись строк со значением SUPERVISOR, которые были извлечены на предыдущем шаге — 40 операций записи;
считывание таблицы dept для извлечения всех строк со значением DALLAS — 40 операций чтения;
запись строк со значением DALLAS, извлеченных на предыдущем шаге — 10 операций записи;
соединение строк со значением SUPERVISOR и со значением DALLAS, извлеченных на предыдущих шагах выполнения данного запроса — всего 40 + 10 = 50 операций записи;
считывание результата соединения, полученного на предыдущем шаге — 50 операций чтения;
итого всего стоимость ввода-вывода составит: 2000 + 2 (40) + 10 + 2 (50) = 2190.

Этот пример, каким бы простым он не был, показывает, что декартовы произведения обходятся дороже, чем соединения с более ограничивающими условиями. Даже выборочная операция соединения, как показывают результаты, обходится дороже, чем операция выбора. Хотя операция соединения в запросе 3 и представляет собой соединение двух сокращенных связей, размер соединения выглядит гораздо меньше, чем у соединения в запросе 2. Оптимизация запросов часто подразумевает выполнение ранних операций выборки (выбор только некоторых строк) и проекции (выбор только каких-то столбцов) для сокращения размера результирующего вывода или источников строк.

Эвристические стратегии для обработки запросов

Применение методики оптимизации по стоимости не является единственным способом выполнения оптимизации запросов. Для обработки запросов в базе данных могут также применяться и менее систематичные методики, известные как эвристические стратегии (heuristic strategies). Операция соединения является бинарной, а операция вроде выбора — унарной. Успешная стратегия в целом заключается в выполнении унарной операции на раннем этапе, чтобы в более сложных и длительных по времени бинарных операциях далее использовались меньшие операнды. Выполнение в первую очередь как можно большего количества унарных операций сокращает источники строк в операциях соединения. Ниже перечислены некоторые наиболее типичные эвристические стратегии по обработке запросов.

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

Выполнение запросов

На последнем этапе процесса обработки запросов осуществляется выполнение оптимизированного запроса (физического плана запроса, который был выбран). Если он представляет собой оператор SELECT, тогда производится возврат соответствующих строк пользователю, а если оператор INSERT, UPDATE или DELETE, тогда — внесение в строки соответствующих изменений. Исполняющий механизм SQL берет план выполнения, полученный на этапе оптимизации, и выполняет его.

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

Читайте также: