viewを対象としないSELECT FOR UPDATEでORA-02014が発生する

春まだ浅いある日、SQL発行でエラーが発生するとの調査依頼がありました。

本番リリース準備中の環境で、構成は以下の通りです。

Oracle RAC（2ノード） EE 12.1.0.2
RHEL Version 7.3

現行環境(11.2.0.4)と同じテーブル、同じインデックスで全く同じSQLを発行しているのに、以前はエラーにならなかったのに、新環境ではエラーになる、という事です。

テーブルとインデックスの定義が連携されて来たので、検証環境でテストしてみました。

SQL> select A.CP_ID, A.H_ID, A.CT_ID, A.O_ID, A.A_NO, A.O_DATE, A.B_DATE, C.RET, C.ERR_CODE, C.ERR_MESSAGE
2 from TBL_A A inner join TBL_B B on A.CP_ID = B.CP_ID
3 left join TBL_C C on A.CP_ID = C.CP_ID and A.H_ID = C.H_ID and A.CT_ID = C.CT_ID and A.O_ID = C.O_ID and A.A_NO = C.A_NO and A.O_DATE = C.O_DATE
4 where B.CAT_CD = '1' for update nowait order by A.CP_ID, A.A_NO ;
select A.CP_ID, A.H_ID, A.CT_ID, A.O_ID, A.A_NO, A.O_DATE, A.B_DATE, C.RET, C.ERR_CODE, C.ERR_MESSAGE
*
行1でエラーが発生しました。:
ORA-02014: DISTINCT、GROUP BYなどを含むビューに対してFOR UPDATE句を使用できません

メッセージの通り、DISTINCT、GROUP BYなどを含むビューに対してSELECT FOR UPDATEを発行した時に出るエラーですが、select対象にビューは含まれていませんし、DISTINCTやGROUP BYも含まれていません。

プロとしてのOracle運用管理入門Oracle現場主義【電子書籍】[ 株式会社コーソル ]

 

検証環境はテーブルを作っただけでデータは入っていないので、統計情報などは関係なさそうです。

MOS(MyOracleSupport)で検索しても、それらしいドキュメントは見当たりませんでした。

検証環境の11gで実行すると正常終了するので、現行と新環境の初期化パラメータの違いが原因ではないかと考え、怪しそうなパラメータを幾つか外してテストしてみました。

結論から言うと、_complex_view_merging=falseに設定してあると、エラーが発生します。セッション単位でtrueに設定する事で、エラーを回避できました。

SQL> alter session set "_complex_view_merging"=true;

セッションが変更されました。

SQL> sho parameter complex

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
_complex_view_merging boolean TRUE
SQL> select A.CP_ID, A.H_ID, A.CT_ID, A.O_ID, A.A_NO, A.O_DATE, A.B_DATE, C.RET, C.ERR_CODE, C.ERR_MESSAGE
2 from TBL_A A inner join TBL_B B on A.CP_ID = B.CP_ID
3 left join TBL_C C on A.CP_ID = C.CP_ID and A.H_ID = C.H_ID and A.CT_ID = C.CT_ID and A.O_ID = C.O_ID and A.A_NO = C.A_NO and A.O_DATE = C.O_DATE
4 where B.CAT_CD = '1' for update nowait order by A.CP_ID, A.A_NO ;

レコードが選択されませんでした。

_complex_view_mergingは複合viewのマージを可能とするかどうかのパラメータで、12cではデータ破壊などの深刻な不具合がある為、falseに設定する事をOracle社コンサルタントから推奨された為に、falseに設定していたのです。（デフォルトはtrue）

とは言え、エラーが発生してしまったらSQLの実行そのものができなくなってしまうので、影響を最低限に抑える為、初期化パラメータは変更せず、このSQLにヒント句を埋め込む事で対処する事になりました。

SQL> sho parameter complex

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
_complex_view_merging boolean FALSE
SQL> select /*+ OPT_PARAM('_complex_view_merging', 'true') */ A.CP_ID, A.H_ID, A.CT_ID, A.O_ID, A.A_NO, A.O_DATE, A.B_DATE, C.RET, C.ERR_CODE, C.ERR_MESSAGE
2 from TBL_A A inner join TBL_B B on A.CP_ID = B.CP_ID
3 left join TBL_C C on A.CP_ID = C.CP_ID and A.H_ID = C.H_ID and A.CT_ID = C.CT_ID and A.O_ID = C.O_ID and A.A_NO = C.A_NO and A.O_DATE = C.O_DATE
4 where B.CAT_CD = '1' for update nowait order by A.CP_ID, A.A_NO ;

レコードが選択されませんでした。

 これでこの件は解決していたのですが、後になって英文のMOSを検索したところ、下記の文書を見つけました。

Select FOR UPDATE With ANSI Join Query Get ORA-02014 After Setting _COMPLEX_VIEW_MERGING=FALSE (ドキュメントID 1102853.1)

ANSI結合構文で結合した場合、そのSQLは内部的にOracle結合構文に変換される必要があり、外部結合に対処する為にビューが使用されるが、_COMPLEX_VIEW_MERGING=FALSE

を設定してあるとビューがマージされずに残ってしまい、outer joinは「DISTINCT、GROUP BYなど」の「など」に該当するので、ORA-02014が発生するという内容です。

ここで複合ビューのマージとは何か、再確認です。

やや古いですが「Oracle8iパフォーマンスのための設計およびチューニング」から引用します。

ビューのマージ

ビューにアクセスするSQL文について、オプティマイザは、文内の問合せをビュー内の問合せとマージして、その結果を最適化します。

複合ビューのマージ

ビューの問合せが GROUP BY 句または DISTINCT 演算子を選択リストに持っている場合は、複合ビューのマージが使用可能になっているときのみ、オプティマイザがビューの問合せをアクセス文にマージできます。また、複合マージは、副問合せに相関関係がない場合に、アクセス文に IN 副問合せをマージするのにも使用できます。

例 1: GROUP BY 句を使用するビュー

ビュー avg_salary_view には、各部門の給与の平均が表示されます。
CREATE VIEW avg_salary_view AS
SELECT deptno, AVG(sal) AS avg_sal_dept,
FROM emp
GROUP BY deptno;
複合ビューのマージを使用できる場合は、London に存在する部門の平均給与を検索する次の問合せをオプティマイザが変換します。
SELECT dept.loc, avg_sal_dept
FROM dept, avg_salary_view
WHERE dept.deptno = avg_salary_view.deptno
AND dept.loc = 'London';
その結果は次の問合せになります。
SELECT dept.loc, AVG(sal)
FROM dept, emp
WHERE dept.deptno = emp.deptno
AND dept.loc = 'London'
GROUP BY dept.rowid, dept.loc;
変換された問合せは、ビューのベース表にアクセスして、London で働いている従業員の行のみを選択し、選択した行を部門別にグループ化します。 

つまり2つ目のSQLのSELECT avg_sal_dept FROM avg_salary_viewの部分が
ビュー定義のSELECT AVG(sal) FROM emp GROUP BY deptnoに該当するので、マージされて3つ目のSQLに変換されるのがビューのマージで、このSQLにGROUP BYや DISTINCTなどが含まれるのが複合ビューのマージになります。

長くなるので例 2は割愛しますが、相関副問合せ以外を使用する IN 句についても複合マージが実行され、ビューへの問い合わせがベース表への問い合わせに変換されます。

この説明だけでは分かりにくいので、具体的に見ていきます。

まず、元のSQLがクエリ変換(query transformation)*1によってどう書き換わるか確認します。

手順は以下の通りです。

SQL> alter session set max_dump_file_size = UNLIMITED;
SQL> alter session set events '10053 trace name context forever, level 1';
<調査対象の SQL 文>
SQL> alter session set events '10053 trace name context off';

10053 は、Cost Base Optimizer(CBO)の動作をトレースするイベントで、CBO に関連する動作、及び、パフォーマンス障害に関する調査の際に役立ちます。

トレースファイルには、CBOが実行計画を立てる途中経過が出力され、クエリの変換後の姿と実行計画が確認できます。

※10053トレースを取得するには、ハードパースが実行される必要があります。

今回のSQLでは、_complex_view_merging=trueだと、以下のようにANSI構文の結合式が、Oracle構文に変換されています。

※見やすいように改行を入れています。

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "A"."CP_ID" "CP_ID"
,"A"."H_ID" "H_ID"
,"A"."CT_ID" "CT_ID"
,"A"."O_ID" "O_ID"
,"A"."A_NO" "A_NO"
,"A"."O_DATE" "O_DATE"
,"A"."B_DATE" "B_DATE",
"C"."RET" "RET","C"."ERR_CODE" "ERR_CODE","C"."ERR_MESSAGE" "ERR_MESSAGE"
FROM "TEST"."TBL_A" "A","TEST"."TBL_B" "B","TEST"."TBL_C" "C"
WHERE "B"."CAT_CD"=1
AND "A"."CP_ID"="C"."CP_ID"(+)
AND "A"."H_ID"="C"."H_ID"(+)
AND "A"."CT_ID"="C"."CT_ID"(+)
AND "A"."O_ID"="C"."O_ID"(+)
AND "A"."A_NO"="C"."A_NO"(+)
AND "A"."O_DATE"="C"."O_DATE"(+)
AND "A"."CP_ID"="B"."CP_ID"
ORDER BY "A"."CP_ID","A"."A_NO"

一方、_complex_view_merging=falseだと最終形はこうなります。

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_003"."QCSJ_C000000000300000_0" "CP_ID"
,"from$_subquery$_003"."H_ID_1" "H_ID"
,"from$_subquery$_003"."CT_ID_2" "CT_ID"
,"from$_subquery$_003"."O_ID_3" "O_ID"
,"from$_subquery$_003"."A_NO_4" "A_NO"
,"from$_subquery$_003"."O_DATE_5" "O_DATE"
,"from$_subquery$_003"."B_DATE_6" "B_DATE",
"C"."RET" "RET","C"."ERR_CODE" "ERR_CODE","C"."ERR_MESSAGE" "ERR_MESSAGE"
FROM
(SELECT "A"."CP_ID" "QCSJ_C000000000300000_0"
,"A"."H_ID" "H_ID_1"
,"A"."CT_ID" "CT_ID_2"
,"A"."O_ID" "O_ID_3"
,"A"."A_NO" "A_NO_4"
,"A"."O_DATE" "O_DATE_5"
,"A"."B_DATE" "B_DATE_6"
,"B"."CAT_CD" "CAT_CD_7"
FROM "TEST"."TBL_A" "A","TEST"."TBL_B" "B" WHERE "A"."CP_ID"="B"."CP_ID" AND "B"."CAT_CD"=1) "from$_subquery$_003"
,"TEST"."TBL_C" "C"
WHERE "from$_subquery$_003"."QCSJ_C000000000300000_0"="C"."CP_ID"(+)
AND "from$_subquery$_003"."H_ID_1"="C"."H_ID"(+)
AND "from$_subquery$_003"."CT_ID_2"="C"."CT_ID"(+)
AND "from$_subquery$_003"."O_ID_3"="C"."O_ID"(+)
AND "from$_subquery$_003"."A_NO_4"="C"."A_NO"(+)
AND "from$_subquery$_003"."O_DATE_5"="C"."O_DATE"(+)
ORDER BY "from$_subquery$_003"."QCSJ_C000000000300000_0","from$_subquery$_003"."A_NO_4"

AとBの内部結合部分がインライン・ビューに変換され（赤字部分。"from$_subquery$_003"というエイリアスが付けられている）、それに対してCと外部結合されていますが、そのビューがマージされなかったのでビューのまま残り、DISTINCT、GROUP BYなどを含むビューに対するFOR UPDATE文となり、エラーが発生した次第です。

ドキュメントID 1102853.1に記載された対処策は_complex_view_merging=trueを指定する、またはOracle構文で結合させる、でしたが、Oracleの外部結合構文はOracleでしか使えないので、パッケージなどでは採用されないかも知れません。

MOSを検索する時は、英語の資料も調べるべきだと改めて思ったのでした。 

*1:より効率的な実行計画の為、論理的に同内容のSQLに書き換える内部動作