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.
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.
FALSE — I'll explain in the context of MySQL's InnoDB engine. You tagged the question both mysql and oracle11g.
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 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;
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:
So, I'd recommend using TRUNCATE with caution but, if you're completely sure you want an empty table, I'd use it.