План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS? / Oracle

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

25 сообщений из 25, страница 1 из 1

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38541148

alex_shink

Гость

Кто знает и кому не трудно, подскажите в чем принципиальная разница между операциями HASH JOIN, MERGE JOIN, NESTED LOOPS в плане выпонения запроса.

---------------------------------------------------
Ниже привожу описание для каждой операции взятое с оф. документации Oracle
http://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm

HASH JOIN
(These are join operations.).
Operation joining two sets of rows and returning the result. This join method is useful for joining large data sets of data (DSS, Batch). The join condition is an efficient way of accessing the second table.
Query optimizer uses the smaller of the two tables/data sources to build a hash table on the join key in memory. Then it scans the larger table, probing the hash table to find the joined rows.

MERGE JOIN
(These are join operations.).
Operation accepting two sets of rows, each sorted by a specific value, combining each row from one set with the matching rows from the other, and returning the result.

NESTED LOOPS
(These are join operations.).
Operation accepting two sets of rows, an outer set and an inner set. Oracle compares each row of the outer set with each row of the inner set, returning rows that satisfy a condition. This join method is useful for joining small subsets of data (OLTP). The join condition is an efficient way of accessing the second table.

...

Рейтинг:

0 / 0

28.01.2014, 19:19

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38541155

Leonid Kudryavtsev

Участник

Сообщения: 9 300

Рейтинг: 0 / 0

Я что то не догоняю. Теперь принято в вопросах на форуме сразу указывать и ответ?

...

Рейтинг:

0 / 0

28.01.2014, 19:23

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38541158

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

Leonid KudryavtsevТеперь принято в вопросах на форуме сразу указывать и ответ?

По крайней мере это приятнее, чем когда забывают указывать вопрос...
Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

28.01.2014, 19:25

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38541165

alex_shink

Гость

Leonid KudryavtsevЯ что то не догоняю. Теперь принято в вопросах на форуме сразу указывать и ответ?

Вопрос как раз и возник в связи с тем что читая официальную дукументацию трудно понять разницу

...

Рейтинг:

0 / 0

28.01.2014, 19:31

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38541178

-2-

Участник

Сообщения: 15 349

Рейтинг: 0 / 0

alex_shinkВопрос как раз и возник в связи с тем что читая официальную дукументацию трудно понять разницуПрочитаем эту документацию обычным мозгом и нашим новым alex-shink. Результат одинаковый!
А если нет разницы, зачем платить больше читать документацию?

...

Рейтинг:

0 / 0

28.01.2014, 19:41

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38541182

Добрый Э - Эх

Гость

alex_shink,

Как правильно ты сам и заметил - разница у них принципиальная.
А читать можно не только в официальной доке, но и у гугла спросить. Логика соединения и её реализация практически не зависит от вендора СУБД. Оно что в Оракле, что в скуль-сервере работают одинаково.
Как вариант - одно из многочисленных обсуждений даже тут на скольюру

...

Рейтинг:

0 / 0

28.01.2014, 19:44

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38541262

alex_shink

Гость

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

...

Рейтинг:

0 / 0

28.01.2014, 21:07

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38541277

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

alex_shinkвыложите пожалуйста простенькие примеры относящиеся к каждому типу
Примерчики чего? Они все делают одно и то же: выполняют операцию JOIN. Но разными способами.
Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

28.01.2014, 21:27

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38541278

alex_shink

Гость

Dimitry Sibiryakovalex_shinkвыложите пожалуйста простенькие примеры относящиеся к каждому типу
Примерчики чего? Они все делают одно и то же: выполняют операцию JOIN. Но разными способами.

Примеры запросов

...

Рейтинг:

0 / 0

28.01.2014, 21:28

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38541288

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

alex_shinkПримеры запросов

Код: sql

select * from table_a join table_b on f1=f2

Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

28.01.2014, 21:49

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38541307

Сергей Арсеньев

Участник

Сообщения: 4 121

Рейтинг: 0 / 0

alex_shink,

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

Nested loop - (сильно грубое описание) Пробегаем по таблице 1 и для каждой строки из нее смотрим какие строки из таблицы 2 ей подходят.
Обычно очень быстро дает первые строки результата (если конечно таблица 2 не гигантская, а единственная строка из первой таблицы, которая попадет в результат - последняя). Еще алгоритм, практически не требует дополнительной памяти для промежуточной работы и индекс во второй таблице может сильно помочь.

Hash join - Поскольку сравнивать каждую строку с каждой очень долго придумали шаманские танцы. Придумываем некую очень быстро вычислимую функцию от сравниваемых полей возвращающую результат от 1 до N. Просматриваем все строки таблицы 1 и раскладываем их в N корзин. Просматриваем таблицу 2 и для каждой строки проверяем только строки из подходящей корзины.
Достоинства - Общее количество операций сравнения гораздо меньше.
Недостатки - Можно оптимизировать только операции равенства, ибо для равных значений равны и значения hash функций, но ">" "<" уже не гарантируются. :( Если коллизии (совпадения hash при разных исходных данных) лягут так, что все строки попадут в одну корзину - то только лишняя работа.

Merge join - построен на том, что если множества упорядочены, то просматривать их целиком не обязательно можно последовательно идти по одному - потом по другому, затем опять по первому и так пока не надоест.
Недостатки - данные надо заранее упорядочить. А если использовать индекс при условии, что таблички не влезают в кеш и будут постоянно вымываться приведет к большому числу операций ввода-вывода.

P.S. Забей на все это - СВО ошибется за тебя. :)

...

Рейтинг:

0 / 0

28.01.2014, 22:18

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38541410

oracle1

Гость

alex_shink, check this, about HJ http://asktom.oracle.com/pls/apex/z?p_url=ASKTOM.download_file?p_file=66776171704447018265&p_cat=hj.swf&p_company=822925097021874

...

Рейтинг:

0 / 0

29.01.2014, 02:15

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38541656

jan2ary

Участник

Откуда: Киев

Сообщения: 1 766

Рейтинг: 0 / 0

alex_shink,

На пальцах
SQL Joins, nested loops and all that in less than 6 minutes

YouTube Video

...

Рейтинг:

0 / 0

29.01.2014, 10:49

| Ответить | Цитировать | Написать

Период между сообщениями больше года.

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38959227

daunito

Участник

Сообщения: 552

Рейтинг: 0 / 0

Сергей АрсеньевHash join - Поскольку сравнивать каждую строку с каждой очень долго придумали шаманские танцы. Придумываем некую очень быстро вычислимую функцию от сравниваемых полей возвращающую результат от 1 до N. Просматриваем все строки таблицы 1 и раскладываем их в N корзин. Просматриваем таблицу 2 и для каждой строки проверяем только строки из подходящей корзины.

допустим есть маленькая таблица из 5 строк, которую нужно сджоинить с таблицей из 10 млрд строк. Нужно построить хэш таблицу, которая так же будет состоять из 5 строк. Затем нужно один раз пройтись по большой таблице, вычислить хэш для ключа, по которому происходит соединение, а затем найти полученный хэш в хэш-таблице. Не пойму в чем преимущество. Можно же сразу взять ключ и найти его в маленькой таблице. То есть получается тот же nested loop только не с исходной таблицей, а с полученной таблицей хэш значений

...

Рейтинг:

0 / 0

14.05.2015, 21:09

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38959272

ЩШШЫШ

Гость

daunitoСергей АрсеньевHash join - Поскольку сравнивать каждую строку с каждой очень долго придумали шаманские танцы. Придумываем некую очень быстро вычислимую функцию от сравниваемых полей возвращающую результат от 1 до N. Просматриваем все строки таблицы 1 и раскладываем их в N корзин. Просматриваем таблицу 2 и для каждой строки проверяем только строки из подходящей корзины.

допустим есть маленькая таблица из 5 строк, которую нужно сджоинить с таблицей из 10 млрд строк. Нужно построить хэш таблицу, которая так же будет состоять из 5 строк. Затем нужно один раз пройтись по большой таблице, вычислить хэш для ключа, по которому происходит соединение, а затем найти полученный хэш в хэш-таблице. Не пойму в чем преимущество. Можно же сразу взять ключ и найти его в маленькой таблице. То есть получается тот же nested loop только не с исходной таблицей, а с полученной таблицей хэш значений

Рекомендую тебе задать этот вопрос где-нибудь на oracle.com... Как много полезных ответов ты видишь среди срача выше?

...

Рейтинг:

0 / 0

14.05.2015, 22:43

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38959299

v-a

Гость

http://oracle-performance-tuning-tips3-dass.blogspot.ru/

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

...

Рейтинг:

0 / 0

14.05.2015, 23:55

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38959301

-2-

Участник

Сообщения: 15 349

Рейтинг: 0 / 0

daunitoНе пойму в чем преимущество.Если бы один способ имел однозначное преимущество, про другие никто не вспоминал бы.

...

Рейтинг:

0 / 0

14.05.2015, 23:57

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38959311

кит северных морей

Участник

Откуда: krsk / nyc / krsk

Сообщения: 808

Рейтинг: 0 / 0

daunitoНе пойму в чем преимущество. Можно же сразу взять ключ и найти его в маленькой таблице.

DDL

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

17:17:50 SQL> create table dropme$small(a primary key, b) as select rownum, ora_hash(rownum) from dual connect by rownum <= 5;

Table created.

Elapsed: 00:00:01.37
17:18:35 SQL> create table dropme$large(a, small_a, b) as select rownum, mod(rownum,5), 100-rownum from dual connect by rownum <= 5e5;

Table created.

Elapsed: 00:00:03.18

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.

17:29:09 SQL> l
  1  select /*+leading(l) use_hash(l)*/ s.a, s.b, l.b from dropme$small s join dropme$large l on s.a = l.small_a
  2*
17:29:13 SQL> /

400000 rows selected.

Elapsed: 00:00:01.81

Execution Plan
----------------------------------------------------------
Plan hash value: 1895470926

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |   636K|    31M|       |   943   (2)| 00:00:17 |
|*  1 |  HASH JOIN         |              |   636K|    31M|    23M|   943   (2)| 00:00:17 |
|   2 |   TABLE ACCESS FULL| DROPME$LARGE |   636K|    15M|       |   155   (4)| 00:00:03 |
|   3 |   TABLE ACCESS FULL| DROPME$SMALL |     5 |   130 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S"."A"="L"."SMALL_A")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        332  consistent gets
          0  physical reads
          0  redo size
    5636601  bytes sent via SQL*Net to client
       6755  bytes received via SQL*Net from client
        801  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     400000  rows processed

17:29:51 SQL> select /*+leading(l) use_nl(l s) full(s)*/ s.a, s.b, l.b from dropme$small s join dropme$large l on s.a = l.small_a;

400000 rows selected.

Elapsed: 00:00:08.93

Execution Plan
----------------------------------------------------------
Plan hash value: 1178186915

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |   636K|    31M|  1144K  (1)| 05:43:24 |
|   1 |  NESTED LOOPS      |              |   636K|    31M|  1144K  (1)| 05:43:24 |
|   2 |   TABLE ACCESS FULL| DROPME$LARGE |   636K|    15M|   155   (4)| 00:00:03 |
|*  3 |   TABLE ACCESS FULL| DROPME$SMALL |     1 |    26 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("S"."A"="L"."SMALL_A")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
    1502014  consistent gets
          0  physical reads
          0  redo size
    6839369  bytes sent via SQL*Net to client
       6755  bytes received via SQL*Net from client
        801  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     400000  rows processed

...

Рейтинг:

0 / 0

15.05.2015, 00:34

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38959312

кит северных морей

Участник

Откуда: krsk / nyc / krsk

Сообщения: 808

Рейтинг: 0 / 0

daunito,

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

...

Рейтинг:

0 / 0

15.05.2015, 00:37

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38959328

кит северных морей

Участник

Откуда: krsk / nyc / krsk

Сообщения: 808

Рейтинг: 0 / 0

кит северных морейво втором - четыреста тысяч раз500, конечно. и таблицы в первом запросе местами перепутал, хотя суть не меняется. поспешишь... :(

...

Рейтинг:

0 / 0

15.05.2015, 01:45

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38959336

daunito

Участник

Сообщения: 552

Рейтинг: 0 / 0

кит северных морейпроще говоря, в первом случае маленькая таблица была прочитана один раз, во втором - четыреста тысяч раз.
за счет чего? Вот получили мы хэш очередного ключа dropme$large, дальше нужно проверить есть ли уже его вхождение в хэш-таблицу. Каким образом это можно сделать не перебрав все значения в таблице?

...

Рейтинг:

0 / 0

15.05.2015, 03:01

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38959345

wurdu

Участник

Откуда: Владивосток

Сообщения: 4 443

Рейтинг: 0 / 0

...

Рейтинг:

0 / 0

15.05.2015, 05:08

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38959410

-2-

Участник

Сообщения: 15 349

Рейтинг: 0 / 0

daunitoкит северных морейпроще говоря, в первом случае маленькая таблица была прочитана один раз, во втором - четыреста тысяч раз.за счет чего? Вот получили мы хэш очередного ключа dropme$large, дальше нужно проверить есть ли уже его вхождение в хэш-таблицу. Каким образом это можно сделать не перебрав все значения в таблице?хеш на то и хеш, что его не надо "перебирать" - это адрес ячейки построенной за один проход хеш-таблицы, а в ячейке уже может быть перебор значений с одинаковым хешем.
Приведенный пример демонстрирует разницу в количестве чтений. Но не совсем честный - с принуждением через leading к многократному чтению сильно неполного блока. На практике, у таблиц есть еще индексы на ключи, что помогает NL в ситуациях джоина на небольшую часть строк.

...

Рейтинг:

0 / 0

15.05.2015, 09:07

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38959585

Я и ёжик

Участник

Откуда: СПб

Сообщения: 1 833

Рейтинг: 0 / 0

daunito,
преданья старины глубокой:
2103207
2105105

...

Рейтинг:

0 / 0

15.05.2015, 11:25

| Ответить | Цитировать | Написать

План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

#38959671

daunito

Участник

Сообщения: 552

Рейтинг: 0 / 0

-2-,

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

...

Рейтинг:

0 / 0

15.05.2015, 12:19

| Ответить | Цитировать | Написать

25 сообщений из 25, страница 1 из 1

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&tid=1891128]:	0ms
get settings:	12ms
get forum list:	15ms
check forum access:	4ms
check topic access:	4ms
track hit:	65ms
get topic data:	14ms
get forum data:	3ms
get page messages:	61ms
get tp. blocked users:	2ms
others:	263ms

total:	443ms