公司有同事清除大表的时候先truncate,然后drop。问为什么不直接drop，答这样效率高，那真的高吗？其实差不多，先测量下redo,drop 比truncate产生的还少，重点是标黄部分。
产生redo 执行时间
truncate 53k 00: 00: 01.06
drop 26k 00: 00: 00.28
SQL> create table test as select * from dba_objects;
SQL> select value
2 from v$mystat, v$statname
3 where v$mystat.statistic# =v$statname.statistic#
4 and v$statname.name ='redo size';
VALUE
----------
94912
SQL> truncate table test;
Table truncated
SQL> select value
2 from v$mystat, v$statname
3 where v$mystat.statistic# =v$statname.statistic#
4 and v$statname.name ='redo size';
VALUE
----------
149588
SQL> select (149588-94912)/1024 from dual;
(149588-94912)/1024
-------------------
53.39453125
SQL> drop table test purge;
Table dropped
SQL> select value
2 from v$mystat, v$statname
3 where v$mystat.statistic# =v$statname.statistic#
4 and v$statname.name ='redo size';
VALUE
----------
168404
SQL> create table test as select * from dba_objects;
Table created
SQL> select value
2 from v$mystat, v$statname
3 where v$mystat.statistic# =v$statname.statistic#
4 and v$statname.name ='redo size';
VALUE
----------
263320
SQL> drop table test purge;
Table dropped
SQL> select value
2 from v$mystat, v$statname
3 where v$mystat.statistic# =v$statname.statistic#
4 and v$statname.name ='redo size';
VALUE
----------
290364
SQL> select (290364-263320)/1024 from dual;
(290364-263320)/1024
--------------------
26.41015625
用10046跟踪truncate 和drop后台在做什么事情(为了造数据方便，用的不是test表),都是对数据字典的操作。
update语句数量 delete语句数量 insert语句数量
drop 2 45 0
truncate 7 6 1
drop table prod_runlog2 purge 10046产生的结果，剔除select语句：
drop table prod_runlog2 purge
---------------------------------------------------------
BEGIN
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) THEN
xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN
xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
END;
---------------------------------------------------------
declare
stmt varchar2(200);
rdf_exception EXCEPTION; pragma exception_init(rdf_exception, -20000);
BEGIN
if dictionary_obj_type = 'USER' THEN
BEGIN
EXECUTE IMMEDIATE
'begin ' ||
'mdsys.rdf_apis_internal.' ||
'notify_drop_user(''' || dictionary_obj_name || '''); ' ||
'end;';
EXCEPTION
WHEN rdf_exception THEN RAISE;
WHEN OTHERS THEN NULL;
END;
end if;
end;
---------------------------------------------------------
delete from object_usage
where
obj# in (select a.obj# from object_usage a, ind$ b where a.obj# = b.obj#
and b.bo# = :1)
---------------------------------------------------------
delete from sys.cache_stats_1$
where
dataobj# = :1
---------------------------------------------------------
delete com$
where
obj#=:1
---------------------------------------------------------
delete from hist_head$
where
obj# = :1
---------------------------------------------------------
delete from dependency$
where
d_obj#=:1
---------------------------------------------------------
delete from source$
where
obj#=:1
---------------------------------------------------------
DELETE FROM sys.sumdelta$ sd
WHERE
sd.tableobj# = :1
---------------------------------------------------------
DELETE FROM sys.sumpartlog$ sp
WHERE
sp.bo# = :1
---------------------------------------------------------
DELETE FROM sys.snap_loadertime$ sd
WHERE
sd.tableobj# = :1
---------------------------------------------------------
BEGIN
aw_drop_proc(ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner)
;
END;
---------------------------------------------------------
declare
stmt varchar2(200);
BEGIN
if dictionary_obj_type = 'USER' THEN
stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' ||
' WHERE ''"''||SDO_OWNER||''"'' = ''"' || dictionary_obj_name || '"'' ';
EXECUTE IMMEDIATE stmt;
stmt := 'DELETE FROM SDO_MAPS_TABLE ' ||
' WHERE ''"''||SDO_OWNER||''"'' = ''"' || dictionary_obj_name || '"'' ';
EXECUTE IMMEDIATE stmt;
stmt := 'DELETE FROM SDO_STYLES_TABLE ' ||
' WHERE ''"''||SDO_OWNER||''"'' = ''"' || dictionary_obj_name || '"'' ';
EXECUTE IMMEDIATE stmt;
stmt := 'DELETE FROM SDO_THEMES_TABLE ' ||
' WHERE ''"''||SDO_OWNER||''"'' = ''"' || dictionary_obj_name || '"'' ';
EXECUTE IMMEDIATE stmt;
stmt := 'DELETE FROM SDO_LRS_METADATA_TABLE ' ||
' WHERE ''"''||SDO_OWNER||''"'' = ''"' || dictionary_obj_name || '"'' ';
EXECUTE IMMEDIATE stmt;
stmt := 'DELETE FROM SDO_TOPO_METADATA_TABLE ' ||
' WHERE ''"''||SDO_OWNER||''"'' = ''"' || dictionary_obj_name || '"'' ';
EXECUTE IMMEDIATE stmt;
end if;
end;
---------------------------------------------------------
DECLARE
type vcurType is REF CURSOR;
vcur vcurType;
stmt VARCHAR2(1000);
stm2 VARCHAR2(200);
rdt VARCHAR2(80);
rsid number;
cnt number;
BEGIN
IF dictionary_obj_type = 'USER' THEN
stmt := 'DELETE FROM SDO_GEOR_SYSDATA_TABLE WHERE SDO_OWNER = :name';
EXECUTE IMMEDIATE stmt using dictionary_obj_name;
ELSIF dictionary_obj_type = 'TABLE' AND
dictionary_obj_owner <> 'MDSYS' AND
dictionary_obj_name <> 'SDO_GEOR_SYSDATA_TABLE' THEN
stmt := 'SELECT COUNT(*) FROM SDO_GEOR_SYSDATA_TABLE ' ||
' WHERE SDO_OWNER = :1 AND GEORASTER_TABLE_NAME = :2';
EXECUTE IMMEDIATE stmt INTO cnt USING dictionary_obj_owner,
dictionary_obj_name;
IF cnt > 0 THEN
stmt := 'SELECT RDT_TABLE_NAME, RASTER_ID FROM SDO_GEOR_SYSDATA_TABLE' ||
' WHERE SDO_OWNER = :1 AND GEORASTER_TABLE_NAME = :2';
open vcur for stmt using dictionary_obj_owner, dictionary_obj_name;
loop
fetch vcur into rdt, rsid;
exit when vcur%NOTFOUND;
if instr(rdt, '.') = 0 then
rdt := dictionary_obj_owner || '.' || rdt;
end if;
stm2 := 'DELETE FROM ' || rdt || ' WHERE rasterid=:1';
begin
execute immediate stm2 using rsid;
exception
when others then
if SQLCODE <> -942 then
raise;
end if;
end;
end loop;
stmt := 'DELETE FROM SDO_GEOR_SYSDATA_TABLE ' ||
' WHERE SDO_OWNER = :name AND GEORASTER_TABLE_NAME = :2';
EXECUTE IMMEDIATE stmt USING dictionary_obj_owner, dictionary_obj_name;
END IF;
END IF;
END;
---------------------------------------------------------
DECLARE
stmt VARCHAR2(256);
BEGIN
stmt := 'DELETE FROM SDO_NETWORK_METADATA_TABLE WHERE SDO_OWNER = :name';
IF dictionary_obj_type = 'USER' THEN
EXECUTE IMMEDIATE stmt using NLS_UPPER(dictionary_obj_name);
END IF;
END ;
---------------------------------------------------------
DECLARE
stmt VARCHAR2(256);
BEGIN
stmt := 'DELETE FROM SDO_NETWORK_CONSTRAINTS WHERE SDO_OWNER = :name';
IF dictionary_obj_type = 'USER' THEN
EXECUTE IMMEDIATE stmt using NLS_UPPER(dictionary_obj_name);
END IF;
END ;
---------------------------------------------------------
DECLARE
stmt VARCHAR2(256);
BEGIN
stmt := 'DELETE FROM SDO_NETWORK_USER_DATA WHERE SDO_OWNER = :name';
IF dictionary_obj_type = 'USER' THEN
EXECUTE IMMEDIATE stmt using NLS_UPPER(dictionary_obj_name);
END IF;
END ;
---------------------------------------------------------
delete from idl_ub1$
where
obj#=:1 and part=:2
---------------------------------------------------------
delete from idl_char$
where
obj#=:1 and part=:2
---------------------------------------------------------
delete from idl_ub2$
where
obj#=:1 and part=:2
---------------------------------------------------------
delete from idl_sb4$
where
obj#=:1 and part=:2
---------------------------------------------------------
delete from ncomp_dll$
where
obj#=:1 returning dllname into :2
---------------------------------------------------------
delete from objauth$
where
obj#=:1
---------------------------------------------------------
delete from col$
where
obj#=:1
---------------------------------------------------------
delete from icol$
where
bo#=:1
---------------------------------------------------------
delete from icoldep$
where
obj# in (select obj# from ind$ where bo#=:1)
---------------------------------------------------------
delete from jijoin$
where
obj# in ( select obj# from jijoin$ where tab1obj# = :1 or tab2obj# = :1)
---------------------------------------------------------
delete from jirefreshsql$
where
iobj# in ( select iobj# from jirefreshsql$ where tobj# = :1)
---------------------------------------------------------
delete from ccol$
where
obj#=:1
---------------------------------------------------------
delete from ind$
where
bo#=:1
---------------------------------------------------------
delete from cdef$
where
obj#=:1
---------------------------------------------------------
delete from tab$
where
obj#=:1
---------------------------------------------------------
delete coltype$
where
obj#=:1
---------------------------------------------------------
delete from subcoltype$
where
obj#=:1
---------------------------------------------------------
delete ntab$
where
obj#=:1
---------------------------------------------------------
delete lob$
where
obj#=:1
---------------------------------------------------------
select o.name
from
obj$ o, refcon$ r where o.oid$ = r.expctoid and r.obj# = :1
---------------------------------------------------------
delete refcon$
where
obj#=:1
---------------------------------------------------------
delete from opqtype$
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
where
ts#=:1 and file#=:2 and block#=:3
---------------------------------------------------------
delete from obj$
where
obj# = :1
---------------------------------------------------------
delete from con$
where
owner#=:1 and name=:2
---------------------------------------------------------
update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv2=:7,priv3=:8
where
ts#=:1 and user#=:2
---------------------------------------------------------
delete from seg$
where
ts#=:1 and file#=:2 and block#=:3
---------------------------------------------------------
truncate table prod_runlog2 10046产生的结果，剔除select语句：
truncate table
---------------------------------------------------------
truncate table prod_runlog1
BEGIN
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) THEN
xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN
xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
END;
---------------------------------------------------------
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)
---------------------------------------------------------
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd,
deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag,
flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp
= :time
where
obj# = :objn
---------------------------------------------------------
insert into sys.mon_mods$
values
(:1, :2, :3, :4, :5, :6, :7)
---------------------------------------------------------
delete from tab_stats$
where
obj#=:1
---------------------------------------------------------
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
where
ts#=:1 and file#=:2 and block#=:3
---------------------------------------------------------
update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv2=:7,priv3=:8
where
ts#=:1 and user#=:2
---------------------------------------------------------
DECLARE
type vcurType is REF CURSOR;
vcur vcurType;
stmt VARCHAR2(1000);
stm2 VARCHAR2(250);
rdt VARCHAR2(80);
rsid number;
cnt number;
m_id number;
BEGIN
IF dictionary_obj_type <> 'TABLE' THEN
return;
END IF;
stmt := 'SELECT COUNT(*) FROM SDO_GEOR_SYSDATA_TABLE ' ||
' WHERE SDO_OWNER = :1 AND GEORASTER_TABLE_NAME = :2';
EXECUTE IMMEDIATE stmt INTO cnt USING dictionary_obj_owner,
dictionary_obj_name;
IF cnt > 0 THEN
stmt := 'SELECT RDT_TABLE_NAME, RASTER_ID FROM SDO_GEOR_SYSDATA_TABLE' ||
' WHERE SDO_OWNER = :1 AND GEORASTER_TABLE_NAME = :2';
open vcur for stmt using dictionary_obj_owner, dictionary_obj_name;
loop
fetch vcur into rdt, rsid;
exit when vcur%NOTFOUND;
if instr(rdt, '.') = 0 then
rdt := dictionary_obj_owner || '.' || rdt;
end if;
stm2 := 'DELETE FROM ' || rdt || ' WHERE rasterid=:1';
begin
execute immediate stm2 using rsid;
exception
when others then
if SQLCODE <> -942 then
raise;
end if;
end;
end loop;
stmt := 'DELETE FROM SDO_GEOR_SYSDATA_TABLE ' ||
' WHERE SDO_OWNER = :name AND GEORASTER_TABLE_NAME = :2';
EXECUTE IMMEDIATE stmt USING dictionary_obj_owner, dictionary_obj_name;
END IF;
stmt := 'SELECT COUNT(*) FROM ALL_TABLES ' ||
' WHERE OWNER = ''MDSYS'' AND TABLE_NAME = ''RDF_MODEL$'' ';
EXECUTE IMMEDIATE stmt INTO cnt;
if (cnt = 1) then
begin
cnt := 0;
stmt := 'SELECT count(*) FROM MDSYS.RDF_MODEL$ ' ||
' WHERE OWNER = :1 AND TABLE_NAME = :2';
EXECUTE IMMEDIATE stmt INTO cnt USING dictionary_obj_owner,
dictionary_obj_name;
if (cnt > 0) then
stmt := 'SELECT model_id FROM MDSYS.RDF_MODEL$ ' ||
' WHERE OWNER = :1 AND TABLE_NAME = :2';
EXECUTE IMMEDIATE stmt INTO m_id USING dictionary_obj_owner,
dictionary_obj_name;
stmt := ' DELETE MDSYS.RDF_LINK$ WHERE model_id = '||to_char(m_id) ;
EXECUTE IMMEDIATE stmt;
end if;
exception
when others then NULL;
end;
end if;
END;
---------------------------------------------------------