Informix Logo

Informix Magazine Russian Edition Сентябрь 1998г.

Инструменты оптимизации SQL-запросов к серверу INFORMIX

Евгений Шкарбанов,
консультант Informix/Россия

Для оптимизации SQL-запросов к серверу БД можно использовать оператор SET OPTIMIZATION и директивы оптимизатора (начиная с версии 7.3x). Оператор SET OPTIMIZATION действует до окончания программы либо до следующего оператора SET OPTIMIZATION. Директивы оптимизатора воздействуют только на конкретный оператор SQL. Также появилась возможность выбирать несколько первых строчек, удовлетворяющих условию WHERE оператора SELECT, а не все, как раньше.

Рассмотрим эти возможности подробнее.

Оператор SET OPTIMIZATION

Этот оператор может употребляться с четырьмя опциями HIGH, LOW, FIRST_ROWS, ALL_ROWS.

Опции оптимизации плана запроса

HIGH

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

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

LOW

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

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

 

Опции оптимизации цели запроса

Начиная с версии 7.3 появились еще две опции FIRST_ROWS и ALL_ROWS. Эти опции относятся к оптимизации цели запроса:

FIRST_ROWS

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

ALL_ROWS

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

Также можно указать цели конкретного запроса с помощью директив цели оптимизации.

Оптимизация хранимых процедур

Для хранимых процедур, которые остаются неизменными или меняются незначительно, вы можете выполнить оператор SET OPTIMIZATION HIGH во время создания процедуры. Этот шаг запомнит оптимальныхй план исполнения процедуры. Далее надо выполнить оператор SET OPTIMIZATION LOW до выполнения процедуры. Тогда при выполнении процедура будет использовать оптимальный план запроса и выполняться с наименьшими затратами.

Директивы оптимизатора

Новым инструментом в оптимизации исполнения запросов в сервере IDS начиная с версии 7.3x является механизм директив оптимизатора. Существуют пять видов директив:

Директива порядка соединения

Директивы управления методом доступа

Директивы методов соединения

Директивы оптимизации цели запроса

EXPLAIN - аналогично оператору SET EXPLAIN ON

По умолчанию директивы допустимы, но их можно отключить с помощью переменной среды IFX_DIRECTIVES или параметра DIRECTIVES в файле ONCONFIG.

Директивы оптимизатора размещаются сразу после ключевого слова SELECT, DELETE или UPDATE в форме комментариев и воздействуют только на текущий оператор SQL.

Нельзя использовать директивы оптимизатора в распределенном запросе (содержащем хотя бы одну удаленную таблицу) и в программе на ESQL/C для оператора, где есть WHERE CURRENT OF.

Директива порядка соединения

Директива ORDERED указывает оптимизатору на необходимость производить соединения таблиц в порядке, указанном в списке FROM. Например, в операторе

SELECT /*+ ORDERED */ name, title, salary, dname

FROM dept, job, emp

WHERE title = 'clerk' AND

emp.dno = dept.dno AND

emp.job = job.job

сначала будет произодиться соединение dept и job, а потом с результатом будет сливаться emp.

Директивы управления методом доступа

Эти директивы указывают оптимизатору способ сканирования таблиц, указанных в запросе. В эту группу входят четыре директивы: INDEX, AVOID_INDEX, FULL, AVOID_FULL.

AVOID_FULL

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

AVOID_INDEX

Не использовать перечисленные индексы. Оптимизатор использует оставшиеся индексы.

FULL

Производить полное сканирование таблицы, даже если есть индексы.

INDEX

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

Пример использования директив управления методом доступа:

SELECT {+AVOID_FULL(emp), AVOID_INDEX(emp dept_no) }

Директивы управления методом соединения

Эти директивы указывают способ, использую который сервер будет соединять таблицы, указанные в запросе. В эту группу входят четыре директивы: USE_NL, AVOID_NL, USE_HASH, AVOID_HASH.

USE_NL

Использовать вложенный цикл по следующим таблицам для соединения. Максимально допустимое число таблиц на одну меньше, чем указано в клаузе FROM.

AVOID_NL

Не использовать вложенного цикла по следующим таблицам. Но эти таблицы могут использоваться в соединениях с вложенными циклами как внешние (OUTER).

USE_HASH

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

AVOID_HASH

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

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

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

SELECT /* USE_HASH (dept /BUILD) */ name, title, salary, dname

FROM emp, dept, job

WHERE loc = "Phoenix" AND

emp.dno = dept.dno AND

emp.job = job.job


Директивы цели оптимизации

Этих директив всего две: ALL_ROWS и FIRST_ROWS. Эти директивы действуют также как и опции оптимизации цели запроса SET OPTIMIZATION ALL_ROWS и SET OPTIMIZATION FIRST_ROWS для конкретного оператора SQL. Пример употребления:

SELECT --+ FIRST_ROWS

FIRST 50 lname, fname, bonus

FROM emp

ORDER BU bonus DESC

Директивы ALL_ROWS и FIRST_ROWS нельзя употреблять в определении представлений (VIEW) и в подзапросах.

Директива перенаправления EXPLAIN

Эта директива действует так же как и оператор SET EXPLAIN ON, но только в рамках конкретного оператора SQL. Эту директиву нельзя применять в определении представлений и в подзапросах. Если же до директивы был применен оператор SET EXPLAIN ON, директива является избыточной. Можно использовать эту директиву для тестирования и отладки плана запроса.

Новые возможности оператора SELECT

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

SELECT FIRST 10 name, salary

FROM emp

ORDER BY salary DESC

Ограничения:

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

  • При определении представлений.
  • Во вложенных операторах SELECT.
  • В подзапросах.
  • В разделе SELECT оператора INSERT или при вставке во временную или внешнюю таблицу.
  • В хранимых процедурах.
  • В операторах, где вложенный SELECT используется как выражение.
  • Как часть запроса с объединением (UNION) (кроме сервера с опциями AD/XP).

 


Украинская баннерная сеть
 

[Home]

Сайт поддерживается группой пользователей Информикс на Украине.

Hosted by NO-more.