truncate czy delete

sql | bazy danych

blog > programowanie > bazy danych > truncate czy delete

TRUNCATE czy DELETE

TRUNCATE służy do usunięcia wszystkich rekordów z tabeli, nie można wybrać rekordów do usunięcia (tak jak w DELETE). Polecenie to działa szybciej niż DELETE, dodatkowo zwalnia zajmowane przez tabelę miejsce w przestrzeni tabel. TRUNCATE sprawia, że nieużyteczne indeksy stają się użyteczne – DELETE nie.

Jeżeli w danym przypadku używasz polecenia:

DELETE FROM [schema_name.]table_name;

możesz zamiast niego użyć polecenia:

TRUNCATE TABLE [schema_name.]table_name;

Różnice pomiędzy poleceniami SQL TRUNCATE TABLE oraz DELETE FROM TABLE mogą być bardzo istotne, zależnie od przypadku w którym używamy tych poleceń. Poniżej mamy krótko opisane zestawienie najważniejszych różnic.

Polecenie SQL TRUNCATE

1) Jest poleceniem typu DDL (Data Definition Language), z tego powodu dla tego polecenia nie mają zastosowania takie polecenia jak: COMMIT i ROLLBACK
(wyjątkami są tutaj PostgreSQL i MSSQL, których implementacja polecenia TRUNCATE pozwala na użycie polecenia w transakcji)
2) Nie można cofnąć operacji usunięcia rekordów, następuje ona automatycznie i jest nieodwracalna (poza powyższymi wyjątkami - jednak pod warunkiem, że operacja jest ujęta w bloku TRANSACTION i sesja nie jest zamknięta). W przypadku Oracle - obejmuje dwa niejawne zatwierdzenia, jedno przed i jedno po wykonaniu instrukcji. W związku z tym nie można wycofać polecenia, a błąd wykonania i tak spowoduje zatwierdzenie
3) Usuwa wszystkie rekordy z tabeli, nie można ograniczyć rekordów do usunięcia. W przypadku Oracle, gdy tabela jest podzielona na partycje, poszczególne partycje mogą zostać obcięte (TRUNCATE) w izolacji, dzięki czemu możliwe jest częściowe usunięcie wszystkich danych z tabeli
4) Zwalnia zajmowane miejsce przez dane w tabeli (w przestrzeni tabel TABLESPACE - na dysku). W przypadku Oracle - jeśli użyjesz klauzuli REUSE STORAGE, segmenty danych nie zostaną cofnięte, czyli zachowasz miejsce z usuniętych wierszy przydzielonych do tabeli, co może być nieco bardziej wydajne, jeśli tabela ma zostać ponownie załadowana danymi. Znak wysokiego poziomu zostanie zresetowany
5) Powoduje wyzerowanie wartości SEQUENCE przypisanej do tabeli. Można jednak zastosować opcje: RESTART IDENTITY lub CONTINUE IDENTITY
Można także skorzystać z opcji CASCADE (RESTART IDENTITY CASCADE). Jeśli zdefiniowano CASCADE, to sekwencje wszystkich tabel, których dotyczy polecenie, są resetowane
6) TRUNCATE działa dużo szybciej niż DELETE
7) TRUNCATE dotyczy tylko tabel lub całego klastra (może być specyficzne dla Oracle)
8) Oracle Flashback w przypadku TRUNCATE zapobiega cofaniu się do stanów przed operacją
9) Oracle - dla TRUNCATE nie może być przyznane uprawnienie (GRANT) bez użycia DROP ANY TABLE
10) TRUNCATE generuje znikomą ilość ponawiania (redo) i cofania (undo)
11) Operacja TRUNCATE sprawia, że nieużyteczne indeksy stają się ponownie użyteczne
12) TRUNCATE nie można zastosować, gdy włączony klucz obcy odwołuje się do innej tabeli, można wówczas:
- wykonać polecenie: DROP CONSTRAINT, następnie TRUNCATE, a później odtworzyć poprzez CREATE CONSTRAINT lub
- wykonać polecenie: SET FOREIGN_KEY_CHECKS = 0; następnie TRUNCATE, a potem: SET_FOREIGN_KEY_CHECKS = 1;
13) TRUNCATE wymaga wyłącznej blokady tabeli (exclusive table lock), dlatego wyłączenie blokady tabeli (exclusive table lock) jest sposobem zapobiegania operacji TRUNCATE na tabeli
14) Wyzwalacze (triggers) DML nie uruchamiają się po wykonaniu TRUNCATE (należy więc być bardzo ostrożnym w tym wypadku, nie powinno się używać TRUNCATE, jeśli w tabeli zdefiniowano wyzwalacz usuwania, aby wykonać automatyczne czyszczenie tabeli lub akcję logowania po usunięciu wierszy). W przypadku Oracle wyzwalacze DDL (triggers) uruchamiają się
15) Oracle - TRUNCATE nie może być zastosowane w przypadku: database link
16) TRUNCATE nie zwraca liczby usuniętych rekordów
17) Log transakcji - jeden log wskazujący cofnięcie alokacji strony (usuwa dane, zwalniając alokację stron danych używanych do przechowywania danych tabeli i zapisuje w dzienniku transakcji tylko dealokacje stron) - szybsze wykonanie niż DELETE. TRUNCATE musi jedynie dostosować wskaźnik w bazie danych do tabeli (High Water Mark) i dane zostają natychmiast usunięte, dlatego zużywa mniej zasobów systemowych i dzienników transakcji.
18) Wydajność (acquired lock) - blokada tabeli i strony - nie zmniejsza wydajności podczas wykonania
19) TRUNCATE zajmuje mniej miejsca na transakcje niż instrukcja DELETE
20) TRUNCATE nie może być używane z widokami indeksowanymi (indexed views)
21) TRUNCATE nie można używać w odniesieniu do tabel zaangażowanych w replikację transakcyjną (transactional replication) lub replikację scalającą (merge replication).

Polecenie SQL DELETE

1) Jest poleceniem typu DML (Data Manipulation Language), z tego powodu dla tego polecenia mają zastosowanie takie polecenia jak: COMMIT i ROLLBACK
2) Można cofnąć operację usunięcia rekordów za pomocą polecenia ROLLBACK
3) Usuwa wszystkie lub niektóre rekordy z tabeli, można ograniczyć rekordy do usunięcia za pomocą klauzuli WHERE
4) Nie zwalnia zajmowanego miejsca przez dane w tabeli (w przestrzeni tabel TABLESPACE - na dysku)
5) Nie powoduje wyzerowania wartości SEQUENCE przypisanej do tabeli
6) DELETE działa znacznie wolniej niż TRUNCATE
7) DELETE można zastosować do tabel i tabel w klastrze (może być specyficzne dla Oracle)
8) Oracle Flashback działa w przypadku DELETE
9) Oracle - dla DELETE można użyć polecenia GRANT
10) DELETE generuje niewielką ilość ponawiania (redo) i dużą ilość cofania (undo)
11) Operacja DELETE nie sprawia, że nieużyteczne indeksy stają się ponownie użyteczne
12) DELETE w przypadku, gdy włączony klucz obcy odwołuje się do innej tabeli, można (lub nie) zastosować zależnie od konfiguracji kluczy obcych (jeśli nie), należy:
- wykonać polecenie: DROP CONSTRAINT, następnie TRUNCATE, a później odtworzyć poprzez CREATE CONSTRAINT lub
- wykonać polecenie: SET FOREIGN_KEY_CHECKS = 0; następnie TRUNCATE, a potem: SET_FOREIGN_KEY_CHECKS = 1;
13) DELETE wymaga wspólnej blokady tabeli (shared table lock)
14) Wyzwalacze (triggers) uruchamiają się
15) DELETE może być zastosowane w przypadku: database link
16) DELETE zwraca liczbę usuniętych rekordów
17) Log transakcji - dla każdego usuniętego rekordu (usuwa wiersze pojedynczo i rejestruje wpis w dzienniku transakcji dla każdego usuniętego wiersza) - wolniejsze wykonanie niż TRUNCATE. Po wykonaniu instrukcji DELETE tabela może nadal zawierać puste strony. DELETE musi czytać rekordy, sprawdzać ograniczenia, aktualizować blok, aktualizować indeksy i generować ponawianie/cofanie. Wszystko to wymaga czasu, stąd trwa to znacznie dłużej niż w przypadku TRUNCATE.
18) Wydajność (acquired lock) - blokada rekordu - zmniejsza wydajność podczas wykonania - każdy rekord w tabeli jest blokowany do usunięcia.
19) DELETE wykorzystuje więcej miejsca na transakcje niż instrukcja TRUNCATE
20) DELETE może być używany z widokami indeksowanymi (indexed views)
21) DELETE może być używany w stosunku do tabeli używanej w replikacji transakcyjnej (transactional replication) lub replikacji scalającej (merge replication).

Na co zwrócić uwagę wybierając odpowiednie polecenie SQL ?

W szczególnych przypadkach zastanów się nad konsekwencjami użycia każdego z tych poleceń. Być może nie zawsze jednak będzie pożądane wykonanie polecenia TRUNCATE, które usunie również zajmowane miejsce w przestrzeni TABLESPACE (standardowo jest to korzystne). Kiedy może wystąpić taka sytuacja? Weźmy pod uwagę następujący scenariusz:
Posiadasz 15 tabel w bazie danych, a dokładniej w hurtownii danych banku, każda z tych tabel zajmuje po 20 GB danych w przestrzeni tabel. Masz zaimplementowaną procedurę składowaną, która przetwarza dane pomiędzy tymi tabelami. Część z tych tabel to tabele przejściowe, które są wykorzystywane przez procedurę do tymczasowego przechowywania/przetwarzania danych w trakcie jej wykonania. Oznacza to, że nie potrzebujesz przetrzymywać na stałe danych w większości z tych tabel, a potrzebujesz jedynie dane w jednej wynikowej tabeli. Jednak załóżmy, że w przestrzeni tabel z której korzysta procedura są również założone konta innych użytkowników, którzy również mają swoje tabele w których przetwarzają dane. Dodatkowo załóżmy, że ilość miejsca w przestrzeni tabel jest ograniczona i zdarza się raz na jakiś czas, że zostaje osiągnięty maksymalny limit wykorzystania miejsca w TABLESPACE. Wówczas jeżeli w kodzie procedury składowanej po każdym przetworzeniu tabeli, gdy dane w niej nie są już potrzebne użyjesz polecenia DELETE to usuniesz dane z tabeli (np. na potrzeby kolejnego uruchomienia procedury z nowymi danymi), ale tabela ta nadal będzie zajmować w przestrzeni tabel np. ok. 20 GB danych - to pozwoli Ci na zabezpieczenie się przed kolejnym uruchomieniem procedury składowanej - wówczas nie zabraknie miejsca w przestrzeni tabel. Jeżeli natomiast, użyłbyś podczas usuwania danych z każdej przejściowej tabeli polecenia TRUNCATE to wówczas, każda tabela kolejno zwalniałaby w przestrzeni tabel kolejno po ok. 20 GB danych. Jeżeli wykonanie procedury zwolniłoby w przestrzeni tabel ok 300 GB danych, to wówczas inny użytkownik tej samej przestrzeni tabel może uruchomić swoją procedurę, która zaalokuje przestrzeń, którą Ty zwolniłeś i może okazać się, że gdy potem uruchomisz swoją procedurę to podczas jej wykonania zabraknie miejsca w przestrzeni tabel i Twoja procedura zakończy się błędem, informującym o braku miejsca w TABLESPACE.
Powyższa sytuacja nie jest typowa, jednak zdażają sią takie przypadki. Dlatego też należy rozważyć sytuację, w której implementujesz swoje rozwiązanie bazodanowe. Generalnie, jeżeli posiadasz standardową aplikację bazodanową, która posiada dedykowaną bazę danych z własnym TABLESPACE, to zdecydowanie najlepszym rozwiązaniem będzie używanie polecenia TRUNCATE, tam gdzie można go użyć i korzystanie z tego, że to polecenie zwalnia miejsce w bazie danych.

Zdarza się również, że TRUNCATE nieumyślnie łamie integralność referencyjną i narusza inne ograniczenia. Atuty, które zyskujesz, modyfikując dane poza transakcją, powinny być zrównoważone odpowiedzialnością, którą dziedziczysz.

Oba polecenia SQL mogą powodować wiele problemów, dopóki nie zapoznasz się ze szczegółami przed ich użyciem. Nieprawidłowy wybór polecenia może skutkować albo bardzo powolnym procesem usuwania danych, albo może nawet wysadzić segment dziennika, jeśli trzeba usunąć zbyt dużo danych, a segment dziennika nie wystarczy. Dlatego ważne jest, aby wiedzieć, kiedy użyć polecenia TRUNCATE i DELETE w SQL, przed ich użyciem należy zdawać sobie sprawę z różnic i być w stanie stwierdzić, kiedy DELETE jest lepszą opcją do usuwania danych niż TRUNCATE.

Jeżeli masz uwagi, sugestie lub potrzebujesz porady w związku z powyższym artykułem prosimy o przesłanie informacji poprzez formularz: kontakt

tagi: truncate, delete, sql, bazy danych, oracle, postgresql, mysql, mssql, db2
data publikacji: 2022-05-09