Question { PreVator, 39620 }

What is difference between TRUNCATE & DELETE?

Answer

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

Question { TCS, 11425 }

how a reference cursor works?what all adnvantages are
gained with it..specify the situation?

Answer

Generally, through static cursors we are using only one select statement at a time for single active set area. Whereas, in ref cursor we are executing number of select statements dynamically for single active set area. That’s why these types of cursors are called dynamic cursors.

1) Strong Ref Cursor: Having return type.
2) Weak Ref Cursor: Does not have return type.