Tabel partisi di kumpulan SQL khusus

Artikel
06/01/2023

Rekomendasi dan contoh untuk menggunakan partisi tabel di kumpulan SQL khusus.

Apa itu partisi tabel?

Partisi tabel memungkinkan Anda membagi data menjadi grup data yang lebih kecil. Dalam kebanyakan kasus, partisi tabel dibuat pada kolom tanggal. Partisi didukung di semua jenis tabel kumpulan SQL khusus; termasuk penyimpan kolom berkluster, indeks berkluster, dan tumpukan. Partisi juga didukung pada semua jenis distribusi, termasuk hash atau round robin yang didistribusikan.

Partisi dapat menguntungkan pemeliharaan data dan performa kueri. Apakah hal ini menguntungkan keduanya atau hanya satu tergantung pada bagaimana data dimuat dan apakah kolom yang sama dapat digunakan untuk kedua tujuan itu, karena partisi hanya dapat dilakukan pada satu kolom.

Keuntungan untuk beban

Keuntungan utama partisi dalam kumpulan SQL khusus adalah untuk meningkatkan efisiensi dan performa pemuatan data menggunakan penghapusan, pengalihan, dan penggabungan partisi. Dalam kebanyakan kasus, data dipartisi pada kolom tanggal yang terkait erat dengan urutan data dimuat ke dalam kumpulan SQL. Salah satu keuntungan terbesar menggunakan partisi untuk mempertahankan data adalah dapat menghindarkan dari pencatatan transaksi. Meskipun menyisipkan, memperbarui, atau menghapus data saja dapat menjadi pendekatan yang paling mudah, dengan sedikit pemikiran dan upaya, menggunakan partisi selama proses pemuatan Anda dapat meningkatkan performa secara signifikan.

Pengalihan partisi dapat digunakan untuk menghapus atau mengganti bagian tabel dengan cepat. Misalnya, tabel fakta penjualan mungkin hanya berisi data selama 36 bulan terakhir. Pada akhir setiap bulan, bulan terlama data penjualan dihapus dari tabel. Data ini dapat dihapus menggunakan pernyataan penghapusan untuk menghapus data untuk bulan terlama.

Namun, menghapus banyak data baris demi baris dengan pernyataan penghapusan dapat memakan terlalu banyak waktu dan menciptakan risiko transaksi besar yang membutuhkan waktu lama untuk mundur jika ada yang salah. Pendekatan yang lebih optimal adalah menghilangkan partisi data terlama. Meskipun menghapus setiap baris bisa memakan waktu berjam-jam, menghapus seluruh partisi bisa memakan waktu beberapa detik.

Keuntungan untuk kueri

Partisi juga dapat digunakan untuk meningkatkan performa kueri. Kueri yang menerapkan filter ke data yang dipartisi dapat membatasi pemindaian hanya ke partisi yang memenuhi syarat. Metode pemfilteran ini dapat menghindari pemindaian tabel penuh dan hanya memindai subset data yang lebih kecil. Dengan diperkenalkannya indeks penyimpanan kolom berkluster, performa eliminasi predikat kurang bermanfaat, tetapi dalam beberapa kasus dapat memberikan keuntungan untuk kueri.

Misalnya, jika tabel fakta penjualan dipartisi menjadi 36 bulan menggunakan bidang tanggal penjualan, maka kueri yang memfilter pada tanggal penjualan dapat melewati pencarian di partisi yang tidak cocok dengan filter.

Ukuran partisi

Sementara partisi dapat digunakan untuk meningkatkan performa beberapa skenario, membuat tabel dengan terlalu banyak partisi dapat memperburuk performa dalam beberapa situasi. Kekhawatiran ini terutama berlaku untuk tabel penyimpanan kolom berkluster.

Agar partisi bermanfaat, penting untuk memahami kapan menggunakan partisi dan jumlah partisi yang perlu dibuat. Tidak ada aturan cepat tentang berapa jumlah partisi yang terlalu banyak, hal ini tergantung pada data Anda dan berapa banyak partisi yang Anda muat secara bersamaan. Skema partisi yang sukses biasanya memiliki puluhan hingga ratusan partisi, bukan ribuan.

Saat membuat partisi pada tabel penyimpanan kolom berkluster, penting untuk mempertimbangkan berapa banyak baris yang termasuk dalam setiap partisi. Untuk kompresi optimal dan performa tabel penyimpanan kolom berkluster, diperlukan minimal 1 juta baris per distribusi dan partisi. Sebelum partisi dibuat, kumpulan SQL khusus sudah membagi setiap tabel menjadi 60 distribusi.

Partisi apa pun yang ditambahkan ke tabel adalah tambahan untuk distribusi yang dibuat di belakang layar. Dengan menggunakan contoh ini, jika tabel fakta penjualan berisi 36 partisi bulanan, dan mengingat bahwa kolam SQL khusus memiliki 60 distribusi, maka tabel fakta penjualan harus berisi 60 juta baris per bulan, atau 2,1 miliar baris ketika semua bulan dihuni. Jika tabel berisi kurang dari jumlah baris minimum yang disarankan per partisi, pertimbangkan untuk menggunakan lebih sedikit partisi guna meningkatkan jumlah baris per partisi.

Untuk informasi selengkapnya, lihat artikel Pengindeksan, yang menyertakan kueri yang dapat menilai kualitas indeks penyimpanan kolom berkluster.

Perbedaan sintaks dari SQL Server

Kumpulan SQL khusus memperkenalkan cara untuk mendefinisikan partisi yang lebih sederhana dari SQL Server. Fungsi dan skema partisi tidak digunakan dalam kumpulan SQL khusus seperti yang ada di SQL Server. Sebaliknya, yang perlu Anda lakukan adalah mengidentifikasi kolom yang dipartisi dan titik batas.

Meskipun sintaks partisi mungkin sedikit berbeda dari SQL Server, konsep dasarnya sama. SQL Server dan kumpulan SQL khusus mendukung satu kolom partisi per tabel, yang dapat berupa partisi rentang. Untuk mempelajari selengkapnya tentang partisi, lihat Tabel dan Indeks Yang Dipartisi.

Contoh berikut menggunakan pernyataan CREATE TABLE untuk mempartisi tabel FactInternetSales pada kolom OrderDateKey:

CREATE TABLE [dbo].[FactInternetSales]
(
    [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductKey])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                    (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )
                )
);

Migrasi partisi dari SQL Server

Untuk memigrasikan definisi partisi SQL Server ke kumpulan SQL khusus, cukup:

Hilangkan skema partisi SQL Server.
Tambahkan definisi fungsi partisi ke CREATE TABLE Anda.

Jika Anda memigrasi tabel yang dipartisi dari contoh SQL Server, SQL berikut dapat membantu Anda mengetahui jumlah baris di setiap partisi. Perlu diingat bahwa jika granularitas partisi yang sama digunakan dalam kumpulan SQL khusus, jumlah baris per partisi berkurang dengan faktor 60.

-- Partition information for a SQL Server Database
SELECT      s.[name]                        AS      [schema_name]
,           t.[name]                        AS      [table_name]
,           i.[name]                        AS      [index_name]
,           p.[partition_number]            AS      [partition_number]
,           SUM(a.[used_pages]*8.0)         AS      [partition_size_kb]
,           SUM(a.[used_pages]*8.0)/1024    AS      [partition_size_mb]
,           SUM(a.[used_pages]*8.0)/1048576 AS      [partition_size_gb]
,           p.[rows]                        AS      [partition_row_count]
,           rv.[value]                      AS      [partition_boundary_value]
,           p.[data_compression_desc]       AS      [partition_compression_desc]
FROM        sys.schemas s
JOIN        sys.tables t                    ON      t.[schema_id]         = s.[schema_id]
JOIN        sys.partitions p                ON      p.[object_id]         = t.[object_id]
JOIN        sys.allocation_units a          ON      a.[container_id]      = p.[partition_id]
JOIN        sys.indexes i                   ON      i.[object_id]         = p.[object_id]
                                            AND     i.[index_id]          = p.[index_id]
JOIN        sys.data_spaces ds              ON      ds.[data_space_id]    = i.[data_space_id]
LEFT JOIN   sys.partition_schemes ps        ON      ps.[data_space_id]    = ds.[data_space_id]
LEFT JOIN   sys.partition_functions pf      ON      pf.[function_id]      = ps.[function_id]
LEFT JOIN   sys.partition_range_values rv   ON      rv.[function_id]      = pf.[function_id]
                                            AND     rv.[boundary_id]      = p.[partition_number]
WHERE       p.[index_id] <=1
GROUP BY    s.[name]
,           t.[name]
,           i.[name]
,           p.[partition_number]
,           p.[rows]
,           rv.[value]
,           p.[data_compression_desc];

Peralihan partisi

Kumpulan SQL khusus mendukung pemisahan, penggabungan, dan peralihan partisi. Masing-masing fungsi ini dijalankan menggunakan pernyataan ALTER TABLE.

Untuk beralih partisi di antara dua tabel, Anda harus memastikan bahwa partisi sejajar pada batas masing-masing dan definisi tabel cocok. Karena batasan pemeriksaan tidak tersedia untuk memberlakukan rentang nilai dalam tabel, tabel sumber harus berisi batas partisi yang sama dengan tabel target. Jika batas partisi tidak sama, maka sakelar partisi akan gagal karena metadata partisi tidak akan disinkronkan.

Pembagian partisi membutuhkan partisi masing-masing (belum tentu seluruh tabel) kosong jika tabel memiliki indeks columnstore clustered (CCI). Partisi lain dalam tabel yang sama dapat berisi data. Partisi yang berisi data tidak dapat dibagi, hal ini akan mengakibatkan kesalahan: ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete. Sebagai solusi untuk membagi partisi yang berisi data, lihat Cara memisahkan partisi yang berisi data.

Cara membagi partisi yang berisi data

Metode paling efisien untuk membagi partisi yang sudah berisi data adalah dengan menggunakan pernyataan CTAS. Jika tabel yang dipartisi adalah penyimpanan kolom berkluster, maka partisi tabel harus kosong sebelum dapat dipisahkan.

Contoh berikut membuat tabel penyimpanan kolom yang dipartisi. Satu baris disisipkan ke dalam setiap partisi:

CREATE TABLE [dbo].[FactInternetSales]
(
        [ProductKey]            int          NOT NULL
    ,   [OrderDateKey]          int          NOT NULL
    ,   [CustomerKey]           int          NOT NULL
    ,   [PromotionKey]          int          NOT NULL
    ,   [SalesOrderNumber]      nvarchar(20) NOT NULL
    ,   [OrderQuantity]         smallint     NOT NULL
    ,   [UnitPrice]             money        NOT NULL
    ,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductKey])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                    (20000101
                    )
                )
);

INSERT INTO dbo.FactInternetSales
VALUES (1,19990101,1,1,1,1,1,1);

INSERT INTO dbo.FactInternetSales
VALUES (1,20000101,1,1,1,1,1,1);

Kueri berikut menemukan jumlah baris menggunakan tampilan katalog sys.partitions:

SELECT  QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
,       i.[name] as Index_name
,       p.partition_number as Partition_nmbr
,       p.[rows] as Row_count
,       p.[data_compression_desc] as Data_Compression_desc
FROM    sys.partitions p
JOIN    sys.tables     t    ON    p.[object_id]   = t.[object_id]
JOIN    sys.schemas    s    ON    t.[schema_id]   = s.[schema_id]
JOIN    sys.indexes    i    ON    p.[object_id]   = i.[object_Id]
                            AND   p.[index_Id]    = i.[index_Id]
WHERE t.[name] = 'FactInternetSales';

Perintah pemisahan berikut menerima pesan kesalahan:

ALTER TABLE FactInternetSales SPLIT RANGE (20010101);

Msg 35346, Level 15, State 1, Line 44
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

Namun, Anda bisa menggunakan CTAS untuk membuat tabel baru untuk menyimpan data.

CREATE TABLE dbo.FactInternetSales_20000101
    WITH    (   DISTRIBUTION = HASH(ProductKey)
            ,   CLUSTERED COLUMNSTORE INDEX              
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101
                                )
                            )
)
AS
SELECT *
FROM    FactInternetSales
WHERE   1=2;

Saat batas partisi diselaraskan, sakelar diizinkan. Hal ini akan menjadikan tabel sumber dengan partisi kosong yang kemudian dapat Anda pisahkan.

ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;

ALTER TABLE FactInternetSales SPLIT RANGE (20010101);

Yang tersisa hanyalah menyelaraskan data ke batas partisi baru menggunakan CTAS, lalu mengalihkan data kembali ke tabel utama.

CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales_20000101]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101;

ALTER TABLE dbo.FactInternetSales_20000101_20010101 SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2;

Setelah Anda menyelesaikan perpindahan data, ada baiknya anda me-refresh statistik di tabel target. Memperbarui statistik memastikan bahwa statistik secara akurat mencerminkan distribusi data baru di partisi masing-masing.

UPDATE STATISTICS [dbo].[FactInternetSales];

Akhirnya, dalam kasus sakelar partisi satu kali untuk memindahkan data, Anda dapat menghilangkan tabel yang dibuat untuk sakelar partisi, FactInternetSales_20000101_20010101 dan FactInternetSales_20000101. Atau, Anda mungkin ingin menyimpan tabel kosong untuk sakelar partisi otomatis reguler.

Muat data baru ke dalam partisi yang berisi data dalam satu langkah

Memuat data ke dalam partisi dengan pengalihan partisi adalah cara yang mudah untuk mengatur data baru dalam tabel yang tidak terlihat oleh pengguna. Hal mungkin akan sulit pada sistem yang sibuk untuk menangani ketidaksesuaian penguncian yang terkait dengan pengalihan partisi.

Untuk menghapus data yang ada dalam partisi, ALTER TABLE yang digunakan diperlukan untuk mengalihkan data. Kemudian ALTER TABLE yang lain diharuskan untuk beralih dalam data baru.

Di kumpulan SQL khusus, opsi TRUNCATE_TARGET didukung dalam perintah ALTER TABLE. Dengan TRUNCATE_TARGET, perintah ALTER TABLE menimpa data yang ada di partisi dengan data baru. Di bawah ini adalah contoh yang menggunakan CTAS untuk membuat tabel baru dengan data yang sudah ada, menyisipkan data baru, lalu mengalihkan semua data kembali ke tabel target, menimpa data yang sudah ada.

CREATE TABLE [dbo].[FactInternetSales_NewSales]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

INSERT INTO dbo.FactInternetSales_NewSales
VALUES (1,20000101,2,2,2,2,2,2);

ALTER TABLE dbo.FactInternetSales_NewSales SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2 WITH (TRUNCATE_TARGET = ON);

Kontrol sumber partisi tabel

Catatan

Jika alat kontrol sumber Anda tidak dikonfigurasi untuk mengabaikan skema partisi, mengubah skema tabel untuk memperbarui partisi dapat menyebabkan tabel dijatuhkan dan dibuat ulang sebagai bagian dari penyebaran, yang mungkin tidak layak. Solusi khusus untuk menerapkan perubahan seperti itu, seperti yang dijelaskan di bawah ini, mungkin diperlukan. Periksa apakah alat integrasi/penyebaran berkelanjutan (CI/CD) Anda memungkinkan untuk ini. Di SQL Server Data Tools (SSDT), cari Pengaturan Penerbitan Tingkat Lanjut "Abaikan skema partisi" untuk menghindari skrip yang dihasilkan yang menyebabkan tabel dijatuhkan dan dibuat ulang.

Contoh ini berguna saat memperbarui skema partisi tabel kosong. Untuk terus menyebarkan perubahan partisi pada tabel dengan data, ikuti langkah-langkah dalam Cara membagi partisi yang berisi data di samping penyebaran untuk memindahkan data sementara dari setiap partisi sebelum menerapkan SPLIT RANGE partisi. Hal ini diperlukan karena alat CI/CD tidak menyadari partisi mana yang memiliki data.

Untuk menghindarkan definisi tabel Anda dari berkarat di sistem kontrol sumber, Anda mungkin ingin mempertimbangkan pendekatan berikut:

Buat tabel sebagai tabel yang dipartisi tetapi tanpa nilai partisi

CREATE TABLE [dbo].[FactInternetSales]
(
    [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductKey])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES () )
);

SPLIT tabel sebagai bagian dari proses penyebaran:

 -- Create a table containing the partition boundaries

CREATE TABLE #partitions
WITH
(
    LOCATION = USER_DB
,   DISTRIBUTION = HASH(ptn_no)
)
AS
SELECT  ptn_no
,       ROW_NUMBER() OVER (ORDER BY (ptn_no)) as seq_no
FROM    (
    SELECT CAST(20000101 AS INT) ptn_no
    UNION ALL
    SELECT CAST(20010101 AS INT)
    UNION ALL
    SELECT CAST(20020101 AS INT)
    UNION ALL
    SELECT CAST(20030101 AS INT)
    UNION ALL
    SELECT CAST(20040101 AS INT)
) a;

 -- Iterate over the partition boundaries and split the table

DECLARE @c INT = (SELECT COUNT(*) FROM #partitions)
,       @i INT = 1                                 --iterator for while loop
,       @q NVARCHAR(4000)                          --query
,       @p NVARCHAR(20)     = N''                  --partition_number
,       @s NVARCHAR(128)    = N'dbo'               --schema
,       @t NVARCHAR(128)    = N'FactInternetSales' --table;

WHILE @i <= @c
BEGIN
    SET @p = (SELECT ptn_no FROM #partitions WHERE seq_no = @i);
    SET @q = (SELECT N'ALTER TABLE '+@s+N'.'+@t+N' SPLIT RANGE ('+@p+N');');

    -- PRINT @q;
    EXECUTE sp_executesql @q;
    SET @i+=1;
END

 -- Code clean-up

DROP TABLE #partitions;

Dengan pendekatan ini, kode dalam kontrol sumber tetap statis dan nilai batas partisi diizinkan untuk menjadi dinamis; berkembang dengan kolam SQL dari waktu ke waktu.

Langkah berikutnya

Untuk informasi selengkapnya tentang mengembangkan tabel, lihat artikel tentang Gambaran Umum Tabel.