TRUNCATE tylko części danych? Challenge accepted!

Michał Pawlikowski

UWAGA

Artykuł pochodzi z czasów, gdy nie istniało polecenie TRUNCATE TABLE WITH PARTITIONS . Dlatego został on nieco zmodyfikowany, odpowiednie komentarze prostują sytuację ;]

Z dokumentacji technet:
https://msdn.microsoft.com/pl-pl/library/ms189461%28v=sql.110%29.aspx

Partitioning data enables you to manage and access subsets of your data quickly and efficiently while maintaining the integrity of the entire data collection. You can use the Transact-SQL ALTER TABLE…SWITCH statement to quickly and efficiently transfer subsets of your data in the following ways:

  • Assigning a table as a partition to an already existing partitioned table.
  • Switching a partition from one partitioned table to another.
  • Reassigning a partition to form a single table.

Niejeden raz słyszałem pytanie czy da się zrobić truncate tylko części danych na tabeli?

Wiemy, że operacja TRUNCATE jest transakcyjną metodą na błyskawiczne pozbycie się danych.
W odróżnieniu od DELETE oznacza jedynie na metadanych tabeli iż ta nie zawiera rekordów a każda zaalokowana wcześniej na niej strona nadaje się do nadpisania. (wbrew powszechnej opinii TRUNCATE wydany w begin tran można wycowac rollbackiem).
Dlatego można ją wykonać TYLKO na całej tabeli. Nie da się zrobic tego na jej części, np od wiersza x do wiersza y.

Innymi słowy tylko DELETE pozwoli nam na usunięcie czegoś spośród pozostałych wierszy. A co jeśli Wam powiem, że istnieje sposób na przeniesienie tych danych do innej tabeli i to na niej będzie można wykonac polecenie TRUNCATE? Wszystko w czasie mniejszym niż sekunda dla dowolnej ilości rekordów? 🙂 W wersji SQL Server 2016 za to nie trzeba już niczego przenosić, wystarczy odpowiednie polecenie!

Oczywiście sytuacja ta jest możliwa tylko w specyficznych warunkach 😛
Ale jeśli nasze operacje na tabelach opierają się w dużej mierze na częstym ładowaniu olbrzymiej ilości danych kasując ich inne, np starsze wersje to gra jest warta świeczki.
W taki sposób podchodzi się do ładowania w hurtowniach danych, gdzie czas i wolumen to najwięksi wrogowie ;]

Tymi specyficznymi warunkami jest:

  • wersja enterprise (enterprise tylko do wersji SQL Server 2016 RTM, od SQL Server 2016 SP1 partycjonowanie jest we wszystkich wersjach SQL Server)
  • obowiązek partycjonowania fizycznego tabeli względem kolumny, po której będziemy przenosić dane (SQL 2005-2014) lub wykonywać TRUNCATE partycji (2016+)
  • warunkiem możliwości przeniesienia danych pomiędzy tabelami jest konieczność partycjonowania również indeksów tej tabeli, oraz jeśli posiada klucz główny , również on musi być rozłożony na partycje a to z kolei wymaga by kolumna po której partycjonujemy była dołączona do klucza głównego.  🙂 Ale nie taki diabeł straszny ;]

Temat szerszego wyjaśniania partycjonowania zostawiam na inny „TSQL na dziś”, tutaj chciałbym Wam jedynie pokazać, że takie rzeczy są możliwe już od wersji SQL Server 2005. Od 2008R2 możemy utworzyć az 15000 partycji co oznacza, że możemy zdefiniować tyle różnych przedziałów na dane.

Przechodzimy do prostego przykładu (pomijam konfigurację partycjonowania, dla zainteresowanych podam sam skrypt). Pokażę, jak można szybko usunąć tylko część danych np względem ich daty załadowania (przedziały miesięczne)

PRZYKŁAD

Mamy prostą tabelę:

 

kilka kluczowych informacji:

  • Na tabeli utworzonych jest 26 partycji  czego 24 przetrzymuje dane odpowiednio dla każdego miesiąca od 2014 do 2015 roku. Dwie pozostałe są przeznaczone na dane spoza zakresów (mniejsze od 2014-01-01 i wieksze niz 2015-12-31)
    Skrypt tworzący:

    USE [test_mipa]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [pf](datetime) AS RANGE RIGHT FOR VALUES (N'2014-01-01T00:00:00', N'2014-02-01T00:00:00', N'2014-03-01T00:00:00', N'2014-04-01T00:00:00', N'2014-05-01T00:00:00', N'2014-06-01T00:00:00', N'2014-07-01T00:00:00', N'2014-08-01T00:00:00', N'2014-09-01T00:00:00', N'2014-10-01T00:00:00', N'2014-11-01T00:00:00', N'2014-12-01T00:00:00', N'2015-01-01T00:00:00', N'2015-02-01T00:00:00', N'2015-03-01T00:00:00', N'2015-04-01T00:00:00', N'2015-05-01T00:00:00', N'2015-06-01T00:00:00', N'2015-07-01T00:00:00', N'2015-08-01T00:00:00', N'2015-09-01T00:00:00', N'2015-10-01T00:00:00', N'2015-11-01T00:00:00', N'2015-12-01T00:00:00', N'2016-01-01T00:00:00')


CREATE PARTITION SCHEME [ps] AS PARTITION [pf] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])




CREATE CLUSTERED INDEX [Partitioned_GenerationDate] ON [dbo].[PartycjonowanaTabela]
(
	[GenerationDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps]([GenerationDate])








CREATE NONCLUSTERED INDEX [col1] ON [dbo].[PartycjonowanaTabela]
(
	[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps]([GenerationDate])




CREATE NONCLUSTERED INDEX [col2] ON [dbo].[PartycjonowanaTabela]
(
	[col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps]([GenerationDate])






COMMIT TRANSACTION

     

  • Najobszerniejszy (263888 rekordow) jest 2014-09 i znajduje się on w 1- partycji. Wykonanie delete trwa niecałe 18sek:
SET STATISTICS TIME ON
BEGIN TRAN
DELETE FROM [dbo].[PartycjonowanaTabela] WHERE YEAR(GenerationDate) = 2014 and MONTH(GenerationDate) = 9

 

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 18955 ms,  elapsed time = 17969 ms.

(263888 row(s) affected)

 

Jak zatem usunąć dane dla tej partycji?

 

WERSJA DLA SQL SERVER 2016+

Wystarczy jedno polecenie (znając uprzednio numer partycji):

TRUNCATE TABLE [dbo].[PartycjonowanaTabela] WITH ( PARTITIONS ( 10 ));

jeśli chcemy usunąć dane z partycji 5 i 10, oraz od 15 do 20:

TRUNCATE TABLE [dbo].[PartycjonowanaTabela] WITH ( PARTITIONS ( 5, 10, 15 TO 20 ));

 

i po temacie… Dla starszych wersji instrukcja znajduje się poniżej 🙂

 

WERSJA DLA SQL SERVER 2005 – 2014

Tutaj musimy więcej się napracować. Mianowicie musimy partycję zamienić na inną tabelę, by móc ją wyczyścić a potem ponownie ją dodać do pierwotnej tabeli jako partycję.

Jak zatem przenieśc tylko jedną partycję do innej tabeli?
Wystarczy wykonac tzw PARTITION SWITCH OUT:
1. Wybieramy Manage partition:

 

 

2. Klikamy next i wybieramy operację SwitchOut:

 

3. Spośród wszystkich dostępnych partycji wybieramy tą, która nas interesuje czyli czały wrzesień 2014.
Chcemy by dane z tabeli PartycjonowanaTabela dla września 2014 trafiły do tabeli o nazwie „staging_PartycjonowanaTabela_2014-09” dlatego wpiszemy ją jako nazwę dla nowej tabeli stagingowej

 

4. Skrypt:

USE [test_mipa]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [pf](datetime) AS RANGE RIGHT FOR VALUES (N'2014-01-01T00:00:00', N'2014-02-01T00:00:00', N'2014-03-01T00:00:00', N'2014-04-01T00:00:00', N'2014-05-01T00:00:00', N'2014-06-01T00:00:00', N'2014-07-01T00:00:00', N'2014-08-01T00:00:00', N'2014-09-01T00:00:00', N'2014-10-01T00:00:00', N'2014-11-01T00:00:00', N'2014-12-01T00:00:00', N'2015-01-01T00:00:00', N'2015-02-01T00:00:00', N'2015-03-01T00:00:00', N'2015-04-01T00:00:00', N'2015-05-01T00:00:00', N'2015-06-01T00:00:00', N'2015-07-01T00:00:00', N'2015-08-01T00:00:00', N'2015-09-01T00:00:00', N'2015-10-01T00:00:00', N'2015-11-01T00:00:00', N'2015-12-01T00:00:00', N'2016-01-01T00:00:00')


CREATE PARTITION SCHEME [ps] AS PARTITION [pf] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])




CREATE CLUSTERED INDEX [Partitioned_GenerationDate] ON [dbo].[PartycjonowanaTabela]
(
	[GenerationDate] 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) ON [ps]([GenerationDate])








CREATE NONCLUSTERED INDEX [col1] ON [dbo].[PartycjonowanaTabela]
(
	[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps]([GenerationDate])




CREATE NONCLUSTERED INDEX [col2] ON [dbo].[PartycjonowanaTabela]
(
	[col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps]([GenerationDate])






COMMIT TRANSACTION

 

Jak widac tabela staging musi miec taka sama strukture i indeksy oraz check constraint gwarantujacy, ze w niej moga byc tylko dane spelniające warunek partycji. Inaczej nie będzie możliwe przeniesienie na nią danych.

I teraz najważniejsze. Cała magia zawiera się w tej linijce:
ALTER TABLE [test_mipa].[dbo].[PartycjonowanaTabela] SWITCH PARTITION 10 TO [test_mipa].[dbo].[staging_PartycjonowanaTabela_2014-09]

 

Wykonanie jej trwa zaledwie 11ms!!!

 

5. Jak zatem to wygląda teraz?

SELECT count(*) FROM [dbo].[PartycjonowanaTabela] WHERE YEAR(GenerationDate) = 2014 AND MONTH(GenerationDate) = 9
SELECT count(*) FROM [dbo].[staging_PartycjonowanaTabela_2014-09] WHERE YEAR(GenerationDate) = 2014 AND MONTH(GenerationDate) = 9

 

 

Możemy zatem nareszcie wykonac upragniony truncate:

TRUNCATE TABLE [dbo].[staging_PartycjonowanaTabela_2014-09]

operacja trwała 8ms 🙂
6. Co zatem ze starą tabelą? Można oczywiście załadować dane zawierające 2014-09 bezpośrednio do PartycjonowanaTabela, ale można też wykorzystać utworzoną tabelę stagingową i to do niej załadować najpierw dane a potem „podłączyć” ją do tabeli poleceniem SWITCH IN 🙂 Warunkiem obligatoryjnym jest to, by partycja na tabeli PartycjonowanaTabela dla danego zakresy partycji była pusta czyli zawierała 0 rekordów.

 

7. Dodajmy dla ulatwienia tylko jeden rekord do tabeli staging_PartycjonowanaTabela_2014

INSERT INTO [dbo].[staging_PartycjonowanaTabela_2014-09] (GenerationDate,id,col1,col2) VALUES (’2014-09-21 11:45′,-1,’test’,666)

 

8. Wchodzimy do zarzadzania partycją na tabeli PartycjonowanaTabela , wybieramy switch in

 

9 . Wybieramy z listy pustych partycji naszą właściwą oraz wskazujemy tabelę, z której będziemy przepinać dane:

 

 

10. Wygenerowany skrypt wygląda następująco:

BEGIN TRANSACTION
ALTER TABLE [dbo].[staging_PartycjonowanaTabela_2014-09] SWITCH  TO [dbo].[PartycjonowanaTabela]PARTITION 10
COMMIT TRANSACTION

operacja trwała 13ms!!!

 

11. Sprawdzamy wynik:

SELECT * FROM [dbo].[PartycjonowanaTabela] WHERE YEAR(GenerationDate) = 2014 AND MONTH(GenerationDate) = 9

 

POST PIERWOTNIE OPUBLIKOWANY JAKO TSQL NA DZIŚ #34

 

Dodaj komentarz