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たのしいな!もっと楽しい方法あったら、教えてください。以上、ありがとうございました。

collect関数で入れ子できた話

環境

18cで

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 26 20:34:01 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 26 2019 20:28:33 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

タイプ作る

3つ今回は。

コード表示

drop type val force;
/
drop type item force;
/
drop type liz force;
/

create or replace type val is object(val clob);
/
create or replace type item is object(rn number,vl val);
/
create or replace type liz is table of item;
/

はじめのデータ

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select * from sub;

実行例

コード表示

AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select * from sub;

           RN STR
------------- ------------
            0 001
            0 002
            0 003
            1 001
            1 002
            1 003
            1 004

7 rows selected.

Elapsed: 00:00:00.00

strをオブジェクト型に変換

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select rn,val(str) as val from sub;

実行例

コード表示

AINE@pdb1> col val for a50
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select rn,val(str) as val from sub;

           RN VAL(VAL)
------------- --------------------------------------------------
            0 VAL('001')
            0 VAL('002')
            0 VAL('003')
            1 VAL('001')
            1 VAL('002')
            1 VAL('003')
            1 VAL('004')

7 rows selected.

Elapsed: 00:00:00.01

mapみたいにitemでラップ

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select item(rn,val(str)) as item from sub;

実行例

コード表示

AINE@pdb1> col item for a50
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select item(rn,val(str)) as item from sub;

ITEM(RN, VL(VAL))
--------------------------------------------------
ITEM(0, VAL('001'))
ITEM(0, VAL('002'))
ITEM(0, VAL('003'))
ITEM(1, VAL('001'))
ITEM(1, VAL('002'))
ITEM(1, VAL('003'))
ITEM(1, VAL('004'))

7 rows selected.

Elapsed: 00:00:00.01

最後はさまっておしまい

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select rn,cast(collect(item(rn,val(str))) as liz) as liz from sub group by rn;

実行例

コード表示

AINE@pdb1> col liz for a100
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select rn,cast(collect(item(rn,val(str))) as liz) as liz from sub group by rn;

           RN LIZ(RN, VL(VAL))
------------- ----------------------------------------------------------------------------------------------------
            0 LIZ(ITEM(0, VAL('001')), ITEM(0, VAL('003')), ITEM(0, VAL('002')))
            1 LIZ(ITEM(1, VAL('001')), ITEM(1, VAL('004')), ITEM(1, VAL('003')), ITEM(1, VAL('002')))

2 rows selected.

Elapsed: 00:00:00.01

サマル単位を指定しないと

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select cast(collect(item(rn,val(str))) as liz)  as liz from sub;

実行例

コード表示

AINE@pdb1> col liz for a170
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select cast(collect(item(rn,val(str))) as liz) as liz from sub;

LIZ(RN, VL(VAL))
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(ITEM(0, VAL('001')), ITEM(0, VAL('002')), ITEM(0, VAL('003')), ITEM(1, VAL('001')), ITEM(1, VAL('002')), ITEM(1, VAL('003')), ITEM(1, VAL('004')))

1 row selected.

Elapsed: 00:00:00.01

パスカルの三角形に関して

参考文献

やっと理解できた気がする。。オマージュしてみる。別のやり方で。

関数から作成、、そのまえに。

引数に渡すものと返却するものを考える。前段の段数番号と前段で構築した文字列。この2つを引数にする。返却値は次の段数で構築する文字列とその段数番号。まずは関数作成のまえに単一クエリで動きを捉えるところから始まる。

コード表示

col rn for 999
col ln for a10
col seq for 999
col seg for a10
col pre_seg for a10

WITH sub AS(
SELECT
	s1.rn
	,s1.ln
	,ROWNUM AS seq
	,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
FROM
	TABLE ( liz(item(3, '1-2-1') ) ) s1
--	TABLE ( liz(item(3, '1-2-1-0') ) ) s1
CONNECT BY
	LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
)
SELECT
	rn
	,ln
	,seq
	,seg
	,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
FROM
	sub
;

どうして、-0を加えているのか最初分からなかった。次の段数計算に使用するためだったのかとトレースしてやっと分かる。

コード表示

  RN LN          SEQ SEG        PRE_SEG   
---- ---------- ---- ---------- ----------
   3 1-2-1         1 1          0         
   3 1-2-1         2 2          1         
   3 1-2-1         3 1          2         

-0を追加したもの。

コード表示

col rn for 999
col ln for a10
col seq for 999
col seg for a10
col pre_seg for a10

WITH sub AS(
SELECT
	s1.rn
	,s1.ln
	,ROWNUM AS seq
	,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
FROM
	TABLE ( liz(item(3, '1-2-1-0') ) ) s1
CONNECT BY
	LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
)
SELECT
	rn
	,ln
	,seq
	,seg
	,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
FROM
	sub
;

この規則性はわからんかった。パスカルの三角形ってきれいだな。直前の文字列が1であることを信じている感じが分かる。

コード表示

  RN LN          SEQ SEG        PRE_SEG   
---- ---------- ---- ---------- ----------
   3 1-2-1-0       1 1          0         
   3 1-2-1-0       2 2          1         
   3 1-2-1-0       3 1          2         
   3 1-2-1-0       4 0          1         

んでもって次段数と次段数に渡す文字列を構築する。

サマリ処理で実現。

コード表示

WITH sub AS(
SELECT
	s1.rn
	,s1.ln
	,ROWNUM AS seq
	,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
FROM
	TABLE ( liz(item(3, '1-2-1-0') ) ) s1
CONNECT BY
	LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
)
SELECT
	rn + 1 AS rn
	,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
FROM
	(
	SELECT
		rn
		,seq
		,seg
		,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
	FROM
		sub
	)
GROUP BY
	rn + 1
;

できた。あとはこいつをコレクションに変換してやるだけ。

コード表示

  RN SEG       
---- ----------
   4 1-3-3-1   

コレクションに変換してやる

コード表示

col liz for a100

WITH sub AS(
SELECT
	s1.rn
	,s1.ln
	,ROWNUM AS seq
	,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
FROM
	TABLE ( liz(item(3, '1-2-1-0') ) ) s1
CONNECT BY
	LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
)
SELECT
	CAST(COLLECT(item(rn,seg)) AS liz) AS liz
FROM
	(
	SELECT
		rn + 1 AS rn
		,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
	FROM
		(
		SELECT
			rn
			,seq
			,seg
			,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
		FROM
			sub
		)
	GROUP BY
		rn + 1
	)
;

コレクションできた!

コード表示

LIZ(RN, LN)                                                                                         
----------------------------------------------------------------------------------------------------
LIZ(ITEM(4, '1-3-3-1'))

ファンクションに落とし込む。with functionでやってみようかな。あとスキーマレベルでのファンクションも作ってみる。多分コード長くなるからファンクションに切り出したほうがいい。

12cからwith functionが使えます。bulk collectしてみる

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
WITH
	FUNCTION calc(p_liz liz) RETURN liz IS
	rt liz :=NULL;
BEGIN
	SELECT
		item(rn,seg)
	BULK COLLECT
	INTO rt
	FROM
		(
		WITH sub AS(
		SELECT
			s1.rn
			,s1.ln
			,ROWNUM AS seq
			,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
		FROM
			(SELECT rn,ln || '-0' AS ln FROM TABLE(p_liz)) s1
		CONNECT BY
			LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
		)
		SELECT
			rn + 1 AS rn
			,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
		FROM
			(
			SELECT
				rn
				,seq
				,seg
				,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
			FROM
				sub
			)
		GROUP BY
			rn + 1
		);
	RETURN rt;
END;
SELECT calc(liz(item(3,'1-2-1'))) as liz FROM dual
/

スキーマレベルのファンクション

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/

CREATE OR REPLACE FUNCTION calc(p_liz liz)
RETURN liz
AS rt liz;
BEGIN
	SELECT
		item(rn,seg)
	BULK COLLECT
	INTO rt
	FROM
		(
		WITH sub AS(
		SELECT
			s1.rn
			,s1.ln
			,ROWNUM AS seq
			,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
		FROM
			(SELECT rn,ln || '-0' AS ln FROM TABLE(p_liz)) s1
		CONNECT BY
			LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
		)
		SELECT
			rn + 1 AS rn
			,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
		FROM
			(
			SELECT
				rn
				,seq
				,seg
				,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
			FROM
				sub
			)
		GROUP BY
			rn + 1
		);
	RETURN rt;
END;
/

SELECT calc(liz(item(3,'1-2-1'))) as liz FROM dual;

LIZ(RN, LN)                                                                                         
----------------------------------------------------------------------------------------------------
LIZ(ITEM(4, '1-3-3-1'))

再帰してみる。まずはイメージから。

5段ぐらいで。

コード表示

SELECT * FROM table(calc(liz(item(1,'1'))));
SELECT * FROM table(calc(liz(item(2,'1-1'))));
SELECT * FROM table(calc(liz(item(3,'1-2-1'))));
SELECT * FROM table(calc(liz(item(4,'1-3-3-1'))));

  RN LN                                                
---- --------------------------------------------------
   2 1-1                                               


  RN LN                                                
---- --------------------------------------------------
   3 1-2-1                                             


  RN LN                                                
---- --------------------------------------------------
   4 1-3-3-1                                           


  RN LN                                                
---- --------------------------------------------------
   5 1-4-6-4-1                                         

スキーマレベルのファンクションでグルグルする

スキーマレベルのファンクションを使用している。おおぉ

コード表示

COL ln FOR a10
COL triangle FOR a50

WITH sub AS(
SELECT LEVEL AS rn FROM dual CONNECT BY LEVEL <= 5
),rec(rn,ln)AS(
SELECT s1.rn,to_char(s1.rn) FROM sub s1 WHERE s1.rn = 1
UNION ALL
SELECT s2.rn,s2.ln FROM rec s1,TABLE(calc(liz(item(s1.rn,to_char(s1.ln)))))s2
WHERE
	s1.rn < 5
)SELECT
	s1.*
	,MAX(LENGTH(s1.ln))OVER() AS mx
	,LENGTH(s1.ln) AS len
	,MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln) AS dif
	,(MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2 AS div
	,TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0) AS tnc
	,lpad(' ',TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0),' ') || s1.ln AS triangle
FROM
	rec s1
;

lpadの使い方がパズルのピースみたいに気持ちよかった。

コード表示

  RN LN                 MX        LEN        DIF        DIV        TNC TRIANGLE                                          
---- ---------- ---------- ---------- ---------- ---------- ---------- --------------------------------------------------
   1 1                   9          1          8          4          4     1                                             
   2 1-1                 9          3          6          3          3    1-1                                            
   3 1-2-1               9          5          4          2          2   1-2-1                                           
   4 1-3-3-1             9          7          2          1          1  1-3-3-1                                          
   5 1-4-6-4-1           9          9          0          0          0 1-4-6-4-1                                         

即時レベルのwithファンクションでグルグルする

即時レベルのwithファンクションを使用している。おおぉ。再帰でないwithと再帰withとwithファンクションのコラボ。再帰でないwithは,(カンマ)つけなくていいんだね。勉強になった。最後は;(セミコロン)でなくて/(スラッシュ)で、締める。

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
WITH
	FUNCTION calc(p_liz liz) RETURN liz IS
	rt liz :=NULL;
BEGIN
	SELECT
		item(rn,seg)
	BULK COLLECT
	INTO rt
	FROM
		(
		WITH sub AS(
		SELECT
			s1.rn
			,s1.ln
			,ROWNUM AS seq
			,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
		FROM
			(SELECT rn,ln || '-0' AS ln FROM TABLE(p_liz)) s1
		CONNECT BY
			LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
		)
		SELECT
			rn + 1 AS rn
			,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
		FROM
			(
			SELECT
				rn
				,seq
				,seg
				,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
			FROM
				sub
			)
		GROUP BY
			rn + 1
		);
	RETURN rt;
END;
sub AS(
SELECT LEVEL AS rn FROM dual CONNECT BY LEVEL <= 5
),rec(rn,ln)AS(
SELECT s1.rn,to_char(s1.rn) FROM sub s1 WHERE s1.rn = 1
UNION ALL
SELECT s2.rn,s2.ln FROM rec s1,TABLE(calc(liz(item(s1.rn,to_char(s1.ln)))))s2
WHERE
	s1.rn < 5
)SELECT
	s1.*
	,MAX(LENGTH(s1.ln))OVER() AS mx
	,LENGTH(s1.ln) AS len
	,MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln) AS dif
	,(MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2 AS div
	,TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0) AS tnc
	,lpad(' ',TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0),' ') || s1.ln AS triangle
FROM
	rec s1
/

lpadの使い方がパズルのピースみたいに気持ちよかった。

コード表示

  RN LN                 MX        LEN        DIF        DIV        TNC TRIANGLE                                          
---- ---------- ---------- ---------- ---------- ---------- ---------- --------------------------------------------------
   1 1                   9          1          8          4          4     1                                             
   2 1-1                 9          3          6          3          3    1-1                                            
   3 1-2-1               9          5          4          2          2   1-2-1                                           
   4 1-3-3-1             9          7          2          1          1  1-3-3-1                                          
   5 1-4-6-4-1           9          9          0          0          0 1-4-6-4-1                                         

再帰段数をバインド変数にしておしまい。

バインド変数stkを入力して使う。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL triangle FOR a170
DEFINE stk=15


WITH sub AS(
SELECT LEVEL AS rn FROM dual CONNECT BY LEVEL <= &stk
),rec(rn,ln)AS(
SELECT s1.rn,to_char(s1.rn) FROM sub s1 WHERE s1.rn = 1
UNION ALL
SELECT s2.rn,s2.ln FROM rec s1,TABLE(calc(liz(item(s1.rn,to_char(s1.ln)))))s2
WHERE
	s1.rn < &stk
)SELECT
	lpad(' ',TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0),' ') || s1.ln AS triangle
FROM
	rec s1
;

tlokwegさんすごすぎ。。。

コード表示

TRIANGLE                                                                                                                                                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                            1
                           1-1
                          1-2-1
                         1-3-3-1
                        1-4-6-4-1
                      1-5-10-10-5-1
                     1-6-15-20-15-6-1
                   1-7-21-35-35-21-7-1
                  1-8-28-56-70-56-28-8-1
               1-9-36-84-126-126-84-36-9-1
           1-10-45-120-210-252-210-120-45-10-1
         1-11-55-165-330-462-462-330-165-55-11-1
       1-12-66-220-495-792-924-792-495-220-66-12-1
   1-13-78-286-715-1287-1716-1716-1287-715-286-78-13-1
1-14-91-364-1001-2002-3003-3432-3003-2002-1001-364-91-14-1

15行が選択されました。 

再帰関数でも実現できた!

バインド変数stkを入力して使う。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL triangle FOR a170

DEFINE stk=15
/
CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
CREATE OR REPLACE FUNCTION calc_rec (
	p_liz liz
	, p_stk NUMBER
) RETURN liz AS
	rt   liz := p_liz;
BEGIN
	SELECT
		CASE
			WHEN p_stk = 0 THEN liz(item(NULL, NULL) )
			ELSE rt MULTISET UNION ALL calc_rec(CAST(collect(item(rn, seg) ) AS liz), p_stk - 1)
		END
	INTO rt
	FROM
		(
			WITH sub AS (
				SELECT
					s1.rn
					, s1.ln
					, ROWNUM AS seq
					, DECODE(ROWNUM, 1, substr(s1.ln, 1, 1), substr(s1.ln, instr(s1.ln, '-', 1, ROWNUM - 1) + 1, nvl(nullif(instr(s1.ln, '-', 1, ROWNUM), 0), 4000) - instr(s1.ln, '-', 1, ROWNUM - 1) - 1) ) AS seg
				FROM
					(
						SELECT
							rn
							, ln || '-0' AS ln
						FROM
							TABLE ( p_liz )
					) s1
				CONNECT BY
					length(s1.ln) - length(replace(s1.ln, '-', '') ) + 1 >= level
			) SELECT
				rn + 1 AS rn
				, LISTAGG(to_number(seg) + to_number(pre_seg), '-') WITHIN GROUP(
					ORDER BY
						seq
				) AS seg
			  FROM
				(
					SELECT
						rn
						, seq
						, seg
						, LAG(seg, 1, 0) OVER(
							ORDER BY
								seq
						) AS pre_seg
					FROM
						sub
				)
			  GROUP BY
				rn + 1
		);

	RETURN rt;
END;
/

SELECT
	lpad(' ',TRUNC((MAX(LENGTH(ln))OVER() - LENGTH(ln))/2,0),' ') || ln AS triangle
FROM
	TABLE(calc_rec(liz(item(1,'1')),&stk))
WHERE
	rn IS NOT NULL
/

connect by の練習

参考文献

訪問経路の列挙  
 
第3回 AVL木で木構造を学ぼう  
 
データ構造の選択次第で天国と地獄の差 (3/3)  
 
グラフ理論  
 
知れば天国、知らねば地獄――「探索」虎の巻  
 

作成したデータはこちら。grpは処理単位をイメージしています。rnは処理単位の中での処理順位をイメージしています。seqはpkのイメージです。処理単位の中での。

コード表示

WITH sub AS (
	SELECT
		ROW_NUMBER() OVER(
			PARTITION BY mod(level, 2), mod(level, 3)
			ORDER BY
				mod(level, 4)
		) AS seq
		, mod(level, 2) AS grp1
		, mod(level, 3) AS grp2
		, mod(level, 4) AS rn
		, level * 10 AS qty
	FROM
		dual
	CONNECT BY
		level <= 20
) SELECT
	grp1
	, grp2
	, rn
	, seq
	, qty
  FROM
	sub
ORDER BY
	1
	, 2
	, 3
	, 4;

         GRP1          GRP2            RN           SEQ           QTY
------------- ------------- ------------- ------------- -------------
            0             0             0             1           120
            0             0             2             2           180
            0             0             2             3            60
            0             1             0             1           160
            0             1             0             2            40
            0             1             2             3           100
            0             2             0             1           200
            0             2             0             2            80
            0             2             2             3            20
            0             2             2             4           140
            1             0             1             1            90
            1             0             3             2           150
            1             0             3             3            30
            1             1             1             1            10
            1             1             1             2           130
            1             1             3             3           190
            1             1             3             4            70
            1             2             1             1           170
            1             2             1             2            50
            1             2             3             3           110

20 rows selected.

Elapsed: 00:00:00.00

作成したデータはこちら。grpは処理単位をイメージしています。rnは処理単位の中での処理順位をイメージしています。seqはpkのイメージです。処理単位の中での。処理単位の中で、seqが1のデータから並列に処理するイメージ。prior grp1 = grp1、prior grp2 = grp2で処理単位はすみ分ける。リニア処理(処理単位でシーケンシャルに処理するイメージ、大体は処理順をrow_numberとかで明記してやるといい感じになる。)をするために、PRIOR seq = seq – 1を指定。sys_connect_by_pathで結果を蓄積されていく様子をデバッグする。

コード表示

WITH sub AS (
	SELECT
		ROW_NUMBER() OVER(
			PARTITION BY mod(level, 2), mod(level, 3)
			ORDER BY
				mod(level, 4)
		) AS seq
		, mod(level, 2) AS grp1
		, mod(level, 3) AS grp2
		, mod(level, 4) AS rn
		, level * 10 AS qty
	FROM
		dual
	CONNECT BY
		level <= 20
) SELECT
	grp1
	, grp2
	, rn
	, seq
	, qty
	, sys_connect_by_path(TO_CHAR(qty), ',') AS path
  FROM
	sub
START WITH
	seq = 1
CONNECT BY PRIOR grp1 = grp1
           AND PRIOR grp2 = grp2
           AND PRIOR seq = seq - 1;


col path for a100

         GRP1          GRP2            RN           SEQ           QTY PATH
------------- ------------- ------------- ------------- ------------- ----------------------------------------------------------------------------------------------------
            0             0             0             1           120 ,120
            0             0             2             2           180 ,120,180
            0             0             2             3            60 ,120,180,60
            0             1             0             1           160 ,160
            0             1             0             2            40 ,160,40
            0             1             2             3           100 ,160,40,100
            0             2             0             1           200 ,200
            0             2             0             2            80 ,200,80
            0             2             2             3            20 ,200,80,20
            0             2             2             4           140 ,200,80,20,140
            1             0             1             1            90 ,90
            1             0             3             2           150 ,90,150
            1             0             3             3            30 ,90,150,30
            1             1             1             1            10 ,10
            1             1             1             2           130 ,10,130
            1             1             3             3           190 ,10,130,190
            1             1             3             4            70 ,10,130,190,70
            1             2             1             1           170 ,170
            1             2             1             2            50 ,170,50
            1             2             3             3           110 ,170,50,110

20 rows selected.

Elapsed: 00:00:00.02

訪問経路の列挙

START WITH depart IN (‘A’,’B’,’C’)とかで処理起点を明記。書かなければ、すべてのレコードが処理起点となり、connect by にしていした条件に従い、再帰する。

コード表示

DROP TABLE test____$_____ PURGE;
CREATE TABLE test____$_____ AS
WITH src AS (
    SELECT
        LEVEL AS rn
    FROM
        dual
    CONNECT BY
        LEVEL <= 26
	ORDER BY
		dbms_random.VALUE
)
SELECT
	CHR(64 + decode(MOD(s1.rn, 26), 0, 26, MOD(s1.rn, 26) ) ) AS depart
	,MIN(CHR(64 + decode(MOD(s2.rn, 26), 0, 26, MOD(s2.rn, 26) ) )) KEEP (DENSE_RANK FIRST ORDER BY dbms_random.VALUE) AS arrival
FROM
	src s1
	, src s2
GROUP BY
	CHR(64 + decode(MOD(s1.rn, 26), 0, 26, MOD(s1.rn, 26) ) )
;

AINE@pdb1> col depart for a20
AINE@pdb1> col arrival for a20
AINE@pdb1> select * from test____$_____;

DEPART               ARRIVAL
-------------------- --------------------
A                    A
B                    Q
C                    S
D                    K
E                    F
F                    R
G                    T
H                    U
I                    W
J                    U
K                    G
L                    F
M                    Y
N                    D
O                    P
P                    D
Q                    C
R                    Y
S                    G
T                    B
U                    C
V                    S
W                    Z
X                    K
Y                    N
Z                    N

26 rows selected.

Elapsed: 00:00:00.02

コード表示

WITH sub AS(
SELECT
	DENSE_RANK() OVER (ORDER BY CONNECT_BY_ROOT depart) AS grp
	,LEVEL AS rn
	, depart
	, arrival
	, PRIOR arrival AS pre_arrival
	, CONNECT_BY_ISLEAF AS isleaf
	, CONNECT_BY_ROOT arrival AS root_arrival_val
	, CONNECT_BY_ROOT depart AS root_depart_val
	, sys_connect_by_path(depart, ',') AS PATH
	, CONNECT_BY_ISCYCLE AS iscycle
FROM
	test____$_____
START WITH
	depart IN ('A','B','C')
CONNECT BY NOCYCLE
	depart = PRIOR arrival
ORDER BY
	LEVEL
	, PATH
)
SELECT
	s1.*
	,CASE
		WHEN NOT EXISTS(
					SELECT
						1
					FROM
						sub s2
					WHERE
						s1.root_depart_val = s2.root_depart_val
					AND s1.rn > s2.rn
					)THEN 1
		ELSE 0
	END AS root_flg
FROM
	sub s1
ORDER BY
	s1.grp
	,s1.rn
;

AINE@pdb1> col path for a50

          GRP            RN DEPA ARRI PRE_        ISLEAF ROOT ROOT PATH                                                     ISCYCLE      ROOT_FLG
------------- ------------- ---- ---- ---- ------------- ---- ---- -------------------------------------------------- ------------- -------------
            1             1 A    A                     1 A    A    ,A                                                             1             1
            2             1 B    Q                     0 Q    B    ,B                                                             0             1
            2             2 Q    C    Q                0 Q    B    ,B,Q                                                           0             0
            2             3 C    S    C                0 Q    B    ,B,Q,C                                                         0             0
            2             4 S    G    S                0 Q    B    ,B,Q,C,S                                                       0             0
            2             5 G    T    G                0 Q    B    ,B,Q,C,S,G                                                     0             0
            2             6 T    B    T                1 Q    B    ,B,Q,C,S,G,T                                                   1             0
            3             1 C    S                     0 S    C    ,C                                                             0             1
            3             2 S    G    S                0 S    C    ,C,S                                                           0             0
            3             3 G    T    G                0 S    C    ,C,S,G                                                         0             0
            3             4 T    B    T                0 S    C    ,C,S,G,T                                                       0             0
            3             5 B    Q    B                0 S    C    ,C,S,G,T,B                                                     0             0
            3             6 Q    C    Q                1 S    C    ,C,S,G,T,B,Q                                                   1             0

13 rows selected.

Elapsed: 00:00:00.01

たとえば、Gまでいく経路はとなったら、where prior arrival = ‘G’と指定すればいけると思う。出発地点を気にせずに。また、Kは経由せずにみたいなときどう書けばいいのだろう。。あるいはK、Yを経由して、今回の例だと、Gまでいく経路は24通り。

方針としてはカンマリストを行展開して検索がいいぽい。いつも思いますけど、書かかれているsqlのレベルが圧倒的で、素敵過ぎます。何度でも読み返したくなる記事ばかりです。

Oracleでカンマ区切りの文字列カラム(Jaywalking)に遭遇したときの対処法  
 

コード表示

WITH sub AS(
SELECT
	DENSE_RANK() OVER (ORDER BY CONNECT_BY_ROOT depart) AS grp
	,LEVEL AS rn
	, depart
	, arrival
	, PRIOR arrival AS pre_arrival
	, CONNECT_BY_ISLEAF AS isleaf
	, CONNECT_BY_ROOT arrival AS root_arrival_val
	, CONNECT_BY_ROOT depart AS root_depart_val
	, sys_connect_by_path(depart, ',') AS PATH
	, CONNECT_BY_ISCYCLE AS iscycle
FROM
	test____$_____
WHERE
	prior arrival = 'G'
CONNECT BY NOCYCLE
	depart = PRIOR arrival
ORDER BY
	LEVEL
	, PATH
)
SELECT
	s1.*
	,CASE
		WHEN NOT EXISTS(
					SELECT
						1
					FROM
						sub s2
					WHERE
						s1.root_depart_val = s2.root_depart_val
					AND s1.rn > s2.rn
					)THEN 1
		ELSE 0
	END AS root_flg
FROM
	sub s1
ORDER BY
	s1.grp
	,s1.rn
;

AINE@pdb1> col path for a50

          GRP            RN DEPA ARRI PRE_        ISLEAF ROOT ROOT PATH                                                     ISCYCLE      ROOT_FLG
------------- ------------- ---- ---- ---- ------------- ---- ---- -------------------------------------------------- ------------- -------------
            1             5 G    T    G                0 Q    B    ,B,Q,C,S,G                                                     0             1
            2             3 G    T    G                0 S    C    ,C,S,G                                                         0             1
            3             3 G    T    G                0 K    D    ,D,K,G                                                         0             1
            4             8 G    T    G                0 F    E    ,E,F,R,Y,N,D,K,G                                               0             1
            5             7 G    T    G                0 R    F    ,F,R,Y,N,D,K,G                                                 0             1
            6             5 G    T    G                0 U    H    ,H,U,C,S,G                                                     0             1
            7             7 G    T    G                0 W    I    ,I,W,Z,N,D,K,G                                                 0             1
            8             5 G    T    G                0 U    J    ,J,U,C,S,G                                                     0             1
            9             2 G    T    G                0 G    K    ,K,G                                                           0             1
           10             8 G    T    G                0 F    L    ,L,F,R,Y,N,D,K,G                                               0             1
           11             6 G    T    G                0 Y    M    ,M,Y,N,D,K,G                                                   0             1
           12             4 G    T    G                0 D    N    ,N,D,K,G                                                       0             1
           13             5 G    T    G                0 P    O    ,O,P,D,K,G                                                     0             1
           14             4 G    T    G                0 D    P    ,P,D,K,G                                                       0             1
           15             4 G    T    G                0 C    Q    ,Q,C,S,G                                                       0             1
           16             6 G    T    G                0 Y    R    ,R,Y,N,D,K,G                                                   0             1
           17             2 G    T    G                0 G    S    ,S,G                                                           0             1
           18             6 G    T    G                1 B    T    ,T,B,Q,C,S,G                                                   1             1
           19             4 G    T    G                0 C    U    ,U,C,S,G                                                       0             1
           20             3 G    T    G                0 S    V    ,V,S,G                                                         0             1
           21             6 G    T    G                0 Z    W    ,W,Z,N,D,K,G                                                   0             1
           22             3 G    T    G                0 K    X    ,X,K,G                                                         0             1
           23             5 G    T    G                0 N    Y    ,Y,N,D,K,G                                                     0             1
           24             5 G    T    G                0 N    Z    ,Z,N,D,K,G                                                     0             1

24 rows selected.

Elapsed: 00:00:00.01

リスト行展開ファンクション

せっかくなので、作られ方をトレースしたいと思います。

Oracleでカンマ区切りの文字列カラム(Jaywalking)に遭遇したときの対処法  
 

まずはリスト作成

コード表示

with sub as(
select listagg(depart,',')within group (order by rownum) as liz from test____$_____
)
select * from sub;

AINE@pdb1> col liz for a100
AINE@pdb1> with sub as( select listagg(depart,',')within group (order by rownum) as liz from test____$_____ ) select * from sub;

LIZ
----------------------------------------------------------------------------------------------------
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z

1 row selected.

Elapsed: 00:00:00.00
AINE@pdb1>

カンマ含む長さとカンマ含まない長さを求める。求めてカンマ要素の個数を求める

コード表示

with sub as(
select listagg(depart,',')within group (order by rownum) as liz from test____$_____
)
select
	length(liz) as oncnm
	,length(replace(liz,',','')) as offcnm
	,length(liz) - length(replace(liz,',','')) as diff
	,length(liz) - length(replace(liz,',','')) + 1 as elecnt
from
	sub
;

AINE@pdb1> with sub as( select listagg(depart,',')within group (order by rownum) as liz from test____$_____ ) select  length(liz) as oncnm  ,length(replace(liz,',','')) as offcnm  ,length(liz) - length(replace(liz,',','')) as diff  ,length(liz) - length(replace(liz,',','')) + 1 as elecnt from  sub  ;

        ONCNM        OFFCNM          DIFF        ELECNT
------------- ------------- ------------- -------------
           51            26            25            26

1 row selected.

Elapsed: 00:00:00.00

要素分、行複写 with connect by

コード表示

with sub as(
select listagg(depart,',')within group (order by rownum) as liz from test____$_____
)
select
	s1.*
	,level
from
	sub s1
connect by
	level <= length(s1.liz) - length(replace(s1.liz,',','')) + 1
;

AINE@pdb1> col liz for a100
AINE@pdb1> with sub as( select listagg(depart,',')within group (order by rownum) as liz from test____$_____ ) select s1.* ,level from sub s1 connect by level <= length(s1.liz) - length(replace(s1.liz,',','')) + 1 ;

LIZ                                                                                                          LEVEL
---------------------------------------------------------------------------------------------------- -------------
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              1
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              2
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              3
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              4
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              5
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              6
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              7
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              8
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              9
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             10
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             11
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             12
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             13
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             14
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             15
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             16
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             17
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             18
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             19
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             20
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             21
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             22
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             23
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             24
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             25
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             26

26 rows selected.

Elapsed: 00:00:00.00


instrとsubstrでバラス。instrって見つからないと0を返却するから、nullifで0だったら、nullにしてそのあと、nvlで置き換えしてるのがミソだとおもう。非再帰項と再帰項は条件分岐点となりうるのが多いと思われるので、decodeなりcaseでいい感じにする。最後の要素切り取るためにnvlで置き換える値は残り全部切り取るぐらいの気持ちで、大きい値を指定する。4000とか。

コード表示

with sub as(
select listagg(depart,',')within group (order by rownum) as liz from test____$_____
)
select
	s1.*
	,substr(liz,1,instr(liz,',',1,1) - 1) as non_rec
	,substr(liz,instr(liz,',',1,1) + 1,instr(liz,',',1,2)- instr(liz,',',1,1)-1) as on_rec_1
	,substr(liz,instr(liz,',',1,2) + 1,instr(liz,',',1,3) - instr(liz,',',1,2)-1) as on_rec_2
	,substr(liz,instr(liz,',',1,3) + 1,instr(liz,',',1,4) - instr(liz,',',1,3)-1) as on_rec_3
	,'--------' as tbc
	,substr(liz,instr(liz,',',1,24) + 1,instr(liz,',',1,25) - instr(liz,',',1,24)-1) as on_rec_24
	,substr(liz,instr(liz,',',1,25) + 1,instr(liz,',',1,26) - instr(liz,',',1,25)-1) as on_rec_25
	,substr(liz,instr(liz,',',1,25) + 1,nvl(nullif(instr(liz,',',1,26),0),4000) - instr(liz,',',1,25)-1) as on_rec_25
	,instr(liz,',',1,26)
	,level
from
	sub s1
connect by
	level <= length(s1.liz) - length(replace(s1.liz,',','')) + 1
;


規則性のあるリテラルをlevelで置き換える。

コード表示

WITH sub AS(
SELECT LISTAGG(depart,',')WITHIN GROUP (ORDER BY ROWNUM) AS liz FROM test____$_____
)
SELECT
	decode(LEVEL,1,substr(liz,1,instr(liz,',',1,LEVEL) - 1),substr(liz,instr(liz,',',1,LEVEL -1 ) + 1,nvl(nullif(instr(liz,',',1,LEVEL),0),4000)- instr(liz,',',1,LEVEL-1)-1)) AS ele
FROM
	sub s1
CONNECT BY
	LEVEL <= LENGTH(s1.liz) - LENGTH(REPLACE(s1.liz,',','')) + 1
;

カンマを与えたら、コレクション返却するようにする。collect関数、cast,コレクションタイプ宣言する。

COLLECT  
 

コンテキスト・スイッチについてplsqlからsql、sqlからplsql。参考。

BULK COLLECTとFORALLによるバルク処理  
 
コード表示

CREATE OR replace TYPE liz AS TABLE OF VARCHAR2(4000);
/

WITH sub AS(
SELECT LISTAGG(depart,',')WITHIN GROUP (ORDER BY ROWNUM) AS liz FROM test____$_____
)
SELECT
	CAST(COLLECT(
		decode(LEVEL,1,substr(liz,1,instr(liz,',',1,LEVEL) - 1),substr(liz,instr(liz,',',1,LEVEL -1 ) + 1,nvl(nullif(instr(liz,',',1,LEVEL),0),4000)- instr(liz,',',1,LEVEL-1)-1))
	)AS liz) as ele
FROM
	sub s1
CONNECT BY
	LEVEL <= LENGTH(s1.liz) - LENGTH(REPLACE(s1.liz,',','')) + 1
;

ファンクション化

コード表示

CREATE OR replace TYPE liz AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION segregate(p_liz VARCHAR2)
RETURN liz
AS v_liz liz;
BEGIN
WITH sub AS(
SELECT
	LEVEL AS rn
	,p_liz AS liz
FROM
	dual
CONNECT BY
	LEVEL <= LENGTH(p_liz) - LENGTH(REPLACE(p_liz,',','')) + 1
)
SELECT
	CAST(COLLECT(
		decode(rn,1,substr(liz,1,instr(liz,',',1,rn) - 1),substr(liz,instr(liz,',',1,rn -1 ) + 1,nvl(nullif(instr(liz,',',1,rn),0),4000)- instr(liz,',',1,rn-1)-1))
	)AS liz) AS ele
INTO v_liz
FROM
	sub;
RETURN v_liz;
END;
/

ファンクションのよびだし

コード表示

with sub as(
select listagg(depart,',')within group (order by rownum) as liz from test____$_____
)select * from (select * from sub),table(segregate(liz));