Shoeisha Technology Media

EnterpriseZine(エンタープライズジン)

EnterpriseZine(エンタープライズジン)

テーマ別に探す

第4回 論理設計はアプリケーション開発者のキホン ―10月の人気記事

edited by DB Online   2011/12/01 00:00

スキーマデザインの鉄則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);

 


著者プロフィール

  • 矢木覚(ヤギサトル)

      Oracle ACE SIerにおいて、Oracle Databaseの最新技術を用いた、企業システムの基幹システム設計/構築に携わる。大規模RACやOracle Exadataによるシステム設計・データベース統合等を行ってきた。その経験を基に、現在ではオラクル...

バックナンバー

連載:オラクル・キュレーション by Oracle ACE

もっと読む

All contents copyright © 2007-2019 Shoeisha Co., Ltd. All rights reserved. ver.1.5