「設計」タグアーカイブ

NULLを排除した設計②

先日投稿した「NULLを排除した設計①」の続きです。

若干変更

前回の投稿から若干変更です。せっかくCLOBカラムを定義したので、I/O負荷を高くするため値を設定する際に4000バイト長のデータにして格納することにしました。（LOBカラムへの正規の値格納方法ではないのですが、簡単に4000バイトのデータを設定しました。）

SQL> update CUSTOMERS_1 set REMARKS = rpad('0',4000,'0') where mod(CUST_ID,5 ) = 0;

10000行が更新されました。

SQL> update CUSTOMERS_1 set REMARKS = rpad('1',4000,'1') where mod(CUST_ID,10) = 1;

5000行が更新されました。

SQL> update CUSTOMERS_1 set REMARKS = rpad('2',4000,'2') where mod(CUST_ID,20) = 2;

2500行が更新されました。

SQL> update CUSTOMERS_1 set REMARKS = rpad('3',4000,'3') where mod(CUST_ID,50) = 3;

1000行が更新されました。

SQL> commit;

コミットが完了しました。

SQL> select
  2   CUST_ID
  3  ,nvl(DBMS_LOB.SUBSTR(REMARKS, 10, 1),'Null') REMARKS
  4  ,DBMS_LOB.GETLENGTH(REMARKS) LENGTH
  5  from CUSTOMERS_1 where CUST_ID < 21
  6  order by CUST_ID;

   CUST_ID REMARKS             LENGTH
---------- --------------- ----------
         1 1111111111            4000
         2 2222222222            4000
         3 3333333333            4000
         4 Null
         5 0000000000            4000
         6 Null
         7 Null
         8 Null
         9 Null
        10 0000000000            4000
        11 1111111111            4000

   CUST_ID REMARKS             LENGTH
---------- --------------- ----------
        12 Null
        13 Null
        14 Null
        15 0000000000            4000
        16 Null
        17 Null
        18 Null
        19 Null
        20 0000000000            4000

20行が選択されました。

SQL> select
  2   nvl(DBMS_LOB.SUBSTR(REMARKS, 10, 1),'Null') REMARKS
  3  ,count(*)
  4  from
  5   CUSTOMERS_1
  6  group by
  7   DBMS_LOB.SUBSTR(REMARKS, 10, 1)
  8  order by
  9   1;

REMARKS           COUNT(*)
--------------- ----------
0000000000           10000
1111111111            5000
2222222222            2500
3333333333            1000
Null                 31500

パフォーマンス比較

テストは同じSQL文をそれぞれ３回連続で実行し、一番経過時間が短かった結果を採用しました。

まずは全件検索（結果：50,000件）

①-1 単純カラム追加（NULLを排除しない設計）

SQL> select
  2   CUST_ID
  3  ,CUST_FNAME
  4  ,CUST_LNAME
  5  ,SEX
  6  ,REMARKS
  7  from
  8   CUSTOMERS_1;

50000行が選択されました。

 経過: 00:00:30.41

実行計画
----------------------------------------------------------
Plan hash value: 2813274236

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 50000 |  3857K|   140   (0)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS_1 | 50000 |  3857K|   140   (0)| 00:00:02 |
---------------------------------------------------------------------------------

統計
----------------------------------------------------------
       0  recursive calls
       0  db block gets
  198074  consistent gets
  148000  physical reads
       0  redo size
24824948  bytes sent via SQL*Net to client
17644472  bytes received via SQL*Net from client
  124002  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
   50000  rows processed

②-1 テーブル追加（Nullを排除した設計）

SQL> select
  2   C.CUST_ID
  3  ,C.CUST_FNAME
  4  ,C.CUST_LNAME
  5  ,C.SEX
  6  ,R.REMARKS
  7  from
  8   CUSTOMERS_2 C left outer join CUST_2_REMARKS R
  9  on C.CUST_ID = R.CUST_ID;

50000行が選択されました。

 経過: 00:00:30.31

実行計画
----------------------------------------------------------
Plan hash value: 2466772454

------------------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                | 50000 |  7373K|       |   361   (1)| 00:00:05 |
|*  1 |  HASH JOIN RIGHT OUTER|                | 50000 |  7373K|  2248K|   361   (1)| 00:00:05 |
|   2 |   TABLE ACCESS FULL   | CUST_2_REMARKS | 18500 |  2023K|       |    40   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | CUSTOMERS_2    | 50000 |  1904K|       |    90   (0)| 00:00:02 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C"."CUST_ID"="R"."CUST_ID"(+))

統計
----------------------------------------------------------
       0  recursive calls
       0  db block gets
  198137  consistent gets
  148000  physical reads
       0  redo size
24824948  bytes sent via SQL*Net to client
17644472  bytes received via SQL*Net from client
  124002  SQL*Net roundtrips to/from client
       1  sorts (memory)
       0  sorts (disk)
   50000  rows processed

このクエリーはOUTER JOINで書くのがポイントです。

コスト値は単純カラム追加の方が半分以下なのですが、physical readsは両者全く同じで、経過時間もほとんど同じです。

②のコストが大きいのはハッシュ・ジョインのためですが、パフォーマンス的には遜色のない結果となっています。

IS NOT NULL検索はどうか？（結果：18,500件）

①-2 単純カラム追加（NULLを排除しない設計）


SQL> select
  2   CUST_ID
  3  ,CUST_FNAME
  4  ,CUST_LNAME
  5  ,SEX
  6  ,REMARKS
  7  from
  8   CUSTOMERS_1
  9  where REMARKS is not null;

18500行が選択されました。

経過: 00:00:24.06

実行計画
----------------------------------------------------------
Plan hash value: 2813274236

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 18500 |  1427K|   143   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS_1 | 18500 |  1427K|   143   (2)| 00:00:02 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("REMARKS" IS NOT NULL)

統計
----------------------------------------------------------
       0  recursive calls
       0  db block gets
  166771  consistent gets
  148000  physical reads
       0  redo size
20641347  bytes sent via SQL*Net to client
17297972  bytes received via SQL*Net from client
   92502  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
   18500  rows processed

②-2 テーブル追加（Nullを排除した設計）

SQL> select
  2   C.CUST_ID
  3  ,C.CUST_FNAME
  4  ,C.CUST_LNAME
  5  ,C.SEX
  6  ,R.REMARKS
  7  from
  8   CUSTOMERS_2 C inner join CUST_2_REMARKS R
  9  on C.CUST_ID = R.CUST_ID;

18500行が選択されました。

経過: 00:00:23.60

実行計画
----------------------------------------------------------
Plan hash value: 2209842270

---------------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                | 18500 |  2728K|       |   361   (1)| 00:00:05 |
|*  1 |  HASH JOIN         |                | 18500 |  2728K|  2248K|   361   (1)| 00:00:05 |
|   2 |   TABLE ACCESS FULL| CUST_2_REMARKS | 18500 |  2023K|       |    40   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| CUSTOMERS_2    | 50000 |  1904K|       |    90   (0)| 00:00:02 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C"."CUST_ID"="R"."CUST_ID")

統計
----------------------------------------------------------
       0  recursive calls
       0  db block gets
  166835  consistent gets
  148000  physical reads
       0  redo size
20641347  bytes sent via SQL*Net to client
17297972  bytes received via SQL*Net from client
   92502  SQL*Net roundtrips to/from client
       1  sorts (memory)
       0  sorts (disk)
   18500  rows processed

このクエリーはINNER JOINで書くのがポイントですが、全件検索と同様に②-1と較べて遜色ないというか、むしろ若干よい結果が出ました。（これは偶然だと思いますが。）

IS NULL検索はどうか？（結果：31,500件）

③-1 単純カラム追加（NULLを排除しない設計）

SQL> select
  2   CUST_ID
  3  ,CUST_FNAME
  4  ,CUST_LNAME
  5  ,SEX
  6  ,REMARKS
  7  from
  8   CUSTOMERS_1
  9  where REMARKS is null;

31500行が選択されました。

経過: 00:00:06.56

実行計画
----------------------------------------------------------
Plan hash value: 2813274236

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 31500 |  2430K|   143   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS_1 | 31500 |  2430K|   143   (2)| 00:00:02 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("REMARKS" IS NULL)

統計
----------------------------------------------------------
      0  recursive calls
      0  db block gets
  31745  consistent gets
      0  physical reads
      0  redo size
4184175  bytes sent via SQL*Net to client
 346972  bytes received via SQL*Net from client
  31502  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
  31500  rows processed

③-2 テーブル追加（Nullを排除した設計）


SQL> select
  2   CUST_ID
  3  ,CUST_FNAME
  4  ,CUST_LNAME
  5  ,SEX
  6  ,null REMARKS
  7  from
  8   CUSTOMERS_2
  9  where not exists (
 10      select
 11       1
 12      from
 13       CUST_2_REMARKS
 14      where CUSTOMERS_2.CUST_ID = CUST_2_REMARKS.CUST_ID
 15                   );

31500行が選択されました。

経過: 00:00:00.75

実行計画
----------------------------------------------------------
Plan hash value: 2401287113

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   | 31500 |  1353K|    93   (4)| 00:00:02 |
|   1 |  NESTED LOOPS ANTI |                   | 31500 |  1353K|    93   (4)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS_2       | 50000 |  1904K|    90   (0)| 00:00:02 |
|*  3 |   INDEX UNIQUE SCAN| PK_CUST_2_REMARKS |  6845 | 34225 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CUSTOMERS_2"."CUST_ID"="CUST_2_REMARKS"."CUST_ID")

統計
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   6386  consistent gets
      0  physical reads
      0  redo size
1577675  bytes sent via SQL*Net to client
  23561  bytes received via SQL*Net from client
   2101  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
  31500  rows processed

このクエリーはNOT EXIST句を使って書くのがポイントですが、テーブル分割した方が圧倒的にパフォーマンスがよいです！

しかし、実はこれには落とし穴があります。IS NULL条件なので結果のREMARKSカラムは必ずNullになります。③-1は以下のように書き換えが可能です。

SQL> select
  2   CUST_ID
  3  ,CUST_FNAME
  4  ,CUST_LNAME
  5  ,SEX
  6  ,null REMARKS
  7  from
  8   CUSTOMERS_1
  9  where REMARKS is null;

31500行が選択されました。

経過: 00:00:00.71

実行計画
----------------------------------------------------------
Plan hash value: 2813274236

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 31500 |  2430K|   143   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS_1 | 31500 |  2430K|   143   (2)| 00:00:02 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("REMARKS" IS NULL)

統計
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   2531  consistent gets
      0  physical reads
      0  redo size
1577675  bytes sent via SQL*Net to client
  23561  bytes received via SQL*Net from client
   2101  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
  31500  rows processed

律儀にREMARKSカラムをクエリーに入れてしまうと、パフォーマンスが極端に悪くなります。

こうして見ると設計の違いでパフォーマンスに特筆すべき差はないので、「単純なカラム追加でよいではないか。」という意見もあるかと思いますが、Nullを排除した設計の利点を次回考察していきたいと思います。

（続く）

NULLを排除した設計①

なぜNullを排除しなければならないのか？

テーブルを設計する際、Nullについてはあまり考慮されていないように思います。主キーは当然としてもむやみにNot Null制約を定義することは、テストデータの作り難さなどの理由でむしろ避けられているかもしれません。

ただし、緻密なテーブル設計を目的とした場合、Not Null制約によってNullデータを排除することは非常に重要です。

Nullには２つの意味がある

例えば

update CUSTOMERS set REMARKS = null where CUST_ID = 25000;

というDMLであたかも「Null」という値を設定できているような印象を持ってしまいますが、それではなぜ

select * from CUSTOMERS where REMARKS = null;

ではなく、

select * from CUSTOMERS where REMARKS is null;

というSQL文にしなければならないのでしょうか？プログラマのためのSQL 第4版 ジョー・セルコ著 には次のような一節があります。

「2値論理は、ONとOFFの2つしか状態を持たないバイナリコンピュータや多くの数学分野と極めて相性がいい。しかしSQLは3値論理を採用している。すなわち、TRUE、FALSE、UNKNOWNである。UNKNOWN という値は、比較述語や他の述語の戻り値として生じるものだが、れっきとした真理値であり、データがないことの目印であるNULLとは異なる。これが、SQLにおいて（x=NULL）ではなく（x IS [NOT] NULL） と書かねばならない理由である。NULL は値でも変数でもないので、値や変数にしか適用できない「＝」という述語は適用できないのだ。」（P.266）

つまり、SQLでは「状態UNKNOWN」としてのNullと、「データがないことの目印」であるNullが混在しているということが誤解を生みやすくなっています。

Nullを許すことの問題点

以下のSQLを見てみましょう。

SQL> select 1/null from dual;

    1/NULL
----------
(Null)

変数の中にNullが紛れ込むと、どんなほとんどの場合（もちろん例外もありますが）関数の結果がNullになります。

その他、外部キーの参照先にNullデータがあると参照整合性制約の定義としては問題なくても値が取得できない問題が発生します。

プログラマのためのSQL 第4版 ジョー・セルコ著 には次のような記述もあります。

「優秀なSQLプログラマならば、NULLに対処するにあたり、まずはDDLにおいてこれを排除することに全力を傾けるだろう。すべてのテーブルのすべての列に原則としてNOT NULL 制約を付与し、正当な理由がある場合にだけNULLの使用を許可する。間違っても、こういう努力をしないまま、クエリで実装依存の変換関数に頼ればいいなどと考えないでほしい。 NULLは、SQL を知らない人々を混乱させるのだ。 加えて、NULLを使うのはコストの高い行為でもある。というのも、通常、NULLはそれが現れる列の行に余計なピットフラグを持つことで実装されている。これは前述のとおり、NULLが列自身の値ではないためだ。これによって、ストレージ要件や、インデックスおよび検索条件において本来なら必要なかったことを考慮する必要がある。」（P.272）

セルコ曰く「NULLを排除した設計」とはどういうものでしょうか？

この部分は重要だと思うので、簡単な例を挙げて説明します。

顧客テーブルを例にして

どこにでもあるような顧客テーブル（CUSTOMERS）を考えてみます。

テーブル構造は以下の様な簡単なもので、50,000件のテストデータが格納されています。

顧客テーブル

性別カラムには必ず「M」または「F」の値が格納されていてそれぞれ25,000件ずつになるようにデータを作成しています。

SQL> select SEX,count(*) from CUSTOMERS group by SEX;

SEX    COUNT(*)
---- ----------
M         25000
F         25000

備考欄を追加する

ここで、顧客毎に異なる特記事項を記録する必要が生じたことを考えてみましょう。特記事項は長くなりそうなのでCLOB型で記録する必要があるものとします。

さあ、どのように変更したらよいでしょうか？

1. 単純にカラムを追加する（Nullを排除しない設計）

それでは、よくあるパターンとしてカラム追加で対応する方法を考えてみます。

元のテーブルを丸ごとコピーしたCUSTOMERS_1テーブルに、CLOB型のREMARKSカラムを追加してみましょう。

顧客テーブル１

 

SQL> desc CUSTOMERS_1
 名前             NULL?    型
 ---------------- -------- ------------------
 CUST_ID          NOT NULL NUMBER(9)
 CUST_LNAME       NOT NULL VARCHAR2(30)
 CUST_FNAME       NOT NULL VARCHAR2(30)
 SEX              NOT NULL VARCHAR2(4)

SQL> alter table CUSTOMERS_1 add (REMARKS clob);

表が変更されました。

SQL> desc CUSTOMERS_1
 名前             NULL?    型
 ---------------- -------- -------------------
 CUST_ID          NOT NULL NUMBER(9)
 CUST_LNAME       NOT NULL VARCHAR2(30)
 CUST_FNAME       NOT NULL VARCHAR2(30)
 SEX              NOT NULL VARCHAR2(4)
 REMARKS                   CLOB

他のカラムはNot Null制約が付与されていますが、追加したばかりのREMARKSカラムはNullデータを許しています。

備考欄にデータを設定する

カラムを追加しただけではデータは「Null」なので、次のUpdate文でデータを設定します。


SQL> update CUSTOMERS_1 set REMARKS = ‘0' where mod(CUST_ID,5)  = 0;

10000行が更新されました。

SQL> update CUSTOMERS_1 set REMARKS = ‘1' where mod(CUST_ID,10) = 1;

5000行が更新されました。

SQL> update CUSTOMERS_1 set REMARKS = '2' where mod(CUST_ID,20) = 2;

2500行が更新されました。

SQL> update CUSTOMERS_1 set REMARKS = '3' where mod(CUST_ID,50) = 3;

1000行が更新されました。

SQL> commit;

コミットが完了しました。

SQL> select
  2   nvl(DBMS_LOB.SUBSTR(REMARKS, 1000, 1),'Null') REMARKS
  3  ,count(*)
  4  from
  5   CUSTOMERS_1
  6  group by
  7   DBMS_LOB.SUBSTR(REMARKS, 1000, 1)
  8  order by
  9   1;

REMARKS           COUNT(*)
--------------- ----------
0                    10000
1                     5000
2                     2500
3                     1000
Null                 31500

2. Nullを排除した設計

ジョー・セルコの言う「DDLにおいてこれを排除する」とは以下の様に備考欄を別テーブルに分割した構造です。

顧客テーブル２

REMARKSカラムにはNot Null制約を定義し、Nullデータを入力できないようにします。

DDLは以下のようになります。（ここでは便宜上CUSTOMER_1表を元にデータと一緒に作成しています。）

SQL> create table CUST_2_REMARKS as select CUST_ID,REMARKS from CUSTOMERS_1 where REMARKS is not null;

表が作成されました。

SQL> alter table CUST_2_REMARKS add constraint FK_CUST_ID_2 FOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS_2 (CUST_ID);

表が変更されました。

SQL> alter table CUST_2_REMARKS modify REMARKS not null;

表が変更されました。

SQL> desc CUST_2_REMARKS
 名前              NULL?    型
 ----------------- -------- -------------
 CUST_ID           NOT NULL NUMBER(9)
 REMARKS           NOT NULL CLOB

パッと見た感じ、２つのテーブルをジョインしなければならないしパフォーマンス的には不利なような気もしますが、次回は両者のパフォーマンスの違いを見てみましょう。

（続く）

DOAって和製英語？

先日のITアーキテクト養成講座で講師のNRI石田さんと話していて初めて知ったのですが、「DOA:Data Oriented Approach（データ指向（あるいは中心）アプローチ）」という言葉は欧米のアーキテクトには通じないそうです。

データベース設計を少しでもかじった経験のある人ならば、このDOAという言葉はどこかで聞いたことがあるのではないでしょうか？
手元にある本を見ても プロとしてのデータモデリング入門（P.2）、楽々ERDレッスン（P.86）、業務システムのための上流工程入門（P.61）にはいずれも数ページをさいてDOAについて解説してあります。

確かに「xOA」という3文字略語はIT業界ではしばしば目にしますが、SOAにせよOOAにせよ「A」は「Architecture」あるいは「Analysis」であって、「Approach」というのはあまり聞きません。むしろ和製英語の香りがプンプンします。

ここでこんな話を持ちだしたのは、和製英語だからダサいとか、米国発の情報がいつも正しいということを言いたいのではなく、むしろ今までいろいろな開発プロジェクトを見てきて、データを中心に考えているそれらは非常にうまく行っているケースが多いという事実を再認識したいのです。

欧米ではBOK:Body Of Knowledgeと呼ばれる特定領域の知識体系を定義する概念なりドキュメントが充実しています。IT業界に関係するBOKとしては以下のようなものがあります。

  • BABOK（ビジネス・アナリスト向け知識体系）
  • PMBOK（プロジェクト・マネジメントの知識体系）
  • SWEBOK（ソフトウェア・エンジニアリングの知識体系）

各領域の最適化がBOKによって担保されたとしても、最終的にシステムとしての全体整合性の責任は誰が持つのでしょうか？それこそがITアーキテクトなのではないかというのがITアーキテクト養成講座の目指すところであるとの説明を聞き大いに納得した次第です。となるとITアーキテクトという考え方自体が「和をもって尊しとなす」日本発の概念なのではないかとも思いました。

P.S. 実はデータ管理にも「DMBOK:Data Management Body of Knowledge」なるものがあります。こんな本も出ているようです。データマネジメント知識体系ガイド 第一版 （データ総研監訳）手元にないので今度探してみたいと思います。