[Oracle][SQL]テンポラリテーブルについて

リカバリ不要なワークテーブルとかに使う。

＜特徴＞
・REDOログを生成しないので高速にDML処理できる。
・生成したレコードは、生成したセッションからのみ参照できる。
・コミット後もしくはセッション終了後、データは削除（TRUNCATE）される。
・一時表領域を使用する。


＜制限事項＞
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

＜定義登録方法＞
▼SQL文
CREATE GLOBAL TEMPORARY TABLE 表１
(
　列名　データ型...
)
ON COMMIT[DELETE ROWS | PRESERVE ROWS]
;

▼テンポラリテーブルのレコードをクリアするタイミングを設定する。
・DELETE ROWS
トランザクション終了時にデータをクリアする。

・PRESERVE ROWS
セッション終了時にデータをクリアする。
※外部アプリがDB接続をコネクションプーリングしている場合、要注意かも。


[Appendix]
・統計情報はどうなる？

[Oracle][SQL]外部結合に関する制約

外部結合演算子(+)を使用する際には、色々結合条件に制約がある。
今日は、たまたまぶちあたった問題についてのメモ。
結合条件に複数項目を指定してる場合、全て外部結合にしなくてはならない。


▼表定義
例えば、こんな表があったとする。

[表１]
CREATE TABLE EMP(
EMP_CD CHAR(8),
EMP_NAME VARCHAR2(100),
DEPT_CD CHAR(8),
DELETE_FLG CHAR(1)
);

[表２]
CREATE TABLE DEPT(
DEPT_CD CHAR(8),
DEPT_NAME VARCHAR2(100),
DELETE_FLG CHAR(1)
);


▼問題点
で、こんなSQLを実行したとする。

[問題のSQL]
1 SELECT
2 E.EMP_CD,
3 D.DEPT_NAME
4 FROM
5 EMP E,
6 DEPT D
7 WHERE E.DEPT_CD = D.DEPT_CD(+)
8 AND D.DELETE_FLG = '0'
9 ;

この場合、外部結合にしたくても、内部結合となってしまう。
EMP表のDEPT_CDが、DEPT表に存在しない場合でもEMP表のレコードは表示したい、
という意図で、7行目に「e.DEPT_CD = d.DEPT_CD(+)」外部結合として記述したが、
8行目の「d.DELETE_FLG = '0'」により外部結合の要件を満たさなくなる。


▼解決方法
で、問題のSQLをうまいこと外部結合にしたい場合はこう書く必要がある。

[解決のSQL]
1 SELECT
2 e.EMP_CD,
3 d.DEPT_NAME
4 FROM
5 EMP e,
6 (SELECT * FROM DEPT WHERE DELETE_FLG ='0') d
7 WHERE e.DEPT_CD = d.DEPT_CD(+)
8 ;

ということで、今回の様に、予め特定条件でフィルタできる場合は
FROM句にてインラインビューとして取得するのが吉。


▼APPENDIX
その他の制限については今後加筆予定。

[Oracle]Materialized View を高速に完全リフレッシュ

以下のコマンドにより、非常に高速に完全リフレッシュを行うことができる。

[コマンド]
DBMS_MVIEW.REFRESH('MVEW_NAME','c',NULL,TRUE,FALSE,1,0,0,FALSE,FALSE);

[注意]
リフレッシュ処理の間、他トランザクションから当該Materialized Viewを参照した場合、0件として表示されるため、読み取り一貫性を保障する必要がある場合注意が必要である。

[方式]
通常の完全リフレッシュでは、Materialized Viewは実体を持つ（結局はテーブルなのだけれど）ため、実体に対してDELETE→INSERT処理となる処理を、TRUNCATE→INSERT処理に変更してリフレッシュする。

なぜ、上記コマンドでDELETEからTRUNCATE処理に変更されるかについては、今後追記する。オンライン開局中よりもオフライン処理中に使うことが多いかもしれない。

[Oracle]PL/SQLにおける自律型トランザクション宣言

プロシージャ中に以下の宣言を追加することで、プロシージャを自律型トランザクションを定義することができる。プロシージャのみ独立してトランザクションを完結したい場合に使用する。逆に、自律型トランザクションでないプロシージャ内で、トランザクションを確定した場合、呼び出し元のトランザクションも確定するので注意。

[実装方法]
・自律型トランザクションプラグマを宣言する。
→PRAGMA AUTONOMOUS_TRANSACTION;

[メリット]
・呼び出し元のトランザクションに依存せずに処理を進めることができる。
→例）ログテーブルにエラーメッセージを出力する。
・ロック時間の短縮が期待できる
→プロシージャ中のDMLが対象とするオブジェクトのロック時間が短くなる。
・SELECT文中でも使用できる
→通常、SELECT文中で呼び出す関数にDMLが含まれるとエラーとなる。

[制限]
・プロシージャ中でトランザクションを確定する必要がある。
→トランザクションが確定しない場合、処理はロールバックし、呼び出し元に例外が通知される。

[サンプル]
CREATE OR REPLACE PROCEDURE PRC_LOGOUTPUT(
Pi_vMSG IN VARCHAR2,
Pi_vPG_ID IN VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION; --プラグマ宣言
BEGIN
INSERT INTO LOGTABLES(MSG, PG_ID, CREATED_ON)
VALUES (Pi_vMSG, Pi_vPG_ID, SYSDATE);
COMMIT; --トランザクションの確定
END;
/