InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 18:36:25 GMT20http://www.sqlservercentral.com/Forums/Topic1247217-1042-1.aspx[quote][b]PaulB-TheOneAndOnly (5/3/2012)[/b][hr][quote][b]Lynn Pettis (5/2/2012)[/b][hr]Question. We know that TRUNCATE TABLE in SQL Server can be rolled back if inside a transaction. How about in Oracle?[/quote]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.[/quote]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.Thu, 03 May 2012 14:32:59 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1247217-1042-1.aspx[quote][b]Lynn Pettis (5/2/2012)[/b][hr]Question. We know that TRUNCATE TABLE in SQL Server can be rolled back if inside a transaction. How about in Oracle?[/quote]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.Thu, 03 May 2012 14:26:35 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1247217-1042-1.aspxAs PaulB mentioned, in OracleTRUNCATE is a DDL operation.Oracle implicitly commits DDL opeations so a truncate cannot be rolled back.Thu, 03 May 2012 00:49:55 GMTalan.jeskins-powell-1134784http://www.sqlservercentral.com/Forums/Topic1247217-1042-1.aspx[quote][b]PaulB-TheOneAndOnly (2/6/2012)[/b][hr][quote][b]fastformation01 (2/6/2012)[/b][hr]Which one is faster delete/truncate? Why?[/quote]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.[/quote]Question. We know that TRUNCATE TABLE in SQL Server can be rolled back if inside a transaction. How about in Oracle?Wed, 02 May 2012 15:13:00 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1247217-1042-1.aspx[quote][b]rossss (5/2/2012)[/b][hr][quote]Can be? It is always faster. See my previous post for details.[/quote]I'm just quoting the official Oracle documentation, see the link.[/quote]It is always faster :-DWed, 02 May 2012 15:10:06 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1247217-1042-1.aspx[quote]Can be? It is always faster. See my previous post for details.[/quote]I'm just quoting the official Oracle documentation, see the link.Wed, 02 May 2012 05:07:58 GMTRoss.Mhttp://www.sqlservercentral.com/Forums/Topic1247217-1042-1.aspx[quote][b]rossss (5/2/2012)[/b][hr]Removing rows with the TRUNCATE statement can be faster than removing all rows with the DELETE statement...[/quote]Can be? It is always faster. See my previous post for details.Wed, 02 May 2012 04:59:12 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1247217-1042-1.aspxRemoving 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.[url]http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10006.htm[/url]Wed, 02 May 2012 03:48:13 GMTRoss.Mhttp://www.sqlservercentral.com/Forums/Topic1247217-1042-1.aspx[quote][b]fastformation01 (2/6/2012)[/b][hr]Which one is faster delete/truncate? Why?[/quote]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.Mon, 06 Feb 2012 06:44:42 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1247217-1042-1.aspxWhich one is faster delete/truncate? Why?-------------------------------------------------------------------[b][url='http://www.starconfig.com.au/']Sydney Web Design Companies[/url][/b][b][url='http://www.starconfig.com.au/']Cheap Web Design Sydney[/url][/b]Mon, 06 Feb 2012 04:56:48 GMTfastformation01