Oracle Hint /*+APPEND*/插入性能总结

oracle append用法

Oracle中的APPEND用法主要用于提高数据插入的效率。

• 基本用法：在使用了APPEND选项后，插入数据会直接加到表的最后面，而不会在表的空闲块中插入数据。这种做法不需要寻找freelist中的free block，从而避免了在高水位线（HWM）下面寻找可插入的数据块，因此可以显著提高数据插入的速度。APPEND属于direct insert，这意味着在归档模式下使用APPEND+TABLE NOLOGGING会大量减少日志的生成，而在非归档模式下，APPEND也会大量减少日志的生成。此外，APPEND方式插入只会产生很少的undo，从而进一步提高了效率。

• 适用场景：当需要向表中快速插入大量数据时，使用APPEND可以显著提高性能。例如，在INSERT INTO /*+ append*/ TABLEA SELECT * FROM TABLEB语句中，如果SELECT出来的数据量很大，使用APPEND可以提高效率。

• 注意事项：

  1. 使用APPEND时，会在目标表上加一个lmode=6的排它锁（TM enqueue），这会导致在APPEND操作进行时，其他用户无法对表进行DML操作。
  2. 在不同版本的Oracle中，APPEND的用法有所不同。例如，在10g版本中，APPEND只能用于INSERT INTO..VALUES SELECT语句；而在11gR2版本中，INSERT VALUES也可以支持APPEND_VALUES。
  3. 使用APPEND/APPEND_VALUES时，必须先提交事务，否则查询会报错ORA-12838。
  4. 避免在单行insert中使用APPEND_VALUES，因为这可能导致极大的空间浪费。结合使用绑定变量和批量提交可以更有效地利用空间。

一、使用APPEND背景

业务过程中有类似把B表千万级数据复制到A表，但是这个语句的效率特别差，需要1小时往上才能插入成功。

insert into A select * from B

二、使用APPEND效果

效果提升到了10来钟

insert   into  /*+APPEND*/ A select * from B

三、查看高水位表使用情况

set linesize 258 pagesize 999 
col WASTED_PERCENT format a20 
col owner for a30 
col table_name for a30 
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 
SELECT owner,
table_name, 
ROUND(BLOCKS * 8192 / 1024 / 1024, 2) "total_size(M)", 
ROUND(num_rows * AVG_ROW_LEN / 1024 / 1024, 2) "used_size(M)", 
ROUND(((BLOCKS * 8192 / 1024 / 1024) - 
(num_rows * AVG_ROW_LEN / 1024 / 1024)), 
2) "wasted_size(M)", 
ROUND(ROUND(((BLOCKS * 8192 / 1024 / 1024) - 
(num_rows * AVG_ROW_LEN / 1024 / 1024)), 
2) / ROUND(BLOCKS * 8192 / 1024 / 1024, 2), 
2) * 100 || '%' wasted_percent, 
LAST_ANALYZED, 
NUM_ROWS 
FROM dba_tables 
WHERE owner='&owner' AND table_name in ('table_name') 
ORDER BY 6 desc;

 执行结果，发现浪费了40%的存储空间

四、APPEND导致极大的空间浪费如何处理

如果空间浪费过多会导致当前表的处理性能下降，一直APPEND的意义不大。哪有什么方式来解决呢？

1、清空表

truncate table A

2、使用表分析

DBMS_STATS.GATHER_TABLE_STATS简介，简单的说，就是收集表和索引的信息，CBO根据这些信息决定SQL最佳的执行路径。通过对表的分析，可以产生一些统计信息，通过这些信息oracle的优化程序可以进行优化。

exec dbms_stats.gather_table_stats(ownname=>'root',tabname=>'table_name',ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,NO_INVALIDATE=> false,cascade=> true, method_opt =>'FOR ALL COLUMNS SIZE AUTO',degree=> 8);