連載
» 2004年11月30日 00時00分 公開

SQL Server 2000 チューニング全工程(3):速報! SQL Server 2005のデータパーティション (2/3)

[沖要和,NRIラーニングネットワーク]

データパーティションの作成サンプル

 パーティション分割されたテーブルでデータの移動や削除をどのように行うのかを確認するために、以降は、SQL Server 2005に付属するAdventureWorksサンプルデータベース で、受注の履歴データを格納するTransactionHistoryテーブルと履歴データのアーカイブ用のTransactionHistoryArchiveテーブルにパーティションを設定する手順を紹介します。TransactionHistoryテーブルとTransactionHistoryArchiveテーブルは、同じテーブル構造を持ちProductionスキーマに定義されています。

注) AdventureWorksサンプルデータベース

SQL Server 2005に付属するデータベースで、既定ではインストールされない。事前にインストールしておく場合は、SQL Server 2005のセットアップ時に、詳細設定から追加する。


図2 TransactionHistoryテーブルとTransactionHistoryArchiveテーブルのテーブル構造 図2 TransactionHistoryテーブルとTransactionHistoryArchiveテーブルのテーブル構造

1. TransactionHistoryテーブルを分割するためのパーティション関数を定義

 リスト4のTransactionHistoryPFパーティション関数では、月ごとのデータを格納するためdatetime型の列に格納された値に基づきTransactionHistoryテーブル、およびインデックスを12のパーティションに分割します。作成されたパーティション関数を確認する場合、「SQL Server Management Studio」のオブジェクトエクスプローラから、[Databases] - [AdventureWorks] - [Storage] - [Partition Functions] を展開し、表示されるオブジェクトを確認します。

USE AdventureWorks
GO
CREATE PARTITION FUNCTION TransactionHistoryPF (datetime)
AS RANGE RIGHT FOR VALUES (
   '10/01/2003', '11/01/2003', '12/01/2003',
   '1/01/2004', '2/01/2004', '3/01/2004', '4/01/2004', 
   '5/01/2004', '6/01/2004', '7/01/2004', '8/01/2004');
GO
リスト4 パーティション関数の定義

2. TransactionHistoryテーブル用のパーティションスキームを定義し、パーティション関数で指定されたパーティションを配置するファイルグループを指定

 リスト5のパーティションスキームは、TransactionHistoryPFパーティション関数で指定されたパーティションを同じPRIMARYファイルグループに配置することを指定しています。作成されたパーティションスキームを確認する場合、「SQL Server Management Studio」のオブジェクトエクスプローラから、[Databases] - [AdventureWorks] - [Storage] - [Partition Schemes] を展開し、表示されるオブジェクトを確認します。

CREATE PARTITION SCHEME TransactionHistoryPS
AS PARTITION TransactionHistoryPF 
TO ([PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY]);
GO
リスト5 パーティションスキームの定義

3. 定義されたパーティションスキームを使って、テーブルを再作成

 リスト6のスクリプトでは、TransactionHistoryテーブルでTransactionHistoryPSパーティションスキームを使用するためテーブルの再作成を行っています。

IF OBJECT_ID(
  '[Production].[FK_TransactionHistory_Product_ProductID]',
  'F') IS NOT NULL
    ALTER TABLE [Production].[TransactionHistory] 
    DROP CONSTRAINT 
      [FK_TransactionHistory_Product_ProductID];
GO
IF OBJECT_ID('[Production].[TransactionHistory]', 'U')
IS NOT NULL
    DROP TABLE [Production].[TransactionHistory];
GO
CREATE TABLE [Production].[TransactionHistory](
    [TransactionID] [int] IDENTITY (1, 1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [ReferenceOrderID] [int] NOT NULL,
    [ReferenceOrderLineNumber] [smallint] NOT NULL 
    CONSTRAINT
      [DF_TransactionHistory_ReferenceOrderLineNumber] 
      DEFAULT (0),
    [TransactionDate] [datetime] NOT NULL 
    CONSTRAINT [DF_TransactionHistory_TransactionDate] 
      DEFAULT (GETDATE()),
    [TransactionType] [nchar](1) NOT NULL, 
    [Quantity] [int] NOT NULL,
    [ActualCost] [money] NOT NULL, 
    [ModifiedDate] [datetime] NOT NULL 
    CONSTRAINT [DF_TransactionHistory_ModifiedDate] 
      DEFAULT (GETDATE()),
    CONSTRAINT [CK_TransactionHistory_TransactionType] 
      CHECK (
        [TransactionType] IN ('W', 'S', 'P', 'w', 's', 'p'))
) ON TransactionHistoryPS (TransactionDate);
GO
ALTER TABLE [Production].[TransactionHistory] ADD 
    CONSTRAINT [FK_TransactionHistory_Product_ProductID]
      FOREIGN KEY 
    (
        [ProductID]
    ) REFERENCES [Production].[Product](
        [ProductID]
    );
GO
リスト6 パーティションスキームを使ったテーブルの再作成

4. TransactionHistoryArchiveテーブルを分割するためのパーティション関数を定義

 リスト7のTransactionHistoryArchivePFパーティション関数では、月ごとのデータを格納するためdatetime型の列に格納された値に基づきTransactionHistoryArchiveテーブル、およびインデックスを2つのパーティションに分割します。

CREATE PARTITION FUNCTION 
  TransactionHistoryArchivePF (datetime) 
AS RANGE RIGHT FOR VALUES ('9/01/2003');
GO
リスト7 テーブル分割のためのパーティション関数定義

5.TransactionHistoryArchiveテーブル用のパーティションスキームを定義し、パーティション関数で指定されたパーティションを配置するファイルグループを指定

 リスト8のパーティションスキームは、TransactionHistoryArchivePFパーティション関数で指定されたパーティションを同じPRIMARYファイルグループに配置することを指定しています。

CREATE PARTITION SCHEME TransactionHistoryArchivePS
AS PARTITION TransactionHistoryArchivePF
TO ([PRIMARY], [PRIMARY]);
GO
リスト8 パーティションスキームの定義

6.定義されたパーティションスキームを使って、テーブルを再作成

 リスト9のスクリプトでは、TransactionHistoryArchiveテーブルでTransactionHistoryArchivePSパーティションスキームを使用するためテーブルの再作成を行っています。

IF OBJECT_ID('[Production].[TransactionHistoryArchive]', 'U')
  IS NOT NULL
    DROP TABLE [Production].[TransactionHistoryArchive];
GO
CREATE TABLE [Production].[TransactionHistoryArchive](
  [TransactionID] [int] NOT NULL,
  [ProductID] [int] NOT NULL,
  [ReferenceOrderID] [int] NOT NULL,
  [ReferenceOrderLineNumber] [smallint] NOT NULL 
  CONSTRAINT 
    [DF_TransactionHistoryArchive_ReferenceOrderLineNumber] 
    DEFAULT (0),
  [TransactionDate] [datetime] NOT NULL 
  CONSTRAINT [DF_TransactionHistoryArchive_TransactionDate] 
    DEFAULT (GETDATE()),
  [TransactionType] [nchar](1) NOT NULL, 
  [Quantity] [int] NOT NULL,
  [ActualCost] [money] NOT NULL, 
  [ModifiedDate] [datetime] NOT NULL 
  CONSTRAINT [DF_TransactionHistoryArchive_ModifiedDate] 
    DEFAULT (GETDATE()),
  CONSTRAINT [CK_TransactionHistoryArchive_TransactionType] 
    CHECK ([TransactionType] IN 
      ('W', 'S', 'P', 'w', 's', 'p'))
) ON TransactionHistoryArchivePS (TransactionDate);
GO
リスト9 パーティションスキームを使ったテーブル再作成

7. TransactionHistoryテーブルとTransactionHistoryArchiveテーブルへのデータロード

 リスト10のスクリプトで、TransactionHistoryテーブルとTransactionHistoryArchiveテーブルにデータをロードします。TransactionHistoryArchiveテーブルには8万9253件、TransactionHistoryテーブルには11万3443件の受注データがロードされるはずです。TransactionHistoryArchiveテーブルには、2001年5月17日から2003年8月31日までのデータが格納されます。TransactionHistoryテーブルには、2003年9月1日から2004年9月3日までのデータが格納されます。

DECLARE 
     @retcode INT
    ,@data_path NVARCHAR(256)
EXECUTE @retcode = master.dbo.xp_instance_regread 
    'HKEY_LOCAL_MACHINE', 
    'Software\Microsoft\Microsoft SQL Server\Setup',
    'SQLPath',
    @param = @data_path OUTPUT
SELECT @data_path = LEFT(@data_path, 
                         PATINDEX('%\MSSQL%', @data_path)) + 
    '90\Tools\Samples\1041\awdb\'

EXECUTE (N'BULK INSERT
  [AdventureWorks].[Production].[TransactionHistoryArchive] 
  FROM ''' + @data_path + N'TransactionHistoryArchive.csv''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''\t'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');
EXECUTE (N'BULK 
  INSERT [AdventureWorks].[Production].[TransactionHistory] 
  FROM ''' + @data_path + N'TransactionHistory.csv''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''\t'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');
リスト10 各テーブルにデータをロード

 次ページでは、実際にサンプルデータベースを使って、データを移動する作業を解説します。(次ページに続く)

Copyright © ITmedia, Inc. All Rights Reserved.

編集部からのお知らせ

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。