PL | EN

rozwoj-oprogramowania.pl

rozwijamy soft

o nas | przetwarzanie danych | kontakt | blog

truncate czy delete


blog > programowanie
blog > bazy danych


TRUNCATE czy DELETE

Polecenie TRUNCATE służy do usunięcia wszystkich rekordów z tabeli, nie moża wybrać rekordów do usunięcia (tak jak w DELETE z klauzulą WHERE). Polecenie to działa szybciej niż DELETE, a dodatkowo zwalnia zajmowane przez tabelę miejsce w przestrzeni tabel (TABLESPACE), czyli na dysku na, którym zainstalowana jest baza danych.

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;


Warto jednak mieć na uwadze, że jest więcej istotnych informacji dotyczących działania TRUNCATE oraz DELETE


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 Microsoft SQL Server, 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). 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
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 tabeli (należy wcześniej wykonać DROP CONSTRAINT, a później odtworzyć poprzez CREATE CONSTRAINT)
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. 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 - szybsze wykonanie niż DELETE
18) acquired lock - blokada tabeli i strony


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 tabeli, można (lub nie) zastosować zależnie od konfiguracji kluczy obcych (jeśli nie - należy wcześniej wykonać DROP CONSTRAINT, a później odtworzyć poprzez CREATE CONSTRAINT)
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 - wolniejsze wykonanie niż TRUNCATE
18) acquired lock - blokada rekordu



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.

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



rozwój oprogramowania 5 / 5 (8 reviews)
Zobacz nasze opinie w google

copyright ©️ rozwoj-oprogramowania.pl 2019