Подсказки для операций соединения
Формат команды
{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 следует создать таблицу, в которую будет помещён вывод команды.
Выборка плана выполнения может выполняться в табличном или вложенном формате.