В чем разница между хэш-соединением и соединением слиянием (Oracle RDBMS)?

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

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

(В версиях, предшествующих 10g, внешние соединения из большой таблицы в маленькую были проблематичными с точки зрения производительности, поскольку оптимизатор не мог разрешить необходимость доступа сначала к меньшей таблице для хэш-соединения, а к большей таблице — для внешнего соединения. Следовательно, хэш-соединения в этой ситуации были недоступны).

Стоимость хэш-соединения можно уменьшить, разделив обе таблицы по ключу(ам) соединения. Это позволяет оптимизатору сделать вывод, что строки из раздела в одной таблице найдут соответствие только в определенном разделе другой таблицы, а для таблиц, имеющих n разделов, хэш-соединение выполняется как n независимых хэш-соединений. Это имеет следующие эффекты:

1. Размер каждой хеш-таблицы уменьшается, что снижает максимальный объем требуемой памяти и потенциально устраняет необходимость в том, чтобы операция требовала временного дискового пространства.
2. Для параллельных операций запросов объем обмена сообщениями между процессами значительно сокращается, что снижает использование ЦП и повышает производительность, поскольку каждое хэш-соединение может выполняться одной парой процессов PQ.
3. Для непараллельных операций запроса потребность в памяти уменьшается в n раз, а первые строки проецируются из запроса раньше.

Следует отметить, что хеш-соединения можно использовать только для эквивалентных соединений, но соединения слиянием более гибкие.

В общем, если вы объединяете большие объемы данных в эквивалентном соединении, лучше использовать хеш-соединение.

Эта тема очень хорошо освещена в документации.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optimops.htm#i51523

Документы 12.1: https://docs.oracle.com/database/121/TGSQL/tgsql_join.htm

Я просто хочу отредактировать это для потомков, чтобы теги для оракула не были добавлены, когда я ответил на этот вопрос. Мой ответ был более применим к MS SQL.

Соединение слиянием является наилучшим из возможных, поскольку оно использует порядок, в результате чего для выполнения соединения требуется один проход по таблицам. ЕСЛИ у вас есть две таблицы (или покрывающие индексы), которые имеют одинаковый порядок, например, первичный ключ и индекс таблицы по этому ключу, тогда при выполнении этого действия произойдет соединение слиянием.

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

В худшем случае это вложенный цикл, который имеет порядок (n * m), что означает отсутствие порядка или размера для использования, а соединение просто для каждой строки в таблице x ищет таблицу y для соединений.