一份笔记。
1. append hint
直接加载插入(direct load insert, direct-path insert)是运行 insert 语句的一种快速方法。对于加载大量数据行特别有用。
1.1 append hint 如何影响性能
- 数据被追加到表的末尾，而不是尝试使用表里已存在的空闲空间。
- 数据被直接写到数据文件，避开了 （写,buffer）缓冲、（读,cache）缓存。
- 引用完整性约束将不会考虑。
- 触发器的处理将不会执行。
后面两点可能导致数据逻辑损化，因此，如果表上允许引用完整性约束和触发器，Oracle 忽略 append hint 并以传统的 insert 方式加载数据。
1.1.1 append hint 对表大小的影响（高水位线, high water mark）
由于直接路径插入把数据追加到表的末尾，它们不断地增加表的高水位线，即使表里还有很多空闲空间。append hint 可能导致很大的表里包含了很多稀疏填充的块。这可以通过下面的收缩操作来管理：
- 导出数据、 truncate 表然后导入数据。
- 使用
create table ... as select(CTAS) 操作来构建新的表，让数据压缩，删除原始表，重命名新表来替代原始的。 - 使用 online table redefinition 操作来重新创建表。
- 使用 online segment shrink 操作压缩数据。
1.1.2 How the APPEND Hint Affects Redo Generation
If the database is running on NOARCHIVELOG mode, using just the APPEND hint will reduce redo generation. In reality, you will rarely run OLTP databases in NOARCHIVELOG mode, so what happens in ARCHIVELOG mode? In ARCHIVELOG mode, using the APPEND hint will not reduce redo generation unless the table is set to NOLOGGING.
1.2 副作用
有如下的副作用：
- 数据被追加到表的末尾。已存在的未使用空间不会被重用。如果你直接加载插入大量行，删除这些行，然后再次插入，这些被删除行的空间不会再被重用。如果你先
truncate表或分区，直接加载插入将重用已存在的空闲空间。 - 直接加载插入只适用于
insert into ... select ...。带有values子句的insert将使用常规的insert。 - 直接加载插入使用回滚段来维护被加载数据的索引。回滚段的大小将限制
insert的大小。在加载前 删除或 invalidate 索引可以避开这个问题，或使用 rowid range 技术来绕过它。 - 直接加载插入与
nologging选项一起使用可以让执行更快。这意味着，数据库从系统崩溃事件中恢复到 insert 直行前，然后继续往前，需要重新执行 insert 。 - 直接加载插入会以独占模式锁住表，其他会话不能插入、更新、删除数据或维护任何索引。
- 在直接加载插入后，执行 insert 的会话可以在同一张表上执行其他的直接加载插入，但不能在表上执行任何其他的动作（
select,update,delete, 常规insert），直到事务被提交。 - 引用完整性（referential integrity(foreign key)） 约束和触发器必须在运行直接加载插入前禁用。
- 直接加载插入不能发生在：
> * 索引组织表
> * 有 CLOB/Object 列的表；
> * 聚簇表
1.3 使用
INSERT /*+ APPEND*/
INTO my_table
SELECT * FROM my_other_table
为了确保 SQL 使用了直接加载插入，在执行计划里运行，将看到 LOAD AS SELECT 。
一般情况下，尽量用常规的 insert 。在这个问答里，Tom Kyte认为 *150,000 is a very small number of records. * 。
append_values hint
append_values hint 是 Oracle 11g R2 引入的。允许我们在使用带有 values 子句的 insert 语句时利用直接路径插入的优点。一般地，我们希望只在 insert 语句是 forall 语句的 bulk 操作的一部分时使用该 hint 。
FORALL i IN 1..numrecords
INSERT /*+ APPEND_VALUES */ INTO orderdata
VALUES(ordernum(i), custid(i), orderdate(i),shipmode(i), paymentid(i));
COMMIT;
该 hin 目前不能跟 SAVE EXCEPTIONS 一起使用。
参考资料
- Direct Load Insert
- Direct-Path INSERT
- APPEND Hint
- APPEND_VALUES Hint in Oracle Database 11g Release 2
- About Direct-Path INSERT
欢迎关注我的微信公众号: coderbee笔记，可以更及时回复你的讨论。
