本來這次處理公司的問題是要用Perl解決,但因為需要一次性的更新145萬筆左右的資料,目標資料表的資料量那是好幾個億筆,而且隨時都在異動中,又加上是線上系統.如果真的使用Perl讀CSV檔後,再一筆一筆的更新資料,真不知道要處理多久.所以在寫了一些的測試程式發現效能太差後,就決定改用SQL指令來批量處理.
第一件要做的事就是把外部檔案匯入到資料庫,需要使用sqlldr指令來完成
sqlldr CONTROL= sqlldr_load_data.ctl LOG= sqlldr_load_data.ctl.log
接著撰寫Control檔匯入CSV檔
LOAD DATA
INFILE '/tmp/data.csv'
INSERT INTO TABLE TMP_DATA
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
CASE_REPORT_TYPE,
HOSP_ID,
HOSP_DATA_TYPE,
FEE_YM,
TREAT_DT,
TREAT_TYPE,
FUNC_SEQ_NO,
RECARD_MARK,
ID,
BIRTHDAY,
ORDER_CODE,
ASSAY_UPLOAD_DATE,
REAL_INSPECT_DATE
)因為公司的主機規格還可以,執行匯入的動作大約只花了幾秒鐘就完成了.
x ------------------------------
x N N M M OOOO N N For online help type: h
x NN N MM MM O O NN N For command line option help:
x N N N M MM M O O N N N quick-hint nmon -?
x N N N M M O O N N N full-details nmon -h
x N NN M M O O N NN To start nmon the same way every time?
x N N M M OOOO N N set NMON ksh variable, for example:
x ------------------------------ export NMON=cmt
x TOPAS_NMON
x 40 - CPUs currently
x 40 - CPUs configured
x 4190 - MHz CPU clock rate
x PowerPC_POWER8 - Processor
x 64 bit - Hardware
x 64 bit - Kernel
x 1,HA - Logical Partition
x 7.1.4.32 TL04 - AIX Kernel Version
x - Hostname
x - Node/WPAR Name
x 84CF697 - Serial Number
x IBM,9119-MME - Machine Type
x Memory qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqx
x Physical PageSpace | pages/sec In Out | FileSystemCachex
x% Used 99.9% 5.8% | to Paging Space 0.0 0.0 | (numperm) 30.2%x
x% Free 0.1% 94.2% | to File System 490.0 65974.8| Process 56.1%x
xGB Used 127.9GB 1.8GB | Page Scans 65125.8 | System 13.6%x
xGB Free 0.1GB 30.2GB | Page Cycles 0.0 | Free 0.1%x
xTotal(GB) 128.0GB 32.0GB | Page Steals 65179.8 | -----x
x | Page Faults 3901.1 | Total 100.0%x
x------------------------------------------------------------ | numclient 30.2%x
xMin/Maxperm 2489MB( 3%) 74685MB( 90%) <--% of RAM | maxclient 90.0%x
xMin/Maxfree 960 1088 Total Virtual 160.0GB | User 77.0%x
xMin/Maxpgahead 2 8 Accessed Virtual 81.6GB 51.0%| Pinned 44.3%x
x | lruable pages x
xqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqx
