Bagikan melalui

Gabungan (SQL Server)

  • Artikel

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

SQL Server melakukan operasi pengurutan, persimpangan, penyatuan, dan perbedaan menggunakan teknologi pengurutan dalam memori dan gabungan hash. Dengan menggunakan jenis rencana kueri ini, SQL Server mendukung pemartisian tabel vertikal.

SQL Server menerapkan operasi gabungan logis, seperti yang ditentukan oleh sintaks Transact-SQL:

  • Gabungan Dalam
  • Gabungan luar kiri
  • Gabungan luar kanan
  • Gabungan luar penuh
  • Gabungan silang

Catatan

Untuk informasi selengkapnya tentang sintaks gabungan, lihat klausa FROM ditambah JOIN, APPLY, PIVOT (Transact-SQL).

SQL Server menggunakan empat jenis operasi gabungan fisik untuk melakukan operasi gabungan logis:

  • Gabungan Perulangan Berlapis
  • Gabungkan gabungan
  • Gabungan hash
  • Gabungan adaptif (dimulai dengan SQL Server 2017 (14.x))

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 dari setiap tabel yang akan digunakan untuk gabungan. Kondisi gabungan umum menentukan kunci asing dari satu tabel dan kunci terkait di tabel lain.
  • Menentukan operator logis (misalnya, = atau <>,) untuk digunakan dalam membandingkan nilai dari kolom.

Gabungan dinyatakan secara logis menggunakan sintaks Transact-SQL berikut:

  • GABUNGAN DALAM
  • KIRI [ LUAR ] GABUNG
  • KANAN [ LUAR ] BERGABUNG
  • FULL [ OUTER ] JOIN
  • CROSS JOIN

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

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 FROM spesifikasi gabungan klausa:

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

Kode berikut adalah pernyataan sederhana SELECT menggunakan gabungan 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 mengembalikan SELECT informasi produk dan pemasok untuk kombinasi bagian apa pun yang disediakan oleh perusahaan di mana nama perusahaan dimulai dengan huruf F dan harga produk 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 diduplikasi antara dua tabel atau lebih yang dirujuk dalam kueri harus memenuhi syarat dengan nama tabel. Semua referensi ke Vendor kolom dalam contoh memenuhi syarat.

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 diperlukan untuk memuat kolom dari setiap tabel dalam gabungan. 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 (Transact-SQL) dan WHERE (Transact-SQL).

Saat proses SQL Server bergabung, Pengoptimal Kueri memilih metode yang paling efisien (dari beberapa kemungkinan) memproses gabungan. 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 karenanya tidak dapat diprediksi dengan andal. Untuk informasi selengkapnya tentang gabungan semi dan anti gabungan semi, lihat Referensi Operator Logis dan Fisik Showplan.

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 CAST fungsi . Untuk informasi selengkapnya tentang konversi implisit dan eksplisit, lihat Konversi Tipe 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.

Catatan

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). Perulangan luar mengonsumsi baris tabel input luar menurut baris. Perulangan dalam, dijalankan untuk setiap baris luar, mencari baris yang cocok di tabel input dalam.

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, seperti yang hanya memengaruhi sekumpulan baris kecil, gabungan perulangan berlapis indeks lebih unggul daripada gabungan gabungan dan gabungan hash. Namun, dalam kueri besar, gabungan perulangan berlapis sering kali bukan pilihan optimal.

Ketika atribut OPTIMIZED dari operator gabungan Nested Loops diatur ke True, itu berarti bahwa Perulangan Berlapis yang Dioptimalkan (atau Pengurutan Batch) digunakan untuk meminimalkan I/O ketika tabel sisi dalam besar, terlepas dari itu diparalelkan atau 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 xml rencana untuk atribut OPTIMIZED, ini menunjukkan gabungan Perulangan Berlapis mungkin mencoba menyusun ulang baris input untuk meningkatkan performa I/O.

Gabungkan gabungan

Jika kedua input gabungan tidak kecil tetapi diurutkan pada kolom gabungan mereka (misalnya, jika mereka diperoleh dengan memindai indeks yang diurutkan), gabungan gabungan adalah operasi gabungan tercepat. Jika kedua input gabungan besar dan dua input memiliki ukuran yang sama, gabungan gabungan dengan pengurutan sebelumnya dan gabungan hash menawarkan performa yang sama. Namun, operasi gabungan hash sering jauh lebih cepat jika dua ukuran input berbeda secara signifikan satu sama lain.

Gabungan gabungan mengharuskan kedua input diurutkan pada kolom penggabungan, yang ditentukan oleh klausa equality (ON) dari predikat gabungan. Pengoptimal kueri biasanya memindai indeks, jika ada pada kumpulan kolom yang tepat, atau menempatkan operator pengurutan di bawah gabungan gabungan. 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 gabungan dalam, baris dikembalikan jika 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 gabungan adalah operasi reguler atau banyak ke banyak. Gabungan gabungan banyak ke banyak menggunakan tabel sementara untuk menyimpan baris. Jika ada nilai duplikat dari setiap input, salah satu input harus mundur ke awal duplikat karena setiap duplikat dari input lain diproses.

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

Gabungkan sendiri sangat cepat, tetapi bisa menjadi pilihan mahal jika operasi pengurutan diperlukan. Namun, jika volume data besar dan data yang diinginkan dapat diperoleh diawali dari indeks pohon B yang ada, gabungan gabungan sering kali merupakan algoritma gabungan 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 menurun dengan baik jika hasil perantara ternyata jauh lebih besar daripada yang diantisipasi.

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. Gabungan hash mengurangi kebutuhan untuk mendenormalisasi. Gabungan hash memungkinkan pemartisian vertikal (mewakili grup kolom dari satu tabel dalam file atau indeks terpisah) menjadi opsi yang layak untuk desain database fisik.

Gabungan hash memiliki dua input: input build dan input pemeriksaan . Pengoptimal kueri menetapkan peran ini sehingga yang lebih kecil dari dua input adalah 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.

Gabungan 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 wadah hash tergantung pada 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 pemeriksaan dipindai atau dihitung satu baris pada satu waktu, dan untuk setiap baris pemeriksaan, nilai kunci hash dihitung, wadah hash yang sesuai dipindai, dan kecocokan diproduksi.

Grace hash join

Jika input build tidak sesuai dalam memori, gabungan hash akan dilanjutkan dalam beberapa langkah. Ini dikenal sebagai gabungan hash rahmat. Setiap langkah memiliki fase build dan fase pemeriksaan. Awalnya, seluruh input build dan probe dikonsumsi dan dipartisi (menggunakan fungsi hash pada kunci hash) ke dalam beberapa file. 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 dikurangi menjadi beberapa, tetapi lebih kecil, instans tugas yang sama. Gabungan hash kemudian diterapkan ke setiap pasangan file yang dipartisi.

Gabungan hash rekursif

Jika input build sangat besar sehingga input untuk penggabungan eksternal standar akan memerlukan beberapa tingkat penggabungan, beberapa langkah partisi dan beberapa tingkat partisi diperlukan. Jika hanya beberapa partisi yang besar, langkah-langkah partisi tambahan hanya digunakan untuk partisi tertentu. Untuk membuat semua langkah partisi secepat mungkin, operasi I/O besar dan asinkron digunakan sehingga satu utas dapat membuat beberapa drive disk sibuk.

Catatan

Jika input build hanya sedikit lebih besar dari memori yang tersedia, elemen gabungan hash dalam memori dan gabungan hash grace digabungkan dalam satu langkah, menghasilkan gabungan hash hibrid.

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 mengantisipasi salah mana dari dua input yang lebih kecil dan, oleh karena itu, seharusnya 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. Pembalikan peran terjadi di dalam gabungan hash setelah setidaknya satu tumpahan ke disk.

Catatan

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.

Bailout hash

Istilah bailout hash terkadang digunakan untuk menggambarkan gabungan hash grace atau gabungan hash rekursif.

Catatan

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

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

Gabungan adaptif

Gabungan Adaptif mode Batch memungkinkan pilihan metode gabungan Hash Join atau Nested Loops untuk ditangguhkan hingga setelah input pertama dipindai. Operator Gabungan Adaptif menentukan ambang batas yang digunakan untuk memutuskan kapan harus beralih ke paket 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 input gabungan build cukup kecil sehingga gabungan Nested Loops akan lebih optimal daripada gabungan Hash, paket beralih ke algoritma Nested Loops.
  • Jika input gabungan build melebihi ambang batas jumlah baris tertentu, tidak ada pengalihan yang terjadi dan paket Anda dilanjutkan dengan gabungan Hash.

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 Statistik Kueri Langsung menampilkan paket berikut:

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

Dalam paket, perhatikan hal berikut:

  1. Pemindaian indeks penyimpan kolom yang digunakan untuk menyediakan baris untuk fase build gabungan Hash.
  2. Operator Gabungan Adaptif 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 dengan >= 78 baris akan menggunakan gabungan Hash. Jika kurang dari ambang batas, gabungan 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 Pencarian Indeks Berkluster untuk digunakan oleh gabungan Nested Loops memiliki ambang batas yang tidak terlampaui. Kami melihat baris "0 dari 336" ditampilkan (cabang tidak digunakan).

Sekarang kontras paket dengan kueri yang sama, tetapi ketika Quantity nilai hanya memiliki satu baris dalam 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 paket berikut:

Cuplikan layar rencana eksekusi, memperlihatkan gabungan Adaptif akhir memperlihatkan satu baris.

Dalam paket, 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.

Keterangan Gabungan Adaptif

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 overhead untuk fase build sebagai operasi stop-and-go versus gabungan streaming Nested Loops yang setara. Dengan biaya tambahan tersebut muncul fleksibilitas untuk skenario di mana jumlah baris berfluktuasi dalam input build.

Mode batch Pekerjaan gabungan Adaptif untuk eksekusi awal pernyataan, dan setelah dikompilasi, eksekusi berturut-turut akan tetap adaptif berdasarkan ambang batas Gabungan Adaptif yang dikompilasi dan baris runtime yang mengalir melalui fase build input luar.

Jika Gabungan Adaptif beralih ke operasi Perulangan Berlapis, itu menggunakan baris yang sudah dibaca oleh build Hash Join. Operator tidak membaca ulang baris referensi luar lagi.

Melacak aktivitas gabungan Adaptif

Operator Adaptive Join memiliki atribut operator paket berikut:

Atribut rencana Deskripsi
AdaptiveThresholdRows Memperlihatkan penggunaan ambang untuk beralih dari gabungan hash ke gabungan perulangan berlapis.
EstimatedJoinType Seperti apa jenis gabungannya.
ActualJoinType Dalam rencana aktual, menunjukkan algoritma gabungan apa yang akhirnya dipilih berdasarkan ambang batas.

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

Tip

Penyimpanan Kueri mengambil dan dapat memaksa paket Gabungan Adaptif 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).
  • Gabungan memenuhi syarat untuk dijalankan baik oleh gabungan Nested Loops terindeks atau algoritma fisik gabungan Hash.
  • 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 persimpangan ini, ambang ditentukan bahwa pada gilirannya menentukan algoritma aktual yang digunakan untuk operasi gabungan.

Bagan garis memperlihatkan ambang batas Gabungan Adaptif yang membandingkan gabungan hash dengan gabungan perulangan berlapis. Gabungan perulangan berlapis memiliki biaya yang lebih rendah pada jumlah baris rendah tetapi rowcount yang lebih tinggi pada baris yang lebih tinggi.

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. Contoh:

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'));

Catatan

Petunjuk kueri USE HINT lebih diutamakan daripada konfigurasi cakupan 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 miliki NULL di kolom yang akan berpartisipasi dalam gabungan:

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, pernyataan berikut SELECT melakukan gabungan luar kiri pada dua tabel ini:

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

Berikut set hasilnya.

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

(3 row(s) affected)

Hasilnya tidak memudahkan untuk membedakan NULL dalam data dari NULL yang mewakili kegagalan untuk bergabung. Ketika nilai NULL ada dalam data yang digabungkan, biasanya lebih baik untuk menghilangkannya dari hasil dengan menggunakan gabungan reguler.

Konten terkait