oracle的truncate table的操作

最新推荐文章于 2024-05-18 11:19:23 发布

原创最新推荐文章于 2024-05-18 11:19:23 发布 · 6.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#table #oracle #object #null #session #events

oracle 专栏收录该内容

20 篇文章

订阅专栏

本文介绍了Oracle数据库中TRUNCATE TABLE操作的独特实现方式。通过实际案例演示了该操作仅更新数据字典而不直接操作数据的特点，展示了TRUNCATE TABLE执行速度快的原因。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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)

....