oracle的truncate table的操作

 

 

oracle對於truncate table的操作有著自己的獨特做法，truncate一個表幾十條資料和千萬條資料所用的時間都差不多，

說穿了oracle對這類操作只做資料字典的更新，並不對實際資料操作，只需要拉回HWM位置。

 

sys@ORCL>  create table big_table as select * from all_objects;

 

Table created.

 

sys@ORCL> insert into big_table select * from big_table;

 

68480 rows created.

 

sys@ORCL> commit;

 

Commit complete.

 

sys@ORCL> select count(*) from big_table;

 

  COUNT(*)

----------

    136960

 

 

sys@ORCL> select OBJECT_ID  ,DATA_OBJECT_ID  from dba_objects where owner='SYS' and object_name='BIG_TABLE';

 

 OBJECT_ID DATA_OBJECT_ID

---------- --------------

     71485          71485

 

sys@ORCL> select dbms_rowid.ROWID_OBJECT(rowid) from big_table where rownum<10;

 

DBMS_ROWID.ROWID_OBJECT(ROWID)

------------------------------

                         71485

                         71485

                         71485

                         71485

                         71485

                         71485

                         71485

                         71485

                         71485

 

9 rows selected.

 

 

sys@ORCL>  alter session set events '10046 trace name context forever, level 12';

 

Session altered.

 

sys@ORCL> truncate table big_table;

 

Table truncated.

 

sys@ORCL> alter session set events '10046 trace name context off';

 

Session altered.

 

再查一下資料字典，發現data_object_id已經變化

 

sys@ORCL> select OBJECT_ID  ,DATA_OBJECT_ID  from dba_objects where owner='SYS' and object_name='BIG_TABLE';

 

 OBJECT_ID DATA_OBJECT_ID

---------- --------------

     71485          71486

 

 

找到對應的trace檔案：

 

sys@ORCL> select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum<=1));

 

sys@ORCL> show parameter user_dump

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

user_dump_dest                       string      /u01/app/oracle/diag/rdbms/orc

                                                 l/orcl/trace

 

truncate table big_table

....

update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,spare6=:35 where obj#=:1

....

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3

...

update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)

....