Подсказки для операций соединения

Формат команды

{DELETE | SELECT | UPDATE} /*+ подсказка текст*/

или

{DELETE | SELECT | UPDATE} --+ подсказка текст

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

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

Командный блок – это отдельная команда SQL или её часть:

- простая команда SELECT, UPDATE или DELETE;

- основная часть сложной команды или её подзапрос;

- часть составного запроса.

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

Подсказки для методов и целей оптимизации

Если команда SQL содержит подсказки, задающие метод и цели оптимизации, оптимизатор будет использовать заданный метод независимо от наличия статистик в словаре, параметра инициализации OPTIMIZATION_MODE или значения параметра OPTIMIZATION_GOAL в команде ALTER SESSION (см. лекцию 4).

/*+ ALL_ROWS текст*/ – явно задаёт способ оптимизации (по стоимости) и цель оптимизации (пропускная способность), оптимизируя общее время выполнения команды.

/*+ FIRST_ROWS текст*/ –

явно задаёт способ оптимизации (по стоимости) и цель оптимизации (время реакции), оптимизируя время на возврат первой строки. В этом случае оптимизатор:

- предпочитает возможность использовать индекс полному просмотру таблицы;

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

- может использовать существующий индекс для реализации сортировки, указанной во фразе ORDER BY.

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

/*+ CHOOSE текст*/ – задаёт возможность выбора способа оптимизации в зависимости от наличия статистик для таблиц, к которым обращается команда SQL. В отсутствии статистик используется оптимизация по синтаксису.

/*+ RULE текст*/ – явно задаёт способ оптимизации по синтаксису.

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

/*+ {FULL | ROWID} (таблица) текст*/ –

задаёт полный просмотр таблицы или просмотр таблицы по ROWID.

/*+ INDEX (таблица [индекс]) текст*/ –

задаёт просмотр по индексу.

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

Например:

Подсказки для порядка соединения

/*+ ORDERED текст*/ – задаёт соединение таблиц в порядке, указанном во фразе FROM.

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

/*+ STAR текст*/ – вынуждает оптимизатор использовать, если возможно, план выполнения запросов типа «звезда».

План выполнения запросов типа «звезда» применяется, когда в запросе последней в порядке соединения указана самая большая таблица, которая соединяется с остальными вложенными циклами по сцепленному индексу. Подсказка STARрименяется, когда существуют, по крайней мере, три таблицы, сцепленный индекс большей из них состоит, как минимум, из трёх столбцов и подсказка не конфликтует с другими подсказками, задающими метод доступа или соединения.

Как правило, при регулярном сборе статистик оптимизатор сам строит эффективный план выполнения запросов типа «звезда».

Подсказки для операций соединения

/*+ {USE_NL USE_MERGE} (таблица) текст*/ –

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

Рекомендуется использовать подсказки USE_NL и USE_MERGE вместе с подсказкой ORDERED. Оптимизатор использует эти подсказки, если указанная таблица становится внутренней таблицей соединения и игнорирует их – в противном случае.

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

Дополнительные подсказки

/*+ {CASH | NOCASH } (таблица) текст*/ –

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

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

/*+ {MERGE | NO_ MERGE } (таблица) текст*/ –

означает, что сложные представления или подзапросы будут выполнены перед основным запросов либо ORACLE не будет объединять подключаемые представления.

Эти подсказки позволяют пользователю влиять на способ обращения к представлению.

Построене плана выполнения запроса в ORACLE

EXPLAIN PLAN

Эта команда выдаёт план выполнения, выбранный оптимизатором для выполнения команд SELECT, UPDATE, INSERT и DELETE.

Сама по себе эта команда не говорит, какие низкоуровнеые процедуры будут выполняться медленно, а какие быстро. Например, если указывается использование индекса, это не означает быстрого выполнения запроса. Индекс может быть весьма неэффективным.

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

EXPLAIN PLAN выдаёт план выполнения, который выбирает оптимизатор в текущий момент времени, с текущими установками параметров инициализации и сеанса. Этот план может не совпадать с планом, который будет использоваться во время фактического выполнения команды, т.к. характеристики объектов, на которых базируется выбор оптимизатора могут к этому моменту существенно измениться.

Перед использованием команды EXPLAIN PLAN посредством запуска командного файла UTLXPLN.SQL следует создать таблицу, в которую будет помещён вывод команды.

Выборка плана выполнения может выполняться в табличном или вложенном формате.