スキーマデザインの鉄則1：Integrity（完全性）はデータベースで実装すること
前回の記事では、データベース技術者（インフラ管理）のタスクである物理設計について紹介させていただきましたが、今回はアプリケーション開発者のタスクである論理設計（スキーマ設計）のポイントについて、オラクル・コーポレーションのアーキテクトであるトム・カイトが、先日実施したセミナーを元に紹介させていただこうと思います。
なお、トム・カイトはセキュリティについても、とても興味深いセッションを行っているので、そちらのレポートもあわせてご参照ください。
データの整合性（例：一意性、外部参照性）は、様々なレイヤーで保持することができます。アプリケーションで実装することもできますし、データベースで実装することもできます。しかし、整合性を保持するには、原則としてデータベースで実装します。
まず第一の理由として、アプリケーションは今後変更される可能性があります。アプリケーションの刷新によって、言語やフレームワークが変わることもあるでしょう。しかし、データベースのデータは、この変わっていくアプリケーションに対して「使い続けられる」のです。したがって、データベースでデータの整合性を保持しておくことが長期的なデータのライフサイクルにおいて合理的です。
次に、パフォーマンス面です。データベースの持つ参照整合制約や、同時実行の機構を利用することで、アプリケーションでそれらを実装するよりも優れたパフォーマンスを得ることができます。
また、データベースのオプティマイザは、データベースの制約も考慮して、最適な実行計画を立てます（例えばマテリアライズド・ビューのクエリーリライト）。データベースの実行計画の観点でも、データの整合性はデータベースで保持するべきでしょう。
もし、クライアント層やアプリケーション層でデータの整合性を保障する必要がある場合においても、必ずデータベースでも同じ整合性の保障を行っておくべきです。なぜなら、そのデータは最初に述べたようにアプリケーションが更改された場合においても使い続けられるからです。
次のSQLで具体例を見てみましょう。
SQL> SELECT COUNT(*) 2 FROM T1, T2, T3 3 WHERE T2.order_id = T1.order_id 4 AND T2.service_order_id = T3.service_order_id (+) 5 AND T3.related_service_order_id = TO_NUMBER(:v0);
このSQLでパフォーマンスが出なかった場合にどのようにチューニングをするでしょうか？
もし、データの整合性がアプリケーションで担保されている場合は、アプリケーションのロジックを見直し、各テーブルの関係や結合の最適化をアプリケーションで実装する必要があり、大変手間がかかります。
しかし、以下のようにデータベースの表を作成する際にデータの整合性をデータベースで実装しているとどうなるでしょうか？
SQL> CREATE TABLE T1 2 ( 3 ORDER_ID NUMBER(18) NOT NULL, 4 ACCOUNT_NO NUMBER(10) NOT NULL, 5 ORDER_NUMBER VARCHAR2(20) NOT NULL, 6 data varchar2(1000) 7 ); Table created. SQL> ALTER TABLE T1 ADD CONSTRAINT T1_PK1 PRIMARY KEY (ORDER_ID); Table altered. SQL> CREATE TABLE T2 2 ( 3 SERVICE_ORDER_ID NUMBER(18) NOT NULL, 4 ORDER_ID NUMBER(18) NOT NULL, 5 ORDER_STATUS_ID NUMBER(6) NOT NULL, 6 data varchar2(1000) 7 ); Table created. SQL> ALTER TABLE T2 ADD CONSTRAINT T2_PK1 2 PRIMARY KEY (SERVICE_ORDER_ID); Table altered. SQL> ALTER TABLE T2 ADD CONSTRAINT T2_OSO_FK1 2 FOREIGN KEY (ORDER_ID) REFERENCES T1 (ORDER_ID); Table altered. SQL> CREATE TABLE T3 2 ( 3 SERVICE_ORDER_ID NUMBER(18) NOT NULL, 4 RELATED_SERVICE_ORDER_ID NUMBER(18), 5 data varchar2(1000) 6 ); Table created. SQL> ALTER TABLE T3 ADD CONSTRAINT T3_ORDER_PK1 2 PRIMARY KEY (SERVICE_ORDER_ID); Table altered. SQL> ALTER TABLE T3 ADD CONSTRAINT T3_OLS_S_FK1 2 FOREIGN KEY (SERVICE_ORDER_ID) REFERENCES T2 (SERVICE_ORDER_ID); Table altered. SQL> CREATE INDEX T3_OLS_RS_1 2 ON T3 (RELATED_SERVICE_ORDER_ID); Index created.
このように、データベース側でデータの整合性を実装することにより、オプティマイザは以下のような実行計画を立てます。
SQL> SELECT COUNT(*) 2 FROM T1, T2, T3 3 WHERE T2.order_id = T1.order_id 4 AND T2.service_order_id = T3.service_order_id (+) 5 AND T3.related_service_order_id = TO_NUMBER(:v0); --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 26 | | | |* 2 | INDEX RANGE SCAN| T3_OLS_RS_1 | 1 | 26 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------
つまり、オプティマイザはデータベースで実装された整合性から、以下のことを判断するのです。
・Where t2.col = t3.col(+) の(+)は必要ない
→OUTER JOINが発生する場合には、T3.related_service_order_idがNULLになってしまい、「T3.related_service_order_id = TO_NUMBER(:v0)」が成立しない
・テーブルT1はクエリーに関係がない
→T1表から結果セットが返されるわけではない
→T1(order_id)が主キーでT2(order_id)と結合されている。すなわち、T2はキー保護表である →T2(order_id)にNOT NULL制約が付与されており、T1表に外部制約が張られている
→したがって、T1とT2を結合する際に、T2の行数とT1・T2との結合表の行数は一致する
・テーブルT2もクエリーに関係がない
→T2表から結果セットが返されるわけではない
→T2(service_order_id)が主キーでT3(service_order_id)と結合されている。すなわち、T3はキー保護表である
→T3(service_order_id)にNOT NULL制約が付与されており、T2表に外部制約が張られている
→したがって、T2とT3を結合する際に、T3の行数とT2・T3の結合表の行数は一致する
このように、データベースで整合性を保持することによって最初の複雑なクエリーは以下のようになり、パフォーマンスも高速化されるのです。
SQL> SELECT COUNT(*) 2 FROM T3 3 WHERE T3.related_service_order_id = TO_NUMBER(:v0);
