Answer / suneel

truncate:
---------
truncate is a ddl command.
ddl commands having auto commit.

delete:
-------
delete is a dml command.
dml commands not having auto commit.

Answer / mohit prabhu

TRUNCATE SQL Command:

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.

TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.

Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE can not be Rolled back.

TRUNCATE is DDL Command.

TRUNCATE Resets identity of the table.

DELETE SQL Command:

DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.

If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE can be Rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.

-----------------------------------------------------------
This is the most appropriate answer.
-----------------------------------------------------------

Answer / ameya aloni

TRUNCATE:
1. Faster - does its work in single execution by
deallocating the data pages used by the table and reducing
the resource overhead of logging the deletions, as well as
the number of locks acquired.
2. Is DDL command
3. Removes all data
4. Does not make entries in a LOG file
3. Can't be rolled-back
4. Can't filter using WHERE clause
5. Can't call DML triggers
6. Can't ensure data consistency in case of foreign-key
references
7. Resets the IDENTITY back to the SEED

DELETE:
1. Slower - does its work by deleting rows one at a time,
logging each row in the transaction log, maintaining log
sequence number (LSN) information and consuming more
database resources and locks.
2. Is DML command
3. Removes data row-by-row
4. Makes entry per row in a LOG file
3. Can use COMMIT or ROLLBACK
4. Can filter using WHERE clause
5. Can call DML triggers
6. Ensures data consistency in case of foreign-key references
7. Does not reset the IDENTITY

Answer / amedela chandra sekhar

Truncate Delete
1)it is DDl command it is DML command
2)it delete records permanently it delete records temporarly
we can't roll back we can roll back the data

Answer / rr

delete command delete the rows from table record by record but truncate removes all the records is nothing but it drops the table and creates table structure as it is

Answer / narendrareddy

Difference Between Delete and Truncate in Detail

On bigger picture they serve the same purpose but there are many Differences listed with examples
Point Delete Truncate

1. Data Recovery
Delete: Come under the DML Category, we need to commit or Rollback explicitly to make the changes permanent, so we can recover the data by Rollback command fully with in a session or up to a point if Save Points are used

Fall In DDL Category (DDL Command issue the Auto commit implicitly) so no chances of Recovery even not using the Flashback table method.
But Truncate operations are also logged , they didn’t generate redo SQL but they are logged , view for truncated data info V$LOGMNR_CONTENTS

2. Data Removal
Delete Can remove all or selected data using the Where Clause predicates. Or we can say delete any subset of rows
We can Truncate complete table or a partition or sub partition of a table.


3. Speed
Delete is Slower because oracle maintain the redo logs for Read Consistency (so that every session connected can see a consistent data at a given point of time ) Delete is very time consuming activity especially when table have numerous indexes and Triggers associated with table
Faster as no data logs are maintained no associated trigger firing.

4. DML Triggers Firing
DML (Delete) triggers associated with table will fire.
DML Trigger will not fire in case of truncate method.

5. Flashback Technology
Data can be recovered even after commit operation using Flashback Table options Flashback_transaction_query table will give what to recover and up to which point.
Data cannot be recovered in truncate method by Flashback table option.

6. Referential Integrity Constraint Behavior
if we don’t have related data in child table then we can delete the data from the parent table or we have variants like On Delete Cascade & on Delete set Null.

We can’t truncate a table with enable Referential Integrity Constraint, even there is no data in the child table, we have to disable or drop the constraint if we want to truncate the table.
Exception: Truncate is possible if the FK is self-referential means primary key and foreign key are on the same table.

7. Space De allocation or Space Utilization
No extent reset with delete when deleting rows from a table, extents are not de allocated,
So if there were 50 extents in the table before the deletion, there will still be 50 after the deletion.
Truncate: When a table is truncated it will free the space allocated except in case of reuse storage clause. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations .All extents are de allocated leaving only the extents specified when the table was originally created .Example So if the table was originally created with min extents 3, there will be 3 extents remaining when the tables is truncated. When you truncate a table, NEXT is automatically reset to the last extent deleted.
8. High Water Mark
Delete will not reset the high water mark

Truncate will reset the High Water mark which is very important for performance point of view as in case of full table scan and full index scan oracle will read all the block under high water mark this makes a lot of difference in terms of performance.
9. Cluster
No as such restriction with delete.

You cannot individually truncate a table that is part of a cluster. You must truncate the cluster, Delete all rows from the table, or drop and re-create the table.
10. Information Capturing
Delete : we can capture the row information what we have deleted using Delete Method, f you are deleting multiple records then use composite data types (collections & records)
Truncate Don’t have this feature of capturing the deleted records.
11. Function Based Index Impact
DELETE You cannot delete rows from a table if a function-based index on the table has become invalid. You must first validate the function-based index.

Truncate: No as such restriction
12. UNUSABLE Indexes
Delete no as such feature.
Truncate if table is not empty then truncate make all unusable indexes to useable.

13. Complex views

You cannot delete data from a Complex view except through INSTEAD OF triggers.
But we can delete data from simple Views and MV.
We cannot truncate a view simple or complex but you can truncate MV with special Features like Preserve MV Logs and Purge MV Logs.

14. Privileges

Delete You need to provide delete table privilege on object.
Truncate you must have drop table privilege there is no truncate table privilege exists.

15. Domain Index
No as such restriction
You cannot truncate the object having domain index in invalid or In progress state

Why do we need unique key in a table?

0 Answers  

What are different clauses used in sql?

0 Answers  

What is a pragma statement?

0 Answers  

How do I start pl sql?

0 Answers  

what is mean by forward declaration and where we'll use it.

4 Answers   TCS,

how to write date and time literals? : Sql dba

0 Answers  

What is sql catalog?

0 Answers  

What is benefit of creating memory optimized table?

0 Answers  

What is difference between Procedures and Functions ?

6 Answers   Hi Caliber IT,

Where the integrity constrints are stored in Data Dictionary?

1 Answers  

how to rename an existing table in mysql? : Sql dba

0 Answers  

How you improve the performance of sql*loader? : aql loader

0 Answers