truncate是oracle中独有的关键字吧!它的作用是清空一个表格,在删除数据方面,其与delete有一些区别,以便自己记住:
?
1、在功能上,truncate是清空一个表的内容,它相当于delete from table_name。 2、delete是dml操作,truncate是ddl操作;因此,用delete删除整个表的数据时,会产生大量的roolback,占用很多的rollback segments, 而truncate不会。 3、在内存中,用delete删除数据,表空间中其被删除数据的表占用的空间还在,便于以后的使用,另外它是“假相”的删除,相当于windows中用delete删除数据是把数据放到回收站中,还可以恢复,当然如果这个时候重新启动系统(OS或者RDBMS),它也就不能恢复了! 而用truncate清除数据,内存中表空间中其被删除数据的表占用的空间会被立即释放,相当于windows中用shift+delete删除数据,不能够恢复! 4、truncate 调整high water mark 而delete不;truncate之后,TABLE的HWM退回到 INITIAL和NEXT的位置(默认)delete 则不可以。 5、truncate 只能对TABLE,delete 可以是table,view,synonym。 6、TRUNCATE TABLE 的对象必须是本模式下的,或者有drop any table的权限 而 DELETE 则是对象必须是本模式下的,或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的权限。 7、在外层中,truncate或者delete后,其占用的空间都将释放。 8、truncate和delete只删除数据,而drop则删除整个表(结构和数据)。?
?
delete 用法
Oracle Delete Statements? Version 10.2? ?? Basic Delete Statements? 1.Delete All Rows:? DELETE <table_name> ?????????????????????????????? or ?????????????????????????????? DELETE FROM <table_name>;? ?????????????????????????????? CREATE TABLE t? AS ?????????????????????????????? SELECT * ????????????????????????????????FROM all_tables;
??????????????????????????????? SELECT COUNT(*) ??????????????????????????????? FROM t;
DELETE FROM t;
COMMIT;
SELECT COUNT(*) FROM t;
2.Delete Selective Rows
?DELETE FROM <table_name> WHERE <condition>;? CREATE TABLE t AS SELECT * FROM all_tables;
SELECT COUNT(*) FROM t;
DELETE FROM t WHERE table_name LIKE ‘%MAP‘;
COMMIT;
SELECT COUNT(*) FROM t;
3.Delete From A SELECT Statement
DELETE FROM (<SELECT Statement>);? CREATE TABLE t AS SELECT * FROM all_tables;
SELECT COUNT(*) FROM t;
DELETE FROM ( ? SELECT * FROM t WHERE table_name LIKE ‘%MAP‘);
SELECT COUNT(*) FROM t;
4.Delete With Returning Clause
?DELETE FROM (<SELECT Statement>);? CREATE TABLE t AS SELECT * FROM all_tables;
set serveroutput on
DECLARE ?r? urowid;? BEGIN ? DELETE FROM t ? WHERE rownum = 1 ? RETURNING rowid INTO r;
? dbms_output.put_line(r); END; /?
5.Delete Restricted To A Partition
DELETE FROM <table_name> PARTITION <partition_name>;? DELETE FROM sales PARTITION (q1_2001_invoices);? Delete From A Remote Database DELETE FROM <table_name>@<database_link>? DELETE FROM?[email?protected]_db;? ?
?在删除大数据量时(一个表中大部分数据时),
方法: 1、先将不需要删除的数据复制到一个临时表中 2、trunc table 表 3、将不需要删除的数据复制回来。
delete只循环了一次,(去查并删)游标循环了二次,(先查出来,再循环一次删)
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|