Table Functions
今日はOOUG(OTTAWA Oracle Users Group)からの引用です。
PL/SQLに関するいくつかのテクニックをまとめて紹介している
プレゼンテーション資料です。
例えば。。。
・DBMS_PROFILER（9月30日号でもご紹介しています）
http://imoment.blogspot.com/2006/11/dbmsprofiler-plsql-performance-tuning_07.html
・自立型トランザクション
（親のトランザクションとは独立して別のトランザクションとして動作できます）
・テーブルファンクションのパイプライン制御
（今回取り上げます）
・DBMS_XPLAN
（autotraceの実行計画表示に比べて色々なことができます）
・DBMS_LOCK.SLEEP
（unixのsleepコマンドと同様に眠れます）
・select for update wait nsec
（指定秒分まつことができます）
などなど、色々とシンプルに紹介されています。
ほとんど英文なしでひたすら例が記載されていますので、
英語が苦手でも大丈夫だと思います。
本当に英文が全然ないので、今回の引用は
選択の余地がありませんでした^^;
■ OTTAWA Oracle Users Group, Inc.
http://www.oug-ottawa.org/
(memberで無くてもDownloadsより資料は参照できます)
■ 引用資料
http://www.oug-ottawa.org/oougpresentations/2005nov24_plsql.zip
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
記事本文
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
◆ Table Functions
Produce collection of rows
(nested table / varray)
Query results or assign to collection variable
Can use Cursor Expressions
Table functions can be:
* pipelined
-Rows are returned as they are produced instead of
all at once, in one batch, when function completes
* parallelized
* streamed
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
英語の解釈
※自然な語順で解釈する癖をつけるために
敢えて不自然な日本語になっています。
()書きは後続修飾節の修飾対象です。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
◆ Table Functions
Produce collection of rows
行のコレクションを生成します。
(nested table / varray)
ネスト表 / varray
Query results or assign to collection variable
参照結果を戻したり、コレクション型の変数に割り当てたりできます。
Can use Cursor Expressions
カーソル式を使用できます。
Table functions can be:
テーブルファンクションはできます
* pipelined
パイプライン制御
-Rows are returned as they are produced
生成されると同時に行は戻ります。
instead of all at once, in one batch,
全ての行を一つのバッチで一度に戻すのではなく
when function completes
ファンクションが完了した時に（戻すのではなく）
* parallelized
パラレル化
* streamed
ストリーム化
★★英語の語順での解釈の元祖はこちら★★
私も大変お世話になりました。
英会話の先生と普通に喋れていた私は
本当の英語の片鱗に触れてショックを受けました。
＜SIM:スーパーエルマー＞
http://jpan.jp/?simurl
ご希望の方には通常の体験CDよりもずっと
本格的な通常では貰えない体験版CDをお譲りします。
2テーマ合計25分以上のCBS Documents、Hop Skip Jamp
それぞれの音声CDとそのスクリプトです。
ご希望の方はこのメールのreplyに希望の旨
お知らせ下さい。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
英語解説
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
Table Functions
※実際のテーブル内容の変わりに関数の戻り値をテーブルの内容として
使用することができます。
select * from table(function_name(cursor(select * from ex_table)));
Produce collection of rows
※当然この関数の戻り値は行の集まりです。
(nested table / varray)
※ネスト表は名前の通り、テーブルの中にさらにテーブルのような配列を
持つことができる機能です。変数として扱えるのでテーブルファンクションの
戻り値としても使用できます。
type rec_typ is record (
col1 number,
col2 varchar(10));
type next_typ is table of rec_typ;
varrayは配列です。
create type eg_varray as varray(10) of varchar2(10);
Can use Cursor Expressions
※テーブルファンクションへの引数としてカーソル式を渡すことができます。
以下の例ではselect結果のカーソルをテーブルファンクションに
引渡しています。
select * from table(function_name(cursor(select * from ex_table)));
* pipelined
※パイプライン化することで複数のレコードを同時並行的に処理
してくれます。(例は解説に掲載しました)
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
★ 英語ぷちクイズ ★
※答えと思うリンクをぷちっとクリックしてください。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
☆ちょっぴり不自然なのはどっち？
◆they recently bought a house.
┗ http://clickenquete.com/a/a.php?M0002066Q0017596A12cdc
◆they lately bought a house.
┗ http://clickenquete.com/a/a.php?M0002066Q0017596A2b27c
○結果を見る
┗ http://clickenquete.com/a/r.php?Q0017596Ce615
締切：2006年11月02日23時00分
協力：クリックアンケート http://clickenquete.com/
似ているようで少し違うrecentlyとlatelyの
ニュアンスの違いご存知ですか？
こたえは次回に発表します。
★★ランキングにも参加しております★★
こちらも「ぽち」していただけるととても嬉しいです。(^-^)
http://english.blogmura.com/in/080814.html
■ 前回のこたえ
☆どれが一番命令口調ですか？
◆You should get this work done.
◆You must get this work done.
◆You ought to get this work done.
◆You had better get this work done.
○結果を見る
┗ http://clickenquete.com/a/r.php?Q0017445Cc87c
答えはぶっちぎりでyou had betterです。
最近はあまり見かけませんが以前は英語の先生でも
had betterの方がshouldよりも低姿勢だと
説明している人もいました。
一見やわらかい表現ですが、極端なシチュエーションだと
銃をつきつけるぐらいの勢いがあります。
以下のようにorと組み合わせて使うことが良くあります。
you had better 命令 OR! 命令を守らない場合の何かしら良くない結末
「you had better get this work done, or the Boss will be mad!」
上司に使ったら首になるかも！？
残りはcontext依存ですが、
had better,must,should,out toの順でしょうか。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
Oracle解説
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
典型的なテーブルファンクションの使用例としては、
他システムから送られてくるcsvファイルなどの
効率的な取り込みなどがあります。
他システムから送られてくるcsvファイルを
外部表(例ではex_table)で読み込んで、
plsqlで1行ずつフェッチして、
複雑な変換などを施してダイレクトに実表に入れ込んでと。。。
これをパラレルでパイプライン化して実行すれば、
SQLLDRなどでは叶わない柔軟な変換を実現でき、
中間テーブルを利用するのに比べると遥かに早いスピードも実現できます。
※外部表について知りたい方は後続の説明をご覧ください
create view ex_view as
select * from table(function_name(cursor(select * from ex_table)));
insert into real_tab select /*+ APPEND */ from ex_view;
パイプライン化することで複数のレコードを同時並行的に処理
してくれます。
create or replace package ex_pkg is
type ex_refcur is ref cursor return ex_table%rowtype;
type ex_recset_typ is record(
col1 number,
col2 varchar2(10)
);
type ex_recset is table of ex_recset_typ;
function ex_func(aaa ex_refcur)
return ex_recset pipelined;
end ex_pkg;
/
create or replace package body ex_pkg is
function ex_func(aaa ex_refcur)
return ex_recset pipelined is
outrec ex_recset_typ;
inrec aaa%rowtype
begin
loop
fetch aaa into inrec;
exit when aaa%notfound;
outrec.col1 := inrec.col1;
outrec.col2 := inrec.col2;
pipe row(outrec);
end loop;
colse aaa;
return;
end;
end ex_pkg;
/
==== 外部表ってなに？ ココカラ ==================
外部表とはあたかもテーブルを検索するように
外部のOSなどのファイルにアクセスする機能です。
例を見れば一目瞭然です。
conn / as sysdba
create or replace directory ex_dir
as '/tmp/test_dir';
grant read,write on directory ex_dir to public;
create table ex_table
(col1 number,
col2 varchar2(10)
)
organization external
(
type oracle_loader
-- oracle_loaderの代わりにoracle_datapumpとすることで
-- data_pumpのダンプファイルを外部表で参照できます。
default directory ex_dir
access parameters
-- アクセスパラメータの詳細はユーティリティマニュアル
-- をご確認ください。
(
records delimited by newline
badfile ex_dir:'%a_%p.bad'
logfile ex_dir:'%a_%p.log'
fields teminated by ','
missing field values are null
(col1,col2)
)
location ('ex1.dat','ex2.dat')
)
parallel 3
reject limit unlimited;
alter session enable parallel dml;
insert into real_tab select * from ex_table;
==== 外部表ってなに？ ココマデ ==================
PL/SQLに関するいくつかのテクニックをまとめて紹介している
プレゼンテーション資料です。
例えば。。。
・DBMS_PROFILER（9月30日号でもご紹介しています）
http://imoment.blogspot.com/2006/11/dbmsprofiler-plsql-performance-tuning_07.html
・自立型トランザクション
（親のトランザクションとは独立して別のトランザクションとして動作できます）
・テーブルファンクションのパイプライン制御
（今回取り上げます）
・DBMS_XPLAN
（autotraceの実行計画表示に比べて色々なことができます）
・DBMS_LOCK.SLEEP
（unixのsleepコマンドと同様に眠れます）
・select for update wait nsec
（指定秒分まつことができます）
などなど、色々とシンプルに紹介されています。
ほとんど英文なしでひたすら例が記載されていますので、
英語が苦手でも大丈夫だと思います。
本当に英文が全然ないので、今回の引用は
選択の余地がありませんでした^^;
■ OTTAWA Oracle Users Group, Inc.
http://www.oug-ottawa.org/
(memberで無くてもDownloadsより資料は参照できます)
■ 引用資料
http://www.oug-ottawa.org/oougpresentations/2005nov24_plsql.zip
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
記事本文
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
◆ Table Functions
Produce collection of rows
(nested table / varray)
Query results or assign to collection variable
Can use Cursor Expressions
Table functions can be:
* pipelined
-Rows are returned as they are produced instead of
all at once, in one batch, when function completes
* parallelized
* streamed
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
英語の解釈
※自然な語順で解釈する癖をつけるために
敢えて不自然な日本語になっています。
()書きは後続修飾節の修飾対象です。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
◆ Table Functions
Produce collection of rows
行のコレクションを生成します。
(nested table / varray)
ネスト表 / varray
Query results or assign to collection variable
参照結果を戻したり、コレクション型の変数に割り当てたりできます。
Can use Cursor Expressions
カーソル式を使用できます。
Table functions can be:
テーブルファンクションはできます
* pipelined
パイプライン制御
-Rows are returned as they are produced
生成されると同時に行は戻ります。
instead of all at once, in one batch,
全ての行を一つのバッチで一度に戻すのではなく
when function completes
ファンクションが完了した時に（戻すのではなく）
* parallelized
パラレル化
* streamed
ストリーム化
★★英語の語順での解釈の元祖はこちら★★
私も大変お世話になりました。
英会話の先生と普通に喋れていた私は
本当の英語の片鱗に触れてショックを受けました。
＜SIM:スーパーエルマー＞
http://jpan.jp/?simurl
ご希望の方には通常の体験CDよりもずっと
本格的な通常では貰えない体験版CDをお譲りします。
2テーマ合計25分以上のCBS Documents、Hop Skip Jamp
それぞれの音声CDとそのスクリプトです。
ご希望の方はこのメールのreplyに希望の旨
お知らせ下さい。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
英語解説
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
Table Functions
※実際のテーブル内容の変わりに関数の戻り値をテーブルの内容として
使用することができます。
select * from table(function_name(cursor(select * from ex_table)));
Produce collection of rows
※当然この関数の戻り値は行の集まりです。
(nested table / varray)
※ネスト表は名前の通り、テーブルの中にさらにテーブルのような配列を
持つことができる機能です。変数として扱えるのでテーブルファンクションの
戻り値としても使用できます。
type rec_typ is record (
col1 number,
col2 varchar(10));
type next_typ is table of rec_typ;
varrayは配列です。
create type eg_varray as varray(10) of varchar2(10);
Can use Cursor Expressions
※テーブルファンクションへの引数としてカーソル式を渡すことができます。
以下の例ではselect結果のカーソルをテーブルファンクションに
引渡しています。
select * from table(function_name(cursor(select * from ex_table)));
* pipelined
※パイプライン化することで複数のレコードを同時並行的に処理
してくれます。(例は解説に掲載しました)
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
★ 英語ぷちクイズ ★
※答えと思うリンクをぷちっとクリックしてください。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
☆ちょっぴり不自然なのはどっち？
◆they recently bought a house.
┗ http://clickenquete.com/a/a.php?M0002066Q0017596A12cdc
◆they lately bought a house.
┗ http://clickenquete.com/a/a.php?M0002066Q0017596A2b27c
○結果を見る
┗ http://clickenquete.com/a/r.php?Q0017596Ce615
締切：2006年11月02日23時00分
協力：クリックアンケート http://clickenquete.com/
似ているようで少し違うrecentlyとlatelyの
ニュアンスの違いご存知ですか？
こたえは次回に発表します。
★★ランキングにも参加しております★★
こちらも「ぽち」していただけるととても嬉しいです。(^-^)
http://english.blogmura.com/in/080814.html
■ 前回のこたえ
☆どれが一番命令口調ですか？
◆You should get this work done.
◆You must get this work done.
◆You ought to get this work done.
◆You had better get this work done.
○結果を見る
┗ http://clickenquete.com/a/r.php?Q0017445Cc87c
答えはぶっちぎりでyou had betterです。
最近はあまり見かけませんが以前は英語の先生でも
had betterの方がshouldよりも低姿勢だと
説明している人もいました。
一見やわらかい表現ですが、極端なシチュエーションだと
銃をつきつけるぐらいの勢いがあります。
以下のようにorと組み合わせて使うことが良くあります。
you had better 命令 OR! 命令を守らない場合の何かしら良くない結末
「you had better get this work done, or the Boss will be mad!」
上司に使ったら首になるかも！？
残りはcontext依存ですが、
had better,must,should,out toの順でしょうか。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
Oracle解説
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
典型的なテーブルファンクションの使用例としては、
他システムから送られてくるcsvファイルなどの
効率的な取り込みなどがあります。
他システムから送られてくるcsvファイルを
外部表(例ではex_table)で読み込んで、
plsqlで1行ずつフェッチして、
複雑な変換などを施してダイレクトに実表に入れ込んでと。。。
これをパラレルでパイプライン化して実行すれば、
SQLLDRなどでは叶わない柔軟な変換を実現でき、
中間テーブルを利用するのに比べると遥かに早いスピードも実現できます。
※外部表について知りたい方は後続の説明をご覧ください
create view ex_view as
select * from table(function_name(cursor(select * from ex_table)));
insert into real_tab select /*+ APPEND */ from ex_view;
パイプライン化することで複数のレコードを同時並行的に処理
してくれます。
create or replace package ex_pkg is
type ex_refcur is ref cursor return ex_table%rowtype;
type ex_recset_typ is record(
col1 number,
col2 varchar2(10)
);
type ex_recset is table of ex_recset_typ;
function ex_func(aaa ex_refcur)
return ex_recset pipelined;
end ex_pkg;
/
create or replace package body ex_pkg is
function ex_func(aaa ex_refcur)
return ex_recset pipelined is
outrec ex_recset_typ;
inrec aaa%rowtype
begin
loop
fetch aaa into inrec;
exit when aaa%notfound;
outrec.col1 := inrec.col1;
outrec.col2 := inrec.col2;
pipe row(outrec);
end loop;
colse aaa;
return;
end;
end ex_pkg;
/
==== 外部表ってなに？ ココカラ ==================
外部表とはあたかもテーブルを検索するように
外部のOSなどのファイルにアクセスする機能です。
例を見れば一目瞭然です。
conn / as sysdba
create or replace directory ex_dir
as '/tmp/test_dir';
grant read,write on directory ex_dir to public;
create table ex_table
(col1 number,
col2 varchar2(10)
)
organization external
(
type oracle_loader
-- oracle_loaderの代わりにoracle_datapumpとすることで
-- data_pumpのダンプファイルを外部表で参照できます。
default directory ex_dir
access parameters
-- アクセスパラメータの詳細はユーティリティマニュアル
-- をご確認ください。
(
records delimited by newline
badfile ex_dir:'%a_%p.bad'
logfile ex_dir:'%a_%p.log'
fields teminated by ','
missing field values are null
(col1,col2)
)
location ('ex1.dat','ex2.dat')
)
parallel 3
reject limit unlimited;
alter session enable parallel dml;
insert into real_tab select * from ex_table;
==== 外部表ってなに？ ココマデ ==================
コメントを投稿