February 6, 2012 at 4:56 am
Which one is faster delete/truncate? Why?
-------------------------------------------------------------------
February 6, 2012 at 6:44 am
fastformation01 (2/6/2012)
Which one is faster delete/truncate? Why?
Assuming you are talking about deleting the whole population of a table it doesn't matter if it's Oracle, SQL Server, DB2 or whatever other RDBMS truncate will always be faster.
In the particular case of Oracle, truncate is not a DML but a pure DDL operation that resets the High Watermark of the table. Truncate statements in Oracle do not generate redo logs therefore, this is an instantaneous process - in SQL Server, truncate statements are minimally logged.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 2, 2012 at 3:48 am
Removing rows with the TRUNCATE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10006.htm
May 2, 2012 at 4:59 am
rossss (5/2/2012)
Removing rows with the TRUNCATE statement can be faster than removing all rows with the DELETE statement...
Can be? It is always faster. See my previous post for details.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 2, 2012 at 5:07 am
Can be? It is always faster. See my previous post for details.
I'm just quoting the official Oracle documentation, see the link.
May 2, 2012 at 3:10 pm
rossss (5/2/2012)
Can be? It is always faster. See my previous post for details.
I'm just quoting the official Oracle documentation, see the link.
It is always faster 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 2, 2012 at 3:13 pm
PaulB-TheOneAndOnly (2/6/2012)
fastformation01 (2/6/2012)
Which one is faster delete/truncate? Why?Assuming you are talking about deleting the whole population of a table it doesn't matter if it's Oracle, SQL Server, DB2 or whatever other RDBMS truncate will always be faster.
In the particular case of Oracle, truncate is not a DML but a pure DDL operation that resets the High Watermark of the table. Truncate statements in Oracle do not generate redo logs therefore, this is an instantaneous process - in SQL Server, truncate statements are minimally logged.
Question. We know that TRUNCATE TABLE in SQL Server can be rolled back if inside a transaction. How about in Oracle?
May 3, 2012 at 12:49 am
As PaulB mentioned, in OracleTRUNCATE is a DDL operation.
Oracle implicitly commits DDL opeations so a truncate cannot be rolled back.
May 3, 2012 at 2:26 pm
Lynn Pettis (5/2/2012)
Question. We know that TRUNCATE TABLE in SQL Server can be rolled back if inside a transaction. How about in Oracle?
Can't rollback TRUNCATE in the Oracle world.
Oracle's TRUNCATE is a DDL operation as opposed to a DML operation; it works at the catalog level reseting the high water mark of the affected table, it generates no redo log at all.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 3, 2012 at 2:33 pm
PaulB-TheOneAndOnly (5/3/2012)
Lynn Pettis (5/2/2012)
Question. We know that TRUNCATE TABLE in SQL Server can be rolled back if inside a transaction. How about in Oracle?Can't rollback TRUNCATE in the Oracle world.
Oracle's TRUNCATE is a DDL operation as opposed to a DML operation; it works at the catalog level reseting the high water mark of the affected table, it generates no redo log at all.
I'm wondering if this why people think the TRUNCATE TABLE in SQL Server can't be rolled back?
Thanks for the info, I'll tuck it away for future reference.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply