┏┏┏┏┏━━━━━━━━━━━━━━━━━━━━━━━━…・・ ┏━
┏┏┏┏┛ 2002.07.10 ┏┛┛
┏┏┏┛ ☆おら！オラ！Oracle -どっぷり検証生活-★ ┏┛┛┛
┏┏┛ ┏┛┛┛┛
┏┛・・…━━━━━━━━━━━━━━━━Vol.112━…・・┏┛┛┛┛┛
◇目次◇
■Oracle検証生活・・・パフォチュー・コンサル日記 その２
■Ｑ＆Ａコーナー
■お知らせ・・・○ＱＡについて
■編集者より
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
■□注意事項□■
本文中にテーブルが含まれていますので、お読みになる際はMSゴシッ
ク等、等幅フォントをお使いただくことをお勧めします。
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 パフォチュー・コンサル日記 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
パフォチュー・コンサル日記 その２
前回は、実態その壱 として"インデックスは主キーだけ"を書きました。その
最後にも少し触れていましたが今回は、実態その弐 "カーディナリティが低い
インデックス"をお送りいたしましょう。
まずはカーディナリティとは何か？日本語にすると"集合の要素の数・基数"と
いうことになります。実際の意味合いを簡単にいってしまえば、どれだけのキー
の種類があるか、キーの偏りはないかといったことです。キーの種類が多い場
合を"カーディナリティが高い"、キーの種類が少ない場合を"カーディナリテ
ィが低い"と表現します。
問題:社員マスターの社員コードはどっち？
社員コードは普通、全て一意性があるので"カーディナリティが高い"です。
では、社員マスターの性別はどっち？
簡単すぎますね。男と女だけなんで当然の如く"カーディナリティが低い"です。
ではちょっと捻って質問です。上の社員マスターを持つ企業は日本の企業です。
しかも日本だけにあります。このとき、社員マスターにある国籍のカーディナ
リティはどっち？
この場合、国はたくさんあります(ソルトレーク・オリンピックに参加した国
と地域で確か80位はあったと思います)。しかし、国籍はほとんど日本になる
と思います。このようなケースでは種類がたくさんあっても、偏りがひどいの
で"カーディナリティが低い"です。
さて、"カーディナリティが低いインデックス"の本題に入りましょう。SQLを
作成して動作状況(アクセスパス)をチェックすると思います。アクセスパスを
チェックすることによってそのSQLがインデックスを使用して正しく(最適な)
稼動しているかを見るのがチューニングの一つです。以下に実例を示します。
SQL文:DELETE FROM TABLE_A
WHERE COL1=10 AND COL2='A' AND COL3<>'Y' AND COL4='NO0121'
アクセスパス:(0)DELETE STATEMENT (Cost=4)
(1)-- TABLE_A から削除(DELETE)
(2)----UNIQUE索引 TABLE_A_IDX(複数行)を使用(RANGE SCAN)
(Cost=4 Card=1 Bytes=81 )
INDEX:TABLE_A_IDX ON TABLE_A(COL1,COL2,COL3,COL4)
といったSQL・アクセスパス・INDEXがあるとします。ぱっと見た感じはユニー
クインデックスを使用して削除処理を行っているので非常にいいSQLが作成で
きた様に見えます。でもこれだけの情報で最適なSQLができたことにはならな
いのです。実際のところこのケースでは数行を削除するのに700ブロックも読
み込んでいたのです。では何がいけないのでしょう？
SQLのパフォーマンスを向上させる為に一番重要なことは何かを考えてください。
そうです、いかにI/Oを少なくして求める行(レコード)を取得できるかです。
SQLは同じ結果を取得する場合でも色々な書き方ができます(これがSQLの利点
でもあり欠点でもあるのです)。データベース・エンジンの内部で1行を返すた
めに全件検索をしている場合もあれば、数件(正確には数ブロック)で1行を返
す場合もあります。
SQLのチューニングのチェックでは何を見ればいいのかです。上に挙げた情報
は必ず見る必要があります。それに加えてそのSQLがどれだけのDBブロックを
読み込んだかに注目する必要があるのです。
上の例では700ブロックも読み込んで数行を削除しています。どう考えても変
ですよね。では、なにが変なんでしょう。上の情報から疑わしい点を見つけま
しょう。
疑わしいのがどこか判りますでしょうか？答えは取得結果が複数行である事で
す。インデックスを複数行読んで処理をしていますよね。複数行の定義は最小
で2行、最大で全件の範囲になることに気づいて頂けましたでしょうか。そう
です、上のSQLはインデックスを限りなく全件に近い複数行を読み込んでいる
のです。RANGE SCANは曲者です。
では、なぜこのようなことが起こるのでしょうか。やっと本題に入ります。上
のSQLでは実際COL3を <> で条件指定しています。つまりインデックス TABLE_A_IDEX
はCOL2まででRANGE SCANを行っているのです。そしてCOL2までは非常にカーデ
ィナリティが低いインデックスでした。(実際はCOL2までで2種類でした・・・
つまり33%はインデックスを読んでいるのです)
また、SQLを見ていただければ判ると思いますが COL3='Y' が多分 'Y' か 'N'
がCOL3には設定されそうです。COL4は番号のように見えますので種類は多そう
です。そうです、このインデックスはCOL4によってカーディナリティが高まっ
ているのです。だからCOL4までを正しく条件設定して有効なインデックスとな
るのです。(COL1からCOL3は=条件で設定しなければ意味の無い状態です)
逆にいえば、COL4まで条件設定されてしまい、SQLでこの TABLE_A_IDX が使用
されたらパフォーマンスに負荷をかけてしまうことになるのです。
ではカーディナリティの見方です。とっても簡単です。
SQLに注目した場合(RANGE SCANしている)、使用されているインデックスで条件
に指定されている(インデックスの利用できる書き方をしていることが絶対条件
です)項目でチェックします。とりあえず上の SQL で例を書きます。
SQL>SELECT COUNT(*) from TABLE_A;
(全件件数を確認)
SQL>SELRCT COL1,COL2,COUNT(*) FROM TABLE_A GROUP BY COL1,COL2;
(キー種類数を確認)
このSQLでカーディナリティをチェックします。
キー種類数 / 全件件数 = カーディナリティ
この時の結果が30%以上のような場合は、意味の無い(負荷の低減どころか、負
荷を増加させている)インデックス利用といったことになります。
以上、今回もパフォチューコンサル日記はきっちんイーターがお届けしました。
◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
一緒に検証してみませんか？
http://www.insight-tec.com/jp/html/company/career.html
◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 Ｑ＆Ａコーナー 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
＜Ｑ：読者さんからの質問 その１＞
私の認識と違う事が記載されていましたので、ご質問致します。
>主キーは比較的、複合キーのケースが多く、しかも、複合キーの順番が組織
>的な場合が非常に多いです。それなのに第一キーを WHERE句 に入れない SELECT
>が圧倒的に多いです。そうです、検索用のインデックスとして利用されない
>んです。データをINSERT,DELETE するときの一意性確保だけの機能になって
>いるのです。
上記は「オプティマイザがルールベース」という条件が付きませんか？「コス
トベース」であれば、INDEXを使用していたと理解していたのですが．．．
＜Ａ：きっちんイーター＞
お答えいたします。複合インデックスの場合、キーのならびに重要なポイント
があります。OracleのインデックスはB-TREE(Balanced-Tree)で作成されてい
ます。bitmapは別ですが、つまり複合されたキーを直列に並べソートされてい
ます。(直列状態で小さい順に並んでいると考えてください)
例をあげます。コンサル日記での複合キーですと、
部 課 担当者 月
01 01 000005 01
01 01 000005 02
01 02 000001 05
01 05 000003 12
02 01 000022 03
02 08 000015 06 のように並んでいます。
この時、部・課を指定しないで担当者、月だけで考えるとソートされていない
状態です。
(順番がばらばらです)つまり、全件を検索しないと担当者・月だけではデータ
がどこにあるかがわからないのです。例えば担当者:000001・月:05はこの２つ
で見れば先頭に並んでいるはずですが、実際はその前に部:01 課:02があるた
め先頭ではありません。
これはコストベース・ルールベースで区別はありません。オプティマイザーは
どのインデックスが最適か、インデックスを使用するべきかの判断をしてくれ
ますが、複合キーの先頭が条件句に書かれていない時点で、その複合インデッ
クスは存在していない状態と判断しています。(実際にはオプティマイザーは
ブラックボックスですので詳細はわかりませんが)
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 ＱＡについて 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
＜皆様からのＱＡを受付けております＞
皆様のＱＡにはできるだけ、お答えしたいと思っています。
すべてのＱＡにお答えすることはできないかもしれませんが、
適宜メルマガ内でとりあげていく予定ですので、是非ＱＡをお寄せください。
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 編集者より 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
日曜日あたりからすごい暑い毎日ですよね。天気が良いのは嬉しいのですが
湿気でベトベトなのがすごく嫌いなんですよね。しかも、台風も接近してき
ているみたいですし・・。なおさら湿気が、と思うと気が重くなる今日この
ごろです。この湿気と上手く付き合うにはどうしたら良いのでしょうか？悩
み相談室っぽくなってしまいましたが、夏のベタベタ対策など知っていまし
たら是非、是非、教えてくださいっ！ by TI
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
登録・解除は以下のURLで行うことができます。
http://www.insight-tec.com/jp/html/ora3/ora3.html
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜おら！オラ！Oracle−どっぷり検証生活−＞
発行／編集：株式会社インサイトテクノロジー
http://www.insight-tec.com
マガジンID：0000030093
本メールマガジンに掲載された記事を許可なく転載することを禁じます。
Copyright (c) 1996-2002, Insight Technology, Inc. All rights reserved.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Insight Technology, Inc
Japanese | English