バルクフェッチしてパフォーマンスアップ

カーソルで一度にフェッチする行数を指定することができる。(バルクフェッチ)

下記バルクフェッチで216万行のT_UKEHARAIを全行出力したところ、
１行ずつでは5:30かかった処理が8行ずつで4:02に短縮された。
(それ以上の行数を指定しても書き込み処理がボトルネックになって速度は上がらなかった)

 DECLARE
t$table_name VARCHAR2(30);
sql_str VARCHAR2(3000);
t$row_data VARCHAR2(32767);
TYPE ROWDATA IS TABLE OF VARCHAR2(3000) INDEX BY BINARY_INTEGER;
t$arr_row ROWDATA;
t$bytes number;
 
TYPE cur_typ IS REF CURSOR;
t$cursor  cur_typ;
 
CURSOR base_sql(p$tname VARCHAR2) IS
	SELECT 'SELECT NULL ' "col" FROM dual
	UNION ALL
	SELECT "col" FROM 
		(SELECT '||'''||CHR(9)||'"''||'||cname||'||''"''' "col"   /*   ||'<tab>"'||xxxコード||'"'   */
		FROM col 
		WHERE tname = p$tname 
		ORDER BY colno)
	UNION ALL 
	SELECT ' FROM '||p$tname "col" FROM dual
	UNION ALL
	SELECT ' where 計上年月日 between ''20080101 000000'' and ''20090101 000000''' FROM dual
	;
 
fp UTL_FILE.file_type;
 BEGIN
t$table_name := 'T_UKEHARAI';
FOR i IN base_sql(t$table_name) LOOP
	sql_str := sql_str || i."col";
END LOOP;
 
BEGIN
	fp := UTL_FILE.fopen (
		'D:/temp',
		t$table_name||'.csv',
		'W',
		32767);
EXCEPTION
	WHEN UTL_FILE.invalid_path THEN
		dbms_output.put_line ('パス間違い');
	WHEN UTL_FILE.invalid_mode THEN
		dbms_output.put_line ('モードエラー');
	WHEN UTL_FILE.invalid_operation THEN
		dbms_output.put_line ('オペレーションエラー');
END;
 
t$row_data := '';
OPEN t$CURSOR FOR sql_str;
LOOP
	FETCH t$CURSOR BULK COLLECT INTO t$arr_row LIMIT 1000;
	EXIT WHEN t$arr_row.count = 0;
	FOR i IN 1 .. t$arr_row.count LOOP
		if LENGTHB(t$row_data) + LENGTHB(t$arr_row(i)) > 32766 THEN /*改行コード分があるので32k-1*/
			UTL_FILE.put_line(fp, t$row_data);
			t$row_data := '';
		END if;
		if t$row_data IS NULL THEN
			t$row_data := LTRIM(t$arr_row(i), CHR(9));
		else
			t$row_data := t$row_data || CHR(10) || LTRIM(t$arr_row(i), CHR(9));
		END if;
	END LOOP;
END LOOP;
CLOSE t$CURSOR;
UTL_FILE.put_line(fp, t$row_data);
IF UTL_FILE.is_open(fp) THEN
	UTL_FILE.fclose (fp);
END if;
 END;
 /

一回フェッチで複数行を取得するため、FETCH INTOする変数は配列にする必要がある。
また、ファイル出力は一回の出力で可能な32767バイトにできる限り近づけ、
put_lineする回数を減らしている。