Delete、Drop、Truncate的比较分析和总结.docxVIP

关键字: delete、drop、truncate 相同点:truncate 和不带 where 子句的 delete, 以及 drop 都会删除表内的数据不同点: truncate 和 delete 只删除数据不删除表的结构(定义) drop 语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引 (index); 依赖于该表的存储过程/函数将保留,但是变为 invalid 状态. delete 语句是DML,这个操作会放到rollback segement 中,事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发. truncate,drop 是DDL, 操作立即生效,原数据不放到rollback segment 中,不能回滚. 操作不触发 trigger. delete 语句不影响表所占用的 extent, 高水线(high watermark)保持原位置不动 显然 drop 语句将表所占用的空间全部释放 truncate 语句缺省情况下见空间释放到 minextents 个 extent,除非使用reuse storage; truncate 会将高水线复位(回到最开始). 速度,一般来说: drop truncate delete 安全性:小心使用 drop 和 truncate,尤其没有备份的时候.否则哭都来不及使用上,想删除部分数据行用 delete,注意带上 where 子句. 回滚段要足够大. 想删除表,当然用 drop 想保留表而将所有数据删除. 如果和事务无关,用 truncate 即可. 如果和事务有关,或者想触发 trigger,还是用 delete. 如果是整理表内部的碎片,可以用 truncate 跟上 reuse stroage,再重新导入/插 入数据 附加 DDL,DML,DCL 区别： DDL is Data Definition Language statements. Some examples: CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed COMMENT - add comments to the data dictionary GRANT - gives users access privileges to database REVOKE - withdraw access privileges given with the GRANT command DML is Data Manipulation Language statements. Some examples: SELECT - retrieve data from the a database INSERT - insert data into a table UPDATE - updates existing data within a table DELETE - deletes all records from a table, the space for the records remain CALL - call a PL/SQL or Java subprogram EXPLAIN PLAN - explain access path to data LOCK TABLE - control concurrency DCL is Data Control Language statements. Some examples: COMMIT - save work done SAVEPOINT - identify a point in a transaction to which you can later roll back ROLLBACK - restore database to original since the last COMMIT SET TRANSACTION - Change transaction options like what rollback segment to use