| truncate and delete [message #374351] |
Thu, 07 June 2001 08:16  |
theresia
Messages: 9
Registered: May 2001
|
Junior Member |
|
|
hi
why is truncate a DDL command and delete
a DML command when the following 2 comands give the same output:
truncate table
delete from
|
|
|
|
|
|
| Re: truncate and delete [message #374362 is a reply to message #374351] |
Thu, 07 June 2001 23:48  |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hai,
I am giving few differences between truncate and delete.
Kindly read the first one as truncate as second one for delete.Alignment is not proper here.
TRUNCATE DELETE
1) You cannot rollback the 1) Transaction can be rollbacked.
transaction
2) As we dont need to rollback 2) Redo logs are generated as we
Redo logs are not generated may have to rollback.
3) Execution is fast here. 3) It consumes significant system
resources like CPU,Redo log space,
rollback segment space,etc.
4) Triggers on that table will not 4) If triggers are there on delete,
be fired as there is no trigger triggers will be fired.
on the truncate.
5) Truncate statement specifies 5) Here we dont have such option.
whether the space allocated for Its is only REUSE STORAGE.
a table should be REUSED or
DROP STORAGE.Default option is
DROP STORAGE.
I can truncate a table with
the following statement
TRUNCATE TABLE EMP REUSE STORAGE;
or
TRUNCATE TABLE EMP DROP STORAGE;
(which is default).
Here we have control on the
storage option.
6) IF we have a table with Primary 6) Here you can delete such records
key and corresponding foreign which are fit for deletion.
key in other table,you cannot
use truncate statement on that
table, even though there is no
record in the table which is
having referencial integrity.
Suppose i have a table PARENT
with primary key THEKEY,
and another table LOG_TABLE with
foreign key THEKEY Column.
Even if i dont have any
record in LOG_TABLE, i cannot
use truncate here.It gives error.
Hence you either disable the
constraint or use delete.
I think this is a useful technique
implemented by oracle to ensure
there you are not truncating
data which leads to data inconsistency
as you cannot rollback this
transaction.
7) If we use truncate, the 7) DELETE WILL NOT RESET THE HIGH
HIGH WATER MARK is reset. WATER MARK.
High Water Mark indicates the amount
of used space,or space that have been
formatted to receive the data.
You cannot release the space below
the HWM even if the space is empty.
Based on the above differences,i think,we have two different options
provided to achieve the same result. Depending on our requirement
we can choose to use DML or DDL.
HTH.
Cheers,
Giridhar Kodakalla
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
