データベーススペシャリスト 試験情報＆徹底解説

即興ですが、物理設計系の例題を作ってみましたので投稿します。

■出題意図
パフォーマンスチューニング理解、復習に役立てばと思い作成しました。
厳密な採点がしたいわけではないので、自由な穴埋め形式としました。
表記ゆれや別解はあるかもしれませんが、それはそれで議論が生まれてよいのかも？

設定はガチガチに固めていないので、必要であれば「一般的なDB製品とデータ」「標準SQL」「午後2問1の[主なRDBMSの仕様]」としてください。
ひねった問題ではなく、ただまっとうに基礎をまとめています。
想像のテーブルとSQL文なので、誤りがあればご指摘・読み替えてください。
近日中に想定している回答例を投稿します。

問題を投稿しようとしたら403Forbiddenエラーが返ってきて貼れない・・・
SQL文を直で書いてあるのがまずいのか・・・？

すみません、試行錯誤して投稿できそうならします。。。

■状況設定
全国チェーンの小売店のレジデータ分析
月次で発行するSQLクエリ（以下、分析クエリ）を作成し、パフォーマンスも見直したい。

分析クエリの内容は以下の通りである。
・「売上履歴」テーブル（全100億レコード）
・「顧客情報」テーブル（全1000万レコード）
上記の2テーブルを用いて、前月の30代男性の売上履歴を表示したい。

ここで、以下の2案を比較する。
・案Ａ
－－テーブル結合後，ＷＨＥＲＥ句で条件抽出
ＳＥＬＥＣＴ  ＊  ＦＲＯＭ  売上履歴  Ｓ  ＩＮＮＥＲ  ＪＯＩＮ  顧客情報  Ｃ  ＯＮ  Ｓ．会員ＩＤ  ＝  Ｃ．会員ＩＤ
ＷＨＥＲＥ  Ｓ．売上年月  ＝  ”２０２２０７”  ＡＮＤ  Ｃ．性別  ＝  ”Ｍ”  ＡＮＤ  Ｃ．年代  ＝  ３０；

・案Ｂ
－－２テーブルを条件抽出した一時テーブルを作成し、結合
ＷＩＴＨ  ＴＥＭＰ＿Ｓ  ＡＳ  （ＳＥＬＥＣＴ  ＊  ＦＲＯＭ  売上履歴  Ｓ  ＷＨＥＲＥ  Ｓ．売上年月  ＝  ”２０２２０７”），
          ＴＥＭＰ＿Ｃ  ＡＳ  （ＳＥＬＥＣＴ  ＊  ＦＲＯＭ  顧客情報  Ｃ  ＷＨＥＲＥ  Ｃ．性別  ＝  ”男”  ＡＮＤ  Ｃ．年代  ＝  ３０）
ＳＥＬＥＣＴ  ＊  ＦＲＯＭ  ＴＥＭＰ＿Ｓ  ＩＮＮＥＲ  ＪＯＩＮ  ＴＥＭＰ＿Ｃ  ＯＮ  ＴＥＭＰ＿Ｓ．会員ＩＤ  ＝  ＴＥＭＰ＿Ｃ．会員ＩＤ；

■問題
以下の文章の空欄[ a ]～[ i ]にあてはまる語句を答えよ。

****************
２案を比較する際のポイントは、JOINとWHEREの順序である。
JOINは計算コストが高い処理であるため、結合前のレコード数は[ a ]い方が良い。
よって、今回は案[ b ]を採用することとする。

また、各テーブルのレコード数がかなり多いため、個別のSQL文の最適化だけでなく
テーブルそのものの構造も見直すことにした。

現在、各テーブルには索引を設定していないため、探索方法はすべて[ c ]探索となっている。
そのため、仮に「顧客情報」テーブルの「性別」列に索引を設定するとして
列値の「男」「女」それぞれに均等に行が存在するとすると
WHERE 性別 = "男"  では[ d ]行だけ索引探索することとなる。

ただし、むやみに索引を設定すると
テーブルの[ e ]のたびに索引の再構成がなされて処理時間が長くなる。
また、列値の種類が[ f ]い方が索引探索で絞り込みが有効に働き、探索行数を減らせる。
今回は結合キーとして使用頻度が高く列値の多い「会員ID」列に索引を設定する。

さらに、データ更新のタイミングなどで適切なアクセスパスが選択されない懸念がある。
今後、実行計画を確認して、オプティマイザが適切なアクセスパスを選択していない場合
明示的に[ g ]の更新をすると改善する可能性がある。

最後に、物理分割（パーティション化）を検討する。
分析クエリを始め、業務で発行するSQL文は月別の集計で使用するものが多いため
売上履歴テーブルの[ h ]列を区分キーとして分割すると良い。
売上履歴テーブルは過去100ヶ月分の履歴を保持しており、各月のレコード数が均等であるとすると
WHERE 売上年月 = "202207"  では1区分の[ i ]行を探索することになる。
索引探索が使用できるSQL文であれば、探索行数はさらに少なくなり、パフォーマンス向上につながると見込める。
****************

強引にまとめたところとかデスぺ過去問っぽくない表現とかありますが
文脈で察していただければ・・・( ´∀｀ )
僕は実務経験も長くないので
「あれ、この人ここ勘違いしてそうだな～」
というところとかあれば、ぜひご指摘ください。

・・・そもそもデスぺで物理設計を選択する人って少ないですかね。

↑SQL文は全角に変換して投稿しました。
半角のままだと何故だかエラーで投稿できなかったため・・・。

たぶん、大文字セレクト句がインジェクション対策でひっかかるんですよね。
応用のサイトだと小文字なら許されてましたけど、ここはselectなんとかいくのかな

HTTPリクエスト中にSQL文があるとWAFによりアクセスが弾かれてしまうことがあります。利便性は落ちますが、必要なセキュリティ対策ですのでご理解をお願いいたします。

SQLインジェクション…テストに出るとこだ
‘;select *

管理人さん、GinSanaさん
ありがとうございます、セキュリティ対策ですね。
SQLインジェクションはちょっと考えましたが、これまでもデスぺだと結構SQL文の投稿多いんじゃなかろうかと思ってました。
全角だと回避できるのが分かりましたので、予約語の先頭を全角にしてみるとかで工夫します！

想定していた解答と補足も投稿しておきます。
[ a ]少な
[ b ]Ｂ
  条件で絞り込みをした上で結合するのがセオリーです。
[ c ]表
  実務ではフルスキャンと言ったりします（現場による？）
[ d ]500万
  インデックスにより行数の絞り込みが行われ、探索する行数を減らせます
[ e ]更新
  代表的なインデックスの弱点です。検索には強いが更新に弱い。
[ f ]多
  インデックスを設定する列は一般に以下の列が選ばれます
  ・列値の種類が多い
  ・種類による偏りがない（デスぺでは原則、列値の種類で均一に行が分布する仮定になってます）
[ g ]統計情報
  テーブルの統計情報をもとにアクセスパスが選択されるので
  最新の統計情報にすることで解決することもあります（過去問でも見覚えが）。
[ h ]売上年月
  月ごとに分割すれば、探索する区分を1区分だけにできます。
  ただし、月次でINSERT処理を行う場合は処理が1区分に偏って性能が低下する懸念があります。
  ※令和3年の午後1問2クレジットカード会社のオーソリ処理でまんま出てます。
[ i ]1億




ご参考になる方がいれば幸いです。
ご指摘あればお願いします。

WITH句は一時表を定義するものじゃないです。
SQLを見やすくする効果くらいしかないです。

まあ、一時表というよりはCTE（共通テーブル式）ですね。
サブクエリで書いた場合に同じ問い合わせが複数回発生するなら効果はありますが、まあこの程度ならないようなもんです。
あとは処理のどこでメモリを確保するか（withは順にメモリに蓄積し、サブクエリは呼ばれるまでは蓄積しない）でしかwith（orサブクエリ）のパフォーマンスは差異はないですね。

あら・・・
メモリに貯めるから多少効果あるかと思ってました。
ちゃんとやるならやはりCREATE TABLEで一時テーブルを作る、ですね。
ありがとうございます。

一時テーブル使ったら早くなるんですか？
詳しく説明できる方いますか？

同じクエリを何度も呼び出す場合、一時的な中間テーブルを作成してそれを呼び出すようにすると高速化が期待できます。

数百行単位の長大なサブクエリ１回で結果を返すと、何度もクエリの計算が走ることになりますが
同じクエリを何度も呼び出している部分をＣＲＥＡＴＥ  ＴＡＢＬＥで中間テーブルにすることで、クエリの結果だけ利用できます（可読性も上がります）。

今回、自作問題の例ではＷＩＴＨを使ったのでテーブルではなく一時的なビューになっています。
例が単純な結合なのでパフォーマンスの差はあまりないと思いますが・・・。
イメージとしては
サブクエリ：都度サブクエリの計算が走るので遅い
ＷＩＴＨ句：クエリの結果をメモリにためこむのでサブクエリよりは速い
  ※メモリが圧迫され、メモリが尽きたらディスクを使用
中間テーブル：クエリの結果を直接保持、参照するので速い
です。

おかしな点があればどなたかご指摘ください。m(_ _)m

繰り返し呼ばれてないですよね？？？

本問ではサブクエリのように何度も同じクエリを呼び出してないので
先に述べた「サブクエリvsWITH句vs一時テーブル」のパフォーマンス差はあまりないかと思います。

大規模なテーブル同士を全レコード結合したうえで条件を絞り込むより
結合前にレコードを減らしておく方がいいよ、ということを問いたい問題でした。