Schlagwort-Archiv SQL Server

Relationale Partitionierung #3 – Partition Merge+Split

Nachdem wir uns nun angeschaut haben, wie man eine relationale Partitionierung einrichtet und uns die Switch-Function angeschaut haben, kommen wir jetzt zu weiteren Möglichkeiten, die eine Partitionierung einer Tabelle mit sich bringt. In diesem Post soll es um die beiden Funktionen Merge und Split gehen.

Anwendungsbeispiele:

In unserem Beispiel mit den ProductInventories könnte man sich vorstellen, das wir kurz nach dem Jahreswechsel die Inhalte der Current-Partition Filegroup in die Hist-Partition Filegroup verschieben möchten.

Für das Simulieren benötigen wir noch weitere Testdaten, damit wir für 2014 das Jahr mit Daten vollmachen können. Ich stelle die vorbereitenden Skripte für dieses Beispiel hier zur Verfügung.

  1. -- Daten Insert in FactProductInventory ab 07.2014 bis 12.2014v
  2. INSERT INTO dbo.[FactProductInventory]
  3. SELECT
  4. [ProductKey]
  5. ,[DateKey]+10000 AS  [Datekey]            -- + 1Jahr
  6. ,DATEADD(dd,365,[MovementDate])  AS [MovementDate]
  7. ,CAST([UnitCost] * 1.03 AS DECIMAL(18,2)) AS [UnitCost]
  8. ,[UnitsIn]
  9. ,[UnitsOut]
  10. ,[UnitsBalance] * 1.03        AS [UnitsBalance]
  11. FROM [dbo].[FactProductInventory]
  12. WHERE DateKey between 20130701 and 20131231

Zunächst müssen wir die letzte Partition „aufsplitten“. Aus einer Partition machen wir zwei.

  1. -- 1. Partition-Split auf die leere 2015er Partition, Cur-Filegroup wird verwendet.
  2. ALTER PARTITION SCHEME [PSProductInventoriesRight] NEXT USED [ProductInventoriesCur]
  3. ALTER PARTITION FUNCTION PFYearsRight() SPLIT RANGE (20160000)  -- > + 1 Jahr
  4. -- 2. Aufteilung der Daten auf die jeweiligen Partitionen anschauen
  5. USE [AdventureWorksDW2014]
  6. GO
  7. SELECT DISTINCT
  8. --SysTables.object_id                                    as [OBJECT_ID]
  9. --,object_schema_name(SysPartitions.object_id)        as [TABLE_SCHEMA]
  10. SysTables.name                                        AS [TABLE_NAME]
  11. ,SysPartitions.partition_number                        AS [PARTITION_NUMBER]
  12. ,SysRangeValue.VALUE                                AS [PARTITIONCOLUMNID_FROM]
  13. ,SysRangeValue.boundary_id                            AS [BOUNDARY_ID]
  14. ,SysPartitions.ROWS                                    AS [PARTITION_ROWS]
  15. ,SysPartitionFunctions.name                            AS [PARTITION_FUNCTION]
  16. ,SysSchemes.name                                    AS [PARTITION_SCHEMA]
  17. ,[SysFileGroups].name                                AS [PARTITION_FILEGROUP]
  18. FROM sys.partitions AS [SysPartitions]
  19. INNER join sys.indexes AS [SysIndizes]
  20. ON SysPartitions.OBJECT_ID = SysIndizes.OBJECT_ID
  21. and SysPartitions.index_id = SysIndizes.index_id
  22. INNER join sys.tables AS SysTables
  23. ON SysPartitions.OBJECT_ID = SysTables.OBJECT_ID
  24. INNER join sys.partition_schemes AS SysSchemes
  25. ON SysIndizes.data_space_id = SysSchemes.data_space_id
  26. INNER join sys.partition_functions AS SysPartitionFunctions
  27. ON SysPartitionFunctions.function_id = SysSchemes.function_id
  28. INNER join sys.partition_range_values AS SysRangeValue
  29. ON SysRangeValue.function_id = SysSchemes.function_id
  30. and SysPartitions.partition_number-1 = SysRangeValue.boundary_id
  31. INNER join sys.allocation_units AS [SysAllocationUnits]
  32. ON [SysAllocationUnits].container_id = [SysPartitions].hobt_id
  33. INNER join sys.filegroups AS [SysFileGroups]
  34. ON [SysFileGroups].data_space_id = [SysAllocationUnits].data_space_id
  35. WHERE [SysTables].name = 'FactProductInventory'
  36. ORDER BY SysTables.name

Als nächstes fügen wir Testdaten für Januar/2015 ein.

HINWEIS: Bei der Erstellen des Beispiels hatte ich nicht an den DimDate-ForeignKey in der Tabelle gedacht. Dieser würde das Einfügen der Testdaten verhindern, da in der DimTime Tabelle diese Einträge fehlen. Deshalb bitte vorher den ForeignKey-Constraint für das durchführen des Beispiels entfernen.

  1. -- Januar 2015 Werte einfügen -- Vorher muss der ForeignKey Constraints von DateKey genommen werden. DimDate enthält nur Daten bis 2014.
  2. INSERT INTO [dbo].[FactProductInventory]
  3. SELECT
  4. [ProductKey]
  5. ,[DateKey]+30000 AS  [Datekey]            -- + 3 Jahr
  6. ,DATEADD(dd,1095,[MovementDate])  AS [MovementDate]
  7. ,CAST([UnitCost] * 1.07 AS DECIMAL(18,2)) AS [UnitCost]
  8. ,[UnitsIn]
  9. ,[UnitsOut]
  10. ,[UnitsBalance] * 1.07        AS [UnitsBalance]
  11. FROM [dbo].[FactProductInventory]
  12. WHERE DateKey between 20120101 and 20120131

Jetzt können wir die 2014er Daten in die Hist-Filegroup „mergen“ in dem wir die 2013er und 2014 Partitionen zusammenziehen. Erst danach machen wir einen Split auf die 2014er Partition. Wichtig hierbei ist die Filegroup mit „NEXT USED“  für die 2014er Partition zu ändern.

  1. -- Die 2014er Daten sollen nun in die Hist Filegroup verschoben werden
  2. -- Die Partition 20140000 in die 2013er mergen
  3. ALTER PARTITION FUNCTION PFYearsRight() MERGE RANGE (20140000)
  4. -- Dann per Split wieder erzeugen. Diesmal aber in die Hist-Filegroup
  5. ALTER PARTITION SCHEME [PSProductInventoriesRight] NEXT USED [ProductInventoriesHist]
  6. ALTER PARTITION FUNCTION PFYearsRight() SPLIT RANGE (20140000)

 

Relationale Partitionierungen von SQL Tabellen #2

Partition-Switching

Im zweiten Teil der Reihe über die relationale Partitionierung soll es um die Partion-Switch Funktion gehen.

Partition-Switch ist mit Abstand die performanteste Lösung, wenn aus einer Tabelle mit mehreren 10 Mio. Datensätzen viele Daten gelöscht werden müssen. Faktentabellen eigenen sich für den Einsatz besonders gut, weil es meistens bei dem Ladeprozess darauf ankommt, möglichst schnell die Daten in der Zieltabelle zu aktualisieren. Dieser Artikel zeigt anhand eines Beispiels wie man Daten mit Hilfe der Switch-Methode aus einer Tabelle löschen und wieder einfügen kann.

Als Beispieltabelle dient wieder die FactProductInventory-Tabelle in der AdventureWorksDW2014 Datenbank. Die Partitionierung muss für diese Tabelle eingerichtet worden sein.

Anhand dieser Tabelle erstellen wir eine neue Tabelle, die absolut identisch mit der Grundtabelle aufgebaut sein muss. Wichtig ist auch, dass das Partitionscheme das Gleiche sein muss, wie in der Grundtabelle.

CREATE TABLE [dbo].[FactProductInventorySwitch](
    [ProductKey] [INT] NOT NULL,
    [DateKey] [INT] NOT NULL,
    [MovementDate] [DATE] NOT NULL,
    [UnitCost] [MONEY] NOT NULL,
    [UnitsIn] [INT] NOT NULL,
    [UnitsOut] [INT] NOT NULL,
    [UnitsBalance] [INT] NOT NULL
) ON [PSProductInventoriesRight]([DateKey])
WITH
(
DATA_COMPRESSION = PAGE
)
GO
 
CREATE NONCLUSTERED INDEX [IXFactProductInventorySwitchDateKey] ON [dbo].[FactProductInventorySwitch]
(
    [DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [PSProductInventories]([DateKey])
GO
 
ALTER TABLE [dbo].[FactProductInventorySwitch]  WITH CHECK ADD  CONSTRAINT [FK_FactProductInventorySwitch_DimDate] FOREIGN KEY([DateKey])
REFERENCES [dbo].[DimDate] ([DateKey])
GO
 
ALTER TABLE [dbo].[FactProductInventorySwitch] CHECK CONSTRAINT [FK_FactProductInventorySwitch_DimDate]
GO
 
ALTER TABLE [dbo].[FactProductInventorySwitch]  WITH CHECK ADD  CONSTRAINT [FK_FactProductInventorySwitch_DimProduct] FOREIGN KEY([ProductKey])
REFERENCES [dbo].[DimProduct] ([ProductKey])
GO
 
ALTER TABLE [dbo].[FactProductInventorySwitch] CHECK CONSTRAINT [FK_FactProductInventorySwitch_DimProduct]
GO

Screen6-ResultPartitionRangesBeforeSwitch

Als nächstes werden wir aus einer Partition-Range die Daten aus der Grundtabelle in die neu angelegt Switch-Tabelle verschieben.

ALTER TABLE [dbo].[FactProductInventory]
SWITCH PARTITION 1 TO [dbo].[FactProductInventorySwitch] PARTITION 1;

Screen7-ResultPartitionRangesAfterSwitch

In einem Ladeprozess würde man nun nur noch ein Truncate Table auf die Switch-Tabelle machen und schon wären die Daten aus der eigentlich Tabelle für diese Partition-Range gelöscht worden. Diese Lösung ist sowie extrem einfach also auch extrem effizient. Einfach ausprobieren! Es lohnt sich. Der Ladeprozess kann so um einen hohen Faktor schneller gemacht werden.