Oracle快速清除表数据

pooi 9年前

一：简介

        删除表数据一般常用的有三种命令、truncate、delete、drop、但是他们的执行方式、效率和结果各有不同、比如删除500W记录级别的数据时、三者之间有很大的不同。

      

二：truncate delete drop

        三者都用可以用于清除表数据、其相似与不同之处如下

相同点：

        1.truncate和不带where子句的delete、以及drop都会删除表内的数据。 

        2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。

不同点：

        1. truncate 和delete 只删除数据不删除表的结构(定义) drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

        2. delete 语句是数据库操作语言(dml)，这个操作会放到rollback segment 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到rollback segment 中，不能回滚，操作不触发 trigger。

         3.delete 语句不影响表所占用的extent，高水线(high watermark)保持原位置不动、drop 语句将表所占用的空间全部释放。truncate 语句缺省情况下将空间释放到 minextents个extent，除非使用reuse storage；truncate 会将高水线复位(回到最开始)。

        4.速度，一般来说: drop>truncate > delete

         5.安全性：小心使用 drop和 truncate，尤其没有备份的时候.否则哭都来不及、使用上,想删除部分数据行用 delete，注意带上where子句. 回滚段要足够大.想删除表,当然用 drop、想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

        6.delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。

         7、TRUNCATE  TABLE   在功能上与不带   WHERE   子句的  DELETE   语句相同：二者均删除表中的全部行。但   TRUNCATE  TABLE   比   DELETE   速度快，且使用的系统和事务日志资源少。DELETE  语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE  TABLE   通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。 

         8、TRUNCATE  TABLE   删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用  DELETE。如果要删除表定义及其数据，请使用   DROP  TABLE   语句。  

         9、对于有   FOREIGN   KEY   约束引用的表，不能使用  TRUNCATE   TABLE，而应使用不带   WHERE   子句的  DELETE   语句。由于   TRUNCATE   TABLE   不记录在日志中，所以它不能激活触发器。    
         10、TRUNCATE  TABLE   不能用于参与了索引视图的表。 

三：oracle外键操作

        1．   查看所有表的外键：

  
    select table_name, constraint_name from user_constraints where constraint_type = 'R';

        2.  禁用所有外键约束： 

    select 'alter table ' || table_name || ' dsable constraint ' || constraint_name ||';' from user_constraints where constraint_type = 'R';


        生成如下sql语句（表名视具体情况而定）： 

    alter table RS_ZGSHBX disable constraint FK_RS_ZGSHB_REFERENCE_RY_JBXX;        alter table WS_JKQK disable constraint FK_WS_JKQK_REFERENCE_RY_JBXX;        alter table WS_YBJZQK disable constraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;


        3.  启用所有外键约束：

  
    select 'alter table ' || table_name || ' enable constraint ' || constraint_name ||';' from user_constraints where constraint_type = 'R';

        生成如下sql语句（表名视具体情况而定）：

  
alter table RS_ZGSHBX enable constraint FK_RS_ZGSHB_REFERENCE_RY_JBXX;    alter table WS_JKQK enable constraint FK_WS_JKQK_REFERENCE_RY_JBXX;    alter table WS_YBJZQK enable constraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;

        4.     删除所有外键约束：

  
    select 'alter table ' || table_name || ' drop constraint '|| constraint_name || ';' from user_constraints where constraint_type = 'R';

        生成如下sql语句（表名视具体情况而定）：

  
    alter table RS_ZGSHBX drop constraint FK_RS_ZGSHB_REFERENCE_RY_JBXX;        alter table WS_JKQK drop constraint FK_WS_JKQK_REFERENCE_RY_JBXX;        alter table WS_YBJZQK drop constraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;

</div> </div>

四：重置sequence初始值

        Oracle中、主键常常使用的是指定的sequence来实现自动增长、本身没有什么问题、但是当我们使用truncate清除千万级别的数据之后、sequence每次增长1的话、删除后下次再插入数据时、其id已经是千万以上了、这一般不是我们想要的、Oracle中一般将自增 sequence重置为初始1时，都是删除再重建，这种方式有很多弊端，依赖它的函数和存储过程将失效，需要重新编译。不过还有种巧妙的方式，不用删除，利用步长参数，先查出sequence的nextval，记住，把递增改为负的这个值（反过来走），然后再改回来。

         假设需要修改的序列名：seq_name： 

    create or replace procedureseq_reset(v_seqname varchar2) as n number(10);               tsqlvarchar2(100);        begin               execute immediate'select '||v_seqname||'.nextval from dual' into n;                n:=-(n-1);                tsql:='altersequence '||v_seqname||' increment by '|| n;                execute immediatetsql;                execute immediate'select '||v_seqname||'.nextval from dual' into n;                tsql:='altersequence '||v_seqname||' increment by 1';                execute immediatetsql;        end seq_reset;

</div> </div>


调用方式： 

    declare               seq_namevarchar2(100);         begin               seq_name :='seq_ry_jbxx';              seq_reset(seq_name);         end;


五：有外键约束的情况下使用truncate

        如前面提到过、有FOREIGNKEY（外键）约束的表不能使用TRUNCATE命令、但是可以通过禁用表的外键、然后使用TRUNCATE 清除表数据、最后再恢复表外键的方式来达到目的。

        1、生成禁用外键的语句、并导出sql。

        2、生成启用外键约束语句、并导出sql。

        3、重新整理成新的sql文件。

        4、在上面新的sql文件中加入对RY_JBXX表对应的sequence:seq_ry_jbxx进行重置。

        5、完整sql文件内容（其中seq_reset是四中生成的存储过程、这里直接调用）： 

alter table RS_ZGSHBX disable constraintFK_RS_ZGSHB_REFERENCE_RY_JBXX;    alter table WS_JKQK disableconstraint FK_WS_JKQK_REFERENCE_RY_JBXX;    alter table WS_YBJZQK disableconstraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;         truncate tableRY_JBXX;    alter table RS_ZGSHBX enable constraintFK_RS_ZGSHB_REFERENCE_RY_JBXX;    alter table WS_JKQK enableconstraint FK_WS_JKQK_REFERENCE_RY_JBXX;    alter table WS_YBJZQK enableconstraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;         select count(*) from ry_jbxx;     declare           seq_name varchar2(100);     begin           seq_name :='seq_ry_jbxx';           seq_reset(seq_name);     end;
</div> </div> 转自：http://blog.csdn.net/crave_shy/article/details/42291965

相关经验