SQL Server 2005 introduce la funzionalità di "Partitioned Table" che consente di partizionare orizzontalmente i dati di una tabella allo scopo di ottimizzarne le performance, la manutenibilità e lo spostamento di dati.
Con il termine "partizionamento orizzontale" ci si riferisce alla separazione fisica delle righe di una tabella in N sottoinsiemi distinti sulla base di un criterio logico applicato al valore di una determinata colonna della tabella.
Tali sottoinsiemi di righe possono essere associati a diversi filegroup e, di conseguenza, posizionati su dischi separati. In questo articolo vedremo come gestire il partizionamento di una tabella mediante istruzioni Transact-SQL.
SQL Server 2000 e il partizionamento
SQL Server 2000 ammetteva una forma limitata di partizionamento con la costruzione di una vista (Partitioned View) che combinava due o più tabelle mediante l'operatore UNION ALL:
Listato 1. Partizionamento con SQL Server 2000
CREATE VIEW myPartitionedView
AS
SELECT col1, col2, .., colN FROM server1.database1.owner.table1
UNION ALL
SELECT col1, col2, .., colN FROM server2.database2.owner.table2
Questa tecnica è stata spesso utilizzata per congiungere tabelle di database distribuiti su istanze di SQL Server diverse (Distribuited Partitioned View). Inoltre, se si voleva che la vista partizionata fosse aggiornabile (ovvero fosse oggetto di istruzioni di INSERT, UPDATE o DELETE), occorreva costruire su di essa un trigger di tipo INSTEAD OF che potesse ridirigere l'istruzione di manipolazione dei dati sulla tabella vera e propria.
Indubbiamente, il lavoro di programmazione da svolgere era di una certa complessità, tale da scoraggiarne l'utilizzo. Di fatto, questa feature non è molto popolare tra i programmatori SQL Server e nonostante sia ancora disponibile in SQL Server 2005, Microsoft ne scoraggia l'uso in favore delle Partitioned Tables.
Creazione di una Partitioned Table
Vediamo, quindi, come realizzare il partizionamento di una tabella. Innanzitutto identifichiamo nella tabella interessata la colonna sulla quale basare la suddivisione delle righe ed il criterio che ne determinerà l'appartenenza a una partizione piuttosto che a un'altra. Iniziamo col creare una PARTITION FUNCTION con lo scopo di definire il criterio di partizionamento da applicare.
Listato 2. Sintassi di CREATE PARTITION FUNCTION
CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,...n ] ] )
Supponiamo che l'oggetto del partizionamento sia una tabella [Ordini] che tra le sue colonne ha la [DataOrdine] di tipo [datetime], e che intendiamo avere due sole partizioni, una con gli ordini degli ultimi due anni e l'altra che contiene tutti quelli precedenti. La partition function sarà la seguente:
Listato 3. Partizionare con la data
CREATE PARTITION FUNCTION [Ordini_PF] (datetime)
AS RANGE RIGHT
FOR VALUES ( '20060101' )
Se applicata a una colonna di tipo [datetime], questa funzione sarà in grado di partizionare una qualsiasi tabella del database nel quale è stata creata. Nel caso della nostra tabella d'esempio [Ordini], la dividerà in due parti: una con le righe dove [DataOrdine] < '20060101' e l'altra con [DataOrdine] >= '20060101'. L'attributo RIGHT denota che i limiti espressi fra i VALUES rientrano nella partizione di destra, anziché in quella di sinistra (LEFT) che tra l'altro rappresenta il default.
L'applicazione della funzione di partizionamento ad una tabella, avviene per mezzo di un
PARTITION SCHEME
. Si tratta di un nuovo oggetto di database il cui scopo è di associare un filegroup di database a ciascuna partizione definita dalla PARTITION FUNCTION.
Listato 4. Sintassi di CREATE PARTITION SCHEME
CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
Proseguiamo con il partizionamento della tabella [Ordini] con la definizione del PARTITION SCHEME che userà un solo filegroup, il sempre presente PRIMARY.
Listato 5. Applicazione di uno schema al filegroup PRIMARY
CREATE PARTITION SCHEME [Ordini_PS]
AS PARTITION [Ordini_PF]
TO ( [PRIMARY], [PRIMARY])
Il partizionamento si concretizza in fase di creazione della tabella specificando, al posto del filegroup in cui creare la tabella, il nome dello schema seguito dalla colonna su cui partizionare. Nel nostro esempio:
Listato 6. Applicare il PARTITION SCHEME alle tabelle
CREATE TABLE [Ordini] (
[Numero] [int] NOT NULL,
[Cliente] [nvarchar] (10),
[Prodotto] [nvarchar] (10),
[DataOrdine] [datetime] NOT NULL
)
ON [Ordini_PS] ([DataOrdine])
Popoliamo la tabella con qualche riga valutando la presunta partizione di appartenenza. Sfruttiamo la nuova funzione $Partition. Poi ne verifichiamo il reale partizionamento interrogando la vista di catalogo sys.partitions.
Listato 7. Popolare la tabella e verificarne il partizionamento
INSERT [Ordini] VALUES (100, 'Alex', 'Bike', '20051201')
INSERT [Ordini] VALUES (101, 'Gianni', 'Moto', '20051231')
INSERT [Ordini] VALUES (102, 'Frank', 'Scuter', '20060101')
INSERT [Ordini] VALUES (103, 'Joey', 'Bike', '20060201')
INSERT [Ordini] VALUES (104, 'Alex', 'Moto', '20070101')
GO
SELECT *, PartitionNumber = $Partition.[Ordini_PF] ([DataOrdine]) FROM [Ordini]
Risultato della query
Numero Cliente Prodotto DataOrdine PartitionNumber ------ ------- -------- ----------------------- --------------- 100 Alex Bike 2005-12-01 00:00:00.000 1 101 Gianni Moto 2005-12-31 00:00:00.000 1 102 Frank Scuter 2006-01-01 00:00:00.000 2 103 Joey Bike 2006-02-01 00:00:00.000 2 104 Alex Moto 2007-01-01 00:00:00.000 2
Listato 8. Contare le righe inserite per ogni partizione
SELECT $Partition.[Ordini_PF] ([DataOrdine]) As PartitionNumber, Count(*) As Rows
FROM [Ordini]
GROUP BY $Partition.[Ordini_PF] ([DataOrdine])
ORDER BY Count(*) DESC
Risultato della query
PartitionNumber Rows --------------- ------- 2 3 1 2
Possiamo anche interrogare la vista di catalogo sys.partitions estraendo le stesse informazioni desunte mediante la funzione $Partition:
Listato 9. Interrogare "sys.partition"
SELECT * FROM sys.partitions WHERE Object_ID = Object_ID ('Ordini')
Risultato della query
partition_id object_id index_id partition_number hobt_id rows ----------------- --------- -------- ---------------- ----------------- ---- 72057594038976512 725577623 0 1 72057594038976512 2 72057594039042048 725577623 0 2 72057594039042048 3
Indici partizionati
Anche gli indici possono essere partizionati seguendo la stessa tecnica descritta per le tabelle. Se costruisco un indice su una tabella partizionata, l'indice sarà partizionato e si dirà "allineato" alla tabella perché il suo partizionamento seguirà di default lo schema di partizionamento della tabella stessa.
È comunque possibile decidere per una logica di partizionamento diversa per l'indice, nel qual caso si parla di indice "disallineato" rispetto alla tabella. Potremmo così avere uno o più indici non-clustered partizionati su una tabella non partizionata, oppure tabella e indici non-clustered partizionati su schema differenti.
È bene tener presente che partizionare un indice clustered vuol dire di fatto partizionare la tabella stessa. Perciò, se su una tabella già partizionata con un PARTITION SCHEMA, creiamo un indice clustered partizionato sullo stesso o altro PARTITION SCHEMA, il primo partizionamento sarà sostituito dal secondo.
In caso di indice "allineati" alla tabella, va considerato che:
1. per l'indice clustered la colonna di partizionamento viene aggiunta tacitamente all'indice (come si può desumere dalla sys.index_columns). Esempio:
CREATE CLUSTERED INDEX IX_Ordini ON Ordini (Numero)
GO
SELECT sys.indexes.Name As IndexName, sys.columns.Name As ColumnName
FROM sys.index_columns
INNER JOIN sys.columns
ON sys.index_columns.object_id = sys.columns.object_id
AND sys.index_columns.column_id = sys.columns.column_id
INNER JOIN sys.indexes
ON sys.indexes.object_id = sys.index_columns.object_id
AND sys.indexes.index_id = sys.index_columns.index_id
WHERE index_columns.Object_ID = Object_ID ('Ordini')
Risultato della query
IndexName ColumnName --------- ------------------- IX_Ordini Numero IX_Ordini DataOrdine
2. per gli indici di tipo "unique" essa va esplicitamente aggiunta nell'indice. Esempio:
CREATE UNIQUE NONCLUSTERED INDEX IX_Ordini_Numero ON Ordini (Numero, DataOrdine)
GO
3. per gli indici non-unique basta che sia inclusa mediante la clausola INCLUDE, altrimenti, se omesso, viene aggiunto implicitamente. Ecco degli esempi:
CREATE NONCLUSTERED INDEX IX_Ordini_Prodotto_1 ON Ordini (Prodotto)
CREATE NONCLUSTERED INDEX IX_Ordini_Prodotto_2 ON Ordini (Prodotto) INCLUDE (DataOrdine)
Alla luce di quest'ultime considerazioni, è facilmente spiegabile perché la seguente istruzione di creazione tabella non è valida:
Listato 10. Istruzione non valida con PRIMARY KEY
CREATE TABLE [Ordini] (
[Numero] [int] PRIMARY KEY NOT NULL,
[Cliente] [nvarchar] (10),
[Prodotto] [nvarchar] (10),
[DataOrdine] [datetime] NOT NULL
)
ON [Ordini_PS] ([DataOrdine])
È la presenza del vincolo di PRIMARY KEY a generare l'errore, in quanto essa produce la creazione implicita di un indice unique e clustered che, per essere allineato alla tabella, necessità della presenza della colonna di partizionamento. Ecco la versione corretta:
Listato 11. Aggiungere nella chiave la colonna di partizionamento
CREATE TABLE [Ordini] (
[Numero] [int] NOT NULL,
[Cliente] [nvarchar] (10),
[Prodotto] [nvarchar] (10),
[DataOrdine] [datetime] NOT NULL,
CONSTRAINT PK_Ordini PRIMARY KEY ([Numero],[DataOrdine])
)
ON [Ordini_PS] ([DataOrdine])
Operare con le partizioni
Disponiamo di tre operatori in grado lavorare direttamente con le partizioni.
SPLIT
Crea una nuova partizione nel cosiddetto "NEXT filegroup", che può essere definito inizialmente nel PARTITION SCHEME oppure aggiunto in seguito come in questo esempio, che altera la struttura del partizionamento originario della tabella [Ordini] portandola a tre partizioni, di cui la terza ospiterà una riga, come testimoniato dalla SELECT sulla sys.partitions:
Listato 12. Suddividere una partizione con SPLIT
ALTER PARTITION SCHEME [Ordini_PS]
NEXT USED [PRIMARY]
GO
ALTER PARTITION FUNCTION [Ordini_PF] ( )
SPLIT RANGE ('20070101')
SELECT Partition_number, rows
FROM sys.partitions WHERE Object_ID = Object_ID ('dbo.Ordini')
Risultato della query
Partition_number rows ---------------- ---- 1 2 2 2 3 1
MERGE
All'opposto di SPLIT serve a congiungere due partizioni in una.
Listato 13. Congiungere due partizioni con MERGE
ALTER PARTITION FUNCTION [Ordini_PF] ( )
MERGE RANGE ('20070101')
SWITCH
Permette uno scambio dei dati tra una tabella non-partizionata e una partizione e viceversa, purché le due tabelle abbiano la stessa struttura, la destinazione sia vuota (sia essa la tabella non-partizionata che la partizione) e, infine, sia presente un CHECK CONSTRAINT sulla tabella non-partizionata che restringa il dominio della colonna di partizione in modo da essere compatibile con il RANGE della partizione origine o destinazione dello scambio.
Listato 14. Esempio di scambio da una partizione a una tabella
CREATE TABLE [tempOrdini] (
[Numero] [int] NOT NULL,
[Cliente] [nvarchar] (10),
[Prodotto] [nvarchar] (10),
[DataOrdine] [datetime] NOT NULL,
CONSTRAINT CK_DataOrdine CHECK ([DataOrdine] >= '20070101')
)
GO
ALTER TABLE [Ordini]
SWITCH PARTITION 3 TO [tempOrdini]
Listato 15. Esempio di scambio da una tabella a una partizione vuota
ALTER TABLE [tempOrdini]
SWITCH TO [Ordini] PARTITION 3
Rispetto a un normale spostamento di dati, l'operazione di SWITCH garantisce tempi di esecuzione ottimali in quanto essa non realizza un vero e proprio spostamento di dati, piuttosto un aggiornamento di metadati che ridefiniscono le allocazioni della tabella e della partizione oggetto dello spostamento.
Conclusioni
Il partizionamento delle tabelle e indici, rappresenta un ulteriore passo in avanti di SQL Server in chiave di ottimizzazione delle performance. Di gran lunga più potenti delle già presenti "Partitioned Views", le "Partitioned Tables" sono in grado di apportare un sicuro miglioramento alle applicazioni che utilizzano motore di database SQL Server 2005.
A beneficiare di questa tecnica sono soprattutto le cosiddette very large tables, ed in particolare quelle le cui query sono caratterizzate dal fatto di interessare sottoinsiemi distinti di righe. È bene precisare che il partizionamento è del tutto trasparente all'applicazione software, la quale vedrà sempre e soltanto una tabella di database identificata da un nome, ignorando del tutto il fatto che i suoi dati sono partizionati o addirittura registrati su dischi separati.
