В документации наткнулся на такой момент:
A sort merge has the same advantage over a nested loops join as the hash join: the database accesses rows in the PGA rather than the SGA, reducing logical I/O by avoiding the necessity of repeatedly latching and reading blocks in the database buffer cache. In general, hash joins perform better than sort merge joins because sorting is expensive. However, sort merge joins offer the following advantages over a hash join
Я посмотрел где это пишут, там ещё пишут
https://docs.oracle.com/database/121/TGSQL/tgsql_join.htm#TGSQL94690 A sort merge join is a variation on a nested loops join.
нормально.
авторДля обоих случаев dedicated и shared подключения, в какой именно area в SGA выполняется nested loops и всегда ли он выполняется в SGA?
Некоторым операциям плана выполнения таким как HASH JOIN, SORT JOIN, SORT ORDER BY, SORT/HASH UNIQUE, BUFFER SORT (который ничего не сортирует кстати) и т.д. надо получить весь набор данных из подчинённого источника, и произвести какие-то действия (скажем отсортировать), прежде чем начать возвращать строки. Для этого выделяется в PGA области называются SQL Workarea (v$sql_worarea, v$sql_worarea_active, и т.д.), где могут храниться данные больших объёмов. Nested Loops не хранит таких объёмов.
Работает это так.
Предположим есть простой запрос и план выполнения
1.
2.
3.
4.
5.
6.
7.
8.
select * from t1;
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS STORAGE FULL| T1 |
------------------------------------------
Здесь шаг 0 SELECT STATEMENT вызывает шаг 1 TABLE ACCESS STORAGE FULL чтобы получить строки. Шаг 1 начинает сканировать таблицу, возвращает строки по одной и так пока не набирается количество строк равное fetch size (то что в SQL*Plus называется arraysize). Как только набралось, шаг 0 упаковывает результат, отправляет клиенту и ждёт следующий fetch call от клиента. Приходит следующий fetch и всё повторяется с того места где в прошлый раз остановились. Каждый fetch call таблица будет просканирована всё дальше.
Теперь предположим у нас есть сортировка
1.
2.
3.
4.
5.
6.
7.
8.
9.
select * from t1 order by n;
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | TABLE ACCESS STORAGE FULL| T1 |
-------------------------------------------
В этом случае шаг 0 вызовет шаг 1, тот вызовет шаг 2, получит все строки из шага 2 в SQL workarea, отсортирует, затем вернёт в шаг 0 количество строк равное fetch size, и каждый следующий fetch call будет возвращать строки из SQL workarea. Поэтому в этом случае первый fetch будет медленный, а остальные относительно быстрые, поскольку таблица уже не сканируется.
Шаг SORT ORDER BY блокирующий, он должен полностью выполниться перед тем как он сможет возвращать строки.
Теперь если у нас Nested Loops
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select * from t1, t2 where t1.n = t2.n;
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS STORAGE FULL| T1 |
|* 3 | INDEX RANGE SCAN | I2 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."N"="T2"."N")
Примерно тоже самое как в первом случае без блокирующих операций. Шаг 0 вызывает шаг 1, шаг 1 вызывает шаг 2, тот возвращает строку в шаг 1, шаг 1 вызывает шаг 3, тот сканирует индекс для данной строки, возвращает строки в шаг 1, тот возвращённые строки возвращает в шаг 0 и так пока не наберётся количество строк равное fetch size. Каждый следующий fetch call продожаем с того места где закончили.
В случае Hash Join
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
| 2 | TABLE ACCESS STORAGE FULL| T1 |
| 3 | TABLE ACCESS STORAGE FULL| T2 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."N"="T2"."N")
Шаг 1 вызывает шаг 2, вытягивает все строки из таблицы строит на основе этого hash table, результат хранит в SQL workarea. Когда построил, шаг 1 вызывает шаг 3, для каждой строки из шага 3 ищет строки в hash table, найденные возвращает. Здесь построение hash table это блокирующая операция, которая должна полностью закончиться прежде чем HASH JOIN сможет возвращать строки. Шаг 2 выполняется один раз во время первого fetch call. Шаг 3 будет выполняться по немногу каждый следующий fetch call.
Начиная с 12с строки передаются между операциями не по одной, а пачками, если параметр _rowsets_enabled=true (default).
Дай знать, если что не ясно.
