トップページインストール,使い方Windows の種々のソフトウェア(インストール,使い方)SQLite 3 の SQL 問い合わせ計画(Windows 上)

SQLite 3 の SQL 問い合わせ計画(Windows 上)

【目次】

  1. 前準備
  2. SQL 問い合わせ計画の表示
  3. 結合問い合わせの SQL 問い合わせ計画の表示
  4. 二次索引がある場合の問い合わせ計画の例

前準備

Windows で SQLite 3 のインストール: 別ページで説明している.

2. SQL 問い合わせ計画の表示

テーブルの全ての行の表示

次の SQL で行う.

SELECT * FROM results;

このときの SQL 問い合わせ計画を表示させる. そのために,SQL 文の前に「EXPLAIN」を付ける

EXPLAIN SELECT * FROM results;

[image]

【表示された問い合わせ計画の要点】

アドレス (addr) オペコード 主なオペランド  
2 OpenRead P2 = 14 ルート・ページが 14 であるようなテーブル (この場合は,テーブル results) のカーソルを作る (Open table 'results' for read, and make a cursor)
3 Rewind P2 = 10 カーソルを,テーブルの先頭を指し示すようにする.テーブルが空の場合には,アドレス 10 (Close)にジャンプする (Use the first row. If the first row is empty then jump to '10')
4,5,6,7 Column P2 = 0,1,2,3, P3 = 1,2,3,4 列番号0, 1, 2, 3の値を,それぞれレジスタ1,2,3,4に格納する. (#1, #2, #3, #4 values are stored into registers)
8 ResultRow P1 = 1, P2 = 4 レジスタ 1 からレジスタ 4 までの値を1行として出力する (Generate output using registers)
9 Next P2 = 4 もし,カーソルが指し示すレコードが末端レコードならば、次の命令に進む. もし,カーソルが指し示すレコードが末端レコードでなければ、カーソルを1つ進めて、アドレス4 にジャンプする. (Advance cursor to the next tow. If there are more rows, then jump to the address '4')

条件を満足する行のみの表示

次の SQL で行う.

SELECT * FROM results WHERE student_name='KK'; 

このときの SQL 問い合わせ計画を表示させる. そのために,SQL 文の前に「EXPLAIN」を付ける

EXPLAIN SELECT * FROM results WHERE student_name='KK'; 

アドレス 6 の「Ne 1 12 2 collseq(BINARY) 69」は「条件付きジャンプ」である. 条件を満たさないような行の場合には、結果を得るプログラムの部分をジャンプ (ここでは、7行目から11行めまで)するようになっている。これで、条件を満たす行だけが得られるようになる。

[image]

最初,カーソルは先頭行にセットされる. アドレス 6 の「Ne 1 12 2 collseq(BINARY) 69」は「条件付きジャンプ」であり、 SQL で指定されていたstudent_name = 'KK'」の処理を行うためのものである. (true の時に限り,新しい行が,評価結果に追加される) その後,カーソルは次の行へ動く. このようにして,results テーブルの全ての行が精査される.

3. 結合問い合わせの SQL 問い合わせ計画の表示

  1. 次のような結合問い合わせを考える.
    SELECT zips.zipcode, zips.choiki_kanji, shichosons.shichoson_kanji, kens.ken_kanji
    FROM zips, kens, shichosons
    WHERE zips.jiscode = shichosons.jiscode
          AND shichosons.ken_kanji=kens.ken_kanji
          AND kens.id = 43;
    

    SQL 文の前に「EXPLAIN」を付けて実行する

    EXPLAIN SELECT zips.zipcode, zips.choiki_kanji, shichosons.shichoson_kanji
    FROM zips, shichosons
    WHERE zips.jiscode = shichosons.jiscode
          AND kens.id = 43;
    
    EXPLAIN SELECT zips.zipcode, zips.choiki_kanji, shichosons.shichoson_kanji, kens.ken_kanji
    FROM zips, kens, shichosons
    WHERE zips.jiscode = shichosons.jiscode
          AND shichosons.ken_kanji=kens.ken_kanji
          AND kens.id = 43;
    

    [image]
    [image]
  2. 今度は,次のような結合問い合わせを考える.
    select distinct R.choiki_kanji 
    FROM TT as R, TT as S 
    WHERE R.choiki_kanji = S.choiki_kanji
          AND R.jiscode <> S.jiscode; 
    

    SQL 文の前に「EXPLAIN」を付けて実行する

    EXPLAIN select distinct R.choiki_kanji 
    FROM TT as R, TT as S 
    WHERE R.choiki_kanji = S.choiki_kanji
          AND R.jiscode <> S.jiscode; 
    

    SQL 文の前に「EXPLAIN」を付ける

    [image]

    [image]

    [image]

    【表示された問い合わせ計画の要点】

    アドレス (addr) オペコード 主なオペランド  
    6 OpenRead P1 = 0, P2 = 7 ルート・ページが 7 であるようなテーブル (この場合は,テーブル TT) のカーソルを作る. カーソル番号は 0 (Open table 'TT' for read, and make a cursor)
    7 OpenRead P1 = 1, P2 = 7 ルート・ページが 7 であるようなテーブル (この場合は,テーブル TT) のカーソルを作る. カーソル番号は 1 (Open table 'TT' for read, and make a cursor)
    11 Rewind P1 = 1, P2 = 18 カーソル 1 を,テーブルの先頭を指し示すようにする.テーブルが空の場合には,アドレス 18 にジャンプする (Use the first row. If the first row is empty then jump to '18')
    12 Rowid P1 = 1, P2 = 12 カーソル 1 が指し示すレコードの主キーの値を、レジスタ12に格納する
    13 Column P1 = 1, P2 = 4, P3 = 10 カーソル 1 が指し示すレコードの列番号4の値を,レジスタ 10 に格納する.(Save #4 column value into the register #10)
    14 Column P1 = 1, P2 = 3, P3 = 11 カーソル 1 が指し示すレコードの列番号3の値を,レジスタ 11 に格納する.(Save #3 column value into the register #11)
    15 MakeRecord P1 = 10, P2 = 3 レジスタ 10 からレジスタ 12 までの値からレコードをつくる(次の IdxInsert で使う)
    16 IdxInsert P1 = 3, P2 = 9 索引 3 に,前の MakeRecord 命令で作ったレコードを挿入する. ※ 「索引 3」といっているのは、アドレス10の「OpenAutoindex」で生成された索引のことである。この索引は、今回の SQL 問い合わせの評価のために一時的に生成された索引である。MakeRecord 命令で作ったレコードの挿入が繰り返されるが、この「索引 3」を使って、整列(ソート)が行われる。
    17 Next P1 = 1, P2 = 12 もし,カーソル 1 が指し示すレコードが末端レコードならば、次の命令に進む. もし,カーソル 1 が指し示すレコードが末端レコードでなければ、カーソルを1つ進めて、アドレス12 にジャンプする. (Advance cursor to the next tow. If there are more rows, then jump to the address '12')
    18 Rewind P1 = 0, P2 = 32 カーソル 0 を,テーブルの先頭を指し示すようにする.テーブルが空の場合には,アドレス 32 にジャンプする (Use the first row. If the first row is empty then jump to '32')
    19 Column P1 = 0, P2 = 4, P3 = 13 カーソル 0 が指し示すレコードの列番号4の値を,レジスタ 13 に格納する.(Save #4 column value into the register #13)
    21 SeekGe P1 = 3, P2 = 31, P3 = 13, P4 = 1 いま,カーソル 3 は,「索引 3」を指し示している. カーソル 3の位置を,レジスタ13に入っている検索キーに等しいか検索キーよりも大きいという条件を満足するなかで最小の要素を指し示すように動かす.そのような要素がない場合には31にジャンプする.
    22 IdxGe P1 = 3, P2 = 31, P3 = 13, P4 = 1 レジスタ13を検索キーとして使う. 検索キーの値を,現在,カーソル3が指し示している索引(正確にはカーソル3が指し示している索引エントリ)を使って比較する. もし,カーソル3が指し示す索引エントリが,検索キーの値と等しいか大きければ31にジャンプするさもなければ次に進む.
    23 Column P1 = 0, P2 = 3, P3 = 9 カーソル 0 が指し示すレコードの列番号3の値を,レジスタ 9 に格納する.(Save #3 column value into the register #11)
    24 Column P1 = 3, P2 = 1, P3 = 14 カーソル 3 が指し示すレコードの列番号1の値を,レジスタ 14 に格納する.(Save #1 column value into the register #14)
    25 Eq P1 = 14, P2 = 30, P3 = 9 条件付きジャンプ. レジスタ 14 の値とレジスタ 9 の値が等しくないときに限り,アドレス 30 にジャンプする.
    26 Column P1 = 0, P2 = 4, P3 = 10 カーソル 0 が指し示すレコードの列番号4の値を,レジスタ 10 に格納する.
    28 MakeRecord P1 = 10, P2 = 2 レジスタ 10 からレジスタ 11 までの値からレコードをつくる(次の IdxInsert で使う)
    29 IdxInsert P1 = 2, P2 = 14 カーソル 2 のために,新しい通し番号 (sequence number) を見つける。これを行っている理由は「DISCINCT」指定による重複除去を行いたいから. ※ 「カーソル 2」といっているのは,最終結果として出力されるテーブルのために作られたカーソルのことである。このカーソルは、アドレス1の「OpenEmphemeral」命令で生成されている。
    30 Next P1 = 3, P2 = 22 もし,カーソル 3 が指し示すレコードが末端レコードならば、次の命令に進む. もし,カーソル 22 が指し示すレコードが末端レコードでなければ、カーソルを1つ進めて、アドレス22 にジャンプする.
    31 Next P1 = 0, P2 = 19 もし,カーソル 0 が指し示すレコードが末端レコードならば、次の命令に進む. もし,カーソル 0 が指し示すレコードが末端レコードでなければ、カーソルを1つ進めて、アドレス19 にジャンプする.

4. 二次索引がある場合の問い合わせ計画の例

今度は,属性 choiki_kanji についての二次索引を作り,先ほどと同じ SQL 問い合わせを評価させる.

create index idex1 on zips(choiki_kanji);
select distinct R.x 
FROM TT as R, TT as S 
WHERE R.choiki_kanji = S.choiki_kanji
      AND R.jiscode <> S.jiscode; 

SQL 文の前に「EXPLAIN」を付ける

EXPLAIN select distinct R.choiki_kanji 
FROM TT as R, TT as S 
WHERE R.choiki_kanji = S.choiki_kanji
      AND R.jiscode <> S.jiscode; 

[image]
[image]

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

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

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