Supprimer de gros volumes de données peut être problématique si l’on se cantonne à utiliser l’instruction DELETE.
Cette dernière a le défaut de poser un verrou exclusif sur la table cible et comme toute transaction sur les données de journaliser les opérations dans le fichier transaction log afin de pouvoir rollback la transaction en cas de problème et d’assurer l’intégrité des données.
Si vous avez besoin de nettoyer une table de données sans supprimer la totalité de la table (sinon l’instruction TRUNCATE TABLE ferait l’affaire), il peut être judicieux d’utiliser les fonctionnalités du partitionnement.
L’idée directrice de cette méthode et d’affecter les données d’une table source partitionnée à une table cible partitionnée sur le même schéma de partitionnement. Dans la mesure où physiquement rien n’est ni modifié ni déplacé et que l’opération porte juste sur les métadonnées, le temps d’exécution de cette manipulation est quasiment instantanée.
Une fois le Switch de partition opéré vous pouvez très facilement lancer l’instruction TRUNCATE TABLE sur la table cible.
Petit rappel : les fonctionnalités de partitionnement sont disponibles à partir de la version 2005 de SQL SERVER.
Ci-dessous je vous ai préparé un petit script d’exemple pour vous illustrer cette manipulation. La manip est faite dans les étapes 7 et 8, avant il ne s’agit que de la préparation de la base.
-----------------------------------------------------------------------------------------
-- ETAPE 01: Création de la base de données
-----------------------------------------------------------------------------------------
Create Database DemoPartitionsDatabase
Go
-----------------------------------------------------------------------------------------
-- ETAPE 02: Création de la fonction de partition et scheme
-----------------------------------------------------------------------------------------
Use DemoPartitionsDatabase
Go
Create Partition Function MaFonctionDePartionnement (int)
As Range LEFT For Values (20110725,20110801)
Go
Create Partition Scheme MaFonctionScheme
As Partition MaFonctionDePartionnement ALL To ([PRIMARY])
Go
-----------------------------------------------------------------------------------------
-- ETAPE 03: Création de la table
-----------------------------------------------------------------------------------------
Create table FactTable (
[date] int not null,
col1 int null,
col2 int null,
col3 int null,
col4 int null,
col5 int null,
col6 int null,
col7 int null,
col8 int null,
col9 int null,
) ON MaFonctionScheme([date])
-----------------------------------------------------------------------------------------
-- ETAPE 04: Mettons un peu de volumétrie dans la table
-----------------------------------------------------------------------------------------
declare @startdate date='2011-07-25',
@i int=0
While @i<9
begin
;with
t0 as (select n = 1 union all select n = 1),
t1 as (select n = 1 from t0 as a, t0 as b),
t2 as (select n = 1 from t1 as a, t1 as b),
t3 as (select n = 1 from t2 as a, t2 as b),
t4 as (select n = 1 from t3 as a, t3 as b),
t5 as (select n = 1 from t4 as a, t4 as b),
result as (select top 1000000 row_number() over (order by n) as n from t5)
insert into FactTable
select (select convert(int,convert(varchar(10),dateadd(dd,@i,@startdate),112))),
n,n,n,n,n,n,n,n,n from result
set @i=@i+1
end
go
-----------------------------------------------------------------------------------------
-- ETAPE 05: Creation d'une vue pour suivre l'état du partitionnement
-----------------------------------------------------------------------------------------
create view [dbo].[partition_info] as
SELECT
OBJECT_NAME(p.object_id) AS ObjectName, i.name AS IndexName,p.index_id AS IndexID,ds.name AS PartitionScheme,p.partition_number AS PartitionNumber,fg.name AS FileGroupName,prv_left.value AS LowerBoundaryValue,prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right
WHEN 1 THEN 'RIGHT'
ELSE 'LEFT' END AS Range,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds
ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf
ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2
ON dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg
ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
ON ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
ON ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
WHERE OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
select * from [dbo].[partition_info]
go
-----------------------------------------------------------------------------------------
-- ETAPE 06: Crééons une table de purge identique à la première
-----------------------------------------------------------------------------------------
Create table FactTablePurge (
[date] int not null,
col1 int null,
col2 int null,
col3 int null,
col4 int null,
col5 int null,
col6 int null,
col7 int null,
col8 int null,
col9 int null,
) ON MaFonctionScheme([date])
-----------------------------------------------------------------------------------------
-- ETAPE 07: Déplaçons (que dis-je affectons) les données à la table de purge
-----------------------------------------------------------------------------------------
alter table dbo.FactTable switch partition 2 to dbo.FactTablePurge partition 2
go
select * from dbo.partition_info
go
-----------------------------------------------------------------------------------------
-- ETAPE 08: Il ne reste plus qu'à truncate la table de purge et à regarder le résultat
-----------------------------------------------------------------------------------------
truncate table dbo.FactTablePurge
go
select * from dbo.partition_info
go
/*****************************************************************************************
** The End : Les données ont disparu instantannément sans trace dans le fichier de log
*****************************************************************************************/
/*****
Si vous n'êtes pas convaincu par cette méthode essayez d'éxécuter le delete sur les 7 millions de lignes avec la requête ci-dessous
--delete from FactTable where $partition.MaFonctionDePartionnement(date)=2
*******/
