Oracle Hints

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

На уровне init.ora можно задать директивы использования:

CHOOSE
RULE
FIRST_ROWS
ALL_ROWS


В OLTP изменение на уровне init.ora с CHOOSE на FIRST_ROWS настраивает оптимизатор
на возвращение первых нескольких строк быстрее. (наилучшее время отклика)
Он делает это разными способами, но основной из них - выполнение соединений в режиме nested loops.

В хранилищах изменение на уровне init.ora с CHOOSE на ALL_ROWS настраивает оптимизатор
на возвращение всех строк быстрее. (лучшая производительность).

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

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

Если вы обнаружите, что при оптимизации системы используете чаще всего одни и те же хинты,
то это возможно симптом более существенной проблемы.

- отсутствие индекса
- нехватка памяти

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

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

Если вы часто используете хинты:

ORDERED
LEADING

для объединений, посмотрите на индексы, для объединяемых таблиц.

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

Синтаксис хинтов:

/*+ HINT1 HINT2 HINT3 */

отступы в один пробел имеют важное значение

/*+ ALL_ROWS */

подсказки всегда форсируют использование стоимостной оптимизации, кроме подсказки RULE.

Если в запросах используются псевдонимы (table alias), в подсказках также должны
использоваться псевдонимы вместо названий таблиц.

select /*+ FULL (my_alias) */ empno
  from emp my_alias
   where empno > 10;


В подсказках не должно быть указания названия схемы:

select /*+ index (scott.emp emp1) */ ...

Некорректные подсказки игнорируются без предупреждений.

Некорректность подсказки может быть неочевидна.

Например указание подсказки FIRST_ROWS (для получения первых строк) для запроса с ORDER BY.
Поскольку данные должны быть отсортированы преде, чем будут возвращены первые строки запроса.
Использование FIRST_ROWS может не дать желаемого результата.

Указанные в подсказке операции с данными должны быть доступны.
Подсказка INDEX с указанием несуществующего индекса будет проигнорирована
без сообщений об ошибках.

Подсказки:

Общие цели оптимизатора:

RULE
ALL_ROWS
FIRST_ROWS
FIRST_ROWS(n)
CHOOSE

Метоы доступа

AND_EQUAL
HASH
INDEX_COMBINE
INDEX_JOIN
CLUSTER
INDEX
INDEX_DESC
NO_INDEX
FULL
INDEX_ASC
INDEX_FFS
ROWID

Преобразование запроса

FACT
NO_FACT
REWRITE
USE_CONCAT
MERGE
NO_MERGE
STAR
NO_EXPAND
NOREWRITE
STAR_TRANSFORMATION

Параллельное исполнение

NOPARALLEL
PARALLEL_INDEX
NOPARALLEL_INDEX
PARALLEL
PQ_DISTRIBUTE

Операции объединения:

DRIVING_SITE
LEADING
NL_AJ
PUSH_SUBQ
USE_NL
HASH_AJ
MERGE_AJ
NL_SJ
USE_HASH
HASH_SJ
MERGE_SJ
ORDERED
USE_MERGE

Другие инструкции

APPEND
NOAPPEND
NOCACHE
UNNEST
CACHE
NO_UNNEST
ORDERED_PREDICATES
CURSOR_SHARING_EXACT
NO_PUSH_PRED
PUSH_PRED





Примеры использования:

Общие цели оптимизатора:

ALTER SYSTEM SET optimizer_mode=RULE SCOPE=MEMORY;
ALTER SYSTEM SET optimizer_mode=ALL_ROWS SCOPE=MEMORY;
ALTER SYSTEM SET optimizer_mode=FIRST_ROWS SCOPE=MEMORY;
ALTER SYSTEM SET optimizer_mode=CHOOSE SCOPE=MEMORY;


SELECT /*+ RULE */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;


SELECT /*+ ALL_ROWS */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;


SELECT /*+ FIRST_ROWS */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$';


SELECT /*+ FIRST_ROWS(10) */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$';


SELECT /*+ CHOOSE */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;