Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I have a question about oracle Expand / Collapse
Author
Message
Posted Monday, February 6, 2012 4:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 6, 2012 4:54 AM
Points: 3, Visits: 4
Which one is faster delete/truncate? Why?
-------------------------------------------------------------------
Sydney Web Design Companies
Cheap Web Design Sydney
Post #1247217
Posted Monday, February 6, 2012 6:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1247302
Posted Wednesday, May 2, 2012 3:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 4:59 AM
Points: 280, Visits: 537
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

Post #1293696
Posted Wednesday, May 2, 2012 4:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1293718
Posted Wednesday, May 2, 2012 5:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 4:59 AM
Points: 280, Visits: 537

Can be? It is always faster. See my previous post for details.


I'm just quoting the official Oracle documentation, see the link.
Post #1293723
Posted Wednesday, May 2, 2012 3:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1294221
Posted Wednesday, May 2, 2012 3:13 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:32 AM
Points: 20,807, Visits: 32,740
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1294222
Posted Thursday, May 3, 2012 12:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 6:06 AM
Points: 2, Visits: 71
As PaulB mentioned, in OracleTRUNCATE is a DDL operation.
Oracle implicitly commits DDL opeations so a truncate cannot be rolled back.
Post #1294369
Posted Thursday, May 3, 2012 2:26 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1294961
Posted Thursday, May 3, 2012 2:32 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:32 AM
Points: 20,807, Visits: 32,740
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1294963
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse