Oracleで特定テーブルの参照整合性制約を確認するSQL

今、Symfony2を使用した開発をしている。DBはOracle。

Symfony2のconsoleでORMの設定からSQLを作成することができる。利用しているDBの状況から最新にするために必要なSQLを作成できるので便利。しかし、その作成に時間がかなりかかるため、その調査をした。

実行ログをみると参照整合性制約を抽出するSQLに時間がかかっていたため、SQLを書き換えて改善されるか試してみた。そのとき作成したSQLが以下。

SELECT
  uc.constraint_name,
  uc.delete_rule,
  uc.search_condition,
  ucc.column_name,
  ucc.position,
  r_ucc.table_name  r_table_name,
  r_ucc.column_name r_column_name
FROM user_constraints uc
JOIN user_cons_columns ucc
  ON ucc.owner             = uc.owner
 AND ucc.constraint_name   = uc.constraint_name
JOIN user_cons_columns r_ucc
  ON r_ucc.owner           = uc.r_owner
 AND r_ucc.constraint_name = uc.r_constraint_name
 AND r_ucc.position        = ucc.position
WHERE
    uc.constraint_type = 'R'
AND uc.table_name = 'FOO'
ORDER BY uc.constraint_name ASC,
         ucc.position ASC
;

確認するために2つのビューを使用する。user_constraintsは、テーブルに定義された制約を示すビューでuser_cos_columnsはその制約で使用しているカラムを示すビュー。

user_constraints.constraint_typeは当該制約の種別でRは参照整合性を意味する。参照整合性制約の場合、user_constraints.ownerとuser_constraints.constraint_nameが参照元の制約であり、参照先の制約はuser_constraints.r_ownerとuser_constraints.r_constraint_name。

それぞれのカラムをuser_cons_columnsの該当カラムと内部結合して、それぞれのテーブル名とカラム名を取得した。

ちなみに上記のSQLで試したところ元のSQLよりは改善されたが、それでも実行には数秒かかった。どうやらuser_constraintsをselectするだけでも時間がかかっているようだった。

参考

• USER_CONSTRAINTS
• USER_CONS_COLUMNS