こんぺいとう

A5で実行してみる

下記のサイトを参考に

https://db.just4fun.biz/?PL/pgSQL%E3%81%A8%E3%81%AF%EF%BC%9F

・どの言語が使えるようになっているかを確認

select * from pg_language;

・作成済みのファンクションを調べる

SELECT * FROM pg_proc;

または

SELECT proname,prosrc FROM pg_proc WHERE proname = 'update_w_blnc_slip_mgt_hist'

prosrcの値が実行される内容

・ストアドプロシージャの作成
[ORACLE]

CREATE [ OR REPLACE ] PROCEDURE ストアドプロシージャ名
[ (引数名 データ型),... ]
IS [ 変数名 データ型,... ]
BEGIN
処理内容
END ;

例：

 CREATE PROCEDURE pro_200
 IS
 BEGIN
     SELECT DISTINCT KK.顧客名 FROM 受注表 JJ,顧客表 KK
         WHERE JJ.顧客コード = KK.顧客コード
               AND JJ.受注個数 >= 200 ;
 END ;

[PostgreSQL]

CREATE FUNCTION 関数名 ( [ 引数のデータ型,... ] )
RETURNS [ SETOF ] 返り値のデータ型
AS
'
[ラベル]
[ DECLARE 変数名 データ型 ... ]
BEGIN
処理内容
END ;
'
LANGUAGE 'sql' ;

例:

 CREATE FUNCTION func_200()
 RETURNS SETOF CHAR(20)
 AS
 '
 BEGIN
     SELECT DISTINCT KK.顧客名 FROM 受注表 JJ,顧客表 KK
         WHERE JJ.顧客コード = KK.顧客コード
               AND JJ.受注個数 >= 200 ;
 END ;
 '
 LANGUAGE 'sql' ;

・ストアドプロシージャの実行と削除

<<ORACLE>>

BEGIN
[ EXECUTE ] ストアドプロシージャ名 [(引数,...)]
END ;

例:

 BEGIN
       pro_200
 END ;

参考URL

https://www.postgresql.jp/document/7.2/programmer/plpgsql.html

https://www.postgresql.jp/document/9.2/html/catalog-pg-proc.html

https://www.techscore.com/tech/sql/SQL13/13_02.html/

https://www.postgresql.jp/document/8.1/html/plpgsql-trigger.html

・サブクエリーを引数に取る場合、IN述語よりもEXISTS述語を使え

IN[NOT IN]とEXISTS[NOT EXISTS]は、たいていの場合、全く等しい結果集合を返します。しかし、この両者でサブクエリーを作る場合は、EXISTSの方が圧倒的に速い。例えば、有名人の誰かと同じ誕生日に生まれた社員を全て探すためのSQLを考えます。以下の2つのSQLは、同じ結果を返しますが、2番目の方が速いです。

使用テーブル：
Personnel(社員テーブル)
Celebrities(有名人テーブル)

１．INを使った場合(遅い)

SELECT name
FROM Personnel
WHERE birthday IN (SELECT birthday
FROM Celebrities); 

２．EXISTSを使った場合(速い)

SELECT P.name
FROM Personnel AS P
WHERE EXISTS (SELECT
FROM Clelebrities AS C
WHERE P.birthday = C.birthday);
 

なぜ、EXISTSの方が速いか？その理由は以下の2点です。

もし結合キー(この場合はbirthday)にインデックスが張られていれば、Celebritiesテーブルの実表は見に行かず、インデックスのみを参照する。
もしCelebritiesテーブルがインデックスを持っていなくても、優れたオプティマイザならば、birthday列をソートした一時テーブルを作り、2分探索することで、全表走査よりも効率的に検索を行なう。

繰り返す。サブクエリーを引数に取る場合、IN述語を使うな

EXISTSで代用でき、しかもたいてい、その方が圧倒的に速いからです。
INの引数にサブクエリーを取る場合、DBは、まずサブクエリーから実行し、その結果を一時的な作業テーブル上に格納します。つまりインライン・ビューを作るのです。その後に、その作業テーブルを全件走査します。これは、多くの場合、非常にコストがかかります。EXISTSを使えば、既に見たように、一時テーブルなど作成されません。
ただし、ソースコードの可読性という点において、IN述語はEXISTS述語に勝ります。要するに、IN述語で書いた方がぱっと見て意味が分かりやすいコードになります。従って、IN述語を使っても十分短い応答時間が確保されているなら、そのSQL文を敢えてEXISTS述語で書き直す必要はありません。

・IN述語の引数リストには、最もありそうなキーを左寄せしろ

なぜなら、INは、左から右へ引数を評価し、見つかった時点でtrueを返しそれより右の引数は見ないからです。以下の2つのSQLを比較してください。

１．遅い(かもしれない)

SELECT *
FROM Address
WHERE prefecture IN ('鳥取', '徳島', '東京', '大阪');

２．速い(かもしれない)

SELECT *
FROM Address
WHERE prefecture IN ('東京', '大阪', '鳥取', '徳島');

・EXISTS述語のサブクエリー内では、SELECT * を使え

サブクエリーのSELECT句を書くには、以下の3つの選択肢があります。

①EXISTS (SELECT * FROM …)
②EXISTS (SELECT カラム名 FROM …)
③EXISTS (SELECT 定数 FROM …)

このうち、最も良いのは①です。この書き方は、オプティマイザにどのカラムを使うべきかの選択を委ねることになります。そして、カラムにインデックスが張られていれば、実表を走査する必要はありません。
ただし、例外的に②や③の方が①よりも高速な場合もあります。古いSQL製品の場合は②のように列名を指定した方が速いこともあります。また、Oracleその他の製品では、③のように「SELECT 1 FROM …」など、定数を指定すると高速になります。この書き方は、行へのポインタさえ得られれば、実際の行を読む必要がないことを、DBMSに指摘するからです。
しかし、②は、もはや使う機会はないでしょうし、③の書き方は意味的な混乱を招くので、①を採用するべきです。

・3つ以上のテーブルを結合させる時は冗長な結合条件を書け

Table1：小さい
Table2：大きい
Table3：大きい

上記のようなサイズの３つのテーブルがあるとします。共通のカラムで3つのテーブルを結合するとき、次のように書けます。

SELECT *
FROM Table1, Table2, Table3
WHERE Table1.common = Table2.common
AND Table1.common = Table3.common;

あるいは、次のようにも書けます。しかし、こちらの方が遅くて非効率です。

SELECT *
FROM Table1, Table2, Table3
WHERE Table2.common = Table3.common
AND Table1.common = Table3.common;

なぜなら、2番目のSQLは、最初にTable2とTable3という大きなテーブルを結合した大きな結果集合と、Table1とTable3を結合した小さな結果集合でマッチングを行なうからです。だから、結合条件は、小さなテーブルを最初に書くべきです。
最も良い書き方は、テーブルのサイズが変わっても大丈夫なように、オプティマイザ自身にテーブルのサイズを決定させる書き方です。そのためには、次のように結合条件に冗長性を持たせます。

SELECT *
FROM Table1, Table2, Table3
WHERE Table1.common = Table2.common
AND Table2.common = Table3.common;
AND Table3.common = Table1.common;

もちろん、テーブルのサイズにあまり変化がない場合は、あえて結合条件に冗長性を持たせる必要はありません。

・行数を数えるときはCOUNT(*)よりもCOUNT(カラム名)を使え

このトリックは、インデックスを使います。したがって、これがうまく働くためには、COUNT関数の引数となるカラムにインデックスが張られている必要があります。例えば、

SELECT COUNT(*)
FROM Sales;

よりは、次のSQLの方が速いかもしれない。

SELECT COUNT(sale_id)
FROM Sales;

ここで、sale_idがSalesテーブルの主キーだとすれば、当然、sale_idにはユニークなインデックスが存在します。それを利用するのが、このトリックです。

・WHERE句の抽出条件は、最も制限の強いものから並べろ

抽出条件がANDでつながっている場合は、制限の強いもの、つまりそれによって選択される行数が少ないものから順に並べる方が、効率がよいです。例えば、以下の通り。

１．遅い

SELECT *
FROM Student
WHERE sex = 'male'
AND grade = 'A';

２．速い

SELECT *
FROM Student
WHERE grade = 'A'
AND sex = 'male';

なぜなら、評価がAの生徒の方が、男子学生よりも少ないから。この学校がハーレムのごとき場所なら話は別ですが。

・UNIONは使うな。UNION ALLを使え。

UNIONは、二つの結果集合をマージします。しかし、重複行を排除するためのソート処理にコストがかかります。もし重複を気にしなくてよい場合なら、UNIONの代わりにUNION ALLを使ってください。そうすればソートは発生しません。

・実はインデックスが使用されていないという罠。

皆さんが検索するテーブルには、きっとインデックスが張られているでしょう。インデックスは、行数や列数の多い表を短時間で検索するためのテクニックとしては非常にポピュラーです。その原理は、C言語のポインタ配列と同じです。サイズの大きなオブジェクト配列を検索するよりも、サイズの小さなポインタ配列を検索した方が効率がいい、というわけです。しかも、2分探索による高速検索が可能なよう工夫されています。
さて、Aという列にインデックスが張られているとします。以下のSQLはそのインデックスを使うつもりで、実のところテーブルXXを全件検索してしまっています。

①検索条件の左側で式を用いている

SELECT A
FROM XX
WHERE A * 1.1 > 100

検索条件の右側で式を用いれば、インデックスが使用されます。従って、代わりに

WHERE A > 100/1.1

という条件を使えばいいわけです。あるいは、関数索引を使うという方法もありますが、トリッキーなので推奨しません。

②NULLを指定している

SELECT A
FROM XX
WHERE A IS NULL

なぜなら、インデックスの中にはNULLは存在しないから。NULLは値ではないのです。

③否定形を用いている

SELECT A
FROM XX
WHERE A <> 100

否定形(NOT EQUAL, NOT IN)はインデックスを使用できません。

④ORを用いている

SELECT A
FROM XX
WHERE A > 100 OR B = 'abc'

理由は知らない。とにかくORを使うな。どうしても使いたいならビットマップ索引を張りましょう。

⑤LIKE述語を用いている

SELECT A
FROM XX
WHERE A LIKE '%a'

理由は知らない。とにかくLIKEを使うな。

・結局のところROWIDによるアクセスが最速

もしあなたがROWIDの存在を知らなければ、今すぐに

SELECT rowid
FROM 適当なテーブル名;

というSQLを実行してください。

ROWID
——————
AAAF+OAAIAAABmMABI
AAAF+OAAIAAABmMABK
AAAF+OAAIAAABmMABL

などといった列が選択されるはずです。　ROWIDはどのテーブルでも必ず持っている擬似列であり、そこに格納されている値はレコードの物理アドレスです。つまりROWIDはポインタの役割を果たす。インデックスもROWIDを使用しています。 ROWIDは、セッションが終了すると変化するかもしれませんが、ユーザセッション中は不変であり、Oracleでは常に最速のアクセスが保証されます。

http://mickindex.sakura.ne.jp/database/db_optimize.html

http://www.ne.jp/asahi/hishidama/home/tech/java/uptodate.html