Oracle 【直接載入】全方位解析與效能優化
1.比較sql*loader使用conventional傳統 和direct直接 方式載入的效能差異。
一、概念
1.直接載入優勢
（1）直接載入比傳統載入效率要高
（2）不掃描原來的空資料塊
（3）不需要sql解析，減少系統的負載
（4）不經過SGA
（5）不走DBWR程式，走自己的專屬程式，所以速度快
2.直接載入限制
（1）不能載入簇表
（2）鎖定整個表，在表上有活動事務的時候不能載入
3.直接載入特點
（1）直接載入是在所有資料塊後面載入新資料塊，修改高水位線，不掃描原來的空資料塊。
（2）直接載入只產生一點點的管理redo，因為要修改資料字典（也可以講不產生redo）。
（3）回滾，如果載入失敗把新分配資料塊抹掉就行了。
（4）無需SGA，無需SQL解析，無需DBWR程式
二、實驗
1.現在我們已經定義了leo_test_sqlload;
LS@LEO> select count(*) from leo_test_sqlload; 現在表裡沒有記錄數
COUNT(*)
----------
0
LS@LEO> select segment_name,extent_id,bytes from user_extents where segment_name='LEO_TEST_SQLLOAD'; 現在分配了1個區
SEGMENT_NAME EXTENT_ID BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_TEST_SQLLOAD 0 65536
[oracle@secdb1 ~]$ sqlldr userid=ls/ls control=leo_test.ctl data=leo_test.data log=leo_test.log 傳統方式載入資料
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Sep 9 10:29:42 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
LS@LEO> select count(*) from leo_test_sqlload; 已經成功載入了10萬條資料
COUNT(*)
----------
100000
LS@LEO> select segment_name,extent_id,bytes from user_extents where segment_name='LEO_TEST_SQLLOAD'; 10萬條資料佔用23個資料塊
SEGMENT_NAME EXTENT_ID BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_TEST_SQLLOAD 0 65536
LEO_TEST_SQLLOAD 1 65536
LEO_TEST_SQLLOAD 2 65536
LEO_TEST_SQLLOAD 3 65536
LEO_TEST_SQLLOAD 4 65536
LEO_TEST_SQLLOAD 5 65536
LEO_TEST_SQLLOAD 6 65536
LEO_TEST_SQLLOAD 7 65536
LEO_TEST_SQLLOAD 8 65536
LEO_TEST_SQLLOAD 9 65536
LEO_TEST_SQLLOAD 10 65536
LEO_TEST_SQLLOAD 11 65536
LEO_TEST_SQLLOAD 12 65536
LEO_TEST_SQLLOAD 13 65536
LEO_TEST_SQLLOAD 14 65536
LEO_TEST_SQLLOAD 15 65536
LEO_TEST_SQLLOAD 16 1048576
LEO_TEST_SQLLOAD 17 1048576
LEO_TEST_SQLLOAD 18 1048576
LEO_TEST_SQLLOAD 19 1048576
LEO_TEST_SQLLOAD 20 1048576
LEO_TEST_SQLLOAD 21 1048576
LEO_TEST_SQLLOAD 22 1048576
23 rows selected.
LS@LEO> delete from leo_test_sqlload; 刪除10萬條資料
100000 rows deleted.
LS@LEO> commit; 提交
Commit complete.
LS@LEO> select segment_name,extent_id,bytes from user_extents where segment_name='LEO_TEST_SQLLOAD';
SEGMENT_NAME EXTENT_ID BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_TEST_SQLLOAD 0 65536
LEO_TEST_SQLLOAD 1 65536
LEO_TEST_SQLLOAD 2 65536
LEO_TEST_SQLLOAD 3 65536
LEO_TEST_SQLLOAD 4 65536
LEO_TEST_SQLLOAD 5 65536
LEO_TEST_SQLLOAD 6 65536
LEO_TEST_SQLLOAD 7 65536
LEO_TEST_SQLLOAD 8 65536
LEO_TEST_SQLLOAD 9 65536
LEO_TEST_SQLLOAD 10 65536
LEO_TEST_SQLLOAD 11 65536
LEO_TEST_SQLLOAD 12 65536
LEO_TEST_SQLLOAD 13 65536
LEO_TEST_SQLLOAD 14 65536
LEO_TEST_SQLLOAD 15 65536
LEO_TEST_SQLLOAD 16 1048576
LEO_TEST_SQLLOAD 17 1048576
LEO_TEST_SQLLOAD 18 1048576
LEO_TEST_SQLLOAD 19 1048576
LEO_TEST_SQLLOAD 20 1048576
LEO_TEST_SQLLOAD 21 1048576
LEO_TEST_SQLLOAD 22 1048576
23 rows selected.
有人會問我們把資料都刪除了為什麼還佔用空間呢，呵呵，oracle的delete操作不回收空間，只是把自己的記錄標記為刪除，實際呢還佔用的空間不釋放
[oracle@secdb1 ~]$ sqlldr userid=ls/ls control=leo_test.ctl data=leo_test.data log=leo_test.log 第二次傳統方式載入資料
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Sep 9 10:29:42 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
LS@LEO> select count(*) from leo_test_sqlload; 已經成功載入了10萬條資料
COUNT(*)
----------
100000
LS@LEO> select segment_name,extent_id,bytes from user_extents where segment_name='LEO_TEST_SQLLOAD';
SEGMENT_NAME EXTENT_ID BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_TEST_SQLLOAD 0 65536
LEO_TEST_SQLLOAD 1 65536
LEO_TEST_SQLLOAD 2 65536
LEO_TEST_SQLLOAD 3 65536
LEO_TEST_SQLLOAD 4 65536
LEO_TEST_SQLLOAD 5 65536
LEO_TEST_SQLLOAD 6 65536
LEO_TEST_SQLLOAD 7 65536
LEO_TEST_SQLLOAD 8 65536
LEO_TEST_SQLLOAD 9 65536
LEO_TEST_SQLLOAD 10 65536
LEO_TEST_SQLLOAD 11 65536
LEO_TEST_SQLLOAD 12 65536
LEO_TEST_SQLLOAD 13 65536
LEO_TEST_SQLLOAD 14 65536
LEO_TEST_SQLLOAD 15 65536
LEO_TEST_SQLLOAD 16 1048576
LEO_TEST_SQLLOAD 17 1048576
LEO_TEST_SQLLOAD 18 1048576
LEO_TEST_SQLLOAD 19 1048576
LEO_TEST_SQLLOAD 20 1048576
LEO_TEST_SQLLOAD 21 1048576
LEO_TEST_SQLLOAD 22 1048576
23 rows selected.
使用傳統方式載入資料，會掃描原來的空資料塊，會把新載入的資料插入到空資料塊內，看我們還是使用原來的23個資料塊
LS@LEO> delete from leo_test_sqlload; 這是第二次刪除10萬條資料
100000 rows deleted.
LS@LEO> commit; 提交
Commit complete.
LS@LEO> select segment_name,extent_id,bytes from user_extents where segment_name='LEO_TEST_SQLLOAD';
SEGMENT_NAME EXTENT_ID BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_TEST_SQLLOAD 0 65536
LEO_TEST_SQLLOAD 1 65536
LEO_TEST_SQLLOAD 2 65536
LEO_TEST_SQLLOAD 3 65536
LEO_TEST_SQLLOAD 4 65536
LEO_TEST_SQLLOAD 5 65536
LEO_TEST_SQLLOAD 6 65536
LEO_TEST_SQLLOAD 7 65536
LEO_TEST_SQLLOAD 8 65536
LEO_TEST_SQLLOAD 9 65536
LEO_TEST_SQLLOAD 10 65536
LEO_TEST_SQLLOAD 11 65536
LEO_TEST_SQLLOAD 12 65536
LEO_TEST_SQLLOAD 13 65536
LEO_TEST_SQLLOAD 14 65536
LEO_TEST_SQLLOAD 15 65536
LEO_TEST_SQLLOAD 16 1048576
LEO_TEST_SQLLOAD 17 1048576
LEO_TEST_SQLLOAD 18 1048576
LEO_TEST_SQLLOAD 19 1048576
LEO_TEST_SQLLOAD 20 1048576
LEO_TEST_SQLLOAD 21 1048576
LEO_TEST_SQLLOAD 22 1048576
23 rows selected.
delete還是不回收空間，我們依然佔用著23個資料塊
Sun Sep 9 20:25:04 CST 2012
[oracle@secdb1 ~]$ sqlldr userid=ls/ls control=leo_test.ctl data=leo_test.data log=leo_test.log direct=true 直接方式載入資料
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Sep 9 20:25:05 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 100000.
[oracle@secdb1 ~]$ date
Sun Sep 9 20:25:14 CST 2012 看我們才用了10秒時間，比傳統載入效率高很多
LS@LEO> select segment_name,extent_id,bytes from user_extents where segment_name='LEO_TEST_SQLLOAD';
SEGMENT_NAME EXTENT_ID BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_TEST_SQLLOAD 0 65536
LEO_TEST_SQLLOAD 1 65536
LEO_TEST_SQLLOAD 2 65536
LEO_TEST_SQLLOAD 3 65536
LEO_TEST_SQLLOAD 4 65536
LEO_TEST_SQLLOAD 5 65536
LEO_TEST_SQLLOAD 6 65536
LEO_TEST_SQLLOAD 7 65536
LEO_TEST_SQLLOAD 8 65536
LEO_TEST_SQLLOAD 9 65536
LEO_TEST_SQLLOAD 10 65536
LEO_TEST_SQLLOAD 11 65536
LEO_TEST_SQLLOAD 12 65536
LEO_TEST_SQLLOAD 13 65536
LEO_TEST_SQLLOAD 14 65536
LEO_TEST_SQLLOAD 15 65536
LEO_TEST_SQLLOAD 16 1048576
LEO_TEST_SQLLOAD 17 1048576
LEO_TEST_SQLLOAD 18 1048576
LEO_TEST_SQLLOAD 19 1048576
LEO_TEST_SQLLOAD 20 1048576
LEO_TEST_SQLLOAD 21 1048576
LEO_TEST_SQLLOAD 22 1048576
LEO_TEST_SQLLOAD 23 1048576
LEO_TEST_SQLLOAD 24 1048576
LEO_TEST_SQLLOAD 25 1048576
LEO_TEST_SQLLOAD 26 1048576
LEO_TEST_SQLLOAD 27 1048576
LEO_TEST_SQLLOAD 28 1048576
LEO_TEST_SQLLOAD 29 1048576
LEO_TEST_SQLLOAD 30 1048576
31 rows selected.
哈哈我們現在發現同樣的10萬條記錄，竟然佔用了31個資料塊，傳統載入只用了23個，而我們使用直接載入到多了8個資料塊，有個上面的概念大家是不是有所啟發呢，對了直接載入不掃描原來的空資料塊，會在所有資料塊之後載入新的資料塊插入資料修改高水位線HWM,當提交事務之後，把高水位線移到新資料之後，其他的使用者就可以看見了。
2.比較直接載入使用conventional 和direct方式產生的redo大小（可以通過/*+ append */模擬直接載入）。
明確：直接載入與logging配合下並不能顯著的減少redo日誌量
直接載入與nologging配合下可以大幅度的減少redo日誌量
LS@LEO> create table leo_t1 as select * from leo_test_sqlload where 1=2; 建立leo_t1表
Table created.
LS@LEO> alter table leo_t1 logging; 設定leo_t1表logging模式
Table altered.
LS@LEO> set autotrace trace stat;
LS@LEO> insert into leo_t1 select * from leo_test_sqlload where rownum <= 20000; 採用傳統方式載入2萬條記錄
20000 rows created.
Statistics 統計資訊
----------------------------------------------------------
1071 recursive calls
2668 db block gets
1860 consistent gets
386 physical reads
1680404 redo size 這是產生的日誌量1680404
680 bytes sent via SQL*Net to client
603 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
20000 rows processed
LS@LEO> rollback; 回滾操作，使用undo表空間
Rollback complete.
LS@LEO> insert /*+ append */ into leo_t1 select * from leo_test_sqlload where rownum <= 20000; 使用直接載入方式插入2萬條記錄
20000 rows created.
Statistics
----------------------------------------------------------
94 recursive calls
268 db block gets
1294 consistent gets
202 physical reads
1627260 redo size 當leo_t1為logging屬性時，直接載入和傳統載入產生redo日誌查不多
664 bytes sent via SQL*Net to client
617 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20000 rows processed
小結：這是因為在logging模式下，所有的資料塊的改變都會產生redo日誌，為以後恢復做準備，這時候直接載入沒有多大的優勢。
直接載入與nologging配合下可以大幅度的減少redo日誌量
重大前提
如果你的資料庫開啟了force_logging=yes模式，那麼不管你是傳統載入還是直接載入都不會減少redo產生量
所以要想大幅度減少redo日誌就必須滿足3個條件
（1）關閉force_logging選項 alter database no force logging; 啟動 alter database force logging;
（2）資料物件級別nologging模式 alter table leo_t1 nologging;
（3）直接載入 insert /*+ append */ into
資料庫歸檔與redo日誌量關係
資料庫處於歸檔模式
當表模式為logging狀態時,無論是否使用append模式,都會生成redo.當表模式為nologging狀態時,只有append模式,不會生成redo。
資料庫處於非歸檔模式
無論是在logging還是nologing的模式下，append的模式都不會生成redo,而no append模式下都會生成redo。
force_logging logging nologging 如果都設定了 有一個優先順序的 排名
force_logging 可以在資料庫級別 表空間級別設定
logging nologging 只能在資料物件級別設定
LS@LEO> alter database no force logging;
LS@LEO> select force_logging from v$database; 已經關閉force_logging選項
FOR
---
NO
LS@LEO> alter table leo_t1 nologging; 設定leo_t1表nologging模式
Table altered.
LS@LEO> select logging from user_tables where table_name='LEO_T1';
LOG
---
NO
LS@LEO> select count(*) from leo_t1; 0條記錄
COUNT(*)
----------
0
LS@LEO> select index_name from user_indexes where table_name='LEO_T1'; 表上沒有索引
no rows selected
LS@LEO> insert /*+ append */ into leo_t1 select * from leo_test_sqlload where rownum <= 20000; 直接載入
20000 rows created.
Statistics
----------------------------------------------------------
1443 recursive calls
649 db block gets
1702 consistent gets
1519 physical reads
44900 redo size 直接載入產生的redo日誌非常少
658 bytes sent via SQL*Net to client
617 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
20000 rows processed
LS@LEO> rollback;
Rollback complete.
LS@LEO> insert into leo_t1 select * from leo_test_sqlload where rownum <= 20000; 傳統載入
20000 rows created.
Statistics
----------------------------------------------------------
4 recursive calls
2207 db block gets
1534 consistent gets
441 physical reads
1634064 redo size 傳統載入產生的redo日誌非常非常的多
673 bytes sent via SQL*Net to client
603 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20000 rows processed
小結：直接載入與nologging配合下可以大幅度的減少redo日誌量，因為插入的資料不產生redo日誌，所以在插入後要做備份操作，一旦資料損壞，就要使用備份來恢復，不能使用redo來恢復。注意要關閉force_logging選項哦！
3.比較direct方式使用並行和非並行選項的效能差異。
資料檔案：leo_test.data 100000記錄
控制檔案：leo_test.ctl
日誌檔案：leo_test.log
序列直接載入sqlload ： sqlldr userid=ls/ls control=leo_test.ctl data=leo_test.data log=leo_test.log direct=true
並行直接載入sqlload ： sqlldr userid=ls/ls control=leo_test.ctl data=leo_test.data log=leo_test.log direct=true parallel=true
1.現在演示“序列直接載入”
LS@LEO> select count(*) from leo_test_sqlload; 載入之前
COUNT(*)
----------
0
Sun Sep 9 10:58:24 CST 2012 58：24
[oracle@secdb1 ~]$ sqlldr userid=ls/ls control=leo_test.ctl data=leo_test.data log=leo_test.log direct=true
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Sep 9 10:58:26 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 100000.
[oracle@secdb1 ~]$ date
Sun Sep 9 10:58:45 CST 2012 58:45-58:24=21秒 即用時21秒 我這是手算的，一會看看日誌裡記錄的是多少
LS@LEO> select count(*) from leo_test_sqlload; 載入之後
COUNT(*)
----------
100000
[oracle@secdb1 ~]$ more leo_test.log
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Sep 9 10:58:26 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: leo_test.ctl
Data File: leo_test.data
Bad File: leo_test.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct 【sql*loader採用序列直接載入方式載入資料】
Table LEO_TEST_SQLLOAD, loaded from every logical record. 載入的表名
Insert option in effect for this table: APPEND 追加方式載入資料
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype 列名
------------------------------ ---------- ----- ---- ---- ---------------------
START_TIME FIRST * | DATE YYYY-MM-DD HH24:MI:SS
END_TIME NEXT * | DATE YYYY-MM-DD HH24:MI:SS
PROTOCOL NEXT * | CHARACTER
PRIVATE_IP NEXT * | CHARACTER
PRIVATE_PORT NEXT * | CHARACTER
SRC_IP NEXT * | CHARACTER
SRC_PORT NEXT * | CHARACTER
DEST_IP NEXT * | CHARACTER
DEST_PORT NEXT * | CHARACTER
The following index(es) on table LEO_TEST_SQLLOAD were processed: 並把索引也載入了10萬個索引鍵值
index LS.LEO_INDEX1 loaded successfully with 100000 keys
Table LEO_TEST_SQLLOAD:
100000 Rows successfully loaded. 成功載入10萬行記錄
0 Rows not loaded due to data errors. 由於資料錯誤，0行沒有載入
0 Rows not loaded because all WHEN clauses were failed. 因為所有條款都失敗的時候，0行沒有載入
0 Rows not loaded because all fields were null. 因為所有欄位都是空的，0行沒有載入
Date cache:
Max Size: 1000
Entries : 65
Hits : 199935
Misses : 0
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0 跳過邏輯記錄數總和0
Total logical records read: 100000 讀取邏輯記錄數總和100000
Total logical records rejected: 0 拒絕邏輯記錄數總和0
Total logical records discarded: 0 丟棄邏輯記錄數總和0
Total stream buffers loaded by SQL*Loader main thread: 26
Total stream buffers loaded by SQL*Loader load thread: 17
Run began on Sun Sep 09 10:58:26 2012 開始的時間
Run ended on Sun Sep 09 10:58:41 2012 結束的時間
Elapsed time was: 00:00:14.70 即用時14.7秒 這是機器算的
CPU time was: 00:00:00.38 CPU佔用0.38秒
2.現在演示“並行直接載入”
並行載入
（1）並行載入和並行insert機制差不多
（2）並行載入可以啟動多個並行程式，同時載入多個檔案
（3）並行載入可以啟動多個並行程式，分拆一個檔案載入
LS@LEO> select count(*) from leo_test_sqlload; 載入之前
COUNT(*)
----------
0
Sun Sep 9 11:28:13 CST 2012 28:13
[oracle@secdb1 ~]$ sqlldr userid=ls/ls control=leo_test.ctl data=leo_test.data log=leo_test.log direct=true parallel=true
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Sep 9 11:28:14 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 100000.
[oracle@secdb1 ~]$ date
Sun Sep 9 11:28:28 CST 2012 28:28-28:13=15秒 即用時15秒 我這是手算的，一會看看日誌裡記錄的是多少
LS@LEO> select count(*) from leo_test_sqlload; 載入之後
COUNT(*)
----------
100000
[oracle@secdb1 ~]$ more leo_test.log
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Sep 9 11:28:14 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: leo_test.ctl
Data File: leo_test.data
Bad File: leo_test.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct - with parallel option. 【sql*loader採用並行直接載入方式載入資料】
Table LEO_TEST_SQLLOAD, loaded from every logical record. 載入的表名
Insert option in effect for this table: APPEND 追加的方式載入資料
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype 列名
------------------------------ ---------- ----- ---- ---- ---------------------
START_TIME FIRST * | DATE YYYY-MM-DD HH24:MI:SS
END_TIME NEXT * | DATE YYYY-MM-DD HH24:MI:SS
PROTOCOL NEXT * | CHARACTER
PRIVATE_IP NEXT * | CHARACTER
PRIVATE_PORT NEXT * | CHARACTER
SRC_IP NEXT * | CHARACTER
SRC_PORT NEXT * | CHARACTER
DEST_IP NEXT * | CHARACTER
DEST_PORT NEXT * | CHARACTER
Table LEO_TEST_SQLLOAD:
100000 Rows successfully loaded. 成功載入10萬行記錄
0 Rows not loaded due to data errors. 由於資料錯誤，0行沒有載入
0 Rows not loaded because all WHEN clauses were failed. 因為所有條款都失敗的時候，0行沒有載入
0 Rows not loaded because all fields were null. 因為所有欄位都是空的，0行沒有載入
Date cache:
Max Size: 1000
Entries : 65
Hits : 199935
Misses : 0
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0 跳過邏輯記錄數總和0
Total logical records read: 100000 讀取邏輯記錄數總和100000
Total logical records rejected: 0 拒絕邏輯記錄數總和0
Total logical records discarded: 0 丟棄邏輯記錄數總和0
Total stream buffers loaded by SQL*Loader main thread: 26
Total stream buffers loaded by SQL*Loader load thread: 17
Run began on Sun Sep 09 11:28:14 2012 開始的時間
Run ended on Sun Sep 09 11:28:24 2012 結束的時間
Elapsed time was: 00:00:09.18 即用時9.18秒 這是機器算的
CPU time was: 00:00:00.42 CPU佔用0.38秒
小結：從時間效率上看“並行直接載入”比“序列直接載入”的效率要高，但我們都知道“並行直接載入”如果表中有索引會導致載入失敗，因為oracle不能一邊並行一邊維護索引的完整性。
4.直接載入對約束性索引和非約束型索引的影響。
一、條件
（1）現在我們使用sql*load來載入100000條資料
（2）載入的leo_test_sqlload表上有索引
二、實驗
1.非約束性索引，直接載入在載入完成後會維護索引的完整性
$ cat leo_test.data | wc -l 檢查資料檔案內的記錄數10萬條，這就是我們要載入的資料
100000
$ cat leo_test.ctl 控制檔案內容
LOAD DATA
INFILE '/home/oracle/leo_test.data' 資料來源
APPEND INTO TABLE leo_test_sqlload 載入資料的表
FIELDS TERMINATED BY '|' 文字資料的分隔符
TRAILING NULLCOLS
(START_TIME DATE 'YYYY-MM-DD HH24:MI:SS',END_TIME DATE 'YYYY-MM-DD HH24:MI:SS',PROTOCOL,PRIVATE_IP,PRIVATE_PORT
,SRC_IP,SRC_PORT,DEST_IP,DEST_PORT) 表中欄位的名
LS@LEO> desc leo_test_sqlload; 表的結構
Name Null? Type
----------------------------------------- -------- ----------------------------
START_TIME DATE
END_TIME DATE
PROTOCOL VARCHAR2(20)
PRIVATE_IP VARCHAR2(20)
PRIVATE_PORT VARCHAR2(20)
SRC_IP VARCHAR2(20)
SRC_PORT VARCHAR2(20)
DEST_IP VARCHAR2(20)
DEST_PORT VARCHAR2(20)
LS@LEO> select index_name,status from user_indexes where table_name='LEO_TEST_SQLLOAD'; 現在表上沒有索引，我們定義一個索引
no rows selected
LS@LEO> create index leo_index1 on leo_test_sqlload(start_time); 我們在start_time欄位新增了索引
Index created.
LS@LEO> select index_name,status from user_indexes where table_name='LEO_TEST_SQLLOAD'; 索引已經生效
INDEX_NAME STATUS
------------------------------ --------
LEO_INDEX1 VALID
[oracle@secdb1 ~]$ sqlldr userid=ls/ls control=leo_test.ctl data=leo_test.data log=leo_test.log direct=true 直接載入
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Sep 9 09:18:13 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 100000.
LS@LEO> select count(*) from leo_test_sqlload; 已經載入了10萬條記錄
COUNT(*)
----------
100000
LS@LEO> select index_name,status from user_indexes where table_name='LEO_TEST_SQLLOAD'; 索引狀態還是有效的
INDEX_NAME STATUS
------------------------------ --------
LEO_INDEX1 VALID
小結：非約束索引下，直接載入會維護索引的完整性，在資料載入入庫後索引還是有效的。
2.約束性索引【主鍵】，直接載入依然會把資料載入入庫，但索引會失效unusable，並且在日誌中沒有提示，必須手工rebuild重新建立
資料檔案：leo_test1.data
控制檔案：leo_test1.ctl
日誌檔案：leo_test1.log
sqlload ： sqlldr userid=ls/ls control=leo_test1.ctl data=leo_test1.data log=leo_test1.log direct=true
LS@LEO> desc leo_test_sqlload1 表結構
Name Null? Type
----------------------------------------- -------- ----------------------------
START_TIME DATE
END_TIME DATE
PROTOCOL VARCHAR2(20)
PRIVATE_IP VARCHAR2(20)
PRIVATE_PORT VARCHAR2(20)
SRC_IP VARCHAR2(20)
SRC_PORT VARCHAR2(20)
DEST_IP VARCHAR2(20)
DEST_PORT VARCHAR2(20)
LS@LEO> select index_name,status from user_indexes where table_name='LEO_TEST_SQLLOAD1'; 沒有主鍵，我們現在建一個
no rows selected
LS@LEO> alter table leo_test_sqlload1 add constraint pk_leo_test1 primary key(dest_port); 建立主鍵
Table altered.
LS@LEO> select index_name,status from user_indexes where table_name='LEO_TEST_SQLLOAD1'; 主鍵已經生效
INDEX_NAME STATUS
------------------------------ --------
PK_LEO_TEST1 VALID
LS@LEO> select count(*) from leo_test_sqlload1; 表中沒有資料我們開始載入
COUNT(*)
----------
0
[oracle@secdb1 ~]$ sqlldr userid=ls/ls control=leo_test1.ctl data=leo_test1.data log=leo_test1.log direct=true
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Sep 9 10:07:49 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 100.
LS@LEO> select * from leo_test_sqlload1 where rownum <= 6;
START_TIME END_TIME PROTOCOL PRIVATE_IP PRIVATE_PORT SRC_IP SRC_PORT DEST_IP DEST_PORT
------------------- ------------------- -------------------- ------------ ------------ -------------------- ----------
2012-08-08 20:59:54 2012-08-08 21:00:28 6 2886756061 1111 3395517721 45031 3419418065 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886900807 1111 395507143 51733 3658060738 80
2012-08-08 20:59:55 2012-08-08 21:00:28 6 2886917742 1111 3395507159 43516 2071873572 80
2012-08-08 20:59:55 2012-08-08 21:00:28 6 2886917742 1111 3395507159 43534 2071873572 80
2012-08-08 20:59:55 2012-08-08 21:00:28 6 2886917742 1111 3395507159 43523 2071873572 80
2012-08-08 21:00:14 2012-08-08 21:00:28 6 2886832065 1111 3395507109 51442 2099718013 80
6 rows selected.
我們已經載入了100條記錄，dest_port列值都是80，違背了主鍵約束，說明索引失效
LS@LEO> select index_name,status from user_indexes where table_name='LEO_TEST_SQLLOAD1'; 索引已經失效
INDEX_NAME STATUS
------------------------------ --------
PK_LEO_TEST1 UNUSABLE
結論：在OLAP系統中實時表不應該使用約束【因為是海量資料重複是正常的】，在維度表中可以使用約束。
3.如果使用並行+直接載入資料的話，如果表中有索引，會導致載入失敗，可以使用skip_index_maintenance選項“跳過索引維護”，來到達資料載入的目的，但是此時索引會無效unusable，必須手工rebuild重新建立
重新搭建環境
LS@LEO> truncate table leo_test_sqlload1;
Table truncated.
LS@LEO> alter table leo_test_sqlload1 drop constraint pk_leo_test1;
Table altered.
LS@LEO> alter table leo_test_sqlload1 add constraint pk_leo_test1 primary key(dest_port);
Table altered.
[oracle@secdb1 ~]$ sqlldr userid=ls/ls control=leo_test1.ctl data=leo_test1.data log=leo_test1.log direct=true parallel=true
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Sep 9 10:29:42 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-951: Error calling once/load initialization
ORA-26002: Table LS.LEO_TEST_SQLLOAD1 has index defined upon it. 表上有索引定義導致載入失敗
[oracle@secdb1 ~]$ sqlldr userid=ls/ls control=leo_test1.ctl data=leo_test1.data log=leo_test1.log direct=true parallel=true
skip_index_maintenance=true
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Sep 9 10:32:51 2012 我們跳過索引維護成功載入100條記錄
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 100.
LS@LEO> select count(*) from leo_test_sqlload1;
COUNT(*)
----------
100
LS@LEO> select index_name,status from user_indexes where table_name='LEO_TEST_SQLLOAD1'; 索引此時是無效狀態
INDEX_NAME STATUS
------------------------------ --------
PK_LEO_TEST1 UNUSABLE
小結：這就告訴我們了並行就是切片，一邊切片一邊維護索引完整性是做不到的，我們只能在載入資料後重新建立索引。
Leonarding
2012.9.9
天津&autumn
分享技術~成就夢想
Blog：http://space.itpub.net/26686207
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26686207/viewspace-743178/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 前端效能優化 --- 懶載入&預載入前端優化
- 【效能優化】Oracle直方圖解析優化Oracle直方圖圖解
- 瀏覽器的載入與頁面效能優化瀏覽器優化
- React 16 載入效能優化指南React優化
- iOS效能優化 - 網路圖片載入優化iOS優化
- 前端效能優化：細說JavaScript的載入與執行前端優化JavaScript
- Oracle直接路徑載入Oracle
- 頁面載入效能之優化LCP優化
- 前端效能優化之載入技術前端優化
- JavaScript無阻塞載入效能優化方案JavaScript優化
- 前端效能優化 – 資源預載入前端優化
- 前端效能優化——延遲載入和非同步載入前端優化非同步
- 12種jQuery效能優化方法解析（常用 轉載）jQuery優化
- 《深入解析Oracle》第十章，效能診斷與SQL優化OracleSQL優化
- Oracle資料載入速度優化Oracle優化
- iOS效能優化之頁面載入速率iOS優化
- JavaScript 的效能優化：載入和執行JavaScript優化
- JavaScript的效能優化：載入和執行JavaScript優化
- ListView效能優化非同步載入圖片View優化非同步
- oracle 效能優化Oracle優化
- Oracle效能優化Oracle優化
- 效能優化 (五) 長圖優化，仿微博載入長圖方式優化
- 【效能優化實踐】優化打包策略提升頁面載入速度優化
- 【效能優化】ORACLE資料庫效能優化概述優化Oracle資料庫
- 使用 Webapck 優化 VS Code 外掛載入效能Web優化
- 前端效能優化 - Resource Hints 資源預載入前端優化
- 無線效能優化：頁面可見時間與非同步載入優化非同步
- Oracle SQL效能優化OracleSQL優化
- oracle 效能優化（一）Oracle優化
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- Web效能優化系列（3）：如何延遲載入JSWeb優化JS
- 《Oracle DBA手記3——資料庫效能優化與內部原理解析》出版Oracle資料庫優化
- ES寫入效能優化優化
- Oracle 效能優化小結Oracle優化
- oracle效能優化之--hintsOracle優化