Index table primary index one primary index for
索引 (Index) テーブル (table) 主索引 (primary index) - 各テーブルに1つの主索引 (one primary index for each table) 二次索引 (secondary index) - 二次索引は任意個 (arbitrary number of secondary index for each table) リレーショナルデータベースの索引 (RDB index) - 高速なアクセスパス (Fast access path) - SQL プログラムのコンパイル時に,索引を使用 するコードが自動生成される (code using index is generated automatically at the SQL program compile time)
■ Table Definition CREATE TABLE reports ( id INTEGER PRIMARY KEY, docid INTEGER, path TEXT, val TEXT ); ■ The attribute ‘id’ is the PRIMARY KEY. The ‘id’ is key that can be used to identify each row. ■ Secondary Index Generation CREATE INDEX idx 1 ON reports( docid ) Index name table name attribute name PRIMARY KEY の属性は しばしば主索引のキーとして使用される (PRIMARY KEY attribute is often used as the key of the primary index) Table ‘Reports’ 属性 id の主索引 (primary index on ‘id’) 属性 docid の二次索引 (secondary index on ‘docid’)
単一テーブルに関する絞込みの例 SELECT * FROM R WHERE <expression> • One Attribute Expression – 完全一致(Exact Match) “id = 1001”, “name = ‘kaneko’” – 範囲検索(Range Query) “year > 2008”, “x > 10 AND x < 20” • Multi Attribute Expression – 完全一致(Exact Match) “a = 20 AND b = 30” – 範囲検索(Range Query) “x > 5 AND x < 10 AND y > 15 AND y < 20”
■ One Attribute Expression <exprected expression> <index generation> “id = 1001” CREATE idx 1 ON R(id) “name = ‘kaneko’” CREATE idx 2 ON R(name) “year > 2008” CREATE idx 3 ON R(year) “x > 10 AND x < 20” CREATE idx 4 ON R(x) ■ Multi Attribute Expression <exprected expression> <index generation> “a = 20 AND b = 30” CREATE idx 5 ON R(a, b) “x > 5 AND x < 10 AND y > 15 AND y < 20” CREATE idx 6 ON R(x, y)
- Slides: 5