Oracle資料載入速度優化

wzq609發表於2017-11-28
Oracle優化
【說明】本文件介紹如何將大量的資料載入到一張表中，並需要儘可能地插入新資料；
【解決方法】
  • 將表的日誌屬性設定為NOLOGGING。
  • 在使用子查詢來確定要插入哪些記錄時，在查詢中加入INSERT /*+ APPEND */
  • 使用VALUES子句的查詢中加入INSERT /*+ APPEND */
  • 使用CREATE TABLE ... AS SELECT語句。

【實驗測試】
1、建立測試表
SQL> create table T01  as select * from all_users where 1=0;

2、檢視錶的狀態
SQL> select table_name, logging  from user_tables    where table_name = 'T01';

TABLE_NAME       LOG
-------------   ---------------- 
T01          YES

3、修改表為NOLOGGING
alter table T01 nologging;

4、建立表TT並插入大量的資料，用於等下對比用；
SQL> create table TT as select * from all_users ;
SQL> INSERT INTO TT SELECT * FROM TT;  (反覆執行)
SQL> SELECT COUNT(1) FROM TT;  
  COUNT(1)
----------
   1245184

5、建立對比表T02，沒有進行任何優化操作；
SQL> create table T02 as select * from all_users where 1=0;

6、進行資料的插入測試

採用直接路徑操作與NOLOGGING的方法：消耗的時間時6.15s
SQL> timing start kk 
SQL> insert /*+ APPEND */ into t01 select * from TT; 
1245184 rows created.
SQL>  timing show;
Elapsed: 00:00:06.15

常規的方法，顯示需要的時間為：15.39
SQL> timing start kk  ;
SQL> insert /*+ APPEND */ into t02 select * from TT;
1245184 rows created.
SQL> timing show;
  timing for: kk
Elapsed: 00:00:15.39

總結：經過測試通過直接路徑插入和nologging的方式確實可以明顯的提升資料的載入速度；
1、因為通過nologging可以產生最少量的重做；
2、避開了緩衝區快取，直接將資料載入到資料檔案中，提高了載入的效率。

但是同時這種方法也存在著以下的問題，畢竟不能什麼好事都佔完吧。不然oracle早就設定成預設的配置了；
  • 當使用直接路徑向表中插入資料的時候，Oracle將會自動把心的資料行插入到高水位線之上，這可能導致表佔用大量的磁碟空間；
  • 確保提交了通過直接路徑載入的資料，否則將不能看到這些資料。因為Oracle會報0RA-12838的錯誤，表明直接路徑載入的資料在能夠查詢之前必須先提交。
  • 如果T01資料插入之後（插入之前有進行備份），系統由於介質故障需要進行恢復，這個時候把表RESTORE到故障之前，然後再RECOVER恢復到故障點，這個時候表T01的資料是不能被恢復的；

【總結】以上這種資料的載入方式並不適用於生產過程中的重要業務資料，比較適合一些類似報表類或中間庫的資料抽取或比對資料的抽取；

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12679300/viewspace-2147868/，如需轉載，請註明出處，否則將追究法律責任。

相關文章