oracle的truncate table的操作
来源:互联网 发布:linux管道原理 编辑:程序博客网 时间:2024/05/22 10:25
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)
....
- oracle的truncate table的操作
- TRUNCATE TABLE 的用法
- oracle delete 和truncate 操作的区别
- oracle truncate table 和delete from table 的 不同点 以及 truncate table 在存储过程里面的用法
- Oracle truncate table 与 delete tabel的区别
- Oracle truncate table 与 delete tabel的区别
- Oracle truncate table 与 delete tabel的区别
- Oracle truncate table 与 delete tabel的区别
- Oracle truncate table 与 delete tabel的区别(转)
- oracle删除表的所有数据truncate table
- Oracle truncate table 与 delete tabel的区别
- oracle外键约束,无法truncate table的时候
- oracle中truncate table与delete的区别
- oracle之truncate table 的drop storage和reuse storage
- ORACLE 给用户授权truncate table 的实现方案
- TRUNCATE TABLE 快速清空数据表的SQL操作
- SQL TRUNCATE TABLE的用法
- SQL TRUNCATE TABLE的用法
- 网络游戏的位置同步
- 内存优化——对象池
- LoadRunner系统资源监视
- java中System.Properties的用法
- 动态调用动态库的方法
- oracle的truncate table的操作
- 类库调用ClientScript输出JS
- 基于open source工具所构建的过程管控体系
- 说说结婚~
- x
- C#中Socket的运用
- 利用Spring进行Quartz定时开发的两种方式进行讲解
- 用MATLAB制造时间维度数据
- 快速排序及其优化