[通信] パーティションテーブルのSQL Server概要

公式文書

パーティションテーブルを作成するための高レベルのステップ

1. ファイルグループを作成する(oracleのtablespaceに似ています)。もちろん作成はできず、すべてのパーティションを1つのファイルグループにまとめることができます

2. 分割関数を確立し、どの範囲に応じてデータを配布します

3. パーティションスキームを確立し、パーティション関数を関連付け、ファイルグループも関連付けます。パーティション関数はデータを複数の範囲に分割します。複数のファイルグループを関連付ける必要がありますが、もちろんこれらのパーティション範囲を同じファイルグループに組み込むことも可能です

4. 表を作成し、ゾーニングスキームを関連付ける

バグに遭遇しました

テーブル構造をエクスポートするためにテーブルを直接右クリックすると、パーティション情報はエクスポートできず、データベースタスク生成スクリプトを右クリックすることでのみテーブルのパーティション情報をエクスポートできます

分割表からのいくつかの結論:

1. 分割されたフィールドは必ずしもインデックス付けされる必要はない

2. 分割されたフィールドはクラスタインデックスまたはクラスタなしインデックスとして作成できます

3. 分割されたフィールドは、クラスタ化されたインデックスであれ非クラスタ化されたインデックスであれ、関連する分割スキームを伴わずクラスタ化されたインデックスに再構築され、分割されたテーブルは非分割されたテーブルになります

4. 通常のテーブルをパーティションテーブルに変換し、テーブル内にクラスタインデックスを作成し、クラスタインデックスのパーティション方式を適用します。 例えば、非分割テーブルのフィールド1がクラスタインデックスを作成し、パーティション方式に関連付けられている場合、そのテーブルはパーティション分割されたテーブルに変換され、パーティションフィールドはフィールド1となります。

5. パーティションフィールドにインデックスがある場合は、パーティションフィールドをクラスタインデックスとして再構築しパーティションスキームと関連付けません。パーティションフィールドにインデックスがない場合は、パーティションフィールドが新しいクラスタインデックスを作成でき、パーティションスキームとは関連付けません

6. 通常のテーブルをパーティションテーブルに変更したり、パーティションテーブルを通常のテーブルに変えたりすることは、クラスタインデックスを用いることでのみ実現できます。なぜなら、クラスタインデックスにはインデックス組織テーブルが存在し、テーブルの再分配はクラスタインデックスの再構成によって実現されるからです。 通常のテーブルがパーティションテーブルになった場合は、パーティションフィールドをクラスタインデックスに再構成し、パーティションスキームと関連付けます。パーティションテーブルが通常のテーブルになった場合は、パーティションスキームを関連付けずにクラスタインデックスに再構築します。

7. 分割テーブルを作成するには、一意性制約に分割列を含める必要があります

8. パーティションスキームを作成する際には、ファイルグループ数がパーティション関数のパーティション範囲セグメントと一致していることを確認し、ファイルグループ名が重複していても問題ありません。

9. 分割関数および分割方式はデータベース内にあり、インスタンス全体を対象としていません

10. パーティションテーブルは大きすぎて多くのディスク容量を消費できず、一部のフィールドを削除してもサイズは変わりません

パーティションテーブルを作成する手順

1.1. ファイルグループを確立する例

Alter database test1 Add FileGroup part1;

Alter database test1 Add FileGroup part1000;

Alter database test1 Add FileGroup part2000;

alter database test1 Add FileGroup part3000;

alter database test1 Add FileGroup part4000;

1.2. ファイルの例を設定し、ファイルグループを関連付ける

ALTER DATABASE test1 ファイル(NAME = test1part1, FILENAME = 'G:\test1part1.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) をファイルグループパート1に追加;

ALTER DATABASE test1 ファイル(NAME = test1part1000,FILENAME = 'G:\test1part1000.ndf',SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)をPART1000にファイルを追加します;

ALTER DATABASE test1 ファイル(NAME = test1part2000,FILENAME = 'G:\test1part2000.ndf',SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)をファイルグループPART2000に追加;

ALTER DATABASE test1 ファイル(NAME = TEST1PART3000,FILENAME = 'G:\test1part3000.ndf',SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)をPART3000にADD FILE3000;

ALTER DATABASE TEST1 ファイル(NAME = test1part4000,FILENAME = 'G:\test1part4000.ndf',SIZE = 5MB, MAXSIZE = 100MB,FILEGROWTH = 5MB)をPART4000にファイルを追加します;

2. 分割関数を確立する例として、分割関数はpartfun1と呼ばれます。

パーティション関数の作成 partfun1 (int)

値の左側の範囲(「1000'、'2000'、'3000'、'4000')

--VALUES ('1000', '2000', '3000', '4000')は、テーブルが5つのゾーンに分割されることを示し、テーブルフィールドの値のサイズに応じて分割されます。5つのゾーンは最も小さいです - 1000、1000-2000、2000-3000、3000-4000、4000-最大

3. スキームを確立する例として、パーティション関数partfun1を関連付け、ファイルグループを関連付けます

パーティションスキームの作成 partschema1

AS PARTITION PARTFUN1

TO(パート1、パート1000、パート2000、パート3000、パート4000);

--パート1、パート1000、パート2000、パート3000、パート4000の複数のファイルグループに基づいて構築されています

パーティションスキームの作成 partschema2

AS PARTITION PARTFUN1

TO(パート1,[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);

--part1、[PRIMARY]ファイルグループを基に構築されており、part1を[PRIMARY]に置き換えることも問題なく、[PRIMARY]ファイルグループ上で構築されるようにします

パーティションスキームの作成 partschema3

AS PARTITION PARTFUN1

オール・トゥ(パート1);

--すべてpart1ファイルグループ上で構築されています

パーティションスキームの作成 partschema4

AS PARTITION PARTFUN1

オールから([プライマリー]);

--すべて[PRIMARY]ファイルグループ上で構築されています

4. 分割テーブルの確立例

CREATE TABLE PARTTABLE1(

[ID] [int] 無効ではない、

[IDText] [ンヴァルチャル](マックス) ヌル

[日付] [デートタイム] NULL)

ON [partschema1](ID);

parttable1に値(1,'1',getdate()-4)を挿入します。

parttable1に値(1001,'1001',getdate()-3)を挿入します。

parttable1 に値 (2001, '2001', getdate()-2) を挿入);

parttable1に値(3001,'3001',getdate()-1)を挿入します。

parttable1に値(4001,'4001',getdate())を挿入します。

5. パーティションテーブルのデータ検証

SELECT: * PARTTABLE1;

--パーティションテーブルのすべての行を返します

特定の特定$PARTITIONを選びます。 [partfun1](4) パートテーブル1より;

--返す IDフィールド値4の行を分割する

$PARTITION の PARTTABLE1 から * を選択します。 [partfun1](ID)=2

--2番目のパーティションのすべての行を返します。IDはパーティションフィールドIDです

注意:SELECT * FROM parttable1 は$PARTITIONでは使えません。 [partfun1](ID)=2 は結果を意味し、それはパーティションテーブルであることを意味し、本論文の最後のテスト7はパーティションなしのテーブルですが、SELECT * FROM parttable1 は $PARTITION。 [partfun1](ID)=2 でも結果は存在します

新しい分割

1. パーティションスキームに使用できるファイルグループを指定する(パーティションスキーム用のファイルグループを追加する)。

2. パーティション関数を修正する(パーティション関数のデータ範囲を追加する)

パーティションスキームのPARTSCHEMA1を次に使用する[primary]

ALTER PARTITION FUNCTION PARTFUN1() 分割範囲('4500')

Sys.indexes i から p.partition_number,p.rows を inner join sys.partitions p で p.object_id = i.object_id と 、 i.object_id = object_id(「parttable2」)の順序で 1 ずつ

--最初の文では、分割スキームがALL TO([primary])を使う場合、この文は実行される必要がありません

--2つ目の文に新しい分割が追加され、範囲は4000-4500です

--3番目の文は新しい分割が存在するかどうか、また行が存在するかどうかを検証します

パーティションの削除/統合

ALTER PARTITION FUNCTION PARTFUN1() MERGE RANGE ('2000')

1000-2000のパーティションは削除され、1000-3000に統合されました

--oracleのようにALTER TABLE NAME TABLENAME DROP PARTITION PARTITIONNAMEを実行できません;

パーティションテーブルと対応するファイルグループを削除してください

削除の順序は次の通りです:パーティションテーブルを削除し、パーティションスキームを削除し、パーティション関数を削除し、最後にファイルグループを削除し、ファイルグループが削除された後に対応するファイルも削除されます

パーティションテーブルを通常のテーブルに、通常のテーブルからパーティションテーブルに変換する例

ドロップテーブルパートテーブル1;

CREATE TABLE PARTTABLE1(

[Id] [int] IDENTITY(1,1) NOT NULL

[名前] [ヴァルチャール](16) 無効ではない、

[Id2][int] ヌルではない

) ON partschema1(Id2);

parttable1に値('1',1)を挿入します。

parttable1の値('1001', 1001)に挿入します。

parttable1の値に挿入('2001', 2001);

parttable1の値('3001', 3001)に挿入します。

parttable1に値('4001', 4001)を挿入します。

1. パーティションテーブル上で作成された一意制約はパーティション列を含まなければなりません。

ALTER TABLE PARTTABLE1 ADD PRESENT CONSTRAINT PK_prattable1_id 主キークラスタ([ID] ASC)

エラー列「Id2」はインデックス「PK_prattable1_id」の分割列です。 一意インデックスの分割列はインデックスキーの部分集合でなければなりません。

2. パーティション列 id2 に新しいクラスタインデックスを作成し、parttable1 は依然としてパーティションテーブルのままです

parttable1(id2)でクラスタインデックスCI_prattable1_id2を作成する;

3. パーティション列id2はクラスタ化されていないインデックスを作成し、parttable1は依然としてパーティションテーブルです

パートテーブル1のインデックスCI_prattable1_id2を落とす;

parttable1(id2)で非クラスタインデックスNCI_prattable1_id2を作成する;

4. 非分割列ID列はクラスタインデックス、parttable1、または分割テーブルを作成し、非分割列がクラスタインデックス列になり得ることを示します

parttable1(id)でクラスタインデックスCI_prattable1_idを作成する;

5. パーティション列ID2は非クラスタ化インデックスに再構築され、分割方式は使われず、parttable1は依然としてパーティションテーブルです

PARTTABLE1(ID2)上で非クラスタインデックスNCI_prattable1_id2を作成し、[PRIMARY]に(DROP_EXISTING = ON)を付けます。

6. 分割列id2はON条件なしのクラスタインデックスに再構成され、parttable1は依然としてパーティションテーブルです

パートテーブル1のインデックスCI_prattable1_idをドロップ;

parttable1のインデックスNCI_prattable1_id2を落とす;

parttable1(id2)でクラスタインデックスCI_prattable1_id2を作成する;

parttable1(id2)上でクラスタインデックスCI_prattable1_id2を作成し、(DROP_EXISTING = ON)を使います。

7. パーティション化された列id2は、パーティショニング方式を使わずON条件付きクラスタインデックスに再構築され、parttable1は非パーティションテーブルとなります

PARTTABLE1(ID2)上でクラスタインデックスCI_prattable1_id2を作成し、[PRIMARY]に(DROP_EXISTING = ON)を付けます。

8. パーティション列id2をクラスタインデックスに再構築し、分割方式を用いることでparttable1はパーティションテーブルとなります

parttable1(id2)上でクラスタインデックスCI_prattable1_id2を作成し、partschema1(Id2)上で(DROP_EXISTING = ON)を使います。

9. 上記の8のクラスタインデックスを削除した後も、parttable1は依然としてパーティションテーブルのままです

パートテーブル1のインデックスCI_prattable1_id2を落とす;

10. パーティション列ID2はクラスタインデックスとして作成され、パーティショニング方式を使わず、parttable1は非パーティションテーブルとなります

[PRIMARY]上でPARTTABLE1(id2)でクラスタインデックスCI_prattable1_id2を作成する;

11. 上記の10のクラスタインデックスを削除しても、parttable1は依然として非分割テーブルのままです

パートテーブル1のインデックスCI_prattable1_id2を落とす;

12. パーティション列id2は新たに非クラスタ化インデックスとして作成されますが、分割方式は使用されますが、依然として非分割テーブルです

parttable1(id2)上で非クラスタ化インデックスNCI_prattable1_id2作成、partschema1(Id2)に。

分割されたテーブルが通常のテーブルに変換されると、主キー制約は削除され、クラスタインデックスは元の主キーのフィールドに対して再構築または主キーに再構築されますが、これは分割方式とは関係ありません

ALTER TABLE Table_name 制約を (> で DROP WITH PK_NAME

Table_name(列)にクラスタインデックスPK_NAMEを作成します。([PRIMARY];

又は

ALTER TABLE PK_NAME Table_name CLUSTER (COLUMN) WITH (ON [PRIMARY];

通常のテーブルをパーティションテーブルに変換し、元のプライマリキーを保持する場合、プライマリキーの制約は削除され、プライマリキーは集約インデックスとして設定せずに作成され、新しいアグリゲートインデックスが作成され、そのパーティショニング方式が集約インデックスで使用されます

ALTER TABLE Table_name 制約を (> で DROP WITH PK_NAME

ALTER TABLE Table_name 主キー PK_NAME NONCLUSTERED(COLUMN) で制約を追加する (ON [PRIMARY];

--プライマリキーを作成しつつ、クラスタインデックスとして設定しない

パーティションスキーム(パーティションフィールド)上のTable_name(列)でクラスタインデックスindex_nameを作成する

--分割スキームを用いる新しいクラスタインデックスを作成する

例えば、パーティションテーブルの行数とサイズをクエリします。例えば、そのテーブルはCRMです。 メールログ

CRM sp_spaceusedエグゼクティブ。 EmailLog』;

パーティションテーブルの情報、例えば各パーティションの行数をクエリします。例えば、テーブルはCRMです。 メールログ

Selectコンバート(varchar(50)、ps.name

) とpartition_scheme、

p.partition_number、

コンバート(ヴァルハル(10)、ds2.name

) ファイルグループとして、

convert(varchar(19), isnull(v.value, ''), 120)をrange_boundaryとして、

str(p.rows, 9) を行として

sys.indexes i より

sys.partition_schemes PSをi.data_space_id = ps.data_space_idに結合する

DDS sys.destination_data_spaces加入

ps.data_space_id = dds.partition_scheme_id

dds.data_space_id = ds2.data_space_id sys.data_spaces DS2に加入してください

Join sys.partitions p on dds.destination_id = p.partition_number

p.object_id = i.object_id、p.index_id = i.index_id

ps.function_id = pf.function_id で PF をsys.partition_functions 結合

左にシステムに加入。 Partition_Range_values v on pf.function_id = v.function_id

そしてv.boundary_id = p.partition_number - pf.boundary_value_on_right

ここでi.object_id = object_id('crm. メールログ)

(0, 1)ではi.index_id

p.partition_number順

クエリ分割関数

sys.partition_functionsから*

パーティションアーキテクチャの表示

選択 * sys.partition_schemes