SQLite - ビューとインデックス

sqlite> create table AAA(ii integer, tt text, rr real);
sqlite> insert into AAA values(1, 'aaa', 0.8),
   ...> (2, 'aaa', 0.7),
   ...> (3, 'bbb', 0.6),
   ...> (4, 'bbb', 0.5),
   ...> (5, 'ccc', 0.4),
   ...> (6, 'ccc', 0.3),
   ...> (7, 'ddd', 0.2),
   ...> (8, 'ddd', 0.1);

sqlite> select * from AAA;
ii          tt          rr
----------  ----------  ----------
1           aaa         0.8
2           aaa         0.7
3           bbb         0.6
4           bbb         0.5
5           ccc         0.4
6           ccc         0.3
7           ddd         0.2
8           ddd         0.1

VIEW

• 参考：SQLite Query Language: CREATE VIEW

VIEWとは既存のテーブルから作成する仮想のテーブル
予め特定の条件でビューを作成しておくことで、検索においてSQLを簡潔に記述できる。

• データをコピーするのではなく既存のテーブルへの参照？を作成する。
• データの追加変更はできない。
• 元テーブルが変更されるとビューの出力も変わる。
• VIEW作成時に元テーブルのROWIDを指定しないと、VIEW側のselectで指定しても取得できない。

sqlite> create view VVV as select tt,rr from AAA where rr<0.5;
sqlite> select * from VVV;
tt          rr
----------  ----------
ccc         0.4
ccc         0.3
ddd         0.2
ddd         0.1

sqlite> .tables
AAA  VVV

sqlite> .mode line
sqlite> select * from sqlite_master where type='view';
    type = view
    name = VVV
tbl_name = VVV
rootpage = 0
     sql = CREATE VIEW VVV as select tt,rr from AAA where rr<0.5

sqlite> drop view VVV;
sqlite> .tables
AAA

INDEX

• 参考：SQLite Query Language: CREATE INDEX

規模の大きいテーブルで、インデックスを作成することで検索効率を上げることができる。
ただし、テーブルが更新される毎にインデックスも更新されるのでその分は負荷がかかる。

sqlite> create index IDX on AAA(ii,tt);

sqlite> .mode line
sqlite> select * from sqlite_master where type='index';
    type = index
    name = IDX
tbl_name = AAA
rootpage = 4
     sql = CREATE INDEX IDX on AAA(ii,tt)

sqlite> drop index IDX;
sqlite> select * from sqlite_master where type='index';
sqlite>

UNIQUE INDEX

重複したデータが格納されていないカラムのみを指定することでユニークインデックスの作成できる。
ただし、ユニークインデックス作成後に重複データを追加するとエラーになる。

sqlite> create unique index UIDX on AAA(ii,rr);
sqlite> insert into AAA values(1,'xxx',1.0);
sqlite> insert into AAA values(1,'xxx',0.8);
Error: UNIQUE constraint failed: AAA.ii, AAA.rr

sqlite> select * from sqlite_master where type='index';
    type = index
    name = UIDX
tbl_name = AAA
rootpage = 4
     sql = CREATE UNIQUE INDEX UIDX on AAA(ii,rr)

sqlite> drop index UIDX;