[Komunikasi] Ringkasan SQL Server dari Tabel yang Dipartisi

Dokumentasi resmi

Langkah-langkah tingkat tinggi untuk membuat tabel partisi

1. Buat grup file (mirip dengan oracle tablespace), tentu saja Anda tidak dapat membuatnya, Anda dapat menempatkan semua partisi dalam satu grup file

2. Tetapkan fungsi partisi dan distribusikan data sesuai dengan rentang apa

3. Buat skema partisi, kaitkan fungsi partisi, dan juga kaitkan grup file, fungsi partisi membagi data menjadi beberapa rentang, Anda perlu mengaitkan beberapa grup file, tentu saja, Anda juga dapat memasukkan rentang partisi ini ke dalam grup file yang sama

4. Buat tabel dan kaitkan skema zonasi

Bug yang ditemui

Saat Anda langsung mengklik kanan tabel untuk mengekspor struktur tabel, Anda tidak dapat mengekspor informasi partisi, dan Anda hanya dapat mengekspor informasi partisi tabel dengan mengklik kanan skrip database-task-generate

Beberapa kesimpulan dari tabel partisi:

1. Bidang yang dipartisi tidak perlu diindeks

2. Bidang yang dipartisi dapat dibuat sebagai indeks berkluster atau indeks tanpa kluster

3. Bidang yang dipartisi, apakah itu indeks berkluster atau indeks tanpa kluster, dibangun kembali ke dalam indeks berkluster tanpa skema partisi terkait, dan tabel yang dipartisi menjadi tabel yang tidak dipartisi

4. Konversi tabel biasa ke tabel yang dipartisi, cukup buat indeks berkluster dalam tabel dan gunakan skema partisi pada indeks berkluster. Misalnya, ketika bidang 1 dari tabel yang tidak dipartisi membuat indeks berkluster dan dikaitkan dengan skema partisi, tabel dikonversi menjadi tabel yang dipartisi, dan bidang yang dipartisi adalah bidang 1.

5. Konversi tabel yang dipartisi ke tabel normal, jika ada bidang partisi dengan indeks, bangun kembali bidang yang dipartisi sebagai indeks berkluster dan tidak dikaitkan dengan skema partisi, jika bidang partisi tidak memiliki indeks, bidang partisi dapat membuat indeks berkluster baru dan tidak dikaitkan dengan skema partisi

6. Mengubah tabel normal ke tabel partisi atau mengubah tabel partisi ke tabel biasa hanya dapat dicapai dengan menggunakan indeks berkluster, karena dengan indeks berkluster adalah tabel organisasi indeks, dan redistribusi tabel direalisasikan melalui rekonstruksi indeks berkluster. Jika tabel biasa menjadi tabel yang dipartisi, rekonstruksi bidang partisi menjadi indeks berkluster dan kaitkan dengan skema partisi, dan jika tabel partisi menjadi tabel normal, rekonstruksi bidang partisi menjadi indeks berkluster tanpa mengaitkan skema partisi.

7. Untuk membuat tabel partisi, batasan keunikan harus menyertakan kolom partisi

8. Saat membuat skema partisi, perlu untuk memastikan bahwa jumlah grup file cocok dengan segmen rentang partisi dari fungsi partisi, dan tidak masalah jika nama grup file diduplikasi.

9. Fungsi partisi dan skema partisi ada dalam database, tidak menghadap seluruh instance

10. Tabel partisi terlalu besar untuk memakan banyak ruang disk, dan ukurannya masih tidak berubah setelah menghapus beberapa bidang

Langkah-langkah untuk membuat tabel partisi

1.1. Contoh pembuatan grup file

mengubah database test1 menambahkan filegroup part1;

mengubah database test1 menambahkan filegroup part1000;

mengubah database test1 menambahkan filegroup part2000;

mengubah database test1 menambahkan filegroup part3000;

mengubah database test1 menambahkan filegroup part4000;

1.2. Buat contoh file dan kaitkan grup file

ALTER DATABASE test1 ADD FILE(NAME = test1part1,FILENAME = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part1;

UBAH DATABASE TEST1 TAMBAHKAN FILE(NAME = test1part1000,FILENAME = 'G:\test1part1000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) KE FILEGROUP part1000;

ALTER DATABASE test1 ADD FILE(NAME = test1part2000,FILENAME = 'G:\test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part2000;

UBAH DATABASE TEST1 TAMBAHKAN FILE(NAME = test1part3000,FILENAME = 'G:\test1part3000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) KE FILEGROUP part3000;

UBAH DATABASE TEST1 TAMBAHKAN FILE(NAME = test1part4000,FILENAME = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) KE FILEGROUP part4000;

2. Contoh membangun fungsi partisi, fungsi partisi disebut partfun1

BUAT FUNGSI PARTISI partfun1 (int)

SEBAGAI RENTANG YANG TERSISA UNTUK NILAI ('1000','2000','3000','4000')

--VALUES ('1000', '2000', '3000', '4000') menunjukkan bahwa tabel akan dibagi menjadi 5 zona, yang dibagi sesuai dengan ukuran nilai di bidang tabel, lima zona adalah yang terkecil - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-terbesar

3. Contoh membuat skema, mengaitkan fungsi partisi partfun1, dan mengaitkan grup file

BUAT SKEMA PARTISI partschema1

SEBAGAI PARTISI partfun1

TO (bagian1,bagian1000,bagian2000,bagian3000,bagian4000);

--Dibangun di atas beberapa grup file part1, part1000, part2000, part3000, part4000

BUAT SKEMA PARTISI partschema2

SEBAGAI PARTISI partfun1

KE (BAGIAN1,[PRIMER],[PRIMER],[PRIMER],[PRIMER]);

--Dibangun di atas part1, grup file [PRIMARY], tidak masalah untuk mengganti part1 dengan [PRIMARY], sehingga mirip dengan membangun di grup file [PRIMARY]

BUAT SKEMA PARTISI partschema3

SEBAGAI PARTISI partfun1

SEMUA KE (bagian 1);

--semua dibangun di atas grup file part1

BUAT SKEMA PARTISI partschema4

SEBAGAI PARTISI partfun1

SEMUA KE ([PRIMER]);

--all dibangun di atas grup file [PRIMARY]

4. Contoh membuat tabel partisi

BUAT TABEL parttable1(

[ID] [int] BUKAN NULL,

[IDText] [nvarchar] (maks) NOL

[Tanggal] [tanggalwaktu] NULL)

ON [partschema1](ID);

masukkan ke dalam nilai parttable1 (1,'1',getdate()-4);

masukkan ke dalam nilai parttable1 (1001,'1001',getdate()-3);

masukkan ke dalam nilai parttable1 (2001,'2001',getdate()-2);

masukkan ke dalam nilai parttable1 (3001,'3001',getdate()-1);

masukkan ke dalam nilai parttable1 (4001,'4001',getdate());

5. Verifikasi data tabel partisi

PILIH * DARI parttable1;

--Mengembalikan semua baris tabel partisi

PILIH $PARTITION yang berbeda. [bagiankesenangan1] (4) DARI PARTTABLE1;

--Mengembalikan partisi baris dengan nilai bidang ID 4 milik

PILIH * DARI parttable1 di mana $PARTITION. [bagiankesenangan1] (ID)=2

--Mengembalikan semua baris di partisi ke-2, ID adalah ID bidang partisi

Catatan: Anda tidak dapat menggunakan SELECT * FROM parttable1 di mana $PARTITION. [bagiankesenangan1] (ID)=2 memiliki hasil, itu berarti bahwa itu adalah tabel yang dipartisi, dan tes terakhir 7 dari makalah ini adalah tabel yang tidak dipartisi, tetapi SELECT * FROM parttable1 di mana $PARTITION. [bagiankesenangan1] (ID)=2 masih memiliki hasil

Partisi baru

1. Tentukan grup file yang dapat digunakan untuk skema partisi (tambahkan grup file untuk skema partisi).

2. Ubah fungsi partisi (tambahkan rentang data fungsi partisi)

UBAH SKEMA PARTISI partschema1 SELANJUTNYA DIGUNAKAN [UTAMA]

UBAH FUNGSI PARTISI partfun1() SPLIT RANGE ('4500')

Pilih p.partition_number,p.rows dari sys.indexes i menggabungkan sys.partitions p pada urutan p.object_id = i.object_id dan i.object_id = urutan object_id('parttable2') dengan 1

--Pernyataan pertama, jika skema partisi menggunakan ALL TO ([PRIMARY]), pernyataan ini tidak perlu dieksekusi

--Partisi baru ditambahkan ke pernyataan kedua, rentangnya adalah 4000-4500

--Pernyataan ketiga memverifikasi apakah partisi baru ada dan apakah ada baris

Hapus\Gabungkan partisi

UBAH FUNGSI PARTISI partfun1() MERGE RANGE ('2000')

Partisi 1000-2000 dihapus dan digabungkan menjadi 1000-3000

--Tidak dapat mengeksekusi ALTER TABLE TABLENAME DROP PARTITIONNAME PARTITION NAME seperti oracle;

Hapus tabel partisi dan grup file yang sesuai

Urutan penghapusan adalah: hapus tabel partisi, hapus skema partisi, hapus fungsi partisi, dan akhirnya hapus grup file, dan file yang sesuai dihapus setelah grup file dihapus

Contoh mengonversi tabel yang dipartisi ke tabel normal dan tabel normal ke tabel yang dipartisi

DROP TABLE parttable1;

BUAT TABEL parttable1(

[id] [int] IDENTITAS(1,1) BUKAN NULL,

[Nama] [varchar] (16) TIDAK NOL,

[Id2] [int] BUKAN NOL

) PADA partschema1(Id2);

masukkan ke dalam nilai parttable1 ('1',1);

masukkan ke dalam nilai parttable1 ('1001',1001);

masukkan ke dalam nilai parttable1 ('2001',2001);

masukkan ke dalam nilai parttable1 ('3001',3001);

masukkan ke dalam nilai parttable1 ('4001',4001);

1. Batasan unik yang dibuat pada tabel partisi harus berisi kolom partisi.

UBAH TABEL PARTTABLE1 MENAMBAHKAN BATASAN PK_prattable1_id KUNCI UTAMA BERKLUSTER ([ID] ASC)

Kolom Kesalahan 'Id2' adalah kolom partisi dari indeks 'PK_prattable1_id'. Kolom partisi untuk indeks unik harus berupa subset dari kunci indeks.

2. Buat indeks berkluster baru di kolom partisi id2, dan parttable1 masih merupakan tabel yang dipartisi

membuat CI_prattable1_id2 indeks berkluster di parttable1(id2);

3. Kolom partisi id2 membuat indeks noncluster, dan parttable1 masih merupakan tabel yang dipartisi

penurunan indeks CI_prattable1_id2 pada parttable1;

membuat NCI_prattable1_id2 indeks non-clustered pada parttable1(id2);

4. Kolom id kolom yang tidak dipartisi membuat indeks berkluster, parttable1 atau tabel yang dipartisi, yang menunjukkan bahwa kolom yang tidak dipartisi dapat berupa kolom indeks kluster

membuat CI_prattable1_id indeks berkluster pada parttable1(id);

5. Kolom partisi ID2 dibangun kembali menjadi indeks nonclustered dan tidak menggunakan skema partisi, parttable1 masih merupakan tabel partisi

membuat NCI_prattable1_id2 indeks nonclustered pada parttable1(id2) DENGAN (DROP_EXISTING = ON) PADA [PRIMARY];

6. Kolom partisi id2 direkonstruksi menjadi indeks berkluster tanpa kondisi ON, dan parttable1 masih merupakan tabel partisi

penurunan indeks CI_prattable1_id pada parttable1;

penurunan indeks NCI_prattable1_id2 pada parttable1;

membuat CI_prattable1_id2 indeks berkluster di parttable1(id2);

membuat CI_prattable1_id2 indeks berkluster pada parttable1(id2) DENGAN (DROP_EXISTING = ON);

7. Kolom id2 yang dipartisi dibangun kembali menjadi indeks berkluster dengan kondisi ON tetapi tanpa menggunakan skema partisi, dan parttable1 menjadi tabel yang tidak dipartisi

membuat CI_prattable1_id2 indeks berkluster pada parttable1(id2) DENGAN (DROP_EXISTING = ON) PADA [PRIMARY];

8. Kolom partisi id2 dibangun kembali ke dalam indeks berkluster dan menggunakan skema partisi, parttable1 menjadi tabel yang dipartisi

membuat CI_prattable1_id2 indeks berkluster pada parttable1(id2) WITH (DROP_EXISTING = ON) pada partschema1(Id2);

9. Setelah menghapus indeks berkluster di 8 di atas, parttable1 masih merupakan tabel yang dipartisi

penurunan indeks CI_prattable1_id2 pada parttable1;

10. Kolom partisi ID2 dibuat sebagai indeks berkluster dan tidak menggunakan skema partisi, dan parttable1 menjadi tabel yang tidak dipartisi

membuat CI_prattable1_id2 indeks berkluster pada parttable1(id2) PADA [PRIMARY];

11. Setelah menghapus indeks berkluster dari 10 di atas, parttable1 masih merupakan tabel yang tidak dipartisi

penurunan indeks CI_prattable1_id2 pada parttable1;

12. Kolom partisi id2 baru dibuat sebagai indeks non-cluster, meskipun skema partisi digunakan, itu masih merupakan tabel yang tidak dipartisi

membuat NCI_prattable1_id2 indeks non-kluster pada parttable1(id2) pada partschema1(Id2);

Jika tabel yang dipartisi dikonversi ke tabel normal, batasan kunci primer dihapus, dan indeks kluster dibangun kembali atau dibangun kembali menjadi kunci primer untuk bidang kunci primer asli, tetapi tidak terkait dengan skema partisi

UBAH TABEL Table_name DROP BATASAN PK_NAME DENGAN (>

BUAT PK_NAME INDEKS BERKLUSTER PADA Table_name(KOLOM) DENGAN (PADA [PRIMARY];

atau

UBAH TABEL Table_name TAMBAHKAN BATASAN PK_NAME KUNCI UTAMA CLUSTERED(kolom) DENGAN (ON [PRIMARY];

Jika tabel normal dikonversi ke tabel yang dipartisi, jika kunci primer asli akan dipertahankan, batasan kunci primer dihapus, dan kemudian kunci utama dibuat tanpa mengaturnya sebagai indeks agregat, dan kemudian indeks agregat baru dibuat, dan skema partisi digunakan dalam indeks agregat

UBAH TABEL Table_name DROP BATASAN PK_NAME DENGAN (>

UBAH TABEL Table_name TAMBAHKAN BATASAN PK_NAME KUNCI PRIMER NONCLUSTERED(kolom) DENGAN (ON [PRIMARY];

--Buat kunci primer, tetapi tidak diatur sebagai indeks berkluster

BUAT index_name INDEKS BERKLUSTER PADA Table_name(kolom) PADA Skema Partisi (Bidang Partisi)

--Buat indeks berkluster baru di mana skema partisi digunakan

Mengkueri jumlah total baris dan ukuran tabel yang dipartisi, misalnya, tabelnya adalah CRM. Log Email

eksekutif sp_spaceused 'crm. Catatan Email';

Mengkueri informasi tabel yang dipartisi, berapa banyak baris yang dimiliki setiap partisi, misalnya, tabelnya adalah CRM. Log Email

Pilih Konversi(VarChar(50), ps.name

) sebagai partition_scheme,

p.partition_number,

konversi(varchar(10), ds2.name

) sebagai grup file,

convert(varchar(19), isnull(v.value, ''), 120) sebagai range_boundary,

str(p.rows, 9) sebagai baris

dari sys.indexes i

Gabungkan sys.partition_schemes ps di i.data_space_id = ps.data_space_id

Bergabunglah sys.destination_data_spaces DDS

pada ps.data_space_id = dds.partition_scheme_id

bergabung sys.data_spaces DS2 di dds.data_space_id = ds2.data_space_id

Gabungkan sys.partitions p pada dds.destination_id = p.partition_number

dan p.object_id = i.object_id dan p.index_id = i.index_id

bergabung sys.partition_functions PF pada ps.function_id = pf.function_id

KIRI BERGABUNG sys. Partition_Range_values v pada pf.function_id = v.function_id

dan v.boundary_id = p.partition_number - pf.boundary_value_on_right

DI MANA i.object_id = object_id('crm. Log Email')

dan i.index_id di (0, 1)

Pesan berdasarkan p.partition_number

Fungsi partisi kueri

Pilih * dari sys.partition_functions

Melihat arsitektur partisi

Pilih * dari sys.partition_schemes