EXP EXPDP数据导入本地性能测试

最新推荐文章于 2022-06-11 20:17:55 发布

lbyyy

最新推荐文章于 2022-06-11 20:17:55 发布

阅读量644

点赞数

分类专栏： oracle

oracle 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

EXP，EXPDP数据导入本地性能测试

from： http://f.dataguru.cn/thread-311247-1-1.html

本文编写背景：
      大家都知道，Oracle数据库有两种比较方便的导出导入工具，那就是exp/imp和expdp/impdp。熟悉此工具的人也会了解到，exp/imp的使用（服务器端和客户端都可以执行，并且备份文件可以在客户端产生）；expdp/impdp的使用（服务器端和客户端都可以执行，备份文件只能存在服务器里面）。
      但鉴于导出导入工具的特点，我们就想能否通过expdp将远端数据库数据导入到本地库中呢？当然可以！下面我们就一起来做一个测试吧。
测试说明：
目的：将远端数据库服务器(IP为65)的数据导入到本地(IP为67)dmp文件中
其中用到服务器(65)一个用户(user_exp)的一张表做测试(t_exp)
Create tablespace tbs_exp datafile ‘D:tbs_exp.dbf’ size 10240m autoextend off;
Create user user_exp identified by user_exp default tablespace tbs_exp;
Grant connect,resource,unlimited tablespace to user_exp;
Conn user_exp/user_exp
create table t_exp as
select * from user_tablespaces;
declare
begin
  for i in 1 .. 10000000 loop
insert into t_exp
   (tablespace_name, block_size, initial_extent,MIN_EXTENTS )
values
   ('tablespace_name',0,0,i);
  end loop;
  commit;
end;
/
1．EXP工具数据导入本地步骤
直接通过以下代码将服务器数据导入到本地：
exp user_exp/user_exp@orcl_65 file=d:exp_01.dmp log=d:exp_01.log tables=(t_exp)
2．EXPDP工具数据导入本地步骤
第一步：本地建立导出用户
Create user db_exp identified by db_exp;
Grant connect,resource,create database link to db_exp;
第二步：本地创建directory,并授权导出用户使用
在操作系统上创建direxp目录
create or replace directory DIR_EXP as 'D:direxp';
grant read,write on directory DIR_EXP to db_exp;
第三步：，并创建db link 链接
-- Create database link
create database link to_exp_65
  connect to user_exp identified by user_exp
  using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.48.1.65)(PORT = 1521))
(CONNECT_DATA =
   (SERVER = DEDICATED)
   (SID = orcl)
)
  )';
Select 1 from dual@to_exp_65;
第四步：导出代码
      expdp db_exp/db_exp@orcl_67 directory=DIR_EXP dumpfile=expdp_01.dmp logfile=expdp_01.log tables=(t_exp) network_link=to_exp_65
其他备注：
Db_exp 本地用户，用来连接远端数据库服务器
Dir_exp 本地db_exp用户下的 directory 用户本地指定expdp写入目录，这里是D盘direxp文件夹
To_exp_65 本地db_exp用户下的 db link ，用来连接远端数据库服务器
3．EXP，EXPDP工具数据导入本地性能对比
性能测试结果：
Exp导出过程如下：

C:\documents and SettingsAdministrator>prompt $P$G$T$G

C:\documents and SettingsAdministrator>16:10:55.37>
C:\documents and SettingsAdministrator>16:11:05.92>exp user_exp/user_exp@orcl_6
5 file=d:exp_01.dmp log=d:exp_01.log tables=(t_exp)
Export: Release 10.2.0.1.0 - Production on 星期三 10月 12 16:11:07 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
服务器使用 AL32UTF8 字符集 (可能的字符集转换)
即将导出指定的表通过常规路径...
. . 正在导出表                         T_EXP导出了 10000007 行
成功终止导出, 没有出现警告。
C:\documents and SettingsAdministrator>16:17:51.04>
用时：6分钟46秒
Expdp导出过程如下：
C:\documents and SettingsAdministrator>16:22:15.25>expdp db_exp/db_exp@orcl_67
directory=DIR_EXP dumpfile=expdp_01.dmp logfile=expdp_01.log tables=(t_exp) netw
ork_link=to_exp_65
Export: Release 10.2.0.1.0 - Production on 星期三, 12 10月, 2011 16:22:16
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
启动 "DB_EXP"."SYS_EXPORT_TABLE_01":  db_exp/********@orcl_67 directory=DIR_EXP
dumpfile=expdp_01.dmp logfile=expdp_01.log tables=(t_exp) network_link=to_exp_65
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 336 MB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
. . 导出了 "USER_EXP"."T_EXP"                         285.8 MB 10000007 行
已成功加载/卸载了主表 "DB_EXP"."SYS_EXPORT_TABLE_01"
******************************************************************************
DB_EXP.SYS_EXPORT_TABLE_01 的转储文件集为:
  D:\DIREXPEXPDP_01.DMP
作业 "DB_EXP"."SYS_EXPORT_TABLE_01" 已于 16:23:35 成功完成
Cocuments and SettingsAdministrator>16:23:36.46>
用时：1分钟21秒
另：Expdp导入到远程数据库服务端所耗时间如下：
C:\documents and SettingsAdministrator>16:27:40.92>expdp user_exp/user_exp@orcl
_65 directory=DATA_FILE_DIR dumpfile=expdp_02.dmp logfile=expdp_02.log
Export: Release 10.2.0.1.0 - Production on 星期三, 12 10月, 2011 16:27:41
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
启动 "USER_EXP"."SYS_EXPORT_SCHEMA_01":  user_exp/********@orcl_65 directory=DAT
A_FILE_DIR dumpfile=expdp_02.dmp logfile=expdp_02.log
正在使用 BLOCKS 方法进行估计...
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 336 MB
处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT
. . 导出了 "USER_EXP"."T_EXP"                         285.9 MB 10000007 行
已成功加载/卸载了主表 "USER_EXP"."SYS_EXPORT_SCHEMA_01"
******************************************************************************
USER_EXP.SYS_EXPORT_SCHEMA_01 的转储文件集为:
  E:ORACLEPRODUCT10.2.0DB_1DEMOSCHEMASALES_HISTORYEXPDP_02.DMP
作业 "USER_EXP"."SYS_EXPORT_SCHEMA_01" 已于 16:24:52 成功完成
C:\documents and SettingsAdministrator>16:27:59.70>
用时：0分钟19秒

可见，从远端服务器导数据到本地，使用数据泵也是可以完成的，同时效率还是远远高于普通的exp导出

------------------------------------------------------------------------------------------------------------------------------

EXP常规模式、EXP直接路径模式和EXPDP三种方式导出的性能对比

from： http://blog.163.com/card_2005/blog/static/11781036820098225542220/

1.首先是EXP的常规路径导出：

exp zhejiang/zhejiang file=/data1/zj_regular.dmp buffer=20480000

常规EXP导出方式执行了1小时24分钟。

2.直接路径导出方式：

exp zhejiang/zhejiang file=/data1/zj_direct.dmp buffer=20480000

recordlength=65535 direct=y

直接路径导入用时18分钟，比常规路径导出速度有一个明显的提高。

3.数据泵的导出速度。

expdp zhejiang/zhejiang dumpfile=zj_datapump.dp directory=d_test schemas=zhejiang

数据泵的导出时间仅用了14分钟，比直接路径导入方式还快了20%多。而且观察三个导出文件的大小可以发现，导出速度越快对应的文件也越小，其中数据泵的导出方式得到的文件要比EXP方式小将近1.5G。

IMP和IMPDP导入性能对比

1.IMP的导入速度：

imp zhejiang/zhejiang file=/data1/zj_regular.dmp full=y buffer=20480000 log=/data1/zj_regular.log

IMP导入花费了3小时17分钟，

2.IMPdp的导入速度：

impdp zhejiang/zhejiang DUMPFILE=zj_datapump.dp DIRECTORY=d_test FULL=y LOGFILE=zj_datapump.log

数据泵的导入操作居然花了3个小时8分钟的时间，和IMP的导入速度十分接近，看来并非所有情况下都像Oracle描述的那样，数据泵的导入比普通导入效率有大幅度的提高。

上面一篇文章的测试中发现IMPDP的导入速度和IMP导入速度相差无几。而Oracle在介绍数据泵的时候，提到IMPDP的导入速度最高是IMP的10倍。不过好在IMPDP还是可以优化调整的，那就是通过设置PARALLEL来提高IMPDP的并行度。

首先还是看一下CPU的数量：

SQL> show parameter cpu

由于数据库服务器的CPU个数为2，下面尝试设置PARALLEL为2来进行导入

impdp zhejiang/zhejiang DUMPFILE=zj_datapump.dp DIRECTORY=d_test FULL=y LOGFILE=zj_datapump.log parallel=2

采用并行度为2的导入方式，发现速度果然提高了很多。并行度为1的导入速度是3小时8分钟，而现在用了不到2个半小时。

由于并行度设置不应该超过CPU数的2倍，因此尝试平行度3和4的导入，导入时间和并行度2十分接近。看来已经无法再使用通过提高并行度的方法来提高性能了。

1.先看直接导出的性能：

$ expdp zhejiang/zhejiang directory=d_test dumpfile=zhejiang.dp

整个导出操作大概用了14分半，

2.尝试使用并行度2进行导出，这时仍然设置一个导出的数据文件：

$ expdp zhejiang/zhejiang directory=d_test dumpfile=zhejiang_p2_1file.dp parallel=2

整个导入过程不到14分钟，不过这个性能的提升实在不是很明显。不过这是有原因的，由于设置了并行度，两个进程在同时执行导出操作，但是二者要将导出的数据写入同一个数据文件中，因此必然会导致资源的争用

3.仍然使用并行度2，但是同时设置两个数据文件再次检查导出性能：

$ expdp zhejiang/zhejiang directory=d_test dumpfile=zhejiang_p2_2file1.dp,zhejiang_p2_2file2.dp parallel=2

这次导出仅仅用了10分半，导出的效率大大的提高。

4.测试一下并行度4，分别导出到4个数据文件中：

用了9分钟整导出完成，设置成并行度4仍然可以获得一定的性能提升，但是并不明显了，这主要是由于整个性能的瓶颈已经不是单个进程的处理能力，多半性能的瓶颈已经变成了磁盘IO瓶颈，此时单单靠增加并行度已经无法明显提升性能了。

----------------------------------------------------------------------------------------------------------------------------------

expdp和exp性能对比与原理分析

......

exp和expdp原理对比
1.exp/imp客户端程序，受网络，磁盘的影响；impdp/expdp服务器端程序，只受磁盘的影响

2.假设文件把文件导出在服务端，exp常规方式是使用SELECT的方式查询数据库中的数据，是需要通过buffer cache并通
过sql的语句处理层再转移至exp的导出文件（即要经过sga,pga才传输到客户端，而exp直接路径则是直接从磁盘上将数
据转移至exp的导出文件，所以速度更快；而expdp是exp的升级版，相当于exp + direct mode + parallel（可能对导
出文件还进行了压缩，这是从导出文件的对比结果发现的，具体不清楚）。所有从总的来说，expdp比exp提供了并行的
方式（写入多个导出文件）

impdp的并行机制也让其比imp快很多

----------------------------------------------------------------------------------------------------------------------------

【IMPDP】当导入数据时遭遇表已存时的应对手段——TABLE_EXISTS_ACTION参数

[转自：http://space.itpub.net/519536/viewspace-631445]

当使用IMPDP完成数据库导入时，如遇到表已存在时，Oracle提供给我们如下四种处理方式：
a.忽略（SKIP，默认行为）；
b.在原有数据基础上继续增加（APPEND）；
c.先DROP表，然后创建表，最后完成数据插入（REPLACE）；
d.先TRUNCATE，再完成数据插入（TRUNCATE）。

真实感受一下这几种方式的效果，供参考。

1.IMPDP在线帮助中关于TABLE_EXISTS_ACTION参数的描述
TABLE_EXISTS_ACTION Action to take if imported object already exists.
Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.

2.Oracle官方文档中的描述
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#sthref365
Oracle官方文档是根本，需研读。

3.使用EXPDP生成一份dump文件
sec@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=`date +"%Y%m%d%H%M%S"`_sec.dmp logfile=`date +"%Y%m%d%H%M%S"`_sec.log

Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 April, 2010 10:29:17

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_01": sec/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "SEC"."T" 2.259 MB 24360 rows
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/20100401102917_sec.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:29:20

4.分别使用四中方式真实的感知一下具体效果
用到的IMPDP语句统一汇总在这里，方便参考。
impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=SKIP
impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=APPEND
impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=REPLACE
impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=TRUNCATE

1）默认行为SKIP
sec@ora10g> select count(*) from t;

COUNT(*)
----------
24360

sec@secDB /expdp$ impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 April, 2010 10:50:41

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SEC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "SEC"."T" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 10:50:42

上面的行为与下面一条显示的指定“SKIP”效果是一样的，不赘述。
sec@secDB /expdp$ impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=SKIP

2）APPEND方式
sec@secDB /expdp$ impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=APPEND

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 April, 2010 10:51:32

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=APPEND
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SEC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39152: Table "SEC"."T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SEC"."T" 2.259 MB 24360 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 10:51:33

此时表T的记录数翻倍。
sec@ora10g> select count(*) from t;

COUNT(*)
----------
48720

3）REPLACE方式
sec@secDB /expdp$ impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=REPLACE

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 April, 2010 10:54:28

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=REPLACE
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SEC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SEC"."T" 2.259 MB 24360 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 10:54:29

sec@ora10g> select count(*) from t;

COUNT(*)
----------
24360

4）TRUNCATE方式
sec@secDB /expdp$ impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=TRUNCATE

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 April, 2010 10:54:55

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=TRUNCATE
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SEC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39153: Table "SEC"."T" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SEC"."T" 2.259 MB 24360 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 10:54:56

sec@ora10g> select count(*) from t;

COUNT(*)
----------
24360

5.小结
IMPDP的TABLE_EXISTS_ACTION参数相比IMP工具是一个进步，我们的控制手段得到加强，莫要固步自封。

Good luck.

secooler

10.04.06

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 不能用于参与了索引视图的表。

一、delete

1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。

2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。

3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

二、truncate

1、truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。

3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。

4、truncatetable不能用于参与了索引视图的表。

三、drop

1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

总结：

1、在速度上，一般来说，drop> truncate > delete。

2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；如果想删除表，当然用drop；如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；如果和事务有关，或者想触发trigger，还是用delete；如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

-- The End --