| commands [message #9596] |
Sat, 22 November 2003 01:27  |
Smriti
Messages: 1
Registered: November 2003
|
Junior Member |
|
|
|
what is the difference between truncate and delete?is delete followed by a commit equivalent to a truncate command?
|
|
|
|
| Re: Truncate vs DELETE : a closer look [message #9597 is a reply to message #9596] |
Sat, 22 November 2003 07:09  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Smriti,
Truncate is a DDL command that doesnt generate undo(ie rollback ) .You cannot rollback and is much faster hence.
Delete is a DML command that generates UNDO(in fact delete generates the most amount of Undo of all the DML statements since the whole row has to be copied to the undo segments) and hence can be rolled back and much slower.
Truncate resets the HWM(ie High water mark) of the table ,but delete doesnt. So after a delete operation,Oracle will still scan all the blocks upto HWM in case of a Full table scan even if they dont contain any rows.
Demo:
-----
thiru@9.2.0:SQL>drop table t;
Table dropped.
-- Lets create a table T with 10000 rows
thiru@9.2.0:SQL>create table t as select * from all_objects where rownum <=10000;
Table created.
-- Lets update the statistics of the table to be used by the Cost based optimizer
thiru@9.2.0:SQL>analyze table t compute statistics;
Table analyzed.
-- As you see below the table T has a total of 512 data blocks out of which 295 are used blocks(HWM) and 217 blocks are above HWM(ie empty blocks)
thiru@9.2.0:SQL>select table_name,blocks,empty_blocks from user_tables where table_name='T';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
T 295 217
thiru@9.2.0:SQL>select blocks,extents from user_Segments where segment_name='T';
BLOCKS EXTENTS
---------- ----------
512 17
-- Lets time the delete operation
thiru@9.2.0:SQL>set timing on
thiru@9.2.0:SQL>delete from t;
10000 rows deleted.
Elapsed: 00:00:11.06
-- Note that DELETE takes about 11 seconds
-- Now,lets find out how much rollback(undo) entries have been generated becos of this DML operation
thiru@9.2.0:SQL>select r.name "UNDO segment" ,t.status,t.cr_get,t.used_ublk,t.noundo
2 from v$session s,v$transaction t,v$rollname r
3 where t.addr=s.taddr AND
4 t.xidusn=r.usn AND
5 s.audsid=userenv('SESSIONID')
6 /
UNDO segment STATUS CR_GET USED_UBLK NOU
------------------------------ ---------------- ---------- ---------- ---
RB01 ACTIVE 282 566 NO
-- You see about 566 undo blocks are used by this transaction(for rollback,read consistency etc).
-- Also you notice that V$Transaction.NOUNDO = NO in this case , which means this is a transaction that CAN BE ROLLED BACK using undo info.
thiru@9.2.0:SQL>commit;
Commit complete.
Elapsed: 00:00:00.00
-- Lets reanalyze the table to get the updated statistics
thiru@9.2.0:SQL>analyze table t compute statistics;
Table analyzed.
Elapsed: 00:00:00.01
thiru@9.2.0:SQL>set timing off
thiru@9.2.0:SQL>select table_name,blocks,empty_blocks from user_tables where table_name='T';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
T 295 217
thiru@9.2.0:SQL>select blocks,extents from user_Segments where segment_name='T';
BLOCKS EXTENTS
---------- ----------
512 17
-- AS you see above the DELETE operation doesnt do anything to the Used or Empty blocks of the table. The HWM remains the same.
-- A full table scan now will scan all those 295 blocks although we have 'emptied' out the table.
-- Now,lets retry with TRUNCATE
thiru@9.2.0:SQL>drop table t;
Table dropped.
thiru@9.2.0:SQL>create table t as select * from all_objects where rownum <=10000;
Table created.
thiru@9.2.0:SQL>analyze table t compute statistics;
Table analyzed.
thiru@9.2.0:SQL>set timing on
thiru@9.2.0:SQL>truncate table t;
Table truncated.
Elapsed: 00:00:00.04
-- Note the TRUNCATE took only .04 seconds ( compare to 11 seconds of delete )
-- Also , as seen below,you find that the TRUNCATE DOESNT generate any undo entries for rollback.
thiru@9.2.0:SQL>select r.name "UNDO segment" ,t.status,t.cr_get,t.used_ublk,t.noundo
2 from v$session s,v$transaction t,v$rollname r
3 where t.addr=s.taddr AND
4 t.xidusn=r.usn AND
5 s.audsid=userenv('SESSIONID')
6 /
no rows selected
Elapsed: 00:00:00.01
-- V$transaction.NOUNDO would have been YES , since there is no UNDO and this cannot be rolled back.
-- Lets update the CBO statistics again
thiru@9.2.0:SQL>analyze table t compute statistics;
Table analyzed.
Elapsed: 00:00:00.00
thiru@9.2.0:SQL>select table_name,blocks,empty_blocks from user_tables where table_name='T';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
T 0 16
Elapsed: 00:00:00.00
thiru@9.2.0:SQL>select blocks,extents from user_Segments where segment_name='T';
BLOCKS EXTENTS
---------- ----------
16 1
-- As you see above, used_blocks have now become 0 and there are 16 blocks above the HWM(ie empty_blocks).
Since the High water mark has been reset now, a Full table scan operation will scan Zero Used blocks ( but will have to scan the 1st extent to get the extent map of the table to realise that ,that is the HWM and there is no more to scan).
HTH
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
