2

I know it might sound like this question has been answered, but no I have read all other questions and their answers and what I am asking is different. I know TRUNCATE is DDL and DELETE is DML, I know DELETE can be used with WHERE to delete just a single row and TRUNCATE deletes entire column,etc.

I have read all these answers: What's the difference between delete from table_a and truncate table table_a in MySQL? Difference between TRUNCATE and DELETE?

what is the difference between truncate and delete command in sql query

what is the diffrence between truncate and delete in sql server?

What I am asking is does these two statements produce the same results?

DELETE FROM my_table;

AND

TRUNCATE TABLE my_table;

The more i read about this the more it confuses me, so just say TRUE or FALSE and then explain please.

I know it might sound dumb but help.

Ghazali
  • 161
  • 1
  • 12

4 Answers4

3

FALSE — I'll explain in the context of MySQL's InnoDB engine. You tagged the question both and .

DELETE

  • DELETE can be part of a transaction. The action can be rolled back. It copies old records to the rollback segment, and if you commit then those record versions are garbage-collected. If you rollback, the record versions are restored to the table.

  • DELETE does not reset the AUTO_INCREMENT counter for the table.

  • DELETE does not create a new tablespace. The size of the tablespace does not shrink. So it won't reduce the footprint of the table on disk, it will only mark most of the pages in the tablspace as "free", i.e. may be overwritten by subsequent data.

  • DELETE executes delete triggers for each row affected.

  • DELETE requires the DELETE privilege.

  • You can do a DELETE with a JOIN clause, so you can optionally delete from multiple tables in one statement.

  • If you use binary logs to record the changes and you use binlog_format=ROW, the binary log will fill with as many row images as the number of rows deleted. This could take a lot of space.

TRUNCATE TABLE

  • TRUNCATE TABLE is a DDL statement, as you said, so it performs an implicit commit.

  • TRUNCATE TABLE creates a new tablespace with minimum size, and drops the original tablespace.

  • TRUNCATE TABLE resets the AUTO_INCREMENT for the respective table.

  • TRUNCATE TABLE does not copy any records to the rollback segment.

  • TRUNCATE TABLE does not execute any triggers.

  • TRUNCATE TABLE requires the DROP privilege.

  • You can only truncate one table per statement.

  • TRUNCATE TABLE will not take more space in the log for a large table. DDL statements are always written to the binary log in statement format, even if you set binlog_format=ROW.

  • TRUNCATE TABLE is basically equivalent to this sequence:

      CREATE TABLE new_table LIKE original_table; -- i.e. with zero rows
  RENAME TABLE original_table TO table_to_drop, new_table TO original_table;
  DROP TABLE table_to_drop;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
2

You asked about Oracle and MySQL. This answer is for Oracle.

No.

TRUNCATE delete all the rows of a table and resets the "high water mark" of the heap to "zero", effectively releasing disk allocation from the tablespace.

DELETE just remove the rows, so the disk space will be still be allocated to the table, even if it doesn't have any rows anymore.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • By `High Water` do you mean the `AutoIncrement Count`? – RiggsFolly Jun 30 '20 at 16:16
  • In Oracle, the heap corresponds to the disk blocks where the table data is stored. Each time you insert a row the allocation for that table increases, something that is called the "high water mark". Even if you delete rows the "high water mark" stays there. Only `TRUNCATE` resets it. – The Impaler Jun 30 '20 at 16:27
1

Simple answer, yes to a great extent they produce the same results - a table with no records in it. While there are other differences (eg how triggers are treated), to me, the biggest differences are:

  1. Truncate doesn't update logs so you can't roll it back.
  2. Because it doesn't update the logs, it runs much faster

So, I'd recommend using TRUNCATE with caution but, if you're completely sure you want an empty table, I'd use it.

Ben
  • 4,798
  • 3
  • 21
  • 35
-1

Delete

It is a dml command. It delete particular row(s) of Data in a Table . can us where condition . can be commit. can be rollback. Truncate

It is a ddl command. It delete of Data in Table. can us where condition . can't be rollback. auto commit.

Himam Basha
  • 11
  • 2