SQL truncate 、delete与drop区别，转载自：编程笔记

相同点：

1.truncate和不带where子句的delete、以及drop都会删除表内的数据。
2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。

不同点：

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

2. delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 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   不能用于参与了索引视图的表。 

转载地址：http://www.cnblogs.com/8765h/archive/2011/11/25/2374167.html

minextents解析：
数据库的逻辑结构如下：数据库是由一系列表空间(tablespace)组成，表空间由若干段(segment)组成，段由若干区(extent)组成，区由若干块(block)组成
当在表空间中创建表时，系统先分配一个初始空间，这个空间大小由initial这个参数决定，此处为64KB,minextents 表示建好表后至少要分配几个区，maxextents 表示表空间最多能分配几个区

高水线解析：
所有的Oracle表都有一个容纳数据的上限（很象一个水库历史最高的水位），我们把这个上限称为“high water mark”或HWM。这个HWM是一个标记(专门有一个数据块用来记录高水标记等)，用来说明已经有多少数据块分配给这个表。HWM通常增长的幅度为一次5个数据块。

高水线的作用：HWM对数据库的操作有如下影响：
a) 全表扫描通常要读出直到HWM标记的所有的属于该表数据库块，即使该表中没有任何数据。
b) 即使HWM以下有空闲的数据库块，键入在插入数据时使用了append关键字，则在插入时使用HWM以上的数据块，此时HWM会自动增大。
因此高水线是oracle优化时一个重要的参数

通常，在我们在表空间中创建一张表时，Oracle就会初始分配一些blocks/extents给该表，之后，随着表中数据量的增加，也会相应的给表自动分配blocks/extents。
举例：
初始创建一张表HWM_TEST，通过查询DBA_SEGMENTS的列BLOCKS和EXTENTS可得到已分配该表的BLOCKS和EXTENTS：
SQL>conn ligle/ligle
Connected.
SQL>select blocks,empty_blocks,num_rows from dba_tables where table_name='HWM_TEST';
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
       103            9       8000       -->表HWM_TEST占用了103个block，9个block未使用，有8000行记录。
SQL>select blocks,extents,header_block from dba_segments where segment_name='HWM_TEST';
    BLOCKS    EXTENTS HEADER_BLOCK
---------- ---------- ------------
       112         14           83         -->Oracle总共为表HWM_TEST分配了112个block，14个extent，段头(Segment Header)所在块是83号。
SQL>delete from hwm_test where rownum < 3001;       -->删除部分数据。
3000 rows deleted.
SQL>commit;
Commit complete.                   -->提交。
现在我们再查询DBA_TABLES、DBA_SEGMENTS时，统计数据是没有任何变动的，需要分析一下：
SQL>analyze table hwm_test compute statistics;
Table analyzed.
SQL>select blocks,empty_blocks,num_rows from dba_tables where table_name='HWM_TEST';
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
       103            9       5000               -->经过analyze后，发现统计的记录数发生了变化，可数据块的数量并没有变化。
SQL>select blocks,extents,header_block from dba_segments where segment_name ='HWM_TEST';
    BLOCKS    EXTENTS HEADER_BLOCK
---------- ---------- ------------
       112         14           83
那如何确定表HWM_TEST究竟使用了多少个block呢？可以通过ROWID的来查询：
SQL>select count(distinct dbms_rowid.rowid_block_number(rowid)) "Used Blocks" from HWM_TEST;
Used Blocks
-----------
         63
那现在可以明白：Oracle分配了112个block给HWM_TEST表，其中有103个数据块保存用户数据，但实际上是有63个block是实际用户数据占用的block数。
降低高水线有多种方法：EXP/IMP、TRUNCATE或者使用MOVE，在10G，Oracle提供了shrink功能。使用哪种方法因人而异且需要根据实际情况，一般情况我习惯使用MOVE和SHRINK。
1、使用alter table ... shrink space
SQL>alter table HWM_TEST enable row movement;
Table altered.                           -->这是必须的步骤， 即将shrink的数据表，必须启用row movement。
SQL>alter table HWM_TEST shrink space;
Table altered.                           -->这就是收缩空间语句的庐山真面目，当然还可以加其他参数，如加cascade，就会把相应的索引段也进行收缩。
SQL>analyze table HWM_TEST compute statistics;
Table analyzed.                       -->再次分析表HWM_TEST。
SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='HWM_TEST';
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
        63            9       5000        -->现在BLOCKS值变为63了。
2、使用alert table ... move
SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='HWM_TEST';
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
        70            2       3001
SQL>alter table hwm_test move;
Table altered.
SQL>analyze table hwm_test compute statistics;
Table analyzed.
SQL>select blocks,empty_blocks,num_rows from dba_tables where table_name='HWM_TEST';
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
        43            5       3001                               
使用alter table ... shrink space/move均可收缩表的高水线

转载地址：http://blog.itpub.net/21416913/viewspace-718643/