テーブル正規化とキー設定

テーブル正規化とキー設定 何でそれをやったほうが良い事になったのか？ 時代背景などを絡めた お は な し。 アルケミックス 久保田 真司 2020/1/31

自己紹介 ～ こんな奴です 業務歴 • SAS歴（ALCHEMICS/ACM） 9年 • ↑この業界VB・ACCESS・C＃＋SQLSERVER入出力SYSTEM（ALCHEMICS/EPS）10年 • ↑普通のSYSTEM屋数年VB・ACCESS・ORACLE・AS400（ALCHEMICS/数社）数年 • ↑ファミコン屋店長など（ALCHEMICS）数年 • ↑写真撮影のスタジオで現場監督（凸版印刷写真部）7年 仕事以外では・・ 物の修繕・作成・二輪大好き・写真は時々・釣りもまれに・良く散歩します 月に３～４日レザークラフト講師（ホームセンターなど） 雑学・映画・洋物TVドラマ・本も大好き・最近キャンプはいかないなぁ・・。 後は普通にお父さん業（朝のゴミだしとか皿洗い、家のメンテナンス）

実は、ACMで、 数回/月 部署内でのPC系雑学等配信 を依頼されておりまして。 今回は、その折の配信内容の、ほぼそのままの焼き直しです。 すなわち、「読むだけ。」のスタイルで記載されていますから。 文字が多いです。ページ多いです。 従いまして、今日はページの概要だけ言いながら「どんどん飛ばします」。 その場では中身が読めない速度で行きます。 興味のある方は 後で読んでください。 なお、大体の場合、概要イメージをつかめれば、良し。 とする性分ですので、細かい突っ込みもご遠慮ください。 また、作成時、内部用大前提で作っていましたので、 画像などの著作権は、完全に無視して作成されています。 ご容赦くださいまし。 こまけぇ ことは いいんだよ

ちゃんとできてますか？（テーブル正規化） お疲れ様です。久保田です。 近々、SQLのお話をしようと思うのですが、どうしても、前もって テーブルの正規化 という意味。 を再確認しておかないといけないと思っての今回です。 この知識があやふやだと、この後のSQLの理解が、 ふわふわ～ なまま、「終わる」公算が非常に高いのです。 で、先ずは前置きから。 私は良く、SASのMERGE文について、 ・SQLでもある、UPDATE(更新)とINSERT（追加）を行う処理である。 ・しかし、フィールドの明示が必要ない事がスタンダードスタイルな上、 「どちらかに無いフィールドは勝手に増加」「同名フィールドはどんどん上書き」をスタンダードとす る、その設計思想が厄介の種。 というのですが、実は、ほぼ正規化されたデータベースで、作業途中で、KEEP文やDROP文を適切 に使っていれば、また、作業途中の生成変数のコントロールをしくじらなければ、実はそれほど問 題でもないのです。

ほな、何で文句言うねん。 それは、作業途中で、KEEP文やDROP文を適切に使っているとはあまり思えないから。 それは、テーブル設定が適切でない場合が多いから。 にもかかわらず、「やっちゃってる」から文句言うのです。 （とはいえ実はSAS-DSは運用上、正規化しきらない方が良い運命ではあります。） ぱっと見が便利だからと言って、「何でもテーブルに持たせて良い」という物ではありません。 もちろん、SASでデータセット作成作業を行う場合は、 あくまで帳票作成の為の、中間作業テーブル作成である。 という事がありますから、利便性の為、カテゴリー分けの為の計算も予めいるでしょうし、 ある程度の計算値を持つ事は良いのですが（普通は避けるべき作業です）、せめて、 ・キー項目でもないのに、「同じ値をあっちこっちのテーブルにばらまく」設計（まいてもいいけど最小に・・） ・作成順でもなく、しかもキーがあっちこっちにばらけている「フィールドのおかしな並び」 の二つは、やめておいた方が良いかと思います。 こんな風に思っているので、このお話。 というわけです。 以上前置き。

/*----- 正規化って何だっけ？ -----*/ すごーっく大まかに言うと！ データを、ルールに基づいて、重複の無い、 整合性の良いデータベース形式に加工する事。 難しい説明では、「１～５段回に分けて・・・」とか、「いや、３段回でいいからこのよ うに。」 というのが良く見る説明です。 （それぞれ、第○正規形という呼ばれ方をしますが、 私はそういうかしこまった用語遊びが大嫌いなので、好んで避けます。） この考えが出てきたのは、リレーショナルデータベース(Relational DataBase Management Sysytem)において、誤りなく、効率良くデータが扱えるようにしよう！ という事で生まれた方法です。 従ってCDISCは、全然その思想でないので、関係ありません。 （形式はRDBMSその物なので、私は反対ですけど。）

これに従うと、何がそんなに良いのかというと！ • データの冗長性を排除できますので、小さいサイズでデータベース を扱えます。 • データの追加・更新・削除などの作業漏れで、データの不整合や喪 失が起きるのを防ぎます。 • 処理速度の向上にとても役に立ちます。 反対の状態は、混沌（カオス）形式 （うそです。）非正規形のデータと 呼ばれます。 一般に、Excelの一覧で、とっても良く見かけます。 ADaM形式のデータも、ほぼほぼ このお仲間でしょう。

/*----- 何で正規化？そのいきさつを知る歴史 -----*/ 正規化のメリットは、上で述べた通りなのですが、 なぜそのような事が行われるようになったか？？ の背景については・・・こんな歴史があるのです。 まず、ハードの大まかな役割がわからないと話も難しいので、ざっくりそこから。 （といっても、下図程度の事がわかればOK です。） PCは大まかに以下のような構造である事はご存じ。だと思います。 記憶装置 高速小容量 処理装置 記憶装置 低速大容量 H マザー ボード network input output ↑ 高速ってどのぐらい？ というと2020年現在で、良い物で理 論値MAX、20GB/s ぐらい。 平均的に10GB/s ぐらい。 DD ↑ 低速ってどのぐらい遅いのかというと 2020年現在で、最も早いSSDの読み込みでも 実効速度はせいぜい500MB/sぐらい HDDならどんなに良くても150MB/s越えなら上等

この速度状況の差を身近な物に置き換えて考えると？ 計算しやすいように、HDDの速度をちょっと水増ししても、 10GB/s:200MB/s → 1:1/50 → 50:1 の関係。 身近な物で、具体的な数値に置き換えると、下記のようなイメージがぴったり。 本１冊を参照する為に あぁ~そこの君、R/Wどっちの速度？とか ランダムで？とか 本当にその辺真中か？とか 無駄な突っ込みはしないように。 大体そんなもんです。 大体。 頼むから、話をややこしくせんでくれたまえ。 H 本棚 60秒必要 とすると DD 図書館 3000秒(50分 ！)必要 どっちが得かよーく考えてみよう

で、歴史。といったので。歴史的速度に当てはめてみると！ なんと！本一冊参照する為に！ 60秒で済むはずが！ 商用のRDBMSがいきわたりだした1980年代当時は、もっとこの差がひろかったわけで。 ローカルベースでも、SSDなんぞこの世に無くって、HDDはある物の、まだ低容量で（MB単位）存在その ものが極めてまれで、家庭用ならカセットテープ。業務用でようやくフロッピーディスク。これでもせいぜ い500KB/Sぐらい！(KB単位 なので、速度は先の例に倣うと、なので120万秒=2万分=333時間=約14日) さ～ぁ行こう 夢に見た島へと～ 14日もあれば優雅に船旅が・・ ネットワークにいたっては、インターネット以前。パソコン通信の時代。パソ通時代終焉の頃の最終最速 で56KB/s 初期なんて 300B/s 笑 分散コンピューティングなんて速度遅すぎてとてもとても。 80日間世界一周・・ (先の例で行くと 最終最速56KB/sで、およそ140日） 140日もあれば 大方２周できますやん。・ （300B/sなら更に186倍だから 26000日あまり 72年 爆死級の速度です。) 音響カプラの使い方 ぴーひゃららら 宇宙暦41720.0 ・・・ 本一冊に どこまでゆかねば ならんのでしょうか・・

まぁそういう時代背景だったわけでして。 現在にいたっても、今なお！上記のような時間差およそ５０倍（概算ね）がはっきりありまして、加え て前頁のような時代であり。 できる事なら、↓ でだけ作業したいわけです。 大丈夫！今やメモリも大きくなってるし！ いやいや、当時より、物理メモリが大きくなっているといっても、 上限はもちろんあるわけで、OS等と場所とり合戦する事は同じ。 例えメモリに何GBも搭載しているPCであっても、それが丸々使えるわけではないのは自明です。 加えて、RDBMS自身でも 日々拡大する必要エリア。 でも、速度の事情がそんなわけですから。 できれば、オンメモリで色々持ちたい わけです。

ま、そんなわけでメモリの様子は、大体こんな感じ。 はいはい。ばしょあけて ※トランザクション処理 さり気に書いてますけど、重要な機能。 処理の一貫性を保つために、 一連の処理は一個の処理単位として 他から独立に、管理実施記録する機能の事。 RDBMSの肝の機能。 わしもつかうねん トランザクション処理 SELECT * FROM TableA WHERE XXX 結果は、毎回動的に 作成されて、 結構複雑な処理 が可能 よして～や データ加工：選択・結合・射影・集計・並べ替え だから、格納エリアは、 節約して使う。 正規化された 無駄のないテーブルを格納する。 これ大事。 大混雑 H わたしらも場所いるねん でも、場所的に足らない時は、 仕方がないので、仮想メモリと称して、HDDに肩代わりしてもらうわけです。 なぜかSASデータセットは、最初から全てが 劇遅 HDD にファイルとして保存されますが・・・。扱いやすいですけどね。 DD

理由お分かりいただけましたでしょうか？ なので、ディスクに処理が回った時の事を考えて、余分なI/Oは可能な限り避 ける。つまり余分な情報は削ぐ。これ大事。となるわけです。 (そういう意味では、Data文で丸ごと引っ張ってきて、しかもIFで条件付けなんて、 最悪の手法であると思っています。ま、しょうがない場合もありますけどね。) その為に提唱された考え方が、三銃士で有名になり、 ラグビーでもよく使われる One for All, All for One 「One Fact in One Place（1つの事実は、1つの場所にだけ存在させる）」 無駄な格納場所を設ける事は、連鎖的に不都合な事柄が起こる要因となる。 ↓ 正規化が登場ということです。

ところで。反対の事を言うようですが！ 例えば、投与開始日などの限られた情報を、 全テーブルに持たせるという事はあながち悪い事ばかりでもないのです。 定番的な結合処理の問い合わせを省けるので、 作業・計算処理・各種操作の高速化に貢献できる。 なーんて言う側面も有り。 なんやねん。結局どっちや？怒！ 要はバランスです。というのが結論です。 正規化を一度ちゃんとやる → 必要なフィールドだけ正規化に逆らって、利便性に配慮して再配置。 この段取りがよろしい。そういう事です。 特にSASにおけるデータセット作成はレポート用の中間作成物ですから 、特に、書き込み更新されるわけではありません。 なのでこういう選択意図を持った、部分的正規化崩しはその役割上ありです。 （仮に将来入力システムのDBに触れる機会があったら、その時は間違っても正規化崩しは考えないように。） ただし、段取りはちゃんと踏んでから！なんとなく増やさない！という事は大事です。

で。これは正規化とはあんまり関係ないのですが！ 昨今RDBMS以外のデータベースもあったり、 なぜそんなもんが出てきたのか？等のついでのお話です。 ついで・ついで・ついで・ついで・ RDBMSのいきわたった時代 には、前述のように、ネットワークなどまだ全然話にもならないレ ベルでしたので、年々要求の厳しくなる速度や容量に対するパワーアップは、 メインサーバーのパワーアップで対処する。 こういう設計思想で世の中回っていたわけです。 これがまた、前述したトランザクション処理とまことに相性が良いというか都合が良い。なにせ、管 理個所が１個所ですから！整合取る作業としてはまことに具合がよろしい。 こういうパワーアップの仕方を スケールアップといいます。 都合よく進んでますから、あまり、現状を疑うことなく、時代は進みますが・・・

さて、やってきましたインターネットの幕開け。 いまや、皆さんオンラインでデータのやり取りを激しくなさっています。 今まででも、件数の多い処理はもちろんあったわけですが、 もはや桁が異なる時代がやって参りました。 一秒間に数万件なんて言うバカみたいな処理をこなさねばならん。。 もしかしたら、内容も馬鹿みたいなデータかもしれなくても。 もはや、ハード的なパワーアップだけではさすがに限界です。 バ ル ス やったひとは？ で、でてきたのが！ 分散サーバーの考え方。 おこらないから てをあげてごらん サーバーひとつじゃなくて、あっちこっちでわけてデータ保存して、 ばらばらで処理して、最後まとめて結果出す。 こういう仕組みですね。 時代は、光ファイバーGB/s。ネットワーク速度も追い付いてきたという事もあり・・

分散サーバー本格運用でデータベースに変革が。 おんなじような、やすいのたくさん用意して ぶんさんして しゅうやくして 入力 これ、端末台だけは安いかもしれません。 でも実はいいことばかりじゃぁないのですね。 出力 こういうパワーアップの仕方を スケールアウトといいます。 まぁ「うまく切り分けできる」内容で 「データが固定的」ならいいんですが・・・ そして、こういう構造で管理できる仕組みのデータベースというのは、従来のRDBMS（←分散全然向いてませ ん。）ではなくNoSQLなどと呼ばれる、（NotOnlySQL）データベース群、（詳しい名称は、どれ一つまともに 使った事ないので知りませんが・・・Hadoopなんかは名前聞くので有名なのかな・・？？） 分散処理での高速処理に最適化されている（らしい）新しいデータベースが登場してきます。 もちろん最適化されているということであれば、自然そちらのデータベースを選ぶことになっていくと思います。 ですが・・・

しかしですねぇ。このニューフェースたちの一 般的傾向が・・・ （ここと次のページは、運用経験が無いので、今回調べましたよ。知りませんでしたよ。でも、語らないのはアンバランス。片手落ちというもの なので、Google、巡りしましたよ。だから間違いご容赦。運用経験があって知っている人、ぜひ間違いあれば教えてください。） はーい並んで並んで、えっ入金 データの前に出金データ来た？順 番逆逆！はい並び変えて。はい。 いっぺんにこない！そこ順番ぬか ししない。あなたあっちのグルー プでしょ。はぐれないように一緒 になって・・ RDBMSにあった このDJポリス顔負けの緑のおばさん 遅くなるから廃止 トランザクション処理 不整合防止の機能・・ 高速処理の為に、なくしちゃった(らしい)んですねぇ。 つまり、データの管理はほぼしないみたいです。 なので、持ってるだけ。つまり、 サーバ間の保存データの整合性は（速度確保の為に）厳密には保証対象外。（それでもデータベースか！） データの衝突が起こってもわしゃ知らんよ。ということらしいです。 非正規化データ上等、重複保持どんと来い。な模様です。 リアルタイム処理は切って捨てて、大容量バッチ処理に特化した・・そんなところでしょうか？ まさにビッグデータ向けですね。 まぁSNSの投稿とか、Googleの検索とか、確かにそんなに整合がいるような気はしませんが。

ほかに一般的傾向として、ある事柄は！ 世の中のほかの事例にもれることなく長所短所併せ持っているようで。 良い点 • 構造に縛られない！自由にデータ形式が組める。 • スケールアウト方式を見据えた設計なので、分散処理でとっても高いパフォーマンスが得られる。 • スケールアウト方式を見据えた設計なので、パワーアップの敷居は低い（ちょっとだけパワーアップなら という前提ですが） • スケールアウト方式を見据えた設計なので、パワーアップの上限が高い（金に糸目をつけないなら という前提ですが） • 単純な読み書きは早い！ • 爆発的な問い合わせにも対応可能。（Webショップとか、チケットとか・？？オンライン処理には不向きらしいですが？？） • 大容量は大いに得意とする！ 悪い点 • あまり複雑な構造のテーブルは持てません。 • 結合、検索、集計、並べ替えなどの基本加工がほぼできない。 のような感じ(らしい)です。 これが現在、Webなんかで幅を利かせている？（のかな？すいません詳しくないです。）手法(らしい)です。 あぁまた脱線が長くなりました。本筋に戻ります。

やっと本筋/*----- 正規化の実際 -----*/ 実際にありそうなデータで、 どのように加工したら、正規化になるのか？ を見ていただいて、覚えていただこうかと思います。 良くある３段回形式でお送りします。 「データベース構造 作ってよ！」と言われた時の基本知識になります ので、是非！この段取りを記憶に残しておきましょう！

０．非正規形のデータ（脳内作業可） ３段回で正規化するとは書きましたが、実は「まず」この非正規形の データにまとめる事が第一段階となります。 これすら作れないようでは、パソコンを使った作業などまったく不可能 です。 ルールは三つ。 ・データを、一覧表に加工する。 ・列↓は、1定義/列。 ・行→は、ユニークキーで一意識別できる事。 ユニークキー＝その値が決まれば、「必ず１行だけ」データ行が決まる。 そういう値の事です。普通、複数フィールドにわたる事が多いです。 キーについては後ほど説明します。

（例はキー１個ですが、複数の事も普通にあ ります）

１．一番目の正規化の手順（繰り返しの排除） （これも、脳内作業＋メモ程度で処理可です。） ・同じ様な繰り返しを排除して、複数の表に分ける。 ・分けた表は、元の表と再結合できるように、元の表のユニークキーを 保持する。 ・分岐表でユニークキーとして機能するために、加える必要な値があ るのであれば、その値を分岐表のユニークキーとして追加する。 最初はこれだけ。繰り返しを除外すれば終わりです。 そして、分岐したそれぞれの表でユニークキーを定めます。

これが・・ 症例ごとに1件だけの情報と・・ 症例ごとに複数件ある情報を、1行1件式に・・ ■は、それぞれの表でのユニークキー

２．二番目の正規化の手順（キーの適正化） （同じく、脳内作業＋メモ程度で処理可です。） ・復号キーをユニークキーとする表に注目する。 ・その構成要素に、従属関係はないかどうか注目し、前工程同様に表を分岐する。 例えば、今回の例だと、測定値は、患者・時期・日・時・項目 まで決まらないと！ 「どこの値」か確定できません。 しかし、項目名や、測定単位、測定値の上下限値等は、 （場合にもよりますが、） 項目 だけ！決定すれば、 当てはめるべき値は決定できます。 よって、測定値特定の為のユニークキーは、上記表のユニークキーがそのまま必要。 その他の正常値や単位特定の為のユニークキーは 項目番号だけで良いという事になります。 ・前工程同様に、分岐表でユニークキーとして機能するために、 さらに必要な値があるのであれば、その値を分岐表のユニークキーとして追加する。

３．三番目の正規化の手順（キー以外で互いに 関連を持つ情報の適正化）（ここにいたっては、 今までの諸々含みで、形にしましょう。） ・キー以外の関連性を持つフィールド全てに注目する。 ・その構成要素に、従属関係はないかどうか注目し、前工程同様に表を分岐する。 今回の例だと、ほぼ患者背景一覧では、 例えば性別は、あくまでも患者に属する性別コードがあるので、その決定に従う形で、 コードに対応する名称が決定する。そういう構造です。 このように、「間接的に決まってゆく」といってよい物を抜き出してしまいます。 場合によっては、そのようにして、抜き出した物をまとめて、 マスターとか台帳とかカタログと呼ばれる、別個の一覧表にまとめる場合もあります。 ・前工程同様に、分岐表でユニークキーとして機能するために、 必要な値があるのであれば、その値を分岐表のユニークキーとして追加する。

結果は以下のようになると思います。

いかがでしたか？ 勘違いはありませんでしたか？ 続いて、後でお話しする。としていた 「キー」のお話です。 そう。まさに鍵なんです。 時々聞きますが、 キーたりません だから並び順がおかしくなって コンペアが一致しません。 もう、こういう話が出てくるということは、 少なくともキー設定時点で、「キー」について分かっていない。 そういう証明ではなかろうか？と思うわけです。 大したことではありませんから、しっかり覚えましょう。 や ね で ん ん な

/*----- キーって何だっけ？ -----*/ 主キー、ユニークキー、インデックスキー、ソートキー、プライマリーキー、外部 キー、ユニークインデックスキー等々 いろんな名称の「キー」があります。 ところで、皆さんがよく、データセット仕様書で書いている、あるいは見ている キー。ありますよね。 これですよ。これ。 キーとだけ書いてありますが・・・ ○○キーと思って扱っていますか？ 下から選んでみてください。 ・主キー ・プライマリーキー ・ソートキー ・インデックスキー ・ユニークキー ・ユニークインデックスキー ・外部キー

こたえ。

で、どういうはなしやねん。 えぇ簡単な話ですとも。 厳密な定義ではないですが、キーの果たす役割は大きく３つ。 もう、ほんとに、これだけですから！じゃぁいきますよ。

主キー プライマリーキー 解説 １つのテーブルで、１種類（１フィールドだけではなく復号でもOK）しか指定できません。 ・指定されたフィールドの値で、レコード（行）が一意に識別できなければなりません。 ・指定されたフィールドにおいて、NULL値は許容されません。 ・指定されたフィールドにおいて、原則として、値の変更は許容されません。(特例はある。) ・他のテーブルとの接続に使用されます。 実は、主キーを設定すると、内部的にですが、 同時に、NULLを許容しない設定のユニークキーインデックスが、 作成される設定になっている場合が多いと思います。 なので、検索機能は付随的に○ 例：患者番号、会員番号、PC側で作成された自動行番号等。

ユニークインデックスキー 解説 １つのテーブルで、複数（１フィールドだけではなく復号でもOK）指定できます。 ・指定されたフィールドの値で、レコード（行）が一意に識別できなければなりません。 （ただし、NULL値が含まれる場合には、一意であるかどうかの判定対象外となってしまいます。） ・指定されたフィールドにおいて、NULL値が許容されます。 ・指定されたフィールドにおいて、値の変更が許容されます。 インデックスなので、設定にもよりますが、検索速度が上がります。（追加・更新・削除 は、遅くなります） 例：患者番号、会員番号等でも、もちろん良いのですが、 例えば、患者番号は主キー、 ユニークインデックスは、住所と氏名と生年月日 あるいは 病院とカルテ番号 というような存在です。

ユニークキー • 解説 名前通り、ユニークインデックスキーから、インデックスを省い た物です。後は同じです。

インデックスキー 解説 インデックス「キー」とは言いますが、実は少々別の存在です。 インデックス＝索引の名前通り、検索を早くするため、本体とは別に、 連動した小規模テーブルを内部的に、別途作成する。という作業が実は発生しています。 どうちがうのか？といいますと！検索を早くする以外の役割は有りません。 詳細は次ページで図示しますが、 ・更新系の命令があるたびに、インデックスを作り直す必要がある為、更新系の動作は遅くなります。 ・テーブルの占有するサイズは肥大化します。 ・検索は早くなるとは言いますが、出番が少ない、的外れのインデックスを作っても、総合的には遅くなるだけです。 一般に、最終選択結果が少なくなるような場合で、 加えて、頻繁にその検索がかけられるような場合に限って、インデックスは有効です。

ソートキー 物事はね。 如何にその本質に迫って 見る事が出来るか。 そこが肝要な所でね。 表面的に見えている事柄、 そんな物だけ追いかけていてもダメな んだよ。 多くの人が、「見ているのに見ていな い。」 わかるかね？ワトソン君。 解説 これも、ソート「キー」とは言いますが、全然別の存在です。 通常、この、ソートキーというやつは、 どの順で並んでいるか？ というだけの話で、キーの役割である上記の三つの役割のどれも果たしません。 そのようなわけで、キーとは名づけられていますが、いわゆる「キー」ではありません。 現状、この使い方で、間に合ってしまう事が多いので、これをして、 キー キー と呼んで、運用してしまっていますが、 データの制御や保持形式、キーという物のありかた、 諸々考えれば、そうではないはずですよね。 もう、運用の仕方、呼称、キーへの理解。共に、ごちゃ混ぜです。 その環境を「普通」としてしまっている、「だから」、DMの新人さんなんかが、 データのありかたの意味がとらえにくいんじゃぁ？そんなことまで考えてしまいます。 いや、もちろん理解して、その 上で、もう、煩いから、付き合 いで「はいはい」と流している だけの方も多いはずなんです よ。でもね、まわりがそうである と、「わかってないひと」は ずーっと解ってないままで、い つか事故起こします。 というか、そういう事故データ 見ますよね？ そしてデータのあり方を良く考 えていない要求受けますよ ね？ つまり、既に巻き込まれていま す。 はい。

外部キー 解説 これは基本的に、普通のテーブルの主キーが、場合により兼任する名前です。 従って結果的に主キーの持つ役割と同じ物を持つ場合もありますが、 外部キーという物の役割の説明に、上記３種類の機能は直接含まれません。 なぜなら、 主キーまたはその一部、 あるいは、 主キーと名のつく物全て が対象となるとは限らないからです。 言葉では少し分かりにくいので図示します。

この例の場合、双方が、各テーブ ルの主キーですから、NULL値は持 てませんので、その部分は排除し て考えて良いです。 なので、外部キー制約自身を、「わ ざわざ」設定するかどうか？は自 由ですが、設定しなかったとしても、 「参照整合性」という物には気を払 わねばいけません。 例えば、 患者が、登録抹消になる場合、単 純に背景からだけデータを抜くと、 有害事象データは「ゴミ」として残っ てしまいます。 そのような操作が必要な場合には、 外部キー側から順序良くデータを 除去して行く作業が必要です。

話は見えた。で、それってSASでは・・ どう書くの？ 結局これだけで良かった？？ まず、Indexの状態を表示確認できるようにして、 ネタになるデータを用意します。 /*------------------------------------*/ /*-- See message about index status --*/ /*------------------------------------*/ options msglevel=i; /*------------------------------------*/ /*-Create Data --*/ /*------------------------------------*/ data ADLB; do SUBJID= 1 to 100000; do PARAMN=1 to 10; AVAL=int(rand('uniform')*100); output; end; end; run; 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 /*------------------------------------*/ /*-- See message about index status --*/ /*------------------------------------*/ options msglevel=i; 10万行用意し て見ました /*------------------------------------*/ /*-Create Data --*/ /*------------------------------------*/ data ADLB; do SUBJID= 1 to 100000; do PARAMN=1 to 10; AVAL=int(rand('uniform')*100); output; end; end; run; NOTE: データセットWORK.ADLBは1000000オブザベーション、3変数です。 NOTE: DATAステートメント処理(合計処理時間): 処理時間 0.08 秒 CPU時間 0.07 秒

～ ～ ～ ～ ～ ～ 途中色々省略表示しています。 次に、問題のキー、インデックスを設定。 そして、設定結果の確認！ /*------------------------------------*/ /*-Create Indexes --*/ /*------------------------------------*/ /*Create using dataset options*/ data ADLB_IDX(index=(PARAMN)); set ADLB; run; /*Create using proc datasets*/ proc datasets; modify ADLB_IDX; index create KEYS=(SUBJID PARAMN)/ unique nomiss; quit; /*Create using proc sql(Options)*/ %let OPTWD=index=(KEYS=(SUBJID PARAMN)/nomiss/unique); proc sql; create table ADLB_IDXQ(&OPTWD.) as select * from ADLB; quit; /*Create using proc sql(Create index)*/ proc sql; create index PARAMN on ADLB_IDXQ(PARAMN); quit; /* *Hey! SAS!! Why is [nomiss] invalid even if you display it yourself?? -_-#; proc sql; create table ADLB_IDXQ(bufsize=65536) as select * from ADLB; create unique nomiss index KEYS on ADLB_IDXQ(SUBJID,PARAMN); quit; */ /*------------------------------------*/ /*-Check the result --*/ /*------------------------------------*/ proc sql; describe table ADLB_IDX; quit; proc sql; describe table ADLB_IDXQ; quit; ～ ～ ～ ～ ～ ～ 301 /*-Create Indexes --*/ INFO: インデックスの作成には、複数の同時スレッドが使用されます。 NOTE: 単一インデックスPARAMNが定義されました。 処理時間 0.20 秒 CPU時間 0.45 秒 308 /*Create using proc datasets*/ # 名前 メンバータイプ ファイルサイズ 2 ADLB_IDX DATA 24313856 ADLB_IDX INDEX 8119296 INFO: インデックスの作成には、複数の同時スレッドが使用されます。 NOTE: 複合インデックスKEYSを定義しました。 NOTE: WORK.ADLB_IDX.DATAが正常に変更されました。 処理時間 0.25 秒 CPU時間 0.43 秒 313 /*Create using proc sql(Options)*/ INFO: インデックスの作成には、複数の同時スレッドが使用されます。 NOTE: 複合インデックスKEYSが定義されました。 処理時間 0.31 秒 CPU時間 0.45 秒 319 /*Create using proc sql(Create index)*/ NOTE: 単純インデックスPARAMNが定義されました。 処理時間 0.14 秒 CPU時間 0.35 秒 331 /*-Check the result --*/ NOTE: SQLテーブルWORK.ADLB_IDXは次のように作成されました: create table WORK.ADLB_IDX( bufsize=65536 ) ( SUBJID num, PARAMN num, AVAL num ); create unique nomiss index KEYS on WORK.ADLB_IDX(SUBJID,PARAMN); create index PARAMN on WORK.ADLB_IDX(PARAMN); NOTE: SQLテーブルWORK.ADLB_IDXQは次のように作成されました: create table WORK.ADLB_IDXQ( bufsize=65536 ) ( SUBJID num, PARAMN num, AVAL num ); create index PARAMN on WORK.ADLB_IDXQ(PARAMN); create unique nomiss index KEYS on WORK.ADLB_IDXQ(SUBJID,PARAMN); インデックスの容量そのも のもなかなかです。 本体の1/3程の大きさです 作成に必要な時間にも注目です。 ┌──┬─────┬─────┐ │ │ 単純 │ 複合 │ ├──┼──┬──┼──┬──┤ │ │処理│CPU │処理│CPU │ ├──┼──┼──┼──┼──┤ │DATA│0.20│0.45│0.25│0.43│ ├──┼──┼──┼──┼──┤ │SQL │0.31│0.45│0.14│0.35│ └──┴──┴──┴──┴──┘ 選択処理に必要であった時間と 後で比べて見ます。 構造を聞いたら、おのれで は、「nomiss こんな感じ～」 と、如何にも、こう書いたら 出来上がる風の表示をす るくせに、いざ命令書くと受 け付けないとは！

で、ひとつだけの条件で、効果の確認。 /*------------------------------------*/ /*-- Speed difference comparison #1 --*/ /*------------------------------------*/ %let CND1=where PARAMN=3; /*Data Step Normal*/ data SEL_Dx1; set ADLB; &CND1.; run; /*Data Step With Index*/ data SEL_DIx1; set ADLB_IDX; &CND1.; run; /*SQL Normal*/ proc sql; create table SEL_Qx1 as select * from ADLB &CND1.; quit; /*SQL With Index*/ proc sql; create table SEL_QIx1 as select * from ADLB_IDXQ &CND1.; quit; 作成コスト ┌──┬─────┬─────┐ │ │ 単純 │ 複合 │ ├──┼──┬──┼──┬──┤ │ │処理│CPU │処理│CPU │ ├──┼──┼──┼──┼──┤ │DATA│0.20│0.45│0.25│0.43│ ├──┼──┼──┼──┼──┤ │SQL │0.31│0.45│0.14│0.35│ └──┴──┴──┴──┴──┘ 今回のように、 機械的に並ん でいる状態から、 大して複雑でな ～ ～ ～ ～ ～ ～ 途中色々省略表示しています。 ～ ～ ～ ～ ～ ～ い条件を指定 2826/*-- Speed difference comparison #1 --*/ した場合、 INDEXを作って 2831 /*Data Step Normal*/ NOTE: データセットWORK.ADLBから100000オブザベーションを読み込みました。 いようといまい WHERE PARAMN=3; と、大して差は NOTE: データセットWORK.SEL_DX1は100000オブザベーション、3変数です。 NOTE: DATAステートメント処理(合計処理時間): 現れないという 処理時間 0.04 秒 例ですね。 CPU時間 0.03 秒 2837 /*Data Step With Index*/ INFO: インデックスPARAMNをWHERE句の最適化に選択しました。 NOTE: データセットWORK.ADLB_IDXから100000オブザベーションを読み込みました。 WHERE PARAMN=3; NOTE: データセットWORK.SEL_DIX1は100000オブザベーション、3変数です。 NOTE: DATAステートメント処理(合計処理時間): 処理時間 0.03 秒 CPU時間 0.03 秒 2843 /*SQL Normal*/ NOTE: テーブルWORK.SEL_QX1(行数100000、列数3)が作成されました。 NOTE: PROCEDURE SQL処理(合計処理時間): 処理時間 0.04 秒 CPU時間 0.04 秒 2848 INFO: NOTE: NOTE: /*SQL With Index*/ インデックスPARAMNをWHERE句の最適化に選択しました。 テーブルWORK.SEL_QIX1(行数100000、列数3)が作成されました。 PROCEDURE SQL処理(合計処理時間): 処理時間 0.04 秒 CPU時間 0.03 秒 作成コストを 考えても、こ んな程度の使 用条件しか見 込まれないの であれば、わ ざわざIndex作 らんでも良い。 ということで すね。

次は、複合条件で、効果の確認。 作成コスト ┌──┬─────┬─────┐ │ │ 単純 │ 複合 │ ├──┼──┬──┼──┬──┤ │ │処理│CPU │処理│CPU │ ├──┼──┼──┼──┼──┤ │DATA│0.20│0.45│0.25│0.43│ ├──┼──┼──┼──┼──┤ │SQL │0.31│0.45│0.14│0.35│ └──┴──┴──┴──┴──┘ /*------------------------------------*/ /*-- Speed difference comparison #2 --*/ /*------------------------------------*/ %let CND2=where SUBJID in (50,500,5000,50000) and PARAMN=3; /*Data Step Normal*/ data SEL_Dx2; set ADLB; &CND2.; run; /*Data Step With Index*/ data SEL_DIx2; set ADLB_IDX; &CND2.; run; /*SQL Normal*/ proc sql; create table SEL_Qx2 as select * from ADLB &CND2.; quit; /*SQL With Index*/ proc sql; create table SEL_QIx2 as select * from ADLB_IDXQ &CND2.; quit; ～ ～ ～ ～ ～ ～ 途中色々省略表示しています。 ～ ～ ～ ～ ～ ～ 2854 /*-- Speed difference comparison #2 --*/ NOTE: データセットWORK.ADLBから4オブザベーションを読み込みました。 WHERE SUBJID in (50, 500, 5000, 50000) and (PARAMN=3); NOTE: データセットWORK.SEL_DX2は4オブザベーション、3変数です。 NOTE: DATAステートメント処理(合計処理時間): 処理時間 0.03 秒 CPU時間 0.03 秒 2865 /*Data Step With Index*/ INFO: インデックスKEYSをWHERE句の最適化に選択しました。 NOTE: データセットWORK.ADLB_IDXから4オブザベーションを読み込みました。 WHERE SUBJID in (50, 500, 5000, 50000) and (PARAMN=3); NOTE: データセットWORK.SEL_DIX2は4オブザベーション、3変数です。 NOTE: DATAステートメント処理(合計処理時間): 処理時間 0.00 秒 CPU時間 0.00 秒 2871 /*SQL Normal*/ NOTE: テーブルWORK.SEL_QX2(行数4、列数3)が作成されました。 NOTE: PROCEDURE SQL処理(合計処理時間): 処理時間 0.03 秒 CPU時間 0.04 秒 2876 INFO: NOTE: NOTE: /*SQL With Index*/ インデックスKEYSをWHERE句の最適化に選択しました。 テーブルWORK.SEL_QIX2(行数4、列数3)が作成されました。 PROCEDURE SQL処理(合計処理時間): 処理時間 0.00 秒 CPU時間 0.01 秒 作成コストか ら考えると、 前出同様、一 回だけの問い 合わせであれ ば、わざわざ Index作らんで も良い程度の コストですが、 ほんの少しの 条件が加わる だけで、明ら かに差が出て いる事が観察 できます。 問い合わせ回 数が、見込ま れるのであれ ば十分作成の 意義が見込め そうです。

用事が済んだので、除去。 そして、設定結果の確認。 /*------------------------------------*/ /*-Drop Indexes --*/ /*------------------------------------*/ proc datasets; modify ADLB_IDX; index delete PARAMN KEYS; quit; proc sql; drop index PARAMN, KEYS from ADLB_IDXQ; quit; /*------------------------------------*/ /*-Check the result --*/ /*------------------------------------*/ proc sql; describe table ADLB_IDX; quit; proc sql; describe table ADLB_IDXQ; quit; ～ ～ ～ ～ ～ ～ 2882 NOTE: NOTE: NOTE: 途中色々省略表示しています。 ～ ～ ～ ～ ～ ～ /*-Drop Indexes --*/ WORK.ADLB_IDX.DATAで定義されたすべてのインデックスは削除されました。 WORK.ADLB_IDX.DATAが正常に変更されました。 PROCEDURE DATASETS処理(合計処理時間): 処理時間 0.02 秒 CPU時間 0.03 秒 NOTE: インデックスPARAMNが削除されました。 NOTE: インデックスKEYSが削除されました。 NOTE: PROCEDURE SQL処理(合計処理時間): 処理時間 0.02 秒 CPU時間 0.03 秒 2888 /*-Check the result --*/ NOTE: SQLテーブルWORK.ADLB_IDXは次のように作成されました: create table WORK.ADLB_IDX( bufsize=65536 ) ( SUBJID num, PARAMN num, AVAL num ); NOTE: PROCEDURE SQL処理(合計処理時間): 処理時間 0.00 秒 CPU時間 0.01 秒 NOTE: SQLテーブルWORK.ADLB_IDXQは次のように作成されました: create table WORK.ADLB_IDXQ( bufsize=65536 ) ( SUBJID num, PARAMN num, AVAL num ); NOTE: PROCEDURE SQL処理(合計処理時間): 処理時間 0.00 秒 CPU時間 0.00 秒

参考先はここです。 そのまま、SASからご覧ください。 /*------------------------------------*/ /*-Reference --*/ /*------------------------------------*/ /* %let REF1=https://documentation.sas.com/; %let REF2=?docsetId=lrcon&docsetTarget=n06cy7dznbx6gen1q9mat8de6rdq.htm&docsetVersion=9.4&locale=ja; x start iexplore "&REF1.&REF2."; */ /*------------------------------------*/ /*-E.O.F --*/ /*------------------------------------*/ 以上となります。 ご静聴ありがとうございました。