When i want to delete rows from a table which has million records then which is the best to do it .
It has indexes ,clusters and so on and it's parent table too .
I want to know what will be the real scenario ,if i use and which is faster and advantages over other
from my experience, truncate table is always the best option because
1. Delete uses the rollback segment and you will need a real big rollback seg if we are talking deleting a million plus records.
2. Deleting a Million records consumes a lot of time.
Truncate on the other hand happens fast and rollback segment is not eaten up. But once done, data is lost, you cannot recover unless you have a tape backup or something.
If you have a partitioned tablespace, you can truncate a single partition which is even the more helpful.
If you drop the table, indexes, constraints, grants will also dropped and has to be recreated. The dependent objects (e.g packages) become invalid and has to be recompiled.
If you truncate a table, all rows wil be deleted, also indexes will be truncated. By default the segment will be reduced to one extent, unused space is freed. You don't lose indexes, constraints and grants. Truncate cannot be rolled back.
Delete removes the rows from a table. Unused space is not freed, the segment will not be reduced. Delete can be rolled back. With many rows, it is slower then truncate and you need a large rollback segment.
Because truncate is DDL you need dynamic SQL to use it in PL/SQL, in 8.0 and 8i this has become easy.
DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE emp');
EXECUTE IMMEDIATE 'TRUNCATE TABLE emp';