appendヒントとかbulk collectとかcollectionとかconnect byとかの話(nologgingパターンは忘れた)

はじめに

plsqlにdml文書いてあそんでいただけの話です。ほんとうにとりとめのない話です。

小ネタ

connect by level <= 10000000はoraerror。1000万件エラー。100万件はOK。connect byの限界(デフォルト)。ただメモリいじればいけると思う。ただマウントからやり直してインスタンス再起動はめんどい。結合で回避。

コード表示

create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 10000000
エラー・レポート -
ORA-30009: CONNECT BY操作のためのメモリーが足りません
30009. 0000 -  "Not enough memory for %s operation"
*Cause:    The memory size was not sufficient to process all the levels of the
           hierarchy specified by the query.
*Action:   In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to
           a reasonably larger value.
           Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a
           reasonably larger value.

select * from V$PARAMETER where NAME in ('workarea_size_policy','pga_aggregate_target');

drop table tbl___src___ purge;
--limit
create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 1000000;
--ORA-30009
create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 10000000;
--join
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;

オブジェクト型とコレクション型作る

クエリ

コード表示

create or replace type item is object (rn number,str varchar2(10));
/
create or replace type liz is table of item;
/

小ネタ

正規表現つかうとオシャンにいろいろできたりする。スクリプトをコールする前で前処理を組み込みたいとき、どうしたらいいかと思う。正規表現使ってハンディにできないかと考えたら、できるという話。

まず、コンパイルする。コンソールにはかれたプロシージャ名をコピってexecなんとかに書き換える。

コード表示

exec PROC___SELECT_INSERT___
exec PROC___DIRECT_PATH_INSERT___
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___

--置換前:$
--置換後:;\r\n/

exec PROC___SELECT_INSERT___;
/
exec PROC___DIRECT_PATH_INSERT___;
/
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/

必要な前処理が以下であるとする。

コード表示

drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

前処理を組み込むまえにonelinerにしておく。

コード表示

--置換前:$
--置換後:\\r\\n

drop table tbl___src___ purge;\r\n
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level &lt;= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;\r\n
drop table tbl___tar___ purge;\r\n
create table tbl___tar___ as select * from tbl___src___ where 1 &lt;&gt; 1;\r\n
alter system checkpoint;\r\n
--alter system switch logfile;\r\n
alter system flush shared_pool;\r\n
alter system flush buffer_cache;\r\n

--置換前:\r\n
--置換後:

drop table tbl___src___ purge;\r\ncreate table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level &lt;= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;\r\ndrop table tbl___tar___ purge;\r\ncreate table tbl___tar___ as select * from tbl___src___ where 1 &lt;&gt; 1;\r\nalter system checkpoint;\r\n--alter system switch logfile;\r\nalter system flush shared_pool;\r\nalter system flush buffer_cache;\r\n

onelinerにした後のスニペットをexecの前にぴろっと入れる。

コード表示

--置換前:^(?=exec)
--置換後:drop table tbl___src___ purge;\r\ncreate table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;\r\ndrop table tbl___tar___ purge;\r\ncreate table tbl___tar___ as select * from tbl___src___ where 1 <> 1;\r\nalter system checkpoint;\r\n--alter system switch logfile;\r\nalter system flush shared_pool;\r\nalter system flush buffer_cache;\r\n

drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___SELECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___DIRECT_PATH_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/


コンパイルスクリプト

まぁあそびです。

コード表示

create or replace procedure proc___select_insert___ as
	srt_tp timestamp;
	end_tp timestamp;
begin
	select systimestamp into srt_tp from dual;
	insert into tbl___tar___
		select
			*
		from
			tbl___src___;
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___select_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___direct_path_insert___ as
	srt_tp timestamp;
	end_tp timestamp;
begin
	select systimestamp into srt_tp from dual;
	insert /*+ append */ into tbl___tar___
		select
			*
		from
			tbl___src___;
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___direct_path_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch');
	dbms_output.put_line(end_tp - srt_tp);
	select systimestamp into srt_tp from dual;
	insert into tbl___tar___
		select
			*
		from
			table ( lz );
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch');
	dbms_output.put_line(end_tp - srt_tp);
	select systimestamp into srt_tp from dual;
	insert /*+ append */ into tbl___tar___
		select
			*
		from
			table ( lz );
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_bulk_collect_insert_no_limit___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_bulk_collect_insert_no_limit___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_bulk_collect_insert_no_limit___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_bulk_collect_insert_no_limit___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_bulk_collect_insert_limit_1000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 1000;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_bulk_collect_insert_limit_1000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_bulk_collect_insert_limit_1000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 1000;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_bulk_collect_insert_limit_1000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_bulk_collect_insert_limit_10000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 10000;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_bulk_collect_insert_limit_10000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_bulk_collect_insert_limit_10000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 10000;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_bulk_collect_insert_limit_10000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_no_bulk_collect_insert___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		cast(collect(item(rn, str) ) as liz)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr into lz;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_no_bulk_collect_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_no_bulk_collect_insert___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		cast(collect(item(rn, str) ) as liz)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr into lz;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_no_bulk_collect_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/

小ネタからできた実行スクリプト

コード表示

drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___SELECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___DIRECT_PATH_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/

実行結果コンソールログ

ここから必要な情報を抜き取る。そのままべと。

コード表示


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___select_insert___
+000000000 00:00:01.451000000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___direct_path_insert___
+000000000 00:00:01.549663000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.844085000
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:08.425256000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.904504000
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:10.287960000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.862762000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.909035000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_bulk_collect_insert_limit_1000___
+000000000 00:00:14.981688000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_bulk_collect_insert_limit_1000___
+000000000 00:01:45.558595000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:14.202969000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:25.469245000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_no_bulk_collect_insert___
+000000000 00:00:24.833968000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_no_bulk_collect_insert___
+000000000 00:00:18.574835000


PL/SQLプロシージャが正常に完了しました。

--置換前:^(?!.*(proc|\+)).*$
--置換後:

--置換前:^\r\n
--置換後:

proc___select_insert___
+000000000 00:00:01.451000000
proc___direct_path_insert___
+000000000 00:00:01.549663000
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.844085000
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:08.425256000
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.904504000
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:10.287960000
proc___no_append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.862762000
proc___append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.909035000
proc___no_append_hint_bulk_collect_insert_limit_1000___
+000000000 00:00:14.981688000
proc___append_hint_bulk_collect_insert_limit_1000___
+000000000 00:01:45.558595000
proc___no_append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:14.202969000
proc___append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:25.469245000
proc___no_append_hint_no_bulk_collect_insert___
+000000000 00:00:24.833968000
proc___append_hint_no_bulk_collect_insert___
+000000000 00:00:18.574835000


--置換前:\r\n
--置換後:\t

proc___select_insert___	+000000000 00:00:01.451000000	proc___direct_path_insert___	+000000000 00:00:01.549663000	proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.844085000	proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:08.425256000	proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.904504000	proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:10.287960000	proc___no_append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.862762000	proc___append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.909035000	proc___no_append_hint_bulk_collect_insert_limit_1000___	+000000000 00:00:14.981688000	proc___append_hint_bulk_collect_insert_limit_1000___	+000000000 00:01:45.558595000	proc___no_append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:14.202969000	proc___append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:25.469245000	proc___no_append_hint_no_bulk_collect_insert___	+000000000 00:00:24.833968000	proc___append_hint_no_bulk_collect_insert___	+000000000 00:00:18.574835000	


--置換前:proc
--置換後:\r\nproc


proc___select_insert___	+000000000 00:00:01.451000000	
proc___direct_path_insert___	+000000000 00:00:01.549663000	
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.844085000	
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:08.425256000	
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.904504000	
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:10.287960000	
proc___no_append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.862762000	
proc___append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.909035000	
proc___no_append_hint_bulk_collect_insert_limit_1000___	+000000000 00:00:14.981688000	
proc___append_hint_bulk_collect_insert_limit_1000___	+000000000 00:01:45.558595000	
proc___no_append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:14.202969000	
proc___append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:25.469245000	
proc___no_append_hint_no_bulk_collect_insert___	+000000000 00:00:24.833968000	
proc___append_hint_no_bulk_collect_insert___	+000000000 00:00:18.574835000	

測定結果

普通にcollectionとかでこねくりまわさんほうがよさげ。limit句で処理量を多くすると早くなるのはほぉーとなった。

おまけ

こういうのって追加パターンあるとメンテめんどってなるから、なるたけハンディにやりたい。そういった話。最初に思いついたやつ。

コード表示

declare
	cursor csr is
	with sub as(
	select 'PROC___SELECT_INSERT___'as proc from dual union all
	select 'PROC___DIRECT_PATH_INSERT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual union all
	select 'PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual
	)select * from sub;

proc clob;

begin
	open csr;
	loop
		begin
		fetch csr into proc;
		exit when csr%notfound;
		execute immediate 'begin '|| proc || '; end;';
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
	close csr;
end;
/

うえのだとあるスクリプトだけ実行したいってなったときコメントアウトするけど、アウトするところによってはコンパイルエラーとなってめんど。なので、実行スクリプト名を格納するテーブル適当につくる。こんな感じ。

コード表示

drop table exec_script purge;
create table exec_script as
	with sub as(
	select 'PROC___SELECT_INSERT___'as proc from dual union all
	select 'PROC___DIRECT_PATH_INSERT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual union all
	select 'PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual
	)select row_number() over (order by rownum) as seq,proc from sub;

select * from exec_script;

んで、cursorのところちょちょっとなおす。こんな感じ。where句に実行したいseqを指定できるようになるから、何番目以降とか指定できたり、特定の処理だけとかもできる。

コード表示

declare
	cursor csr is select proc from exec_script where seq = 1;
--	cursor csr is select proc from exec_script where seq >= 1;
--	cursor csr is select proc from exec_script where seq between 3 and 5;
	proc clob;
begin
	open csr;
	loop
		begin
		fetch csr into proc;
		exit when csr%notfound;
		execute immediate 'begin '|| proc || '; end;';
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
	close csr;
end;
/

前処理とかもテーブル入れておけばいいですかね。後処理とかも同様なノリでできるでしょう。

コード表示

drop table pre_script purge;
create table pre_script as
with sub as(
select 'drop table tbl___src___ purge' as proc from dual union all
select 'create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7' as proc from dual union all
select 'drop table tbl___tar___ purge' as proc from dual union all
select 'create table tbl___tar___ as select * from tbl___src___ where 1 <> 1' as proc from dual union all
select 'alter system checkpoint' as proc from dual union all
select '--alter system switch logfile' as proc from dual union all
select 'alter system flush shared_pool' as proc from dual union all
select 'alter system flush buffer_cache' as proc from dual
)select row_number() over (order by rownum) as seq,proc from sub;
select * from pre_script;

まぁ、なんでこの話したかっていうと、merge文のパターン忘れたなとおもったからで、実行するプロシージャと処理するプロシージャは切り離しておけばいいかなーとめんどくならないかなーとおもっただけのことです。実行するプロシージャもオブジェクトにしておけば実行もそんなにかかなくていいですね。こんな感じ。

コード表示

create or replace procedure script_run(p_pre_from_seq number:=1,p_pre_to_seq number:=1,p_exec_from_seq number:=1,p_exec_to_seq number:=1)
authid current_user
as
	cursor pre_csr is select proc from pre_script where seq between p_pre_from_seq and p_pre_to_seq;
	cursor exec_csr is select proc from exec_script where seq between p_exec_from_seq and p_exec_to_seq;
	pre_proc clob;
	exec_proc clob;
begin
	open exec_csr;
	loop
		begin
		fetch exec_csr into exec_proc;
		exit when exec_csr%notfound;
			open pre_csr;
			loop
				begin
				fetch pre_csr into pre_proc;
				exit when pre_csr%notfound;
				dbms_output.put_line(pre_proc);
				execute immediate pre_proc;
				exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
				end;
			end loop;
			close pre_csr;
		dbms_output.put_line('begin '|| exec_proc || '; end;');
		execute immediate 'begin '|| exec_proc || '; end;';
		dbms_output.put_line(lpad('_',60,'_'));
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
	close exec_csr;
end;
/

ついでに忘れた、merge文のパターンも

コード表示

create or replace procedure proc___merge_insert___ as
	srt_tp timestamp;
	end_tp timestamp;
begin
	select systimestamp into srt_tp from dual;
	merge into tbl___tar___ tar
	using(select * from tbl___src___) src
	on(tar.rn = src.rn and tar.str = src.str)
	when not matched then insert values(src.rn,src.str);
    commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___merge_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/

うえでつくったプロシージャ名をテーブルに投入。

コード表示

select * from exec_script;

merge into exec_script tar
using (
	with sub as(
	select 'PROC___MERGE_INSERT___' as proc from dual
	)select max(s2.seq) + 1 as seq,max(s1.proc) as proc from sub s1,exec_script s2
)src
on(tar.proc = src.proc)
when not matched then insert values(src.seq,src.proc);
commit;

select * from exec_script;

実行例。コメントとしてテーブル格納しておけば、無視してくれてるポイから、いけてる雰囲気。

コード表示

set serveroutput on;
exec script_run(1,8,1,2);
/
drop table tbl___src___ purge
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7
drop table tbl___tar___ purge
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1
alter system checkpoint
--alter system switch logfile
[ -900]ORA-00900: SQL文が無効です。
alter system flush shared_pool
alter system flush buffer_cache
begin PROC___SELECT_INSERT___; end;
proc___select_insert___
+000000000 00:00:01.351316000
____________________________________________________________
drop table tbl___src___ purge
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7
drop table tbl___tar___ purge
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1
alter system checkpoint
--alter system switch logfile
[ -900]ORA-00900: SQL文が無効です。
alter system flush shared_pool
alter system flush buffer_cache
begin PROC___DIRECT_PATH_INSERT___; end;
proc___direct_path_insert___
+000000000 00:00:01.449993000
____________________________________________________________


PL/SQLプロシージャが正常に完了しました。


おわりに

plsqlたのしいな!もっと楽しい方法あったら、教えてください。以上、ありがとうございました。