Insert
直接路径法
这种方法不去查找已有块中的空间, 它直接从高水位之上开始插入数据. 直接使用的是 nologging模式, 记住默认情况下通过直接路径插入进行加载的表上的索引仍然是会产生undo 和 redo. 而表的数据因为 nologging 不会产生redo 和 undo , 仅仅是对数据字典来进行修改.
insert /*+ append */ into table select * from hr.employees nologging;
insert /*+ append_values */ into dual(dumy) values(‘Y’);
然而, 快速方法有下面几个问题.
- 在任何给定的时点一张表上只能有一个直接路径写入
- 数据将被插入到高水位之上, 因此任何高水位之下的可用空间都不能在直接路径插入中使用
- 在开始之后进行插入的会话不能对表做任何事情(甚至是对表进行select), 指导进行提交或回滚.
- 不支持一些不太常用的数据结构(对象类型, 索引组织表等)
- 不支持引用约束(也就是说它们将导致通过传统方法进行插入)
多表插入
条件插入
以上两个, 在别的 blog 中都有介绍过
DML 错误日志
这个功能提供了一种机制来使得你的一百万行的数据插入不会仅仅由于几行有问题而失败, 10g以后引入, 类似于 SQL*Loader的错误日志功能, 它的基本原理是将任何可能导致语句失败的记录转移, 放入到一张错误记录表中. 另外, LOG ERRORS 子句在其他 DML 语句中同样适用(update, delete, merge)
1) 适用 DBMS_ERRLOG.CREATE_ERROR_LOG 来创建爱你错误日志表
2) 在 INSERT 语句中声明 LOG ERRORS 子句
execute DBMS_ERRLOG.CREATE_ERROR_LOG(‘big_emp’, ‘big_emp_bad’);
例如:
insert into big_emp (employee_id, first_name, last_name, hire_date, email, department_id, job_id)
values (303, ‘bob’, ‘loblaw’, ‘01-jan-10’, ‘bob@yflawyer.com’, ‘2a45’, 1)
log errors into big_emp_bad;
这样, 如果出现插入错误, 那么数据就会插入到 big_emp_bad 表中, 然后, 我们可以修改插入数据的脚本, 根据 big_emp_bad 表中不能插入的内容, 再重新插入, 因为刚刚插入时, 如果遇到错误, 那么就会回滚, 所以, 很有可能1条数据都没有插入.
尽管 DML 的错误记录非常强大, 你需要注意下面这些警告
- LOG ERRORS 子句不引起隐式提交, 错误记录的插入是由一个自治事务来处理的, 也就是说, 即使返回了错误并将坏记录插入到了ERRORS表中, 你也可以提交或回滚插入到基表中的记录, 即使事务进行了回滚, 载入到 ERRORS 表中的记录也将会保留.
- LOG ERRORS 子句不会禁用 APPEND 提示. 如果使用了 APPEND提示, 对于基表的插入将会使用直接路径写入机制来完成. 但是, 任何到ERRORS表的写入都不会使用直接路径写入. 这通常并不是问题, 因为你很少会将大量数据载入到 ERRORS表中.
- 违反唯一键或索引约束的直接路径插入运算将会引起语句失败并进行回滚.
- LOG ERRORS 子句不会追踪 LOG, LONG 或对象类型列的值, 它可以与包含这些不支持的数据类型列的表一起使用但是这些不支持的数据类型的列将不会插入到ERRORS表中. 所以, 这一项还是要注意, 看是否还继续使用 ERRORS, 如果还是需要使用, 必须使用 CREATE_ERROR_LOG 存储过程的 SKIP_UNSUPPORTED参数.
update
如果需要更新很多内容, 比如 某个应用需要每晚更新10亿数据, 最好的办法是 首先截断然后重新加载. 如果不行, 可以使用 create table as select 来创建一张新表.
大量更新不是一个好主意.
DELETE
与大量更新类似, 大量删除通常也不是好主意. 通常重建一张表或一个分区比删除表中很大百分比的数据行要更快. 重建的方法最大的弊端就是在重建期间必须对对象进行保护以阻止其他修改. 基本思想
1) 创建一张临时表
2) 将不需要删除的记录放到临时表中 (可以使用 append 方法)
3) 重建相关对象(索引, 约束, 授权以及触发器)
4) 重命名表
另外, 如果Bussiness 逻辑准许我们 truncate , 那么这也是一个很好的解决方案
MERGE
语法这里就不介绍了, MERGE 提供了最大的灵活性, 因此不要忽略这个单独的 SQL 语句在一次执行过程中可以进行多种DML运算来运算这样一个事实.