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可以提高效率。 -
注意事项：
- 使用APPEND时，会在目标表上加一个lmode=6的排它锁（TM enqueue），这会导致在APPEND操作进行时，其他用户无法对表进行DML操作。
- 在不同版本的Oracle中，APPEND的用法有所不同。例如，在10g版本中，APPEND只能用于
INSERT INTO..VALUES SELECT语句；而在11gR2版本中，INSERT VALUES也可以支持APPEND_VALUES。 - 使用APPEND/APPEND_VALUES时，必须先提交事务，否则查询会报错ORA-12838。
- 避免在单行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);