パフォチュー・コンサル日記 その１
パフォチュー・コンサル日記 その１
きっちんイーター
さて、今回からはパフォチューに行ったときに感じたシステムの実態に関して
少し書いてみようと思ってます。
題して”インデックスの実態”とでもしましょう。パフォチューと書くとかなり
高度な技術を使って．．．と思われるかも知れませんが、多くのパフォチュー
はSQL文の問題を解決する事にあるといってもいいでしょう。
では、”インデックスの実態”をお話しましょう。
インデックスの目的はメルマガを読まれている人には当たり前だとは思います
が一応書いておきます。
その壱 データの一意性を確保する為の使用(主キーですね) その弐 検索のパフォーマンスを向上する為の使用
実態その壱 “インデックスは主キーだけ”
多くのケースで見られますが、インデックスが無いんです。主キーだけなので
す。それなのにいろいろな項目を条件として検索をしているんです。いろいろ
な項目を結合条件としてで JOIN しているんです。全部が全部、全件検索にな
っているのです。こんなケースは山ほどあります。
主キーは比較的、複合キーのケースが多く、しかも、複合キーの順番が組織的
な場合が非常に多いです。それなのに第一キーを WHERE句 に入れない SELECT
が圧倒的に多いです。そうです、検索用のインデックスとして利用されないん
です。データをINSERT,DELETE するときの一意性確保だけの機能になっている
のです。
例をあげてみましょう。あるトランザクション・データがあります。
SQL> CREATE TABLE 売上計画 (部 VARCHAR2(10),課 VARCHAR2(10) 2 ,担当者 VARCHAR2(20),月 VARCHAR2(2),売上計画金額 NUMBER); SQL> ALTER TABLE 売上計画 ADD PRIMARY KEY(部,課,担当者,月);
ある担当者の5月の計画が見たいといった様な検索をしてみます。
SQL> SELECT 担当者,月,売上計画金額 from 売上計画 WHERE 担当者='私' and 2 月='05';
SQLの文法的にはOKです。
そう、こんなSQLがいろいろな会社で、いろいろなシステムでゴロゴロ転がっ
ています。当然、このSELECT文はインデックスを使用しません。
全件検索ですよ全件！！！担当者(営業マン)が500人いて、1年間だとデータ件
数は6000件です。そのうち1件を持ってくるだけなのに毎回6000件の内部読込
を行っているんです。まぁ～6000件くらいの全件検索ならいいじゃないかと思
うかもしれませんが、実際は製品・顧客などの分類も入ると10倍くらいのデー
タ件数になったります。
と、なると60000件からたった1件をSELECTする為に毎回全件検索をしている事
になるんです。
じゃ～どうすればいいんだなんて声が聞こえました。秘策をお教えしましょう。
一番WHERE句に使用される項目を第一キーにして主キーを作り直せばいいので
す。一意性を保つのにキーの順番は関係ないんですから。
上の例ですと、このテーブルを検索する場合、ほとんどが担当者をWHERE句の
条件に入れていると仮定しましょう。そんな場合は、
制約名取得
SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = '売上計画'; INDEX_NAME ------------------------------ SYS_C00906
制約をDROP
SQL> ALTER TABLE 売上計画 DROP CONSTRAINT SYS_C00906; 表が変更されました。
制約作り直し
SQL> ALTER TABLE 売上計画 ADD PRIMARY KEY(担当者,月,部,課); 表が変更されました。
こうすれば、主キーも検索用のインデックスに早代わりです。上のSELECTもめ
でたくインデックスを利用してくれます。
担当者と月をWHERE句で指定しているので内部的に読み込むのは1件(ブロック
やインデックスの読込はここでは無視して書いています。細かいことは忘れま
しょう)だけになります。かたや60000件、この差は大きいですよ。
こまでは勝手に書いてきましたが上の例をちゃんと検証しましょう。データは
6000件です。
SQL> CREATE TABLE SALES_PLAN (DEPT NUMBER(2),SECT NUMBER(2), 2 EMP NUMBER(5),MONT NUMBER(2),SLPLN NUMBER(10));
でテーブルを作成し、以下の2パターンで主キーの付け方を変えて実行計画を
取ります。
SQL> ALTER TABLE SALES_PLAN ADD PRIMARY KEY (DEPT,SECT,EMP,MONT);
SQL> SELECT EMP,MONT,SLPLN FROM SALES_PLAN WHERE EMP=10002
2 AND MONT = 5;
SQL> ALTER TABLE SALES_PLAN ADD PRIMARY KEY (EMP,MONT,DEPT,SECT);
SQL> SELECT EMP,MONT,SLPLN FROM SALES_PLAN WHERE EMP=10002
2 AND MONT = 5;
EMP MONT SLPLN
---------- ---------- ----------
10002 5 1600000
実行計画 (TEST)
PRIMARY KEY (DEPT,SECT,EMP,MONT) PRIMARY KEY (EMP,MONT,DEPT,SECT)
-----------------------------------------------------------------
(0)SELECT STATEMENT (0)SELECT STATEMENT
(1)--テーブル SALES_PLAN (1)--ROWIDでテーブル SALの全表走査
を実行(FULL) SALES_PLAN
をアクセス(BY INDEX ROWID)
(2)----UNIQUE索引 SYS_C004673(複数行)
を使用(RANGE SCAN)
計測結果
-----------------------------------------------------------------
実行時間 (秒) 0.07 実行時間 (秒) 0.01
ユーザCPU時間 (秒) 0 ユーザCPU時間 (秒) 0
システムCPU時間 (秒) 0 システムCPU時間 (秒) 0
論理リード 48 論理リード 18
DB Block Gets (block) 4 DB Block Gets (block) 0
Consistent Gets (block) 44 Consistent Gets (block) 18
物理リード (block) 20 物理リード (block) 1
物理ライト (block) 0 物理ライト (block) 0
REDOサイズ (byte) 600 REDOサイズ (byte) 0
取得行数 6000 取得行数 0
取得ブロック数 20 取得ブロック数 0
PARSE数 5 PARSE数 5
といった結果になりました。テストでは1ブロックにかなりの行数が格納され
ているのであまりブロック取得数に差がでませんが、1ブロックに数行の場合
だと、ものすごく差が出ます。
おっと！注意点を忘れていました。一番WHERE句に使う項目って書きましたが、
カーディナリティーが低いのは駄目ですよ。カーディナリティーってなんだっ
て声も聞こえました。その説明は 実態その弐 でご説明いたします。
次回は、実態その弐”カーディナリティーが低いインデックス”に関して説明し
ます。
以上、今回のパフォチューコンサル日記はきっちんイーターがお届けしました。