カテゴリー別アーカイブ: インデックス

ビットマップインデックス(Oracle)

ITコーディネータのシュウです。

1395797133260_2

今月27日に再び発生した大規模なサイバー攻撃によって、世界各地で被害が出ています。今回のランサムウェアは専門家の間で「Petya」と呼ばれるウイルスが使われたとみられているそうです。WannaCryと同様に、WindowsのSMB(Server Message Block)の脆弱性(通称Eternal Blue)を利用しているとのことで、対策としては、Windowsを常に最新の状態にすること、アンチウィルスソフトウェアを最新のものにしておくこと、さらにメールにあるURLをクリックする際には十分な注意が必要ですね。また、いざというときにバックアップを取ることも推奨されています。
IPA独立行政法人情報処理推進機構セキュリティセンターが出している「情報セキュリティ10大脅威 2017」でも、個人向け、組織向けともに、ランサムウェアによる被害が2位に入っていますね。組織向けの1位は、こちらもよく話題になる標的型攻撃による情報流出です。様々な情報を扱う企業としては、社員への教育を含め様々なリスクを考慮した対策が必要ですね。
本当に、いつも危険と隣り合わせていることを自覚しながらPCを使わないといけない時代になってきたと感じます。

さて、dbSheetClientに新しい事例がまたアップされています。
dbSheetClientで内製化を実現、MS-Accessで開発した『総合管理システム』をdbSheetClientでWeb化し、抱えていた課題を全てクリア !!ということで、綜合エナジー株式会社様の事例です。
興味のある方は以下をご参照ください。
http://www.newcom07.jp/dbsheetclient/usrvoice/sogo_energy.html

<本日の題材>
ビットマップインデックス(Oracle

Oracleで検索の性能を向上するためにインデックスを作成しますが、データウェアハウスなどを構築した際には、カーディナリティの値が低い(取りうる値が限られている)列に対しては、ビットマップインデックスを作成することで効果があるという話しを聞くことがよくありますが、今回、それを試してみたいと思います。

例)
性別、血液型などのような取りうる値が限られている列を持つ顧客マスタを作成し、そこに300万件の顧客データをテスト的に作成してみたいと思います。前回題材に上げたバルク処理を使って、データの登録処理時間を短縮したいと思います。

まず、顧客マスタテーブルを作成します。

CREATE TABLE t_customer(
 c_id number(7)
,c_gender  varchar2(4)          -- 性別
,c_blood_type varchar2(2)  -- 血液型
,c_age     number(3)                  -- 年齢
,constraint PK_t_customer Primary key (c_id));

次に、前回題材として取り上げたバルク処理を使って、テストデータを作成してみます。

 DECLARE
  TYPE c_id_t IS TABLE OF t_customer.c_id%TYPE
  INDEX BY PLS_INTEGER;
  c_id c_id_t;

  TYPE c_gender_t IS TABLE OF t_customer.c_gender%TYPE
  INDEX BY PLS_INTEGER;
  c_gender c_gender_t;

  TYPE c_blood_type_t IS TABLE OF t_customer.c_blood_type%TYPE
  INDEX BY PLS_INTEGER;
  c_blood_type c_blood_type_t;

  TYPE c_age_t IS TABLE OF t_customer.c_age%TYPE
  INDEX BY PLS_INTEGER;
  c_age c_age_t;

BEGIN
 FOR i IN  1..3000000  LOOP
   c_id(i) := i;
   c_gender(i) := CASE mod(c_id(i), 3) WHEN 0 THEN '男性' WHEN 1 THEN '女性' ELSE null END;
   c_blood_type (i) := CASE mod(c_id(i), 5) WHEN 0 THEN 'A' WHEN 1 THEN 'B' WHEN 2 THEN 'O' WHEN 3 THEN 'AB' ELSE null END;
   c_age(i) := 20+mod(c_id(i), 50);
 END LOOP;

 FORALL j IN 1..3000000
   INSERT INTO t_customer(c_id, c_gender, c_blood_type, c_age) VALUES(c_id(j), c_gender(j), c_blood_type(j), c_age(j));
END;
/

今回も、処理時間を測定したいので、sqlplus 上で以下を実行してから、上記の処理を実行します。
SET TIMING ON
上記のpl/sqlを実行します。

blog80_buld

24.89秒で処理が終了しています。これを、通常のLOOP処理で実行すると、3分22秒程度時間がかかりましたので、やはりバルク処理が有効だということを改めて確認しました。

データを確認してみます。

SELECT * FROM t_customer
ORDER BY c_id;

blog80_select

件数も確認してみます。
Select count(*) from t_customer;

blog80_count

データが確かに300万件登録されているのが確認できます。

この t_customer テーブルのデータの中に、男性以外(NULLデータも含む)で、血液型が「A」型と「B」型の人が何人いるかを抽出してみます。そのときも実行計画も合わせて表示させてみます。

set autotrace on

select count(*) from t_customer
where c_gender <> '男性'
 and c_blood_type in ('A','B');

blog80_jiko1

実行計画を見ると、t_customerテーブルを「TABLE ACCESS FULL」で全レコードにアクセス(フルスキャン)して該当レコードの件数を確認していることがわかります。

この t_customer テーブルの性別、血液型項目に通常のインデックスを作成してみます。

CREATE INDEX cust_idx2 ON t_customer (c_gender);
CREATE INDEX cust_idx3 ON t_customer (c_age);

blog80_btree_ind

それぞれ、7.2秒、5.98秒と、多少時間がかかることがわかります。

この状態で、再度先ほどの男性以外で、血液型が「A」型と「B」型の人が何人いるかを、同じSQLで実行してみます。

blog80_jiko2

実行計画は、先ほどのインデックスがないときと同じで、特に追加したインデックスを使用しない動作になっています。
カーディナリティの値が小さい列へのBtreeのインデックスは作成してもあまり効果がないとも言われます(実行するSQLにも当然依ります)が、DBがフルスキャンを選択したことは今回のケースではBtreeインデックスは有効ではないことをがわかります。

今度は、先ほどのインデックスを削除し、性別、血液型項目にビットマップインデックスを作成してみます。

CREATE BITMAP INDEX cust_bm_idx2 ON t_customer (c_gender);
CREATE BITMAP INDEX cust_bm_idx3 ON t_customer (c_blood_type);

blog80_bitmap_ind

ビットマップインデックスの作成時間はかなり短いですね。

この状態で、再度先ほどの男性以外で、血液型が「A」型と「B」型の人が何人いるかを、同じSQLで実行してみます。

blog80_jiko3

今度は、作成したビットマップインデックスを使用した実行計画になり、処理の実行時間も0.17秒とかなり短縮されることがわかります。このように、データにnullが含まれている場合でも、カーディナリティが低い列であれば、ビットマップインデックスを作成することで、抽出処理が高速化することが確認できると思います。
※ただし、ビットマップインデックスを作成した状態での、データの登録や更新は、b-treeのインデックスがある場合に比べてかなり遅くなることも事実であり、データウェアハウスなどの抽出が中心のデータベースの場合などに絞ったほうがよいようです。
 
今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

ファンクションインデックス

ITコーディネータのシュウです。

082

コスモスがとてもきれいだったので写真に撮りました。ここしばらく、仕事のほうが忙しく、なかなかブログを作成できなかったのですが、久しぶりの投稿です。

さて、実りの秋、食欲の秋、スポーツの秋、読書の秋、いろいろな言葉で表現される秋もかなり深まってきました。
私も先月は、妻に引っ張られながら、なまった体に鞭打って運動会にも何とか頑張って参加しました。子供の学校においては、高校では文化祭、中学では合唱のクラス対抗発表会などもあり、たまには父親らしいことをしようと、子供たちの姿を見に行って来ました。
プロ野球は日本シリーズが終わり、今はプレミア12が始まっています。日本は3連勝で頑張っていますね。体操の世界選手権では日本が37年ぶりの金メダル、そして、内村航平選手が、前人未到の個人総合6連覇! いやあ、挙げ出すといろいろありますね。そしてみんな頑張っているんですよね。私も、ちょっと疲れているけど、頑張るぞ!
...と気合は入れてみたんですが、なかなか力が出ないのも事実。今日は久しぶりに早く帰ろうかな~。

<本日の題材>
ファンクションインデックス

いろいろなシステムを担当すると、あるデータを抽出しなければならないときに、テーブル同士のジョインに、既存のキーとなる項目をそのまま使用することができず、関数を使用して項目を加工したかたちで条件を設定しなければならない場合に時折遭遇します。
そんなとき、データ件数が多い場合には、インデックスをうまく使えないために処理時間がかなりかかってしまい、問題になることがあります。

最近の開発案件でもそういうケースがあり、どうしたらよいかを検討したところ、ORACLEの機能にファンクションインデックスというものがあり、それを使うことで処理時間を短縮することができました。
今日は、それを取り上げてみたいと思います。

実際に行ったケースはちょっと複雑だったため、簡単な例で試してみたいと思います。

テーブル「TAB_C」、テーブル「TAB_D」があり、定義は以下のようだとします。

CREATE TABLE TAB_C(
 C_CODE_1   VARCHAR2(20)
,C_数量       NUMBER(12)
,CONSTRAINT PK_TAB_C PRIMARY KEY (C_CODE_1));

CREATE TABLE TAB_D(
 D_CODE_1   VARCHAR2(20)
,D_数量       NUMBER(12)
,CONSTRAINT PK_TAB_D PRIMARY KEY (D_CODE_1));

データを以下のように作成します。
TAB_Cの「C_CODE_1」は、最初の文字が「C」で後は1からの連番、TAB_Dの「D_CODE_1」は、最初の文字が「D」で後は1からの連番とします。また、C_数量、D_数量については、1~1000000 の間のランダムな整数を設定することにします。

DECLARE
  v_count NUMBER := 0;
  v_ccode VARCHAR2(20) := ' ';
  v_dcode VARCHAR2(20) := ' ';
BEGIN
  WHILE v_count < 1000000 LOOP
    v_count := v_count + 1;
    v_ccode := 'C'||CAST(v_count AS VARCHAR2);
    v_dcode := 'D'||CAST(v_count AS VARCHAR2);

    INSERT INTO TAB_C(C_CODE_1, C_数量)VALUES
  (v_ccode, FLOOR(DBMS_RANDOM.VALUE(1, 1000001)));
   INSERT INTO TAB_D(D_CODE_1, D_数量)VALUES
  (v_dcode, FLOOR(DBMS_RANDOM.VALUE(1, 1000001)));
END LOOP;
END;
/

※DBMS_RANDOM.VALUEは乱数を取得するのに使えます。

データの作成結果を確認してみます。
SELECT * FROM TAB_C
ORDER BY CAST(SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1) AS NUMBER);
TAB_C結果1

SELECT * FROM TAB_D
ORDER BY CAST(SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1) AS NUMBER);
TAB_D結果1

TAB_C、TAB_Dとも1000000件作成されていて、数量はランダムな整数(1~1000000の間)になっているのが確認できます。

この2つのテーブルは、それぞれのテーブルの主キーである「C_CODE_1」「D_CODE_1」の2桁目以下の値でジョインすることで、1対1のデータを抽出できます。

このときのジョインの条件は、例えば以下のようになります。
SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1) = SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1)

この場合、C_CODE_1、C_CODE_2は、それぞれのテーブルのプライマリーキーであったとしても、SUBSTRやLENGTHという関数を使っているためにうまくインデックスを使った検索をしてくれない(全件検索になる)ので、テーブル件数が多い場合には、処理時間が非常にかかってしまいます。

実際に、ジョインした結果を抽出してみます。
(条件として、キーの2桁目以降が700000~710000のものに絞っています)

SELECT
SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1), SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1)
  FROM TAB_C C
  JOIN TAB_D D
    ON SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1) = SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1)
 WHERE SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1) BETWEEN 700000 AND 710000
 ORDER BY SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1);

ファンクションインデックス作成前2

実際に実行計画を取得してみると、
------------------------ 実行計画 --------------------------
SELECT STATEMENT   Cost = 126399
    SORT ORDER BY 
        HASH JOIN  
            TABLE ACCESS FULL TAB_C
            TABLE ACCESS FULL TAB_D
-----------------------------------------------------------------
「TAB_C」「TAB_D」とも「TABLE ACCESS FULL」となっていて、フルスキャンしていることがわかります。
 
そこで、ファンクションインデックスを作成してみます。
CREATE  INDEX  IX_TAB_C_FUNC  ON  TAB_C
(SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1));
 
CREATE  INDEX  IX_TAB_D_FUNC  ON  TAB_D
 (SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1));

ファンクションインデックスは、索引自体と索引が定義される表が分析されるまで、使用されないということなので、分析します。

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'BLOG_TEST', TABNAME => 'TAB_C');
ファンクションインデックス作成1

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'BLOG_TEST', TABNAME => 'TAB_D');
ファンクションインデックス作成2

この状態で、再度、先ほどのSQLの実行計画を取得してみます。

------------------------ 実行計画 --------------------------
SELECT STATEMENT   Cost = 1445
    SORT ORDER BY 
        HASH JOIN  
            INDEX FAST FULL SCAN IX_TAB_C_FUNC
            INDEX FAST FULL SCAN IX_TAB_D_FUNC
-----------------------------------------------------------------

すると、確かに作成したファンクションインデックス「IX_TAB_C_FUNC」「IX_TAB_D_FUNC」が利用されていることが確認できますし、COSTもかなり小さな値になっています。
実際の抽出結果は、

ファンクションインデックス作成後

処理自体、先ほどよりは早く結果が返って来ました。
こういうケースで、ファンクションインデックスを作成することは、レスポンス改善としては効果があることが分かります。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村