drop与truncate的区别

     公司有同事清除大表的时候先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_runlog1BEGIN  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; ---------------------------------------------------------