トップページ -> 研究道具箱と入門演習 -> データベース特論・支援 Web ページ -> 二次索引 (secondary index)
[サイトマップへ]   [全文検索へ]   [統計情報へ]   [掲示板へ]  

二次索引 (secondary index)

URL: http://www.db.is.kyushu-u.ac.jp/rinkou/addb/7.html










演習

演習で行うこと


SQLite の SQL 演習に関連する部分

SQLite の SQL の説明は http://www.hwaci.com/sw/sqlite/lang.html (English Web Page) にある.


郵便番号データベース (Japanese ZIP code database)

郵便番号データベースは zips, kens, shichosons の 3 つのテーブルから構成される.


Sqliteman で既存のデータベースを開く (Open an existing database using Sqliteman)

以下の手順で,既存のデータベース「C:\SQLite\mydb」を開く. (Open an existing database 'C:\SQlite\mydb')

  1. ディレクトリ C:\SQLite を使う

    ここに データベースファイルを置くことにする.

  2. File」→ 「Open

  3. データベースファイル C:\SQLite\mydb を選び, 「開く」をクリック (Click '開く' after choosing the database file "C:\SQLite\mydb")

  4. データベースの中身が表示されるので確認する (Database appears)


Sqliteman を用いたデータのブラウズ (Browse Data using Sqliteman)


Sqliteman を用いた二次索引の追加 (generate a secondary index using Sqliteman)

ここでは,テーブル zips の属性 jiscodeの二次索引と, zipcodeの二次索引を作る. (Generate a secondary index of the table 'zips')
  1. CREATE INDEX を用いた二次索引の生成 (Generate a secondary index using 'CREATE INDEX')

     
    CREATE INDEX idx1 ON zips( jiscode );
    CREATE INDEX idx2 ON zips( zipcode );
    

    「idx1」と「idx2」は索引名である.索引の管理(索引の削除など)に使用される. ('idx1' and 'idx2' are index name).


    索引は数秒以内で生成される.(The secondary index will be generated in a several seconds)

  2. Sqliteman での二次索引の確認


二次索引による問い合わせ計画の変化 (secondary index and query plan)

  1. データベースの構造の確認 (Database Structure)

    sqlite_master をクリック (Click 'sqlite_master)

    テーブルと二次索引のルート・ページ番号が分かる (Root page number of each table and secondary index)

    二次索引 idx1 テーブルのルート・ページ番号(ここでは「12379」)と idx2 テーブルのルート・ページ番号(ここでは「13855」) を確認しておく.ルート・ページ番号は,データベース管理システムが決める値なので,違う値になっているはずである.

    (Inspect the root page number of the secondary index 'idx1' and 'idx2'. The number is automatically decided by the database management system)

  2. 問い合わせ計画の表示 (query plan)

    SQL 文の前に「EXPLAIN」を付ける.(Add 'EXPLAIN' before a SQL statement)

     
    EXPLAIN SELECT zipcode, jusho FROM zips WHERE jiscode = 40135;
    

    【問い合わせ計画の要点】

    二次索引が働くとき,処理は二次索引上で行われる.

    最初,カーソルは二次索引の先頭ページにセットされる. 二次索引の中から,「jiscode = 40135」という条件式を満たす索引エントリを見つけ, そのエントリを使って,データが取り出される. カーソルは,二次索引の中だけを動く.


演習問題

次の問いに答えよ. Answer the following questions.

問い (Questions)

1. 次の PTABLE テーブルに関する問題 (About the following 'PTABLE' table)

name    |  type   |  color
------------------------------
apple   |  fruit  |  red
apple   |  fruit  |  blue
rose    |  flower |  white
rose    |  flower |  red
rose    |  flower |  yellow

このテーブルの行数は 1,000,000 行以上に増える予定である.(The number of row of the table will be more than 1,000,000).

次の SQL を高速に処理するための二次索引を生成しなさい.二次索引の索引名は「idx3」にしなさい. (Write a SQL to generate a secondary index named 'idx3' that is used for the following SQL)

SELECT *
FROM PTABLE
WHERE name = 'apple'

2. 次の PLACE テーブルに関する問題 (About the following 'PLACE' table)

name    |  x      |  y
------------------------------
tenji   |  101    |  104
hakata  |  180    |  125
nishijin|  45     |  108 

このテーブルの行数は 1,000,000 行以上に増える予定である.(The number of row of the table will be more than 1,000,000).

次の SQL を高速に処理するための二次索引を生成しなさい.二次索引の索引名は「idx4」にしなさい. (Write a SQL to generate a secondary index named 'idx4' that is used for the following SQL)

SELECT *
FROM PLACE 
WHERE x > 80 AND x < 120 AND y > 90 AND y < 110 

3. 次のテーブルに関する問題 (About the following table)

CREATE TABLE R (
  id  INTEGER PRIMARY KEY,
  val INTEGER,
  note TEXT );

テーブル R の属性 val に対する二次索引を生成した場合,どのような処理が遅くなるか? (What kinds of database processing become slower when a secondary index on 'val' of the table 'R').

解答例 (Answers)

  1. CREATE INDEX idx3 ON PTABLE( name );
  2. CREATE INDEX idx4 ON PLACE( x, y );
  3. テーブル R への行の挿入や削除が遅くなる (Insertion of rows into R. Deletion of rows from R).

以下,書きかけ

  • explain を用いた問い合わせ計画の表示

     
    explain SELECT * FROM zips WHERE zipcode = 8190012;
    

  • テーブル zips の 属性 jiscode に作られた二次索引を使ってみる (Try to use the secondary index)
    SELECT zipcode, jusho 
    FROM zips, shichosons
    WHERE zips.jiscode = shichosons.jiscode 
          AND ken_kanji = '福岡県';
    

    「zips.jiscode = shichosons.jiscode 」の部分は「完全一致」での絞込みである.この部分で,二次索引 idx2 が利用される. ("zips.jiscode = shichosons.jiscode" is exact match. The secondary index 'idx2' is used here)

    結果が得られるまで数秒かかる (It will take several seconds until result)

  • 同じ問い合わせを繰り返す (Repeat the same query)
    SELECT zipcode, jusho 
    FROM zips, shichosons
    WHERE zips.jiscode = shichosons.jiscode 
          AND ken_kanji = '福岡県';
    

    同じ結果が得られる.しかし,ずっと遅い (The result is same. But much slower)