Apakah halaman ini bermanfaat?
Tanggapan Anda atas konten ini sangat penting. Berikan pendapat Anda.
Tanggapan lainnya?
1500 karakter tersisa
Understanding Merge Joins
Collapse the table of content
Expand the table of content
EN
Konten ini tidak tersedia dalam bahasa Anda dan berikut adalah versi bahasa Inggris.

Understanding Merge Joins

The merge join requires both inputs to be sorted on the merge columns, which are defined by the equality (ON) clauses of the join predicate. The query optimizer typically scans an index, if one exists on the proper set of columns, or it places a sort operator below the merge join. In rare cases, there may be multiple equality clauses, but the merge columns are taken from only some of the available equality clauses.

Because each input is sorted, the Merge Join operator gets a row from each input and compares them. For example, for inner join operations, the rows are returned if they are equal. If they are not equal, the lower-value row is discarded and another row is obtained from that input. This process repeats until all rows have been processed.

The merge join operation may be either a regular or a many-to-many operation. A many-to-many merge join uses a temporary table to store rows. If there are duplicate values from each input, one of the inputs will have to rewind to the start of the duplicates as each duplicate from the other input is processed.

If a residual predicate is present, all rows that satisfy the merge predicate evaluate the residual predicate, and only those rows that satisfy it are returned.

Merge join itself is very fast, but it can be an expensive choice if sort operations are required. However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm.

Konten Komunitas

Tambah
Tampilkan:
© 2015 Microsoft