Frage Was ist der Unterschied zwischen TRUNCATE und DELETE in SQL?


Ich habe eine Antwort auf diese Frage irrtümlich auf eine Frage nach dem Unterschied zwischen DROP und TRUNCATE geschrieben, aber ich dachte, es wäre eine Schande, sie nicht zu teilen, also werde ich meine eigene Antwort auf meine eigene Frage veröffentlichen ... ist das? sogar ethisch? :)

Bearbeiten: Wenn Ihre Antwort plattformspezifisch ist, können Sie dies bitte angeben.


197
2017-09-26 13:53


Ursprung


Antworten:


Hier ist eine Liste der Unterschiede. Ich habe Oracle-spezifische Funktionen hervorgehoben, und hoffentlich kann die Community auch den spezifischen Unterschied anderer Anbieter hinzufügen. Unterschiede, die den meisten Anbietern gemeinsam sind, können direkt unter den Überschriften angezeigt werden, wobei die Unterschiede im Folgenden hervorgehoben werden.


Gesamtübersicht

Wenn Sie alle Zeilen schnell aus einer Tabelle löschen möchten und Sie wirklich sicher sind, dass Sie dies tun möchten, und Sie keine Fremdschlüssel für die Tabellen haben, ist ein TRUNCATE wahrscheinlich schneller als ein DELETE .

Verschiedene systemspezifische Probleme müssen berücksichtigt werden, wie im Folgenden beschrieben.


Anweisungstyp

Löschen ist DML, Abschneiden ist DDL


Commit und Rollback

Variabel nach Kreditor

SQL Server

Abschneiden kann zurückgesetzt werden.

PostgreSQL

Abschneiden kann zurückgesetzt werden.

Orakel

Da ein TRUNCATE DDL ist, beinhaltet es zwei Commits, eins vor und eins nach der Ausführung der Anweisung. Abschneiden kann daher nicht rückgängig gemacht werden, und ein Fehler im Abschneideprozess hat ohnehin ein Commit ausgelöst.

Siehe Flashback unten.


Platzrückgewinnung

Löschen stellt keinen Speicherplatz wieder her, Trunkiert stellt Speicherplatz wieder her

Orakel

Wenn Sie die Klausel REUSE STORAGE verwenden, werden die Datensegmente nicht freigegeben, was geringfügig effizienter sein kann, wenn die Tabelle mit Daten neu geladen werden soll. Die obere Wassermarke wird zurückgesetzt.


Zeilenumfang

Löschen kann verwendet werden, um alle Zeilen oder nur eine Teilmenge von Zeilen zu entfernen. Abschneiden entfernt alle Zeilen.

Orakel

Wenn eine Tabelle partitioniert ist, können die einzelnen Partitionen isoliert abgeschnitten werden, wodurch eine teilweise Entfernung aller Tabellendaten möglich ist.


Objekttypen

Löschen kann auf Tabellen und Tabellen innerhalb eines Clusters angewendet werden. Abschneiden gilt nur für Tabellen oder den gesamten Cluster. (Kann Oracle spezifisch sein)


Datenobjekt Identität

Orakel

Löschen wirkt sich nicht auf die Datenobjekt-ID aus, aber truncate weist eine neue Datenobjekt-ID zu es sei denn Seit seiner Erstellung gab es noch nie einen Insert für die Tabelle. Selbst bei einer einzelnen Einfügung, die zurückgesetzt wird, wird beim Abschneiden eine neue Datenobjekt-ID zugewiesen.


Rückblende (Oracle)

Flashback funktioniert über Löschvorgänge hinweg, aber ein Abschneiden verhindert einen Rückfall in Zustände vor dem Vorgang.

Ab 11gR2 ist dies jedoch mit der FLASHBACK ARCHIVE-Funktion möglich, außer in der Express Edition

Verwendung von FLASHBACK in Oracle http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638


Privilegien

Variable

Orakel

Löschen kann für einen anderen Benutzer oder eine andere Rolle in einer Tabelle gewährt werden, aber das Abschneiden kann nicht ohne eine DROP ANY TABLE-Berechtigung erfolgen.


Wiederholen rückgängig

Löschen erzeugt eine kleine Menge von Wiederherstellen und eine große Menge Rückgängig. Abschneiden erzeugt eine vernachlässigbare Menge von jedem.


Indizes

Orakel

Eine Abschneideoperation macht unbrauchbare Indizes wieder nutzbar. Löschen nicht.


Fremde Schlüssel

Eine Abschneidung kann nicht angewendet werden, wenn ein aktivierter Fremdschlüssel auf die Tabelle verweist. Die Behandlung mit dem Löschen hängt von der Konfiguration der Fremdschlüssel ab.


Sperren von Tabellen

Orakel

Abschneiden erfordert eine exklusive Tabellensperre, Löschen erfordert eine gemeinsame Tabellensperre. Daher ist das Deaktivieren von Tabellensperren eine Möglichkeit, Abschneideoperationen für eine Tabelle zu verhindern.


Löst aus

DML-Trigger werden nicht auf einem Abschneiden ausgelöst.

Orakel

DDL-Trigger sind verfügbar.


Remoteausführung

Orakel

Abschneiden kann nicht über eine Datenbankverbindung ausgegeben werden.


Identitätsspalten

SQL Server

Truncate setzt die Sequenz für IDENTITY-Spaltentypen zurück, delete nicht.


Ergebnismenge

In den meisten Implementierungen a DELETE Die Anweisung kann die gelöschten Zeilen an den Client zurückgeben.

z.B. In einem Oracle PL / SQL-Unterprogramm könnten Sie:

DELETE FROM employees_temp
WHERE       employee_id = 299 
RETURNING   first_name,
            last_name
INTO        emp_first_name,
            emp_last_name;

203
2017-10-15 17:07



Der Unterschied zwischen truncate und delete ist unten aufgeführt:

+----------------------------------------+----------------------------------------------+
|                Truncate                |                    Delete                    |
+----------------------------------------+----------------------------------------------+
| We can't Rollback after performing     | We can Rollback after delete.                |
| Truncate.                              |                                              |
|                                        |                                              |
| Example:                               | Example:                                     |
| BEGIN TRAN                             | BEGIN TRAN                                   |
| TRUNCATE TABLE tranTest                | DELETE FROM tranTest                         |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
| ROLLBACK                               | ROLLBACK                                     |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
+----------------------------------------+----------------------------------------------+
| Truncate reset identity of table.      | Delete does not reset identity of table.     |
+----------------------------------------+----------------------------------------------+
| It locks the entire table.             | It locks the table row.                      |
+----------------------------------------+----------------------------------------------+
| Its DDL(Data Definition Language)      | Its DML(Data Manipulation Language)          |
| command.                               | command.                                     |
+----------------------------------------+----------------------------------------------+
| We can't use WHERE clause with it.     | We can use WHERE to filter data to delete.   |
+----------------------------------------+----------------------------------------------+
| Trigger is not fired while truncate.   | Trigger is fired.                            |
+----------------------------------------+----------------------------------------------+
| Syntax :                               | Syntax :                                     |
| 1) TRUNCATE TABLE table_name           | 1) DELETE FROM table_name                    |
|                                        | 2) DELETE FROM table_name WHERE              |
|                                        |    example_column_id IN (1,2,3)              |
+----------------------------------------+----------------------------------------------+

130
2017-09-26 14:28



Alle guten Antworten, zu denen ich hinzufügen muss:

Schon seit TRUNCATE TABLE ist eine DDL (Datendefinitionssprache), keine DML (Datenmanipulation Langauge) Befehl, der Delete Triggers renne nicht.


22
2018-04-15 08:02



FALLEN

Der DROP-Befehl entfernt eine Tabelle aus der Datenbank. Alle Zeilen, Indizes und Privilegien der Tabellen werden ebenfalls entfernt. Keine DML-Trigger werden ausgelöst. Die Operation kann nicht zurückgesetzt werden.

KÜRZEN

TRUNCATE entfernt alle Zeilen aus einer Tabelle. Die Operation kann nicht rückgängig gemacht werden und es werden keine Trigger ausgelöst. Daher ist TRUCATE schneller und benötigt weniger Speicherplatz als ein DELETE.

LÖSCHEN

Der DELETE-Befehl wird verwendet, um Zeilen aus einer Tabelle zu entfernen. Eine WHERE-Klausel kann verwendet werden, um nur einige Zeilen zu entfernen. Wenn keine WHERE-Bedingung angegeben ist, werden alle Zeilen entfernt. Nachdem Sie eine DELETE-Operation ausgeführt haben, müssen Sie die Transaktion COMMIT oder ROLLBACK ausführen, um die Änderung dauerhaft zu machen oder sie rückgängig zu machen. Beachten Sie, dass diese Operation dazu führt, dass alle DELETE-Trigger in der Tabelle ausgelöst werden.

Von: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands


22
2017-09-26 13:56



Wenn es bei SQL Server oder MySQL ein PK mit automatischem Inkrement gibt, setzt truncate den Zähler zurück.


14
2017-09-26 14:12



"Abschneiden protokolliert nichts" ist korrekt. Ich würde weiter gehen:

Abschneiden wird im Kontext einer Transaktion nicht ausgeführt.

Der Geschwindigkeitsvorteil von trunkate über delete sollte offensichtlich sein. Dieser Vorteil reicht je nach Situation von trivial bis enorm.

Ich habe jedoch festgestellt, dass abgeschnittene Dateien unbeabsichtigt die referenzielle Integrität verletzen und andere Einschränkungen verletzen. Die Macht, die Sie durch das Ändern von Daten außerhalb einer Transaktion erhalten, muss gegen die Verantwortung abgewogen werden, die Sie erben, wenn Sie ohne Netz auf dem Drahtseil gehen.


11
2018-05-14 16:07



Zusammenfassung von Delete Vs in SQL Server abschneiden
Für den vollständigen Artikel folgen Sie diesem Link: http://dotnetmob.com/sql-server-article/delete-vs-truncate-in-sql-server/

enter image description here

Aus dem Artikel von dotnet mob:Löschen Vs Abschneiden in SQL Server


10
2017-09-27 13:46



Ja, DELETE ist langsamer, TRUNCATE ist schneller. Warum?

DELETE muss die Datensätze lesen, Einschränkungen überprüfen, den Block aktualisieren, Indizes aktualisieren und Redo / Rückgängig generieren. All das braucht Zeit.

TRUNCATE passt einfach einen Zeiger in der Datenbank für die Tabelle (die High Water Mark) und poof! Die Daten sind weg.

Dies ist Oracle-spezifisch, AFAIK.


5
2017-08-20 17:32



Wenn Sie versehentlich alle Daten aus der Tabelle mit Löschen / Abschneiden entfernt haben. Sie können eine festgeschriebene Transaktion zurücksetzen. Stellen Sie die letzte Sicherung wieder her und führen Sie das Transaktionsprotokoll bis zu dem Zeitpunkt durch, an dem das Löschen / Verkürzen kurz bevor steht.

Die folgenden Informationen stammen von ein Blogeintrag:

Während wir an der Datenbank arbeiten, verwenden wir Delete und Truncate ohne   die Unterschiede zwischen ihnen kennen. In diesem Artikel werden wir diskutieren   der Unterschied zwischen Löschen und Abschneiden in Sql.

Löschen:

  • Löschen ist ein DML-Befehl.
  • Delete-Anweisung wird mit einer Zeilensperre ausgeführt, jede Zeile in der Tabelle ist zum Löschen gesperrt.
  • Wir können Filter in where-Klausel angeben.
  • Es löscht spezifizierte Daten, wenn eine Bedingung existiert.
  • Löschen Sie Aktivitäten einen Trigger, da die Operation einzeln protokolliert wird.
  • Langsamer als Abschneiden, weil es Protokolle hält

Kürzen

  • Abschneiden ist ein DDL-Befehl.
  • Tabelle abschneiden sperrt immer die Tabelle und die Seite, aber nicht jede Zeile. Es entfernt alle Daten.
  • Where-Bedingung kann nicht verwendet werden.
  • Es entfernt alle Daten.
  • Die Tabelle truncate kann einen Trigger nicht aktivieren, da der Vorgang das Löschen einzelner Zeilen nicht protokolliert.
  • In der Leistung schneller, weil es keine Protokolle speichert.

Hinweis: Löschen und Verkürzen können beide zurückgesetzt werden, wenn sie mit verwendet werden   Transaktion. Wenn die Transaktion abgeschlossen ist, bedeutet dies, dass wir es nicht tun können   Rollback Truncate-Befehl, aber wir können den Delete-Befehl immer noch zurücksetzen   aus Log-Dateien, wie löschen schreibt sie in Log-Datei, falls dies der Fall ist   benötigt, um in Zukunft von Protokolldateien Rollbacks.

Wenn Sie eine Fremdschlüsseleinschränkung haben, die sich auf die Tabelle bezieht, die Sie sind   versucht zu kürzen, wird dies nicht funktionieren, auch wenn die verweisende Tabelle keine hat   Daten drin. Dies liegt daran, dass die Fremdschlüsselüberprüfung mit DDL durchgeführt wird   anstatt DML. Dies kann durch vorübergehende Deaktivierung der   Fremdschlüsseleinschränkung (en) für die Tabelle.

Delete table ist eine protokollierte Operation. So wird die Löschung jeder Zeile erhalten   im Transaktionslog protokolliert, wodurch es langsam wird. Tabelle abschneiden   Löscht auch alle Zeilen in einer Tabelle, aber das Löschen von wird nicht protokolliert   In jeder Zeile wird stattdessen die Freigabe der Datenseiten der   Tabelle, die es schneller macht.

~ Wenn Sie versehentlich alle Daten aus der Tabelle mit entfernt haben   Löschen / Abschneiden. Sie können eine festgeschriebene Transaktion zurücksetzen. Wiederherstellen der   letzte Sicherung und Ausführung des Transaktionsprotokolls bis zum Löschen / Verkürzen   wird bald geschehen.


5
2017-12-09 11:31



TRUNCATEist die DDL-Anweisung, während DELETE ist eine DML-Anweisung. Im Folgenden sind die Unterschiede zwischen den beiden:

  1. Wie TRUNCATE ist eine DDL (Datendefinitionssprache) Anweisung es erfordert keine Verpflichtung, um die Änderungen dauerhaft zu machen. Aus diesem Grund konnten Zeilen, die durch truncate gelöscht wurden, nicht zurückgesetzt werden. Andererseits DELETE ist eine DML (Datenmanipulierungssprache) Anweisung erfordert daher explizites Commit, um ihre Wirkung dauerhaft zu machen.

  2. TRUNCATE entfernt immer alle Zeilen aus einer Tabelle, wobei die Tabelle leer bleibt und die Tabellenstruktur intakt bleibt DELETE kann bedingt entfernen, wenn die where-Klausel verwendet wird.

  3. Die Zeilen gelöscht von TRUNCATE TABLE Die Anweisung kann nicht wiederhergestellt werden, und Sie können die where-Klausel in der TRUNCATE Erklärung.

  4. TRUNCATE Anweisungen löst Trigger im Gegensatz zu Delete Trigger on nicht aus DELETE Erklärung

Hier ist der sehr gute Link zum Thema.


5
2018-06-23 07:57