外部表を触ってみた（ORGANIZATION EXTERNAL)

http://www.oracle.co.jp/iSeminars/071120_1600/doc/DataLoading_071120.pdf
この辺に触発されて
・外部表
・テーブル圧縮（COMPRESS）
の2つを試して見たくなった。
COMPRESSについてはEEでのみ使える機能とのことなので会社の開発環境にて試そうと思うが
今回は外部表を触ってみた。

とりあえず
http://www.shift-the-oracle.com/table/external-table-practice.html
を見ながらDirectory

CREATE DIRECTORY CSV AS 'c:\csv';

と外部表のTableを作ってみた。

CREATE TABLE DL_CSV (
	DLDATE DATE,
	MID NUMBER,
	DLCOUNT NUMBER)
ORGANIZATION EXTERNAL(
	TYPE ORACLE_LOADER
	DEFAULT DIRECTORY csv
	ACCESS PARAMETERS(
		RECORDS DELIMITED BY NEWLINE
		FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
		(
			DLDATE CHAR DATE_FORMAT DATE MASK 'YYYY/MM/DD',
			MID,
			DLCOUNT
		)
	)
	LOCATION(
		'dl.txt'
	)
);

動作確認できたのでSQLLoaderとの比較をしてみる。
dl.txt：900,000件
処理：DLDATE,MID,DLCOUNTをDLDATE,DLCOUNTへ集計
集計テーブルはこんな感じ

CREATE TABLE DAILY_DL(
	DLDATE DATE,
	DLCOUNT NUMBER
);

まずは外部表から
さっき作った外部表からselectinsertしてみる

INSERT INTO DAILY_DL
	SELECT
		 DLDATE
		,SUM(DLCOUNT) DLCOUNT
	FROM
		DL_CSV
	GROUP BY
		DLDATE

結果15秒くらい

次にSQLLoaderで試してみる
まずコントロールファイル作成

LOAD DATA
INFILE 'c:\csv\dl.txt'
BADFILE 'c:\csv\dl.bad'
TRUNCATE INTO TABLE DL_LDR
FIELDS TERMINATED BY ","
(
DLDATE "TO_DATE(:DLDATE,'YYYY/MM/DD')",
MID,
DLCOUNT
)

でロードしてみたところ2分50秒かかった
まぁ、外部表の時と違って1レコード単位でInsertしてるのでしょうがないか。。。

んで、selectinsert

INSERT INTO DAILY_DL
	SELECT
		 DLDATE
		,SUM(DLCOUNT) DLCOUNT
	FROM
		DL_LDR
	GROUP BY
		DLDATE

ここで4秒　＝　約3分

結果
外部表：15秒
ローダー：3分

どうも、ローダーの場合は1行1行Insertしてて分が悪いようなので外部表でも実テーブルへ全行Insertしてみた。

INSERT INTO DL_LDR SELECT * FROM DL_CSV

結果28秒

おーすごいですね。
ちなみにダイレクトパスインサートもできるみたいなのでついでに試して見た。

INSERT /*+APPEND*/ INTO DL_LDR SELECT * FROM DL_CSV

結果14秒　半分になりました。

これは使えるかもしれない！！