So sánh truncate với delete oracel năm 2024

SQL Server cung cấp 2 phương pháp để xóa dữ liệu, DELETE và TRUNCATE. Cú pháp của hai lệnh này như sau:

DELETE

DELETE dbo.Tblxxx WHERE...

hoặc

DELETE a FROM dbo.Tblxxx a WHERE...

Khi cần xóa dữ liệu với điều kiện liên quan đến bảng khác:

DELETE a FROM dbo.Tblxxx a JOIN dbo.Tblyyy b ON a.Col1 = b.Col1

hoặc:

DELETE a FROM dbo.Tblxxx a WHERE EXISTS(SELECT 1 FROM dbo.Tblyyy b WHERE a.Col1 = b.Col1)

TRUNCATE không có tùy biến nào

TRUNCATE TABLE dbo.Tblxxx

Tuy cùng để xóa dữ liệu, nhưng hai lệnh này có những khác nhau cơ bản:

• DELETE cung cấp các lựa chọn để xóa những dòng dữ liệu thỏa mãn các điều kiện nhất định, như WHERE hoặc JOIN với các bảng khác. TRUNCATE không có lựa chọn nào, mà luôn cắt bỏ toàn bộ dữ liệu của bảng. Nói cách khác, ta không thể TRUNCATE 1 nửa hay 1 phần của bảng.
• DELETE hỗ trợ transaction. Khi lệnh DELETE nằm trong 1 transaction và trong một tình huống nào đó transaction được ROLLBACK thì các bản ghi bị xóa bởi lệnh DELETE sẽ trở lại bảng không có gì suy xuyển. TRUNCATE thì ngược lại, không hỗ trợ transaction. Một khi đã thực hiện thì không thể lấy lại dữ liệu được nữa.
• DELETE khi thực hiện bao gồm quá trình tìm các bản ghi thỏa mãn điều kiện của câu lệnh, và xóa các bản ghi này. Việc tìm các bản ghi cần xóa được thực hiện giống hệt như một câu lệnh SELECT, cũng tối ưu hóa, lựa chọn giữa các phương án thực hiện khác nhau và chọn ra phương án tối ưu (dựa vào index, statistics…). TRUNCATE thì chỉ có một phương án thực hiện duy nhất, đó là cắt bỏ tất cả các dòng dữ liệu của bảng.
• Với DELETE, các bản ghi bị xóa sẽ được kiểm tra xem có vi phạm ràng buộc FOREIGN KEY không. Ví dụ ta có hai bảng MAT_HANG và BAN_HANG là quan hệ 1-n thông qua MA_MH; nếu MA_MH=1 đã có giao dịch, nghĩa là bảng BAN_HANG đã có bản ghi với MA_MH=1, thì khi DELETE bản ghi với MA_MH=1 từ bảng MAT_HANG (bảng cha) SQL SERVER sẽ báo lỗi và không cho xóa. Nếu trước đó, khi ta định nghĩa ràng buộc FOREIGN KEY mà có lựa chọn CASCADE DELETE, thì thay vì báo lỗi SQL Server sẽ đồng thời xóa hết các bản ghi trong cả bảng BAN_HANG với MA_MH=1.
• TRUNCATE thì không có những đoạn kiểm tra dài dòng như thế. Nếu bảng có ràng buộc FOREIGN KEY, SQL Server sẽ báo lỗi và không cho thực hiện (nhớ là lựa chọn CASCADE DELETE trong khai báo FOREIGN KEY chỉ ảnh hưởng đến lệnh DELETE chứ không tác dụng đối với TRUNCATE).
• Vì DELETE hỗ trợ transaction và dùng transaction log, nó có thể dùng với bảng nằm trong một replication hoặc database có dùng log shipping. TRUNCATE thì vì không ghi gì vào transaction log nên khi gặp một trong các tình huống trên sẽ bị từ chối ngay.
• Với DELETE, nếu bảng có index thì các index cũng sẽ được cập nhật để xóa đi các node tương ứng với các bản ghi bị xóa. TRUNCATE thì rất đơn giản, các index của bảng cũng bị cắt cụt theo.
• DELETE không ảnh hưởng đến giá trị IDENTITY. Nếu bảng có 100 bản ghi và cột IDENTITY có giá trị từ 1-100; nay ta DELETE bản ghi có cột IDENTITY=100 rồi INSERT một bản ghi mới; bản ghi mới sẽ có cột IDENTITY=101. TRUNCATE luôn đặt lại IDENTITY trở về 1. Bản ghi đầu tiên được INSERT sau khi TRUNCATE sẽ có cột IDENTITY=1.
• DELETE thực ra chỉ đánh dấu xóa các bản ghi chứ ngay sau đó dữ liệu của các bản ghi bị xóa vẫn nằm nguyên tại chỗ. Dần dần khi ta INSERT thêm dữ liệu vào bảng thì các bản ghi mới sẽ ghi đè lên các vùng lưu trữ đó. Ta có thể kiểm tra để thấy kích thước bảng không thay đổi ngay cả sau khi chạy DELETE FROM TblName (xóa hết các bản ghi). TRUNCATE thì xóa hết dữ liệu đồng thời giải phóng vùng lưu trữ giành cho bảng, trả lại cho SQL Server. Ta có thể so sánh DELETE như là xóa file, còn TRUNCATE thì như format lại ổ cứng.
• DELETE cho phép áp dụng đối với bảng ở server khác được nối qua linked server. TRUNCATE không cho phép điều này, bạn chỉ có thể TRUNCATE bảng nằm trên cùng server.

Vì những lý do trên, DELETE luôn luôn chậm hơn TRUNCATE. Càng có nhiều bản ghi DELETE càng chậm, còn TRUNCATE thì không phụ thuộc vào lượng dữ liệu. DELETE có phạm vi ứng dụng rộng hơn; còn TRUNCATE chỉ dùng được mỗi một việc, nhưng nó lại làm rất nhanh. Vì vậy, hãy nhớ dùng TRUNCATE khi có thể được.

What’s the difference between truncating, deleting, and dropping a table in SQL? Find out in this article.

There are a lot of ways to delete data in SQL, including the DELETE, TRUNCATE TABLE and

DELETE FROM name_table WHERE col=value;

0 commands. Which one should you use in a given situation?

In this article, you’ll learn the syntax of each command in different database engines like MySQL, PostgreSQL, SQL Server, and Oracle. And you’ll understand the

DELETE FROM name_table WHERE col=value;

0 vs. DELETE vs. TRUNCATE TABLE debate.

If you want to practice your SQL skills, I recommend our SQL learning platform LearnSQL.com. LearnSQL.com offers over 50 interactive SQL courses on various levels of proficiency. For example, try out our SQL Practice track with over 600 hands-on SQL exercises to practice your SQL skills.

Now, let’s get started!

DELETE is a DML (Data Manipulation Language) command. This command removes records from a table. It is used only for deleting data from a table, not to remove the table from the database.

You can delete all records with the syntax:

DELETE FROM name_table;

Or you can delete a group of records using the WHERE clause:

DELETE FROM name_table WHERE col=value;

If you’d like to remove all records from a given table, use DELETE FROM followed by the table name. Notice that there aren’t any column names in this syntax; you’re removing all records, not the data in selected columns.

If you want to remove specific records, use

DELETE FROM name_table WHERE col=value;

6 with filtering conditions, as in the second example.

Let’s use these commands in an example. Here’s the table

DELETE FROM name_table WHERE col=value;

7:

idnameprice 1milk2.40 2bread3.68 3butter5.55 4sugar2.88

This query ...

DELETE FROM product;

… removes all the data in the table

DELETE FROM name_table WHERE col=value;

7. After this query, the table

DELETE FROM name_table WHERE col=value;

7 will be empty.

But the

DELETE FROM name_table WHERE col=value;

6 query ...

DELETE FROM product WHERE price<2.90;

… deletes only the records for milk and sugar, because their prices are lower than $2.90. (Milk is $2.40 and sugar is $2.88.)

Now the table

DELETE FROM name_table WHERE col=value;

7 only has records with prices higher than $2.90:

idnameprice 2bread3.68 3butter5.55

How does DELETE work?

If you don’t want to remove table structure or you’re only deleting specific rows, use the DELETE command. It can remove one, some, or all rows in a table. DELETE returns the number of rows removed from the table.

However, DELETE uses a row lock during execution and can be rolled back. Every deleted row is locked, so it will require a lot of locks if you’re working in a large table.

DELETE also keeps the auto-increment ID in the table. If you remove the last record in the table with ID=20 and then add a new record, this record will have ID=21 – even though the record immediately before it will be ID=19.

DELETE can be executed by triggers. A trigger can be called before, after, or instead of the DELETE operation. It can be executed for any row change or when all rows are removed. Removing rows in another table can also trigger DELETE.

Of course, to use the DELETE command you need DELETE permission for that table.

TRUNCATE TABLE

TRUNCATE TABLE is similar to DELETE, but this operation is a DDL (Data Definition Language) command. It also deletes records from a table without removing table structure, but it doesn’t use the

DELETE FROM name_table WHERE col=value;

6 clause. Here’s the syntax:

TRUNCATE TABLE table_name;

If you use this command, all rows in this table will be removed. The following query ...

TRUNCATE TABLE product;

… deletes all records stored in the table

DELETE FROM name_table WHERE col=value;

7.

How does TRUNCATE TABLE work?

Be careful using this command.

DELETE FROM product WHERE price<2.90;

5 transactions can be rolled back in database engines like SQL Server and PostgreSQL, but not in MySQL and Oracle.

DELETE FROM product WHERE price<2.90;

5 is faster than DELETE, as it doesn't scan every record before removing it. TRUNCATE TABLE locks the whole table to remove data from a table; thus, this command also uses less transaction space than DELETE.

Unlike DELETE,

DELETE FROM product WHERE price<2.90;

5 does not return the number of rows deleted from the table. It also resets the table auto-increment value to the starting value (usually 1). If you add a record after truncating the table, it will have ID=1. Note: In PostgreSQL, you can choose to restart or continue the auto-increment value. Oracle uses a sequence to increment values, which is not reset by

DELETE FROM product WHERE price<2.90;

5.

Of course, you need permission to use TRUNCATE TABLE. In PostgreSQL, you need the privilege

DELETE FROM product WHERE price<2.90;

5; in SQL Server, the minimum permission is

TRUNCATE TABLE table_name;

5 table; in MySQL, you need the

TRUNCATE TABLE table_name;

6 privilege. Finally, Oracle requires the

TRUNCATE TABLE table_name;

7 system privilege to use this command.

You can learn more in the course “The Basics of Creating Tables in SQL”, which is part of our .

DROP TABLE

The

DELETE FROM name_table WHERE col=value;

0 is another DDL (Data Definition Language) operation. But it is not used for simply removing data from a table; it deletes the table structure from the database, along with any data stored in the table.

Here is the syntax of this command:

DROP TABLE table_name;

All you need after

DELETE FROM name_table WHERE col=value;

0 is the name of the table you want to delete. For example, if you’d like to remove the entire

DELETE FROM name_table WHERE col=value;

7 table from the database, you’d write:

DROP TABLE product;

This removes all data in the table

DELETE FROM name_table WHERE col=value;

7 and the structure of the table.

How does DROP TABLE work?

The

DELETE FROM name_table WHERE col=value;

0 operation removes the table definition and data as well as the indexes, constraints, and triggers related to the table.

This command frees the memory space.

No triggers are fired when executing

DELETE FROM name_table WHERE col=value;

0.

This operation cannot be rolled back in MySQL, but it can in Oracle, SQL Server, and PostgreSQL.

In SQL Server,

DELETE FROM name_table WHERE col=value;

0 requires

TRUNCATE TABLE table_name;

5 permission in the schema to which the table belongs; MySQL requires the DROP privilege; Oracle the requires the

TRUNCATE TABLE table_name;

7 privilege. In PostgreSQL, users can drop their own tables.

DROP TABLE vs. DELETE TABLE vs. TRUNCATE TABLE in SQL

Which cases call for

DELETE FROM name_table WHERE col=value;

0? When should you use

DELETE FROM product WHERE price<2.90;

5 or opt for a simple DELETE? We’ve prepared the table below to summarize the properties of each command:

DELETETRUNCATEDROP TypeDMLDDLDDL Uses a lockRow lockTable lockTable lock Works in WHEREYesNoNo Removes ...One, some, or all rows in a table.All rows in a table.Entire table structure: data, privileges, indexes, constraints, triggers. Resets ID auto-incrementNoMySQL: Yes Oracle: No PostgreSQL: User decides SQL Server: YesDoesn’t apply RollbackYesMySQL: No Oracle: No PostgreSQL: Yes SQL Server: YesMySQL: No Oracle: Yes PostgreSQL: Yes SQL Server : Yes Transaction loggingEach rowWhole table (minimal)Whole table (minimal) Works with indexed viewsYesNoNo Space requirementsMore spaceLess spaceMore space Fires triggersYesNoNo SpeedSlowFastestFaster

Which operation is best for which use case?

• To remove specific rows, use DELETE.
• To remove all rows from a large table and leave the table structure, use TRUNCATE TABLE. It’s faster than DELETE.
• To remove an entire table, including its structure and data, use DELETE FROM name_table WHERE col=value; 0.

You can learn more about creating database structures in our .

In SQL, DELETE, TRUNCATE TABLE, and

DROP TABLE table_name;

6 all have pluses and minuses. Hopefully, this article has helped you understand when and how to use each command.

Truncate và DELETE khác nhau như thế nào?

Như các bạn có thể thấy bản chất câu lệnh DELETE là xóa từng row khi có 109 rows đã bị tác động còn TRUNCATE đơn giản là xóa cả 1 dữ liệu của 1 bảng không kể số lượng dòng khi có 0 rows đã bị tác động.

Truncate Table là gì?

TRUNCATE TABLE được hiểu là một lệnh nhằm xóa tất cả dữ liệu trong 1 bảng của SQL Server. Ngoài ra bạn cũng có thể sử dụng DROP TABLE để xóa toàn bộ bảng nhưng tôi khuyên bạn nên hạn chế sử dụng lệnh này nếu chưa hiểu rõ.

Truncate Oracle là gì?

Khi sử dụng TRUNCATE, Oracle không thực hiện việc xóa dữ liệu hàng loạt, thay vào đó, nó chỉ xóa đi các liên kết đến dữ liệu. Điều này giúp cho việc xóa dữ liệu trở nên nhanh chóng hơn và đồng thời cũng giải phóng tài nguyên được sử dụng trước đó.