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);
?