Partitionierung von bestehenden Tabellen

Posted

In dem Artikel Partitionierung von SQL Server Tabellen habe ich bereits beschrieben, wie man die Partionierung auf eine neue Tabelle anwendet. In der Praxis führt man solche Technologien doch eher an bestehenden Tabellen ein, wenn man merkt, das die Tabelle doch größer als erwartet wird. Daher stellt sich die Frage: Wie partitioniere ich eine bestehende Tabelle?

Gründsätzlich könnte man die bestehende Tabelle auslagern, neu erstellen und dann wieder alle Daten reinkopieren. Das ist u.U nicht immer ganz einfach, besonders dann, wenn die Tabelle referentielle Integritäten besitzt (Fremdschlüssel, bzw. „Verlinkungen in andere Tabellen“) und man durch ein Löschen und Neuerstellen doch mehr Arbeit produziert, als notwendig.

Zuerst erstellen wir uns die Umgebung (auf Basis des Beispiels von dem Artikel: Partitionierung von SQL Server Tabellen):
CREATE TABLE dbo.buchungen (
[uid] INT NOT NULL PRIMARY KEY,
[name] VARCHAR(50),
[datum] DATETIME DEFAULT GETDATE() NOT NULL,
[wert] MONEY);
GO

CREATE TABLE dbo.buchungen_Log (
[uid] INT NOT NULL PRIMARY KEY,
[buchungen_uid] INT NOT NULL);
GO

ALTER TABLE dbo.buchungen_Log ADD CONSTRAINT FK_buchungen_Log_buchungen FOREIGN KEY
(buchungen_uid) REFERENCES dbo.buchungen
(uid)
GO

INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (1, 'Januar 2008', '20080308', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (2, 'März 2009', '20090308', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (3, 'Februar 2010', '20100209', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (4, 'Januar 2011', '20110120', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (5, 'Januar 2012', '20120115', '300');
GO
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (1, 1);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (2, 2);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (3, 3);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (4, 4);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (5, 5);
GO

Nun haben wir die Tabelle „Buchungen“ die die zu partitionierenden Daten enthält und auch den Fremschlüssel für die Tabelle „buchungen_log“ stellt.
Ansich geht man nun wir bei der Partitionierung einer neuen Tabelle vor, indem man zuerst die Dateigruppen und die jeweiligen Dateien erstellt:

ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv]
GO

ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2009.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2010.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2011.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2012.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\Archiv.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
GO

Nun benötigt man die Partitionierungs-Funktion und das Schema für die Datenverteilung:
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv]
GO

ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2009.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2010.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2011.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2012.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\Archiv.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
GO

Nun wird die Partionierungsfunktion wie auch das Schema erstellt, um die Datentrennung zu regeln:
CREATE PARTITION FUNCTION ArchivierungsFunktion (DATETIME) AS RANGE RIGHT
FOR VALUES (
'20090101' -- Kleiner als 01.01.2009
, '20100101' -- Kleiner als 01.01.2010, größer als 01.01.2009
, '20110101' -- Kleiner als 01.01.2011, größer als 01.01.2010
, '20120101' -- Kleiner als 01.01.2012, größer als 01.01.2011
);

CREATE PARTITION SCHEME ArchivierungsSchema AS PARTITION ArchivierungsFunktion
TO ('Archiv', 'Archiv2009', 'Archiv2010', 'Archiv2011', 'Archiv2012');

Nun benötigt man einen Primär Schlüssel, wo jedoch auch das Datums Feld (Kennzeichen für die Partionierung) enthalten ist. Dafür muss jedoch der Schlüssel erstmal entfernt werden, wie auch die Fremschlüssel Verbindung:
ALTER TABLE [dbo].[buchungen_Log] DROP CONSTRAINT [FK_buchungen_Log_buchungen]
GO
ALTER TABLE [dbo].[buchungen] DROP CONSTRAINT [PK__buchunge__DD7012647F60ED59]
GO

Nun kann der Primärschlüssel erstellt werden, der dann an die Partionierungsfunktion gehängt wird (dieser muss ein Gruppierter Index sein, eine HEAP Tabelle (ohne Gruppierten Index) wird hier nicht unterstützt, da die Daten sonst nicht verschoben werden können):
ALTER TABLE dbo.buchungen ADD CONSTRAINT
PK_buchungen PRIMARY KEY CLUSTERED
([uid], [datum]) on ArchivierungsSchema(datum);

Kontrolliert werden kann dies folgendermaßen:
SELECT
sys.filegroups.name 'Partitionsname' , sys.partitions.rows 'Zeilenanzahl'
FROM
sys.partitions INNER JOIN sys.allocation_units
ON sys.allocation_units.container_id = sys.partitions.hobt_id
INNER JOIN sys.filegroups ON sys.filegroups.data_space_id = sys.allocation_units.data_space_id
WHERE sys.partitions.object_id = OBJECT_ID('dbo.buchungen')

Das wars.

Schreibe einen Kommentar