SQLパズル:解答と解説

 
 それでは、先週のSQLパズルの解答・解説です。解答をお寄せいただいた nano さん、岡本さん、ありがとうございました。

 解答といっても、最初におとこわりしたように、この手の設計の問題にいつでも通じる最適解はありません。
 そこで、お二人ともB案を支持しておられたので、私のほうからはA案を採用した場合のメリットを紹介することで解答に代えさせてもらいましょう。

 A案、つまり階層付きコードの粒度を細かく分割して、この場合なら3列にすることの最大のメリットは、検索条件で索引を使いやすくなることです。例えば、大分類までを検索したい場合、B案だと文字列を切り出すために、列に対して付与した索引が働きません。一方、A案をとって3列に対する複合索引にしておけば、大のみ、大 + 中 のときの検索でも索引が使えます。

  • A案:icd10_big = 'F'
  • B案:SUBSTRING(icd10, 1, 1) = 'F'

 同じことは、EXISTS を使った結合条件の際にも言えます。中分類までを絞りたい場合には、A案ならば次のように書けるので、やはり2列に対する索引が有効です。


・・・・・・
EXISTS (SELECT *
FROM ICD
WHERE Foo.icd10_big = ICD.icd10_big
AND Foo.icd10_mid = ICD.icd10_mid);

 ICD10のように分類数が非常に多いコード体系(確か数十万を超える)では、この手の高速化はけっこう重要です。今回は論点から除外しましたが、体系が経年変化することを考えても、テーブルの規模は増える一方です。

 B案の場合も、SUBSTRING 後の値に対して関数索引を張ることで同じ効果は望めるけど、これは関数索引を使える DB に限られるので実装依存になります。

 また、もう一点A案のよいところを挙げるなら、それはコードの可読性がよいところです。列名に big や mid を付けておくことで、大分類や中分類であることが自ずと読み取れる。B案のように SUBSTRING(icd10, 1, 1) とか SUBSTRING(icd10, 2, 2) のように書くことは、頭の中で余計な一ステップを踏ませるため、可読性を下げます。

 さて、A案の擁護はこの程度で終えるとして、最後に、はてなブックマークで kamataro さんからコメントを戴いた、A案とB案の併用、つまり、3列 + 1列の合計4列持つ方法はどうか、という質問にお答えしましょう。

 私の考えでは、それは避けた方がよい、と思います。理由は更新の際に二度手間になるし、それによるデータ不整合を起こす要因を増やします。「一箇所に一つの事実」という正規化の原則を破るために、そうなってしまうのです。冗長性を減らすことが RDB の設計における第一目的です。