SQLite 3 の SQL 問い合わせ計画（Windows 上）

前準備

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

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

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

次の SQL で行う．

SELECT * FROM results;

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

EXPLAIN SELECT * FROM results;

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

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

次の 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」は「条件付きジャンプ」である． 条件を満たさないような行の場合には、結果を得るプログラムの部分をジャンプ （ここでは、７行目から１１行めまで）するようになっている。これで、条件を満たす行だけが得られるようになる。

最初，カーソルは先頭行にセットされる． アドレス 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;
   

   
   
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」を付ける．

   
   
   

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

   アドレス (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 が指し示すレコードが末端レコードでなければ、カーソルを１つ進めて、アドレス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」といっているのは，最終結果として出力されるテーブルのために作られたカーソルのことである。このカーソルは、アドレス１の「OpenEmphemeral」命令で生成されている。
   30	Next	P1 = 3, P2 = 22	もし，カーソル 3 が指し示すレコードが末端レコードならば、次の命令に進む． もし，カーソル 22 が指し示すレコードが末端レコードでなければ、カーソルを１つ進めて、アドレス22 にジャンプする．
   31	Next	P1 = 0, P2 = 19	もし，カーソル 0 が指し示すレコードが末端レコードならば、次の命令に進む． もし，カーソル 0 が指し示すレコードが末端レコードでなければ、カーソルを１つ進めて、アドレス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; 

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

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

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

• 二次索引のサイズは，テーブル本体のサイズよりずっと小さい (The size of secondary index is much smaller than the table)
• 二次索引は，高速処理に向いたデータ構造になっている (Secondary index is fast access path)