Gabungan (SQL Server)

Berlaku untuk:SQL ServerAzure SQL Database Azure SQL Managed Instance Azure Synapse AnalyticsSQL database di Microsoft Fabric

SQL Server menggunakan gabungan untuk mengambil data dari beberapa tabel berdasarkan hubungan logis di antara mereka. Gabungan sangat mendasar untuk operasi database relasional dan memungkinkan Anda menggabungkan data dari dua tabel atau lebih ke dalam satu kumpulan hasil.

SQL Server mengimplementasikan operasi gabungan logis (didefinisikan oleh sintaks Transact-SQL) dan operasi gabungan fisik (algoritma aktual yang digunakan untuk menjalankan gabungan). Memahami kedua aspek membantu Anda menulis kueri yang efisien dan mengoptimalkan performa database.

Operasi gabungan logis meliputi:

  • Gabungan dalam
  • Gabungan kiri, kanan, dan luar penuh
  • Gabungan silang

Operasi gabungan fisik meliputi:

  • Gabungan Perulangan Berlapis
  • Gabungkan gabungan
  • Gabungan hash
  • Gabungan adaptif (Berlaku untuk: SQL Server 2017 (14.x) dan versi yang lebih baru)

Artikel ini menjelaskan cara kerja gabungan, kapan menggunakan jenis gabungan yang berbeda, dan bagaimana Pengoptimal Kueri memilih algoritma gabungan yang paling efisien berdasarkan faktor-faktor seperti ukuran tabel, indeks yang tersedia, dan distribusi data.

Note

Untuk informasi selengkapnya tentang sintaks gabungan, lihat klausa FROM ditambah JOIN, APPLY, PIVOT.

Menggabungkan dasar-dasar

Dengan menggunakan gabungan, Anda dapat mengambil data dari dua tabel atau lebih berdasarkan hubungan logis antara tabel. Gabungan menunjukkan bagaimana SQL Server harus menggunakan data dari satu tabel untuk memilih baris di tabel lain.

Kondisi gabungan menentukan cara dua tabel terkait dalam kueri dengan:

  • Menentukan kolom pada setiap tabel yang akan digunakan untuk penggabungan. Kondisi join yang umum biasanya menentukan kunci asing dari satu tabel dan kunci terkaitnya di tabel lain.
  • Menentukan operator logis (misalnya, = atau <>,) untuk digunakan dalam membandingkan nilai dari kolom.

Gabungan dinyatakan secara logis menggunakan sintaks Transact-SQL berikut:

  • [ INNER ] JOIN
  • LEFT [ OUTER ] JOIN
  • RIGHT [ OUTER ] JOIN
  • FULL [ OUTER ] JOIN
  • CROSS JOIN

Inner join dapat ditentukan dalam klausul FROM atau WHERE. Gabungan luar dan gabungan silang hanya dapat ditentukan dalam klausa FROM saja. Kondisi join dikombinasikan dengan kondisi pencarian WHERE dan HAVING untuk mengontrol baris yang dipilih dari tabel dasar yang dirujuk dalam klausa FROM.

Menentukan kondisi gabungan dalam FROM klausul membantu memisahkannya dari kondisi pencarian lain yang mungkin ditentukan dalam WHERE klausul, dan merupakan metode yang direkomendasikan untuk menentukan gabungan. Sintaks gabungan klausa ISO FROM yang disederhanakan adalah:

FROM first_table < join_type > second_table [ ON ( join_condition ) ]
  • join_type menentukan jenis gabungan apa yang dilakukan: gabungan dalam, luar, atau silang. Untuk penjelasan tentang berbagai jenis gabungan, lihat klausa FROM.
  • join_condition menentukan predikat yang akan dievaluasi untuk setiap pasangan baris yang digabungkan.

Kode berikut adalah contoh dari sebuah spesifikasi penggabungan klausa FROM:

FROM Purchasing.ProductVendor INNER JOIN Purchasing.Vendor
     ON ( ProductVendor.BusinessEntityID = Vendor.BusinessEntityID )

Kode berikut adalah pernyataan sederhana SELECT yang menggunakan join ini:

SELECT ProductID, Purchasing.Vendor.BusinessEntityID, Name
FROM Purchasing.ProductVendor INNER JOIN Purchasing.Vendor
    ON (Purchasing.ProductVendor.BusinessEntityID = Purchasing.Vendor.BusinessEntityID)
WHERE StandardPrice > $10
  AND Name LIKE N'F%';
GO

Pernyataan SELECT mengembalikan informasi produk dan pemasok untuk kombinasi komponen apa pun yang dipasok oleh perusahaan yang nama perusahaannya diawali dengan huruf F dan harga produknya lebih dari $10.

Saat beberapa tabel dirujuk dalam satu kueri, semua referensi kolom harus tidak ambigu. Dalam contoh sebelumnya, tabel ProductVendor dan Vendor memiliki kolom bernama BusinessEntityID. Nama kolom apa pun yang sama di dua tabel atau lebih yang dirujuk dalam kueri harus diawali dengan nama tabel. Semua referensi ke kolom Vendor dalam contoh dituliskan secara lengkap.

Saat nama kolom tidak diduplikasi dalam dua tabel atau lebih yang digunakan dalam kueri, referensi ke dalamnya tidak harus memenuhi syarat dengan nama tabel. Ini ditunjukkan dalam contoh sebelumnya. SELECT Klausa seperti itu terkadang sulit dipahami karena tidak ada yang menunjukkan tabel yang menyediakan setiap kolom. Keterbacaan kueri ditingkatkan jika semua kolom memenuhi syarat dengan nama tabelnya. Keterbacaan lebih lanjut ditingkatkan jika alias tabel digunakan, terutama ketika nama tabel itu sendiri harus memenuhi syarat dengan nama database dan pemilik. Kode berikut adalah contoh yang sama, kecuali bahwa alias tabel telah ditetapkan dan kolom memenuhi syarat dengan alias tabel untuk meningkatkan keterbacaan:

SELECT pv.ProductID, v.BusinessEntityID, v.Name
FROM Purchasing.ProductVendor AS pv
INNER JOIN Purchasing.Vendor AS v
    ON (pv.BusinessEntityID = v.BusinessEntityID)
WHERE StandardPrice > $10
    AND Name LIKE N'F%';

Contoh sebelumnya menentukan kondisi gabungan dalam FROM klausa, yang merupakan metode yang disukai. Kueri berikut berisi kondisi gabungan yang sama yang ditentukan dalam WHERE klausa:

SELECT pv.ProductID, v.BusinessEntityID, v.Name
FROM Purchasing.ProductVendor AS pv, Purchasing.Vendor AS v
WHERE pv.BusinessEntityID=v.BusinessEntityID
    AND StandardPrice > $10
    AND Name LIKE N'F%';

Daftar SELECT untuk gabungan dapat mereferensikan semua kolom dalam tabel yang digabungkan, atau subset kolom apa pun. Daftar SELECT tidak harus berisi kolom dari setiap tabel dalam penggabungan. Misalnya, dalam gabungan tiga tabel, hanya satu tabel yang dapat digunakan untuk menjenuhkan dari salah satu tabel lain ke tabel ketiga, dan tidak ada kolom dari tabel tengah yang harus dirujuk dalam daftar pilih. Ini juga disebut anti semi join.

Meskipun kondisi gabungan biasanya memiliki perbandingan kesetaraan (=), operator perbandingan atau relasional lainnya dapat ditentukan, seperti halnya predikat lain. Untuk informasi selengkapnya, lihat Operator Perbandingan dan WHERE.

Saat SQL Server memproses join, Pengoptimal Kueri memilih metode yang paling efisien (dari beberapa kemungkinan) untuk memproses join tersebut. Ini termasuk memilih jenis gabungan fisik yang paling efisien, urutan di mana tabel akan digabungkan, dan bahkan menggunakan jenis operasi gabungan logis yang tidak dapat langsung diekspresikan dengan sintaks Transact-SQL, seperti gabungan semi dan gabungan anti semi. Eksekusi fisik berbagai gabungan dapat menggunakan banyak pengoptimalan yang berbeda dan oleh karena itu tidak dapat diprediksi dengan andal. Untuk informasi selengkapnya tentang semi join dan anti semi join, lihat Referensi operator showplan logis dan fisik.

Kolom yang digunakan dalam kondisi gabungan tidak diperlukan untuk memiliki nama yang sama atau tipe data yang sama. Namun, jika jenis data tidak identik, jenis data harus kompatibel, atau merupakan jenis yang dapat dikonversi secara implisit oleh SQL Server. Jika jenis data tidak dapat dikonversi secara implisit, kondisi gabungan harus secara eksplisit mengonversi jenis data menggunakan fungsi .CAST Untuk informasi selengkapnya tentang konversi implisit dan eksplisit, lihat Konversi jenis data (Mesin Database).

Sebagian besar kueri yang menggunakan gabungan dapat ditulis ulang menggunakan subkueri (kueri yang ditumpuk dalam kueri lain), dan sebagian besar subkueri dapat ditulis ulang sebagai gabungan. Untuk informasi selengkapnya tentang subkueri, lihat Subkueri (SQL Server).

Note

Tabel tidak dapat digabungkan langsung pada kolom ntext, teks, atau gambar. Namun, tabel dapat digabungkan secara tidak langsung pada kolom ntext, teks, atau gambar dengan menggunakan SUBSTRING. Misalnya, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) melakukan gabungan dalam dua tabel pada 20 karakter pertama dari setiap kolom teks dalam tabel t1 dan t2. Selain itu, kemungkinan lain untuk membandingkan kolom ntext atau teks dari dua tabel adalah membandingkan panjang kolom dengan WHERE klausul, misalnya: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)

Memahami gabungan perulangan berlapis

Jika satu input gabungan kecil (kurang dari 10 baris) dan input gabungan lainnya cukup besar dan diindeks pada kolom gabungannya, gabungan perulangan berlapis indeks adalah operasi gabungan tercepat karena memerlukan I/O paling sedikit dan perbandingan terkecil.

Gabungan perulangan berlapis, juga disebut perulangan berlapis, menggunakan satu input gabungan sebagai tabel input luar (ditampilkan sebagai input teratas dalam rencana eksekusi grafis) dan satu sebagai tabel input dalam (bawah). Loop luar memproses tabel input luar baris demi baris. Loop internal, yang dijalankan untuk setiap baris luar, mencari baris yang sesuai dalam tabel input internal.

Dalam kasus paling sederhana, pencarian memindai seluruh tabel atau indeks; ini disebut gabungan perulangan berlapis naif. Jika pencarian mengeksploitasi indeks, itu disebut gabungan perulangan berlapis indeks. Jika indeks dibangun sebagai bagian dari rencana kueri (dan dihancurkan setelah menyelesaikan kueri), indeks ini disebut gabungan perulangan berlapis indeks sementara. Semua varian ini dipertimbangkan oleh Pengoptimal Kueri.

Gabungan perulangan berlapis sangat efektif jika input luar kecil dan input dalam sudah diindeks dan besar. Dalam banyak transaksi kecil, misalnya yang hanya memengaruhi sejumlah kecil baris, join nested loop berbasis indeks lebih unggul daripada merge join maupun hash join. Namun, dalam kueri besar, nested loop join sering kali bukan pilihan yang optimal.

Ketika atribut OPTIMIZED dari operator join Nested Loops diatur ke True, artinya Optimized Nested Loops (atau Batch Sort) digunakan untuk meminimalkan I/O ketika tabel di sisi dalam berukuran besar, baik dijalankan secara paralel maupun tidak. Kehadiran pengoptimalan ini dalam rencana tertentu mungkin tidak terlalu jelas ketika menganalisis rencana eksekusi, mengingat penyortiran itu sendiri adalah operasi tersembunyi. Tetapi dengan melihat atribut OPTIMIZED dalam XML rencana, hal ini menunjukkan bahwa join Nested Loops mungkin mencoba menyusun ulang baris masukan untuk meningkatkan kinerja I/O.

Gabungkan gabungan

Jika kedua input join tidak berukuran kecil tetapi diurutkan berdasarkan kolom join-nya (misalnya, jika diperoleh dengan memindai indeks yang terurut), merge join adalah operasi join yang paling cepat. Jika kedua input join berukuran besar dan ukurannya serupa, merge join dengan pengurutan sebelumnya dan hash join memberikan kinerja yang serupa. Namun, operasi gabungan hash sering jauh lebih cepat jika dua ukuran input berbeda secara signifikan satu sama lain.

Gabungan memerlukan kedua input untuk diurutkan pada kolom penggabungan, yang ditentukan oleh klausa kesetaraan (ON) predikat gabungan. Pengoptimal kueri biasanya memindai indeks, jika tersedia pada kumpulan kolom yang sesuai, atau menempatkan operator pengurutan di bawah join merge. Dalam kasus yang jarang terjadi, mungkin ada beberapa klausa kesetaraan, tetapi kolom penggabungan hanya diambil dari beberapa klausa kesetaraan yang tersedia.

Karena setiap input diurutkan, operator Gabungkan Gabungan mendapatkan baris dari setiap input dan membandingkannya. Misalnya, untuk operasi inner join, baris-baris akan dikembalikan jika nilainya sama. Jika tidak sama, baris bernilai lebih rendah akan dibuang dan baris lain diperoleh dari input tersebut. Proses ini berulang hingga semua baris telah diproses.

Operasi merge join dapat berupa operasi reguler atau operasi banyak-ke-banyak. Gabungan gabungan banyak ke banyak menggunakan tabel sementara untuk menyimpan baris. Jika terdapat nilai duplikat pada masing-masing input, salah satu input perlu kembali ke awal deretan nilai duplikat setiap kali nilai duplikat dari input lainnya diproses.

Jika predikat residu ada, semua baris yang memenuhi predikat penggabungan mengevaluasi predikat residu, dan hanya baris yang memenuhinya yang dikembalikan.

Merge join itu sendiri sangat cepat, tetapi bisa menjadi opsi yang mahal jika operasi pengurutan diperlukan. Namun, jika volume data tersebut besar dan data yang diinginkan dapat diperoleh dalam keadaan sudah diurutkan sebelumnya dari indeks B-tree yang ada, merge join sering kali merupakan algoritma join tercepat yang tersedia.

Gabungan hash

Gabungan hash dapat memproses input besar, tidak terurut, dan tidak terindeksasi secara efisien. Ini berguna untuk hasil perantara dalam kueri yang kompleks karena:

  • Hasil perantara tidak diindeks (kecuali secara eksplisit disimpan ke disk lalu diindeks) dan sering kali tidak cocok diurutkan untuk operasi berikutnya dalam rencana kueri.
  • Pengoptimal kueri hanya memperkirakan ukuran hasil menengah. Karena perkiraan bisa sangat tidak akurat untuk kueri yang kompleks, algoritma untuk memproses hasil perantara tidak hanya harus efisien, tetapi juga harus tetap bekerja dengan baik jika hasil perantara ternyata jauh lebih besar daripada yang diperkirakan.

Gabungan hash memungkinkan pengurangan penggunaan denormalisasi. Denormalisasi biasanya digunakan untuk mencapai performa yang lebih baik dengan mengurangi operasi gabungan, terlepas dari bahaya redundansi, seperti pembaruan yang tidak konsisten. Join hash mengurangi kebutuhan untuk melakukan denormalisasi. Gabungan hash memungkinkan pemartisian vertikal (mewakili grup kolom dari satu tabel dalam file atau indeks terpisah) menjadi opsi yang layak untuk desain database fisik.

Join hash memiliki dua input: input build dan input probe. Pengoptimal kueri menentukan peran-peran ini sehingga input yang lebih kecil dari kedua input tersebut menjadi input build.

Gabungan hash digunakan untuk banyak jenis operasi pencocokan set: gabungan dalam; gabungan kiri, kanan, dan luar penuh; gabungan semi kiri dan kanan; persimpangan; uni; dan perbedaan. Selain itu, varian gabungan hash dapat melakukan penghapusan dan pengelompokan duplikat, seperti SUM(salary) GROUP BY department. Modifikasi ini hanya menggunakan satu input untuk peran build dan probe.

Bagian berikut menjelaskan berbagai jenis gabungan hash: gabungan hash dalam memori, gabungan hash grace, dan gabungan hash rekursif.

Penggabungan hash dalam memori

Gabungan hash pertama kali memindai atau menghitung seluruh input build dan kemudian membangun tabel hash dalam memori. Setiap baris dimasukkan ke dalam bucket hash berdasarkan nilai hash yang dihitung untuk kunci hash. Jika seluruh input build lebih kecil dari memori yang tersedia, semua baris dapat dimasukkan ke dalam tabel hash. Fase build ini diikuti oleh fase pemeriksaan. Seluruh input probe dipindai atau diproses satu baris pada satu waktu, dan untuk setiap baris probe, nilai kunci hash dihitung, bucket hash yang sesuai dipindai, dan hasil yang cocok dihasilkan.

Grace hash join (penggabungan hash Grace)

Jika input build tidak muat di memori, join hash dilakukan dalam beberapa langkah. Ini dikenal sebagai grace hash join. Setiap langkah memiliki fase pembangunan dan fase penelusuran. Awalnya, seluruh masukan build dan probe dibaca lalu dipartisi ke dalam beberapa file (menggunakan fungsi hash berdasarkan kunci hash). Menggunakan fungsi hash pada kunci hash menjamin bahwa dua rekaman gabungan harus berada dalam sepasang file yang sama. Oleh karena itu, tugas menggabungkan dua input besar telah dipecah menjadi beberapa instans yang lebih kecil dari tugas yang sama. Gabungan hash kemudian diterapkan ke setiap pasangan file yang dipartisi.

Penggabungan hash rekursif

Jika masukan build begitu besar sehingga masukan untuk penggabungan eksternal standar memerlukan beberapa tingkat penggabungan, diperlukan beberapa langkah partisi dan beberapa tingkat partisi. Jika hanya beberapa partisi yang besar, langkah-langkah partisi tambahan hanya digunakan untuk partisi tertentu. Agar semua langkah pemartisian berlangsung secepat mungkin, digunakan operasi I/O berukuran besar dan asinkron sehingga satu utas dapat membuat beberapa drive tetap bekerja.

Note

Jika input build hanya sedikit lebih besar daripada memori yang tersedia, elemen dari in-memory hash join dan grace hash join dikombinasikan dalam satu langkah, sehingga menghasilkan hash join hibrida.

Tidak selalu dimungkinkan selama pengoptimalan untuk menentukan gabungan hash mana yang digunakan. Oleh karena itu, SQL Server dimulai dengan menggunakan gabungan hash dalam memori dan secara bertahap transisi ke gabungan hash grace, dan gabungan hash rekursif, tergantung pada ukuran input build.

Jika Pengoptimal Kueri salah memperkirakan input mana dari keduanya yang lebih kecil dan, oleh karena itu, seharusnya menjadi input build, peran build dan probe dibalik secara dinamis. Gabungan hash memastikan bahwa ia menggunakan file luapan yang lebih kecil sebagai input build. Teknik ini disebut pembalikan peran. Pertukaran peran terjadi di dalam operasi hash join setelah setidaknya satu kali data ditulis ke disk.

Note

Pembalikan peran terjadi terlepas dari petunjuk atau struktur kueri apa pun. Pembalikan peran tidak ditampilkan dalam rencana kueri Anda; ketika itu terjadi, itu transparan bagi pengguna.

Penyelamatan hash

Istilah hash bailout terkadang digunakan untuk merujuk pada grace hash join atau recursive hash join.

Note

Operasi hash join rekursif atau hash bailout menyebabkan penurunan kinerja pada server Anda. Jika Anda melihat banyak peristiwa Peringatan Hash dalam pelacakan, perbarui statistik pada kolom yang sedang digabungkan.

Untuk informasi selengkapnya tentang hash bailout, lihat Kelas Peristiwa Peringatan Hash.

Gabungan adaptif

Mode batch Gabungan Adaptif memungkinkan pemilihan metode join Hash Join atau Nested Loops ditunda hingga setelah input pertama dipindai. Operator Join Adaptif menentukan nilai ambang yang digunakan untuk memutuskan kapan harus beralih ke rencana Nested Loops. Oleh karena itu, rencana kueri dapat secara dinamis beralih ke strategi gabungan yang lebih baik selama eksekusi tanpa harus dikompresi ulang.

Tip

Beban kerja dengan osilasi yang sering antara pemindaian input gabungan kecil dan besar akan mendapat manfaat paling besar dari fitur ini.

Keputusan runtime didasarkan pada langkah-langkah berikut:

  • Jika jumlah baris dari input build join cukup kecil sehingga join Nested Loops akan lebih optimal daripada join Hash, rencana eksekusi beralih ke algoritma Nested Loops.
  • Jika input build join melebihi ambang jumlah baris tertentu, tidak terjadi peralihan dan rencana eksekusi Anda tetap menggunakan Hash join.

Kueri berikut digunakan untuk mengilustrasikan contoh Gabungan Adaptif:

SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 360;

Kueri mengembalikan 336 baris. Mengaktifkan Live Query Statistics akan menampilkan rencana eksekusi berikut:

Cuplikan layar rencana eksekusi memperlihatkan hasil kueri 336 baris di operator gabungan adaptif akhir.

Dalam rencana tersebut, perhatikan hal-hal berikut:

  1. Pemindaian indeks columnstore yang digunakan untuk menghasilkan baris bagi fase build Hash join.
  2. Operator Adaptive Join yang baru. Operator ini menentukan ambang batas yang digunakan untuk memutuskan kapan harus beralih ke paket Nested Loops. Untuk contoh ini, ambang batasnya adalah 78 baris. Apa pun yang memiliki >= 78 baris akan menggunakan Hash join. Jika nilainya kurang dari nilai ambang, join Nested Loops akan digunakan.
  3. Karena kueri mengembalikan 336 baris, ini melebihi ambang batas, sehingga cabang kedua mewakili fase pemeriksaan operasi gabungan Hash standar. Statistik Kueri Langsung memperlihatkan baris yang mengalir melalui operator - dalam hal ini "672 dari 672".
  4. Dan cabang terakhir adalah Clustered Index Seek yang akan digunakan oleh join Nested Loops jika ambang batas tidak terlampaui. Terlihat "0 dari 336" baris ditampilkan (cabang tersebut tidak digunakan).

Sekarang bandingkan rencana dengan kueri yang sama, tetapi saat nilai Quantity hanya memiliki satu baris di tabel:

SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 361;

Kueri mengembalikan satu baris. Mengaktifkan Statistik Kueri Langsung menampilkan rencana berikut:

Cuplikan layar rencana eksekusi, yang menampilkan join adaptif akhir dengan satu baris.

Dalam paket ini, perhatikan hal berikut:

  • Dengan satu baris dikembalikan, Pencarian Indeks Berkluster sekarang memiliki baris yang mengalir melaluinya.
  • Dan karena fase build Hash Join tidak berlanjut, tidak ada baris yang mengalir melalui cabang kedua.

Catatan tentang Adaptive Join

Gabungan adaptif memperkenalkan persyaratan memori yang lebih tinggi daripada paket yang setara Nested Loops Join yang diindeks. Memori tambahan diminta seolah-olah Nested Loops adalah gabungan Hash. Ada juga beban tambahan untuk fase build karena merupakan operasi stop-and-go, dibandingkan dengan join streaming Nested Loops yang setara. Dengan biaya tambahan tersebut muncul fleksibilitas untuk skenario di mana jumlah baris berfluktuasi dalam input build.

Adaptive Join mode batch berfungsi untuk eksekusi awal suatu pernyataan, dan setelah dikompilasi, eksekusi-eksekusi berikutnya akan tetap adaptif berdasarkan ambang Adaptive Join hasil kompilasi dan baris pada waktu proses yang mengalir melalui fase build dari input luar.

Jika Adaptive Join beralih ke operasi Nested Loops, operasi tersebut menggunakan baris yang sudah dibaca pada tahap build Hash Join. Operator tersebut tidak membaca ulang baris referensi terluar lagi.

Melacak aktivitas gabungan Adaptif

Operator Adaptive Join memiliki atribut operator paket berikut:

Atribut rencana Description
AdaptiveThresholdRows Menampilkan nilai ambang yang digunakan untuk beralih dari metode hash join ke nested loop join.
EstimatedJoinType Tipe join yang kemungkinan akan digunakan.
ActualJoinType Dalam rencana aktual, menunjukkan algoritma join yang pada akhirnya dipilih berdasarkan nilai ambang batas.

Perkiraan rencana menunjukkan bentuk rencana Gabungan Adaptif, bersama dengan ambang batas Gabungan Adaptif yang ditentukan dan perkiraan jenis gabungan.

Tip

Query Store menangkap dan dapat memaksa rencana Adaptive Join mode batch.

Pernyataan yang memenuhi syarat gabungan adaptif

Beberapa kondisi membuat gabungan logis memenuhi syarat untuk mode batch Gabungan Adaptif:

  • Tingkat kompatibilitas database adalah 140 atau lebih tinggi.
  • Kueri adalah SELECT pernyataan (pernyataan modifikasi data saat ini tidak memenuhi syarat).
  • Operasi join ini dapat dieksekusi baik dengan algoritma Nested Loops join terindeks maupun algoritma fisik Hash join.
  • Gabungan Hash menggunakan mode Batch, diaktifkan melalui keberadaan indeks penyimpan kolom dalam kueri secara keseluruhan, tabel terindeks penyimpan kolom yang direferensikan langsung oleh gabungan, atau melalui penggunaan mode Batch pada rowstore.
  • Solusi alternatif yang dihasilkan dari gabungan Nested Loops dan gabungan Hash harus memiliki anak pertama yang sama (referensi luar).

Baris Ambang Adaptif

Bagan berikut menunjukkan contoh persimpangan antara biaya gabungan Hash versus biaya alternatif gabungan Nested Loops. Pada titik perpotongan ini, nilai ambang ditentukan, yang selanjutnya menentukan algoritma yang sebenarnya digunakan untuk operasi join.

Diagram garis yang menunjukkan ambang batas Adaptive Join yang membandingkan hash join dengan nested loop join. Nested loop join memiliki biaya yang lebih rendah pada jumlah baris yang rendah, tetapi jumlah baris yang lebih tinggi pada jumlah baris yang lebih besar.

Nonaktifkan gabungan Adaptif tanpa mengubah tingkat kompatibilitas

Gabungan adaptif dapat dinonaktifkan pada database atau cakupan pernyataan sambil tetap mempertahankan tingkat kompatibilitas database 140 dan tingkat yang lebih tinggi.

Untuk menonaktifkan gabungan Adaptif untuk semua eksekusi kueri yang berasal dari database, jalankan yang berikut ini dalam konteks database yang berlaku:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;

-- Azure SQL Database, SQL Server 2019 and later versions
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = OFF;

Saat diaktifkan, pengaturan ini muncul sebagai diaktifkan di sys.database_scoped_configurations.

Untuk mengaktifkan kembali gabungan adaptif untuk semua eksekusi kueri yang berasal dari database, jalankan yang berikut ini dalam konteks database yang berlaku:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;

-- Azure SQL Database, SQL Server 2019 and later versions
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = ON;

Gabungan adaptif juga dapat dinonaktifkan untuk kueri tertentu dengan menunjuk DISABLE_BATCH_MODE_ADAPTIVE_JOINS sebagai petunjuk kueri USE HINT. Contohnya:

SELECT s.CustomerID,
       s.CustomerName,
       sc.CustomerCategoryName
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc
       ON s.CustomerCategoryID = sc.CustomerCategoryID
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'));

Note

USE HINT Petunjuk kueri lebih diutamakan daripada konfigurasi terlingkup database atau pengaturan bendera pelacakan.

Nilai dan gabungan null

Ketika ada nilai null dalam kolom tabel yang digabungkan, nilai null tidak cocok satu sama lain. Kehadiran nilai null dalam kolom dari salah satu tabel yang digabungkan hanya dapat dikembalikan dengan menggunakan gabungan luar (kecuali WHERE klausul mengecualikan nilai null).

Berikut adalah dua tabel yang masing-masing memiliki NULL di kolom yang akan digunakan dalam join:

table1                          table2
a           b                   c            d
-------     ------              -------      ------
      1        one                 NULL         two
   NULL      three                    4        four
      4      join4

Gabungan yang membandingkan nilai dalam kolom a dengan kolom c tidak mendapatkan kecocokan pada kolom yang memiliki nilai NULL:

SELECT *
FROM table1 t1 JOIN table2 t2
   ON t1.a = t2.c
ORDER BY t1.a;
GO

Hanya satu baris dengan nilai 4 dalam kolom a dan c dikembalikan:

a           b      c           d
----------- ------ ----------- ------
4           join4  4           four

(1 row(s) affected)

Nilai null yang dikembalikan dari tabel dasar juga sulit dibedakan dari nilai null yang dikembalikan dari gabungan luar. Misalnya, SELECT pernyataan berikut melakukan left outer join pada kedua tabel ini:

SELECT *
FROM table1 t1 LEFT OUTER JOIN table2 t2
   ON t1.a = t2.c
ORDER BY t1.a;
GO

Berikut adalah kumpulan hasilnya.

a           b      c           d
----------- ------ ----------- ------
NULL        three  NULL        NULL
1           one    NULL        NULL
4           join4  4           four

(3 row(s) affected)

Hasil tersebut tidak memudahkan pembedaan antara NULL dalam data dan NULL yang menunjukkan kegagalan untuk bergabung. Ketika terdapat nilai NULL dalam data yang akan digabungkan, biasanya lebih baik menghilangkannya dari hasil dengan menggunakan join reguler.

Konten terkait

  • Terakhir diperbarui pada