この記事は約46分37秒で読むことができます。

パイプラインについて

参考文献

パイプライン・テーブル・ファンクション  

native plsqlはplsql functionを1つのみ書けばOK。native plsql覚えるか。

津島博士のパフォーマンス講座 第25回 良いSQLについて(4)

とりあえず、パイプライン・ファンクション作ってみた

カーソルのオープンクローズは暗黙的にあーだこーだってやつかな。省けるのはいいね。

コード表示

DROP TABLE test___item___ PURGE;
CREATE TABLE test___item___ ( rn NUMBER  ,nm VARCHAR2(20 BYTE) ); 
INSERT INTO test___item___ (rn,nm) VALUES (100,'Cookies');
INSERT INTO test___item___ (rn,nm) VALUES (200,'Brownies');
INSERT INTO test___item___ (rn,nm) VALUES (300,'Pancakes');
COMMIT;
/

DROP TYPE item_liz;
/
DROP TYPE item;
/
CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER, nm VARCHAR2(20));
/
CREATE OR REPLACE TYPE item_liz IS TABLE OF item;
/

CREATE OR REPLACE FUNCTION test___ppl___(p_csr SYS_REFCURSOR) RETURN item_liz PIPELINED IS
	rec test___item___%rowtype;
	BEGIN
		LOOP
			FETCH p_csr INTO rec;
			EXIT WHEN p_csr%notfound;
			PIPE ROW(item(rec.rn,rec.nm));
		END LOOP;
		RETURN;
	END;
/

カーソル式でかましてからファンクションに渡して返却されてきたコレクションをtableでキュッとする。

コード表示

SELECT * FROM TABLE(test___ppl___(CURSOR(SELECT * FROM test___item___)));
コード表示

[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 Mon Mar 18 23:14:35 2019
Version 18.3.0.0.0

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

Last Successful login time: Mon Mar 18 2019 21:33:50 +09:00

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

AINE@pdb1> SELECT * FROM TABLE(test___ppl___(CURSOR(SELECT * FROM test___item___)));

           RN NM
------------- --------------------
          100 Cookies
          200 Brownies
          300 Pancakes

3 rows selected.

Elapsed: 00:00:00.01

実行計画みてみる

コード表示

ALTER SESSION SET STATISTICS_LEVEL = ALL;

SELECT * FROM TABLE(test___ppl___(CURSOR(SELECT * FROM test___item___)));

SET LINESIZE 300;
SET PAGESIZE 1000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALL ALLSTATS LAST ADAPTIVE'));

テーブル・ファンクション処理はCOLLECTION ITERATOR PICKLER FETCHと出力されるそう

Oracle DatabaseでSQLの性能計測2(DBMS_XPLAN&DBMS_SQLTUNE編)【Oracle Database or GoldenGate Advent Calendar 2018 Day 8】  
 
コード表示

SQL_ID  7yq95vy4kkd1c, child number 3
-------------------------------------
SELECT * FROM TABLE(test___ppl___(CURSOR(SELECT * FROM test___item___)))
 
Plan hash value: 2260397670
 
----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |      1 |        |       |    29 (100)|          |      3 |00:00:00.01 |       9 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| TEST___PPL___  |      1 |   8168 | 16336 |    29   (0)| 00:00:01 |      3 |00:00:00.01 |       9 |
|   2 |   TABLE ACCESS FULL               | TEST___ITEM___ |      0 |      3 |    75 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F5BB74E1 / KOKBF$0@SEL$2
   2 - SEL$3        / TEST___ITEM___@SEL$3
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - VALUE(A0)[22], VALUE(A0)[20]
   2 - "TEST___ITEM___"."RN"[NUMBER,22], "TEST___ITEM___"."NM"[VARCHAR2,20]
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

とりあえず、パイプライン・ファンクション作ってみたけど、なにそれなにができるの

ETL (Extract Transform Load) 的な一連の処理結果を受け取ることができるぽい。

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER, nm VARCHAR2(20));
/

CREATE OR REPLACE TYPE item_liz IS TABLE OF item;
/

CREATE OR REPLACE FUNCTION test___ppl___(p_csr SYS_REFCURSOR) RETURN item_liz PIPELINED IS
	rec test___item___%rowtype;
	BEGIN
		LOOP
			--Extract
			FETCH p_csr INTO rec;
			EXIT WHEN p_csr%notfound;
			--Transform
			rec.nm := upper(rec.nm);
			--Load
			PIPE ROW(item(rec.rn,rec.nm));
		END LOOP;
		RETURN;
	END;
/

大文字にしただけだ

コード表示

AINE@pdb1> SELECT * FROM TABLE(test___ppl___(CURSOR(SELECT * FROM test___item___)));

           RN NM
------------- --------------------
          100 COOKIES
          200 BROWNIES
          300 PANCAKES

3 rows selected.

Elapsed: 00:00:00.01

table functionかませば、他のテーブルとも普通に結合できる。テーブルと同じように扱えるんだってしっておく

コード表示

SELECT s1.rn,s2.nm AS bef,s1.nm AS aft FROM TABLE(test___ppl___(CURSOR(SELECT * FROM test___item___))) s1,test___item___ s2 WHERE s1.rn = s2.rn;

AINE@pdb1> SELECT s1.rn,s2.nm AS bef,s1.nm AS aft FROM TABLE(test___ppl___(CURSOR(SELECT * FROM test___item___))) s1,test___item___ s2 WHERE s1.rn = s2.rn;

           RN BEF                  AFT
------------- -------------------- --------------------
          100 Cookies              COOKIES
          200 Brownies             BROWNIES
          300 Pancakes             PANCAKES

3 rows selected.

Elapsed: 00:00:00.01

実行計画。こっちのほうがselect rn,nm as bef,upper(nm) as aft from test___item___;よりもcostとかおおきくなるのはね。

コード表示

SQL_ID  bq7c4g2ua84kg, child number 0
-------------------------------------
SELECT s1.rn,s2.nm as bef,s1.nm as aft FROM 
TABLE(test___ppl___(CURSOR(SELECT * FROM test___item___))) 
s1,test___item___ s2 where s1.rn = s2.rn
 
Plan hash value: 2243294319
 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |IN-OUT| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |      1 |        |       |    32 (100)|          |      |      3 |00:00:00.01 |      16 |       |       |          |
|*  1 |  HASH JOIN                         |                |      1 |   8168 |   215K|    32   (0)| 00:00:01 |      |      3 |00:00:00.01 |      16 |  1744K|  1744K|  829K (0)|
|   2 |   TABLE ACCESS FULL                | TEST___ITEM___ |      1 |      3 |    75 |     3   (0)| 00:00:01 |      |      3 |00:00:00.01 |       7 |       |       |          |
|   3 |   COLLECTION ITERATOR PICKLER FETCH| TEST___PPL___  |      1 |   8168 | 16336 |    29   (0)| 00:00:01 |      |      3 |00:00:00.01 |       9 |       |       |          |
|   4 |    TABLE ACCESS FULL               | TEST___ITEM___ |      0 |      3 |    75 |     3   (0)| 00:00:01 | PCWP |      0 |00:00:00.01 |       0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / S2@SEL$1
   3 - SEL$F5BB74E1 / KOKBF$0@SEL$2
   4 - SEL$3        / TEST___ITEM___@SEL$3
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S2"."RN"=VALUE(KOKBF$))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) "S2"."NM"[VARCHAR2,20], VALUE(A0)[22], VALUE(A0)[20]
   2 - (rowset=256) "S2"."RN"[NUMBER,22], "S2"."NM"[VARCHAR2,20]
   3 - VALUE(A0)[22], VALUE(A0)[20]
   4 - "TEST___ITEM___"."RN"[NUMBER,22], "TEST___ITEM___"."NM"[VARCHAR2,20]
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

ちなみにparallelだとこんなかんじにかけたりする

今は用途は見えてこない。が、移行時に役立つと信じている。あとでやる。pragma autonomous_transactionとかとコラボすれば、多分いい感じになる。

コード表示


DROP TABLE test___item___ PURGE;
CREATE TABLE test___item___ ( rn NUMBER  ,nm VARCHAR2(20 BYTE) ); 
INSERT INTO test___item___ (rn,nm) VALUES (100,'Cookies');
INSERT INTO test___item___ (rn,nm) VALUES (200,'Brownies');
INSERT INTO test___item___ (rn,nm) VALUES (300,'Pancakes');
COMMIT;
/
DROP TYPE item_liz;
/
DROP TYPE item;
/
CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER, nm VARCHAR2(20));
/
CREATE OR REPLACE TYPE item_liz IS TABLE OF item;
/
CREATE OR REPLACE FUNCTION test___ppl_prl___(p_csr SYS_REFCURSOR) RETURN item_liz PIPELINED PARALLEL_ENABLE (PARTITION p_csr by any) IS
	rec test___item___%rowtype;
	BEGIN
		LOOP
			FETCH p_csr INTO rec;
			--Extract
			EXIT WHEN p_csr%notfound;
			--Transform
			rec.nm := upper(rec.nm);
			--Load
			PIPE ROW(item(rec.rn,rec.nm));
		END LOOP;
		RETURN;
	END;
/

相関意識すればselect句にもかけたりする。

コード表示

SELECT s1.*,test___ppl___(CURSOR(SELECT * FROM test___item___ s2 WHERE s1.rn = s2.rn)) AS liz FROM test___item___ s1;

SELECT s1.*,test___ppl_prl___(CURSOR(SELECT * FROM test___item___ s2 WHERE s1.rn = s2.rn)) AS liz FROM test___item___ s1;

コード表示

AINE@pdb1> SELECT s1.*,test___ppl___(CURSOR(SELECT * FROM test___item___ s2 WHERE s1.rn = s2.rn)) AS liz FROM test___item___ s1;

           RN NM                   LIZ(RN, NM)
------------- -------------------- ----------------------------------------------------------------------------------------------------
          100 Cookies              ITEM_LIZ(ITEM(100, 'Cookies'))
          200 Brownies             ITEM_LIZ(ITEM(200, 'Brownies'))
          300 Pancakes             ITEM_LIZ(ITEM(300, 'Pancakes'))

3 rows selected.

Elapsed: 00:00:00.01

AINE@pdb1> SELECT s1.*,test___ppl_prl___(CURSOR(SELECT * FROM test___item___ s2 WHERE s1.rn = s2.rn)) AS liz FROM test___item___ s1;

           RN NM                   LIZ(RN, NM)
------------- -------------------- ----------------------------------------------------------------------------------------------------
          100 Cookies              ITEM_LIZ(ITEM(100, 'COOKIES'))
          200 Brownies             ITEM_LIZ(ITEM(200, 'BROWNIES'))
          300 Pancakes             ITEM_LIZ(ITEM(300, 'PANCAKES'))

3 rows selected.

Elapsed: 00:00:00.00

あとはcursor式の使い勝手としては流し込む量をwhere句で絞ってから投入できるとこかな。流し込んでから絞ってもいいとは思うけど、処理量をどの段階で絞るかを意識することが大事。insert-select merge bulk collect parallel hint とかとかと比較してみるとか。

コード表示

SELECT * FROM TABLE(test___ppl___(CURSOR(SELECT * FROM test___item___ where rn < 200))) s1,test___item___ s2 WHERE s1.rn = s2.rn;

SELECT * FROM TABLE(test___ppl_prl___(CURSOR(SELECT * FROM test___item___ where rn < 200))) s1,test___item___ s2 WHERE s1.rn = s2.rn;

コード表示

AINE@pdb1> SELECT * FROM TABLE(test___ppl___(CURSOR(SELECT * FROM test___item___ where rn < 200))) s1,test___item___ s2 WHERE s1.rn = s2.rn;

           RN NM                              RN NM
------------- -------------------- ------------- --------------------
          100 Cookies                        100 Cookies

1 row selected.

Elapsed: 00:00:00.01

AINE@pdb1> SELECT * FROM TABLE(test___ppl_prl___(CURSOR(SELECT * FROM test___item___ where rn < 200))) s1,test___item___ s2 WHERE s1.rn = s2.rn;

           RN NM                              RN NM
------------- -------------------- ------------- --------------------
          100 COOKIES                        100 Cookies

1 row selected.

Elapsed: 00:00:00.00

これからのための情報収集

いい記事見つけた。DBmagazine読んだことなかったいままで。

Oracle - 日本エクセム  
Pipelined Table Functions  

いい動画見つけた。sleepいいね。

Efficient Function Calls From SQL (Part 5) : Pipelined Table Functions  

sleepといえばあの記事!このsleep使いようによっては面白いことできそうなんだよなってずっと思っている。時間どうにか確保して作れ。

DBMS_LOCK.SLEEPの代替SQLをいろいろ考えてみよう(Oracle)  

type周り具体例交えてあって分かりやすい!!!

12cからレコード型のコレクションでもテーブル表現が使えるぞ (Oracle)  

移行作業時、このログの吐き方いいよなー。linuxのpackage展開とかしているときのインジケータみたいなの作れそうってずっと思っている。あと何分で終わるかって重要。時間どうにか確保して作れ。

Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle  

パラレル処理の動き

How Parallel Execution Works  

オブジェクトタイプなんだろうな使いやすいのはきっと

ディクショナリから動的にcreate or replace 文作成する。

How Parallel Execution WorksPL/SQL で例外が発生しても処理を継続させる  
コード表示

DROP TABLE test___src___ PURGE;
CREATE TABLE test___src___ AS WITH src AS ( SELECT LEVEL AS rn FROM dual CONNECT BY LEVEL <= 1000 ) SELECT s1.rn  ,dbms_random.STRING('X',5) AS str FROM src s1; 
DROP TABLE test___tar___ PURGE;
CREATE TABLE test___tar___ AS SELECT * FROM test___src___ WHERE 1 <> 1;

SELECT COUNT(*) FROM test___src___;
SELECT COUNT(*) FROM test___tar___;


権限不足??

コード表示

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE create_obj_type AS

	CURSOR csr IS WITH sub AS (
		SELECT
			table_name
			, to_clob('create or replace type '
			            || table_name
			            || 'typ is object('
			            || RTRIM(XMLAGG(XMLELEMENT(E, column_name
			                                                || ' '
			                                                || data_type
			                                                || '('
			                                                || data_length
			                                                || ')', ',').EXTRACT('//text()') ).getclobval(), ',')
			            || ')') AS create_obj_typ
			, to_clob('create or replace type '
			            || table_name
			            || 'liz is table of '
			            || table_name
			            || 'typ') AS create_liz
			, to_clob('drop type '
			            || table_name
			            || 'liz') AS drop_liz
			, to_clob('drop type '
			            || table_name
			            || 'typ') AS drop_obj_typ
		FROM
			user_tab_columns
		GROUP BY
			table_name
	) SELECT
		vals
	  FROM
		sub UNPIVOT ( vals
			FOR cols
		IN ( create_obj_typ
		, create_liz
		, drop_liz
		, drop_obj_typ ) )
	ORDER BY
		CASE
			WHEN cols LIKE 'DROP%' THEN 1
			ELSE NULL
		END
		, table_name
		, CASE
				WHEN cols LIKE '%OBJ_TYP' THEN 1
				ELSE NULL
			END
		, table_name;

	build_sql   CLOB;
BEGIN
	build_sql := to_clob(' ');
	OPEN csr;
	LOOP
		BEGIN
			FETCH csr INTO build_sql;
			EXIT WHEN csr%notfound;
			dbms_output.put_line(build_sql);
			EXECUTE IMMEDIATE build_sql;
			EXCEPTION WHEN OTHERS THEN dbms_output.put_line('[ '|| SQLCODE||']'||sqlerrm);
		END;
	END LOOP;
	CLOSE csr;
END;
/

EXEC create_obj_type;
/

権限不足??所有者権限とか実行者権限とかかな

role_sys_privs SESSION_PRIVS USER_TAB_PRIVS_RECD USER_TAB_PRIVS_MADE SESSION_ROLESらへんをいい感じにまとめて一覧化するsqlかく

個々のテーブルがSELECT可能でもビュー化するとコンパイルできないことがある。 個々の SQL が直接実行可能でもストアド・サブプログラム化するとコンパイルできないことがある。 主な理由はロールによる権限が無効化されることを忘れているため。 DBA ロールユーザだとよくやってしまう。(SELECT ANY TABLE権限では足りない) 実行ユーザーには個別に各種権限の付与を行っておく必要がある。らしいので、調べる。

で結局、BEQUEATH VIEWってなんなのさ?(Oracle)  

コード表示

drop type TEST___SRC___typ
[ -4043]ORA-04043: オブジェクトTEST___SRC___TYPは存在しません。
drop type TEST___SRC___liz
[ -4043]ORA-04043: オブジェクトTEST___SRC___LIZは存在しません。
drop type TEST___TAR___typ
[ -4043]ORA-04043: オブジェクトTEST___TAR___TYPは存在しません。
drop type TEST___TAR___liz
[ -4043]ORA-04043: オブジェクトTEST___TAR___LIZは存在しません。
create or replace type TEST___SRC___typ is object(RN NUMBER(22),STR VARCHAR2(4000))
[ -1031]ORA-01031: 権限が不足しています
create or replace type TEST___SRC___liz is table of TEST___SRC___typ
[ -1031]ORA-01031: 権限が不足しています
create or replace type TEST___TAR___typ is object(RN NUMBER(22),STR VARCHAR2(4000))
[ -1031]ORA-01031: 権限が不足しています
create or replace type TEST___TAR___liz is table of TEST___TAR___typ
[ -1031]ORA-01031: 権限が不足しています


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

Leave a Reply

Your email address will not be published. Required fields are marked *