Вы здесь

SQL Hints в документации Oracle

Ссылки

Oracle SQL Hints by Wei Huang
Oracle 12c new SQL Hints by Wei Huang

Замечания

QREF: SQL Statement HINTS [ID 29236.1]:

  • «О синтаксисе: /*+ HINT HINT … */ в PL/SQL пробел между «+» и первой буквой подсказки имеет значение, в случае отсутствия пробела подсказка может быть игнорирована, т.е. /*+ ALL_ROWS */ правильное использование, а /*+ALL_ROWS */ — неправильное
  • Подсказки всегда «форсируют» использование стоимостной оптимизации (cost based optimizer) — кроме подсказки RULE
  • Если в запросе используются псевдонимы (table alias), в подсказках также должны использоваться псевдонимы вместо названий таблиц:
SQL> SELECT /*+ FULL ( emp )     */ empno FROM emp myalias WHERE empno > 10; -- НЕправильно
 
SQL> SELECT /*+ FULL ( myalias ) */ empno FROM emp myalias WHERE empno > 10; -- правильно
  • В посказках не должно быть указания названия схемы:
SQL> SELECT /*+ index(scott.emp emp1) */...
  • Некорректные подсказки (invalid hints) игнорируются без предупреждений… некорректность подсказки может быть неочевидна, например:
  • указание подсказки FIRST_ROWS (для получения первых строк) для запроса с ORDER BY (поскольку данные должны быть отсортированы прежде, чем будут возвращены первые строки запроса, использование first_rows может не дать желаемого результата)
  • указанные в подсказке операции с данными (access path) должны быть доступны Например: подсказка INDEXс указанием несуществующего индекса будет проигнорирована без сообщений об ошибках…»

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

Подсказки

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

/*+ RULE */
/*+ ALL_ROWS */
/*+ FIRST_ROWS */
/*+ FIRST_ROWS(n) */

— порядок доступа

/*+ LEADING */
/*+ ORDERED */

— методы соединения

/*+ USE_HASH */
/*+ USE_NL */
/*+ USE_MERGE */
/*+ USE_HASH_AGGREGATION */
/*+ NATIVE_FULL_OUTER_JOIN */
/*+ INDEX_JOIN */
/*+ INDEX_COMBINE */
/*+ NUM_INDEX_KEYS */

— способы выполнения [под]запроса

/*+ DRIVING_SITE */
/*+ MATERIALIZE */
/*+ INLINE */
/*+ PRECOMPUTE_SUBQUERY */

— статистика объектов

/*+ DYNAMIC_SAMPLING */
/*+ DYNAMIC_SAMPLING_EST_CDN */
/*+ CARDINALITY */
/*+ OPT_ESTIMATE */
/*+ TABLE_STATS | INDEX_STATS | COLUMN_STATS */

— трансформации [под]запросов

/*+ NO_QUERY_TRANSFORMATION */
/*+ PUSH_SUBQ */
/*+ NO_UNNEST */
/*+ UNNEST */
/*+ NO_ELIMINATE_OBY */
/*+ MERGE */ /*+ NO_MERGE */
/*+ USE_CONCAT */
/*+ NO_EXPAND */
/*+ PUSH_PRED */ /*+ NO_PUSH_PRED */
/*+ FACTORIZE_JOIN */ /*+ NO_FACTORIZE_JOIN */
/*+ OR_EXPAND */

— использование курсоров

/*+ CURSOR_SHARING_EXACT */
/*+ BIND_AWARE */ /*+ NO_BIND_AWARE */

— параллельное выполнение

/*+ PARALLEL */
/*+ SHARED */
/*+ STATEMENT_QUEUING */ /*+ NO_STATEMENT_QUEUING */

— прочие

/*+ QB_NAME */
/*+ GATHER_PLAN_STATISTICS */
/*+ OPT_PARAM */
/*+ OPTIMIZER_FEATURES_ENABLE */
/*+ APPEND */ /*+ NOAPPEND */
/*+ APPEND_VALUES */
/*+ RESULT_CACHE */ /*+ NO_RESULT_CACHE */
/*+ IGNORE_ROW_ON_DUPKEY_INDEX */

 

Optimization Goals and Approaches hints

Подсказки,определяющие общие цели и подходы для оптимизации плана выполнения запроса, включая правила и методы доступа к данным. Соответсвуют (почти) описаниям значений параметра OPTIMIZER_MODE

/*+ RULE */

Официально не поддерживается, начиная с Oracle 10, используется оптимизация по точно оределённым правилам (Rule Based Optimization) без учёта статистики объектов бд. Описание применяемых правил для инилизационного параметра optimizer_mode = rule

В Oracle 11gR2 введены дополнительные ограничения использования подсказки /*+ rule*/

В частности, несмотря на использование подсказки RULE Oracle будет использовать Cost-Based Optimization, если:

  • кроме RULE в запросе используются другие подсказки
  • запрос использует партицированные или таблицы, организованные в виде индекса (IOT), или материализованные представления (mview)
  • в запросе используются кляузы SAMPLE, SPREADSHEET, конструкции GROUPING SETS
  • в запросе используются ANSI left|full outer join
  • запрос выполняется параллельно
  • используется Flashback cursor (as of [scn|timestamp])

В 11.2 при использовании RBO может формироваться трейс оптимизатора и использоваться некоторые преобразования запросов за исключением Cost-Based Query Transformation (что логично:) — см. замечания в Индекс в статусе unusable, подсказка INDEX и обновлённый RBO в Oracle 11.2

/*+ ALL_ROWS */

«Подсказка ALL_ROWS определяет целью скорейшее выполнение всего запроса с минимальным расходом ресурсов (best throughput при извлечении всего результирующего набора данных). При одновременном с ALL_ROWS или FIRST_ROWS указании подсказок, определяющих методы доступа к данным (NO_INDEX_SS, INDEX_COMBINE,..) или указывающие методы объединения объектов БД (LEADING, USE_NL_WITH_INDEX,..), оптимизатор отдаёт предпочтение подсказкам методов доступа и объединения»

/*+ FIRST_ROWS */

Из документации Oracle 8: «Подсказка FIRST_ROWS определяет стоимостной подход (cost-based approach) для оптимизации блоков запроса (statement block) с целью лучшего времени отклика (response time, минимального расхода ресурсов для возвращения первых строк запроса). В соответствии с этой подсказкой оптимизатор делает следующие предпочтения [в выборе операций доступа к данным и методов соединения]:

  • При наличии оптимизатор использует сканирование по индексу (index scan) вместо полного сканирования таблицы (full table scan)
  • Если доступно сканирование по индексу (index scan), оптимизатор выбирает nested loops join вместо sort-merge join в случае, когда сканируемая индексированная таблица может быть использована как ведомая таблица (inner table) для операции nested loops
  • Если использование индекса (index scan) может быть использовано для получения отсортированных данных (в порядке, определённом фразой ORDER BY), оптимизатор выбирает индексный доступ во избежание дополнительной сортировки»

Начиная с Oracle 9i: «Подсказка FIRST_ROWS указанная без аргументов, предназначенная для оптимизации плана выполнения с целью скорейшего возвращения первой строки запроса, сохраняется только для обратной совместимости (backward compatibility) и стабильности планов выполнения (plan stability

Значение инилизационного параметра OPTIMIZER_MODE=FIRST_ROWS (что равносильно применению подсказки FIRST_ROWS для всех запросов) аннонсируется в документации вплоть до версии Oracle 11.2

/*+ FIRST_ROWS(n) */

Оптимизация, основанная на стоимости (Cost Based Optimization) + использование правил (предпочтений в выборе плана) с целью получения лучшего времени отклика для получения первых n строк. План рассчитывается с учётом значения n, как целевого количества выбранных запросом строк (query cardinality).

См. описание правил для параметра optimizer_mode = first_rows

Не совсем понятная фраза в документации: «Оптимизатор игнорирует эту подсказку в SQL предложениях DELETE и UPDATE и в запросах SELECT, включающих блокирующие операции, такие как сортировки и группировки. Такие SQL предложения не могут быть оптимизированы с целью наименьшего времени отклика (best response time), поскольку Oracle должен обработать все строки запроса до того, как вернуть первую строку результата. При указании этой подсказки запросы указанного типа оптимизируются с целью лучшего времени получения всех строк запроса с минимальным расходом ресурсов (best throughput, как при использовании подсказки ALL_ROWS)»

Oracle always uses ALL_ROWS mode internally for DML statement — в блоге Dion Cho есть показательный пример для версии 10.2

В Oracle 11.2 ничего не изменилось — независимо от количества обновляемых строк (Rows), устанавливаемого функцией rownum ни планы выполнения (кроме дополнительной операции COUNT STOPKEY), ни стоимость, ни ожидаемое время (Time) не меняются:

-- План при обновлении ВСЕХ строк таблицы
 
11.2.0.3@SQL> update T
...
5  /
 
-------------------------------------------------------------------------------
| Id  | Operation                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |  1129K|    43M|  5751K (20)| 17:34:32 |
|   1 |  UPDATE                       |       |       |            |          |
|*  2 |   HASH JOIN SEMI              |  1129K|    43M|   105K  (1)| 00:19:26 |
|   3 |    TABLE ACCESS BY INDEX ROWID|  1129K|    33M| 75115   (1)| 00:13:47 |
|*  4 |     INDEX RANGE SCAN          |  1129K|       |  5531   (1)| 00:01:01 |
|*  5 |    INDEX FAST FULL SCAN       |    11M|    95M| 16079   (1)| 00:02:57 |
|   6 |   TABLE ACCESS BY INDEX ROWID |     1 |     9 |     4   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN          |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
-- План при обновлении ОДНОЙ строки таблицы
 
11.2.0.3@SQL> update T
...
and rownum 6  /
 
--------------------------------------------------------------------------------
| Id  | Operation                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT               |     1 |    40 |  5751K (20)| 17:34:32 |
|   1 |  UPDATE                        |       |       |            |          |
|*  2 |   COUNT STOPKEY                |       |       |            |          |
|*  3 |    HASH JOIN SEMI              |  1129K|    43M|   105K  (1)| 00:19:26 |
|   4 |     TABLE ACCESS BY INDEX ROWID|  1129K|    33M| 75115   (1)| 00:13:47 |
|*  5 |      INDEX RANGE SCAN          |  1129K|       |  5531   (1)| 00:01:01 |
|*  6 |     INDEX FAST FULL SCAN       |    11M|    95M| 16079   (1)| 00:02:57 |
|   7 |   TABLE ACCESS BY INDEX ROWID  |     1 |     9 |     4   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN           |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

— при попытке обновить только одну строку Oracle выбирает тот же план с недешёвой операцией HASH JOIN SEMI (в запроса используется конструкция EXISTS) — более подходящей для получения всех строк обновляемой таблицы (ALL_ROWS mode)

Простой тест для показывает, как по-разному режимы ALL_ROWSFIRST_ROWS и FIRST_ROWS(n) влияют на поведение оптимизатора: собственно, планы выполнения и методы доступа к данным + Cost + Rows :) на примерах сортировки или группировки при использовании бессмысленного условия object_id > 1 — которому удовлетворяют все строки таблицы T1 — по условию создания таблицы min(object_id)=2)*:

11.2.0.3.@SQL> show parameter optimizer_mode
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS
 
SQL> create table T1 as select * from dba_objects
2  /
 
Table created.
 
SQL> create index T1_IDX on T1(OBJECT_ID)
2  /
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats('','T1');
 
PL/SQL procedure successfully completed.
 
SQL> ----------------------------------------------------------- ORDER BY
SQL> select /*+ ALL_ROWS*/
2   *
3    from t1
4   where object_name like '%#%'
5     and object_id > 1
6   order by object_id;
 
30 rows selected.
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2952 |   279K|   164   (2)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |  2952 |   279K|   164   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |  2952 |   279K|   163   (1)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter("OBJECT_NAME" LIKE '%#%' AND "OBJECT_NAME" IS NOT NULL
AND "OBJECT_ID">1)
 
SQL> select /*+ FIRST_ROWS*/
2   *
3    from t1
4   where object_name like '%#%'
5     and object_id > 1
6   order by object_id;
 
30 rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 546753835
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |  2952 |   279K|   553   (1)| 00:00:01 | -- при тех же ожидаемых Rows=2952 - изменились план и стоимость
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1     |  2952 |   279K|   553   (1)| 00:00:01 | -- исключена сортировка
|*  2 |   INDEX RANGE SCAN          | T1_IDX | 59030 |       |    66   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
1 - filter("OBJECT_NAME" LIKE '%#%' AND "OBJECT_NAME" IS NOT NULL)
2 - access("OBJECT_ID">1)                                         -- бессмысленное условие: INDEX RANGE SCAN только экономит на сортировке
 
SQL> select /*+ FIRST_ROWS(10)*/
2   *
3    from t1
4   where object_name like '%#%'
5     and object_id > 1
6   order by object_id;
 
30 rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 546753835
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    10 |   970 |     4   (0)| 00:00:01 | -- абс.тот же план (Plan hash value) рассчитан по-другому:
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1     |    10 |   970 |     4   (0)| 00:00:01 | -- изменились Cardinality (Rows) и итоговая стоимость
|*  2 |   INDEX RANGE SCAN          | T1_IDX |       |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
1 - filter("OBJECT_NAME" LIKE '%#%' AND "OBJECT_NAME" IS NOT NULL)
2 - access("OBJECT_ID">1)
 
SQL> ----------------------------------------------------------- GROUP BY
SQL> select /*+ ALL_ROWS*/
2   object_id, count(*)
3    from t1
4   where object_name like '%#%'
5     and object_id > 1
6   group by object_id;
 
30 rows selected.
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2952 | 88560 |   164   (2)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |  2952 | 88560 |   164   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |  2952 | 88560 |   163   (1)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter("OBJECT_NAME" LIKE '%#%' AND "OBJECT_NAME" IS NOT NULL
AND "OBJECT_ID">1)
 
SQL> select /*+ FIRST_ROWS*/
2   object_id, count(*)
3    from t1
4   where object_name like '%#%'
5     and object_id > 1
6   group by object_id;
 
30 rows selected.
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2952 | 88560 |   164   (2)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |  2952 | 88560 |   164   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |  2952 | 88560 |   163   (1)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("OBJECT_NAME" LIKE '%#%' AND "OBJECT_NAME" IS NOT NULL
              AND "OBJECT_ID">1)
 
SQL> select /*+ FIRST_ROWS(10)*/
2   object_id, count(*)
3    from t1
4   where object_name like '%#%'
5     and object_id > 1
6   group by object_id;
 
30 rows selected.
 
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    10 |   300 |     4   (0)| 00:00:01 | -- абсолютно другой план - дешевле и с более точной оценкой Rows
|   1 |  SORT GROUP BY NOSORT        |        |    10 |   300 |     4   (0)| 00:00:01 | -- вместо HASH GROUP BY используется SORT GROUP BY NOSORT,
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1     |    10 |   300 |     4   (0)| 00:00:01 | -- благодаря индексному доступу к отсортированным данным
|*  3 |    INDEX RANGE SCAN          | T1_IDX |       |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("OBJECT_NAME" LIKE '%#%' AND "OBJECT_NAME" IS NOT NULL)
   3 - access("OBJECT_ID">1)

*) статистика для таблицы T1 актуальна и оптимизатор прекрасно «знает» о минимальном и максимальном значении T1.Object_id:

SQL> set serveroutput on
SQL> declare
2    low_value_raw  RAW(2000);
3    high_value_raw RAW(2000);
4    low_value_num  number;
5    high_value_num number;
begin
7    select low_value, high_value
8      into low_value_raw, high_value_raw
9      from user_tab_col_statistics
10     where table_name = 'T1'
11       and column_name = 'OBJECT_ID';
12    dbms_stats.convert_raw_value(low_value_raw, low_value_num);
13    dbms_stats.convert_raw_value(high_value_raw, high_value_num);
14    dbms_output.put_line('Low value T1.OBJECT_ID = ' || low_value_num);
15    dbms_output.put_line('High value T1.OBJECT_ID = ' || high_value_num);
16  end;
17  /
 
Low value T1.OBJECT_ID = 2
High value T1.OBJECT_ID = 107516

или так, попроще:

SQL> select UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) as "LOW",
2         UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) as "HIGH"
3    from user_tab_col_statistics
4   where table_name = 'T1'
5     and column_name = 'OBJECT_ID';
 
       LOW       HIGH
---------- ----------
         2     107516

Access Path Hints

Подсказки,определяющие конкретные способы доступа к данным, порядок и применяемые методы объединения промежуточных наборов данных (result sets)*

Том Кайт называет эти подсказки плохими (bad hints):

«Плохие подсказки указывают оптимизатору как следует действовать [при выполнении запроса], какой индекс использовать, в каком порядке обрабатывать таблицы, с помощью какой операции (join technique) производить соединение [источников данных]»

*) Перед применение необходимо учитывать вероятность изменения статистики системы и объектов (таблиц и индексов), используемых в запросе со временем и, как следствие, возможную неоптимальность указываемых операций для будущих наборов данных. План выполнения, оптимизированный с помощью подсказок этого типа в среде разработки, может оказаться далеко не оптимальным для боевой бд (production system) из-за отличающихся наборов данных и характеристик системы («железо»)

/*+ LEADING( [@query_block] [tablespec],[tablespec],.. ) */

«Подсказка LEADING указывает оптимизатору использовать перечисленный порядок доступа к таблицам при построении плана выполнения запроса… более гибкая, чем ORDERED… Полностью игнорируются при использовании двух или более конфликтующих подсказок LEADING. Для оптимизатора подсказка ORDERED имеет преимущество против LEADING»

В версии 11.2 может игнорироваться оптимизатором при выполнении преобразований, основанных на стоимости (Cost-Based Query Transformation), например, Table Expansion

/*+ ORDERED */

«Подсказка ORDERED указывает Oracle [при выполнении запроса] проводить соединение таблиц в том же порядке, в котором таблицы перечислены в конструкции FROM. Oracle рекомендует вместо ORDERED использовать подсказку LEADING, обладающую большей гибкостью…», т.е. дающей оптимизатору больше возможностей в выборе плана выполнения

/*+ USE_HASH( [@query_block] [tablespec] [tablespec]… ) */
/*+ NO_USE_HASH( [@query_block] [tablespec] [tablespec]… ) */

…указывает оптимизатору использовать / не использовать операцию hash join для соединения каждой указанной таблицы с прочими источниками данных Операции CBO: Hash Join

/*+ USE_NL ( [@query_block] [inner_table] ) */

Индексный доступ к таблицам удалённого обзора

/*+ NO_USE_NL ( [@query_block] [inner_table] ) */
/*+ USE_NL_WITH_INDEX ( [@query_block] inner_table [indexspec]) */

Операции CBO: Nested Loop Join

/*+ USE_MERGE( [@query_block] [tablespec] … ) */
/*+ NO_USE_MERGE( [@query_block] [tablespec] … ) */

Операции CBO: Sort Merge Join

/*+ USE_HASH_AGGREGATION([@query_block]) */
/*+ NO_USE_HASH_AGGREGATION([@query_block]) */

указывают оптимизатору использовать или не использовать вместо классической операции Sort group by относительно «новую» операцию группировки Hash Group By

/*+ NATIVE_FULL_OUTER_JOIN*/
/*+ NO_NATIVE_FULL_OUTER_JOIN*/

управление использованием механизма Native Full Outer Join

/*+ INDEX_JOIN ( [@query_block] tablespec [indexspec],… ) */

использовать для получения результатов запроса временный индекс, получающийся в рез-те объединения существующих индексов методом Index [Hash] Join

/*+ INDEX_COMBINE ( [@query_block] tablespec [indexspec],… ) */

использовать для получения результатов запроса Bitmap операции с ROWID, полученными при индексном доступе.

Сравнение 2-х последних методов: Index Join vs Index Bitmap и использование db file parallel read при доступе к блокам таблицы

/*+ NUM_INDEX_KEYS( table index numkeys ) */

добавлен в версии 10.2 для управления кол-вом индексных ключей, используемых в INLIST ITERATOR с индексом по нескольким полям Bug 5152325 Enh: Add NUM_INDEX_KEYS hint
Функционал подсказки исправлен для 11.2.0.3 и зафиксирован в 12.2 — Unresolved quiz: Avoiding in-list iterator

/*+ DRIVING_SITE ( [@query_block] [tablespec] ) */

«… подсказка указывает оптимизатору выполнять запрос на сайте [сайте таблицы, указанной в хинте], отличном от выбранного бд [Oracle]. Хинт полезен для оптимизации выполнения распределённых запросов»

В зависимости от подсказки запрос выполняется полностью на удалённом сайте — при указании удалённой таблицы в хинте DRIVING_SITE(e):

SQL> select--+ DRIVING_SITE(e)
2   count(*)
3    from emp@loopback_dblink e,
4           dual
5  /
 
Execution Plan
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|        |     1 |     3   (0)| 00:00:01 |        |      | -- ***
|   1 |  SORT AGGREGATE        |        |     1 |            |          |        |      |
|   2 |   MERGE JOIN CARTESIAN |        |    14 |     3   (0)| 00:00:01 |        |      |
|   3 |    REMOTE              | DUAL   |     1 |     2   (0)| 00:00:01 |      ! | R->S |
|   4 |    BUFFER SORT         |        |    14 |     1   (0)| 00:00:01 |        |      |
|   5 |     INDEX FULL SCAN    | PK_EMP |    14 |     1   (0)| 00:00:01 | ORCL1~ |      |
-----------------------------------------------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   3 - SELECT 0 FROM "SYS"."DUAL" "A1" (accessing '!' )
 
Note
-----
   - fully remote statement -- ***

, либо локально, с копированием данных из удалённой таблицы на локальный инстанс — при использовании подсказки DRIVING_SITE(dual), указывающей на локальную таблицу dual. В плане указывается операция REMOTE_TO_SERIAL (R->S):

SQL> select--+ DRIVING_SITE(dual)
2   count(*)
3    from emp@loopback_dblink e,
4           dual
5  /
 
Execution Plan
---------------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     3   (0)| 00:00:01 |        |      |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |        |      |
|   2 |   NESTED LOOPS   |      |    14 |     3   (0)| 00:00:01 |        |      |
|   3 |    FAST DUAL     |      |     1 |     2   (0)| 00:00:01 |        |      |
|   4 |    REMOTE        | EMP  |    14 |     1   (0)| 00:00:01 | LOOPB~ | R->S | -- ***
---------------------------------------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   4 - SELECT 0 FROM "EMP" "E" (accessing 'LOOPBACK_DBLINK.LOCAL_DOMAIN.COM' )

Limitations of DRIVING_SITE Hint (Doc ID 825677.1):

Версии: 9.2.0.1 — 11.2.0.2 … распределённый DML должен выполняться в бд, где расположена целевая таблица DML. Подсказка DRIVING_SITE не меняет этого поведения

DRIVING_SITE предполагает управление выполнением (mapping) всего курсора (а не отдельных подзапросов)… [однако можно перенести выполнение части курсора / подзапроса в удалённый обзор]

Подсказка DRIVING_SITE предназначена для оптимизации запросов (SELECT) и не предназначена для DML или DDL

/*+ MATERIALIZE */

www.club-oracle.com: «Подсказка Materialize представляет собой технику оптимизации запросов и может быть особенно полезна для больших наборов данных. Материализация подзапроса означает создание определённого типа динамической временной таблицы (dynamic temporary table) для использования во время выполнения запроса»

Tom Kyte: «… подсказка указывает оптимизатору Oracle «материализовать» блок запроса (query block), в котором присутствует подсказка, в виде временной таблицы [только] на время выполнения запроса .., подсказка поддерживается даже в текущей версии 11g»

SQL> with emp_sq as
2   (select  * from emp where comm > 0)
select e.ename, d.loc from emp_sq e, dept d where d.deptno = e.deptno
4  /
 
-----------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     3 |    66 |     6  (17)|
|   1 |  MERGE JOIN                  |         |     3 |    66 |     6  (17)|
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    44 |     2   (0)|
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)|
|*  4 |   SORT JOIN                  |         |     3 |    33 |     4  (25)|
|*  5 |    TABLE ACCESS FULL         | EMP     |     3 |    33 |     3   (0)|
-----------------------------------------------------------------------------
 
Statistics
----------------------------------------------------------
          1  recursive calls
         10  consistent gets
          0  redo size
          rows processed
 
SQL> with emp_sq as
2   (select /*+ MATERIALIZE */ * from emp where comm > 0)
select e.ename, d.loc from emp_sq e, dept d where d.deptno = e.deptno
4  /
 
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |     3 |    93 |     8  (13)|
|   1 |  TEMP TABLE TRANSFORMATION    |                           |       |       |            |
|   2 |   LOAD AS SELECT              | SYS_TEMP_0FD9D665D_A5677E |       |       |            |
|*  3 |