Transcript データベースについて

データベースについて
1. RDBの構造
商品番号
商品マスタ
表名
商品名
価格
001
002
：
えんぴつ
けしごむ
：
50
100
：
定義域 ( dom ain )
商品番号
商品名
価格
属性名
001
えんぴつ
50
002
消しゴム
100
003
ふでばこ
500
004
はさみ
600
属性 ( attribute )
列 ( colum n )
組 ( tuple )
行 ( row )
関係 ( relation )
表 ( table )
2. 関数従属
関数従属
関係 R において、ある属性値がきまると、対応する別の属性値が一意的に決まる(従属する)性質
商品番号
商品名
価格
001
えんぴつ
50
002
消しゴム
100
商品番号がきまると、すべての属性値( 商
品名、価格 ) が一意に特定できる。
商品番号 → { 商品名 , 価格 }
完全関数従属
C1
01
01
01
02
C2
01
02
03
01
C3
A
A
B
C3
C4
abc
def
ghi
jkl
関数従属 { C 1 ,C 2 ,C 3} → C 4
関数従属で、あるが、C 4は、C 1、C 2の部分に
従属している。
完全関数従属 { C 1 ,C 2 } → { C 3 ,C 4 }
3. キーの種類
キー名称
スーパーキー
super key
候補キー
candidate key
主キー
primary key
外部キー
foreign key
複合キー
compound key
代替キー
alternate key
内容
表中で、n個組を一意に識別する1個または1個以上の属性
必要のない属性が含まれることが有る
表中で、n個組を一意に識別し、冗長性のない1個又1個上の属性
表の中に候補キーは複数個あってもよい
補キーのうちどれか一つを主たるものとする時、これを主キーという
表内にただ一つ、ナル値は認められない
表間に従属関係(参照制約)があるとき、参照する側の項目
参照される側は主キー、または候補キー
複数の属性を合わせてキーとしているもの
候補キーのうち、主キー以外のもの
主キー、候補キー
スーパーキー
候補キー
外部キー
4. 正規化
正規化を行うことにより、1事実1箇所( 1 fact in 1 place ) が実現され、データベースに新しいデータ
が追加されたときも、データベース構造およびアプリケーションプログラムの変更を最小限に抑え
ることができる。
名称
定義
正規化方法
第2正規形
( 2N F )
繰り返しグループの排除
上位のキーを下位に埋め込
む
関係R が第1正規形で、関係R の 部分関数従属性の排除
非キー属性すべてが、すべての
候補キーに完全関数従属
第3正規形
( 3N F )
関係R が第2正規形で、候補
推移関数従属性の排除
キーと、非キー属性との間に推
移関数従属性がない
第1正規形
( 1N F )
繰り返しグループ(非単純定義
域)を一つも含まない
非キー属性 ・
・
・候補キーに含まれない属性
5. 第1正規化
非正規形
注文番号
顧客番号
顧客名
注文日
00001
01
A社
2004/04/01
00002
00003
02
02
B社
A社
2004/04/02
2004/04/02
商品番号
01
02
03
03
03
商品名
鉛筆
消しゴム
ふでばこ
ふでばこ
ふでばこ
単価
50
100
500
500
500
数量
10
5
1
2
3
商品名
鉛筆
消しゴム
ふでばこ
ふでばこ
ふでばこ
単価
50
100
500
500
500
数量
10
5
1
2
3
繰り返しグループあ
りR D B に登録不可
第1正規化
注文表
注文番号
00001
00001
00001
00002
00003
顧客番号
01
01
01
02
02
顧客名
A社
A社
A社
B社
A社
注文日
2004/04/01
2004/04/01
2004/04/01
2004/04/02
2004/04/02
■ 挿入異常
「
C 社」
の情報が必要になった場合、「
注文表」
に挿入し
ようとすると、主キーが N U LLとなってしまい、挿入できな
い。
商品番号
01
02
03
03
03
■ 削除異常
「
注文番号」
が "00002"のデータを削除すると、「
B 社」
についての情報が削除されてしまう。
■ 更新異常
「
A 社」
の社名が変更になった場合、「
注文表」
の4箇所
のデータを更新しなければいけない。
非正規形では、RDBに登録できないため、第1正規形に分解して、登録する。
しかし、第1正規形の状態では、挿入異常、削除異常、更新異常などの不整合が発生する。
6. 第2正規化
注文表
注文番号
00001
00001
00001
00002
00003
顧客番号
01
01
01
02
02
顧客名
A社
A社
A社
B社
A社
注文日
2004/04/01
2004/04/01
2004/04/01
2004/04/02
2004/04/02
商品番号
01
02
03
03
03
第2正規化
注文表
注文番号
00001
00002
00003
顧客番号
01
02
01
注文明細表
注文番号 商品番号
00001
01
00001
02
00001
03
00002
03
00003
03
顧客名
A社
B社
A社
数量
10
5
1
2
3
注文日
2004/04/01
2004/04/02
2004/04/02
商品表
商品番号
01
02
03
商品名
単価 数量
鉛筆
50
10
消しゴム
100
5
ふでばこ
500
1
ふでばこ
500
2
ふでばこ
500
3
注文表の主キーは
{ 注文番号 ,商品番号 }
となる。
非キー属性 { 顧客番号 ,顧客名 ,注文日} は、
主キーの一部に従属しているため、分解
商品名
鉛筆
消しゴム
ふでばこ
単価
50
100
500
非キー属性 { 商品名 ,単価 ,数量 } は、主
キーの一部に従属しているため、分解
7. 第3正規化
注文表
注文番号 顧客番号
00001
01
00002
02
00003
01
注文表の主キーは
{ 注文番号 } となる。
顧客名
A社
B社
A社
注文日
2004/04/01
2004/04/02
2004/04/02
第3正規化
注文表
注文番号
00001
00002
00003
顧客番号
01
02
01
注文明細表
注文番号 商品番号
00001
01
00001
02
00001
03
00002
03
00003
03
注文日
2004/04/01
2004/04/02
2004/04/02
数量
10
5
1
2
3
顧客表
顧客番号
01
02
商品表
商品番号
01
02
03
{ 注文番号 } → { 顧客番号 } → { 顧客名 }
を分解
顧客名
A社
B社
商品名
鉛筆
消しゴム
ふでばこ
単価
50
100
500
8. 結合-1
Inner Join
tableA
tableB
NULL
tableA
tableB
・SELEC T * FR O M tableA IN N ER JO IN tableB O N tableA .Key_N am e = tableB .Key_N am e
・SELEC T * FR O M tableA ,tableB W H ER E tableA .Key_N am e = tableB .Key_N am e
Left Outer Join
・SELEC T * FR O M tableA LEFT O U TER JO IN tableB O N tableA .Key_N am e = tableB .Key_N am e
・SELEC T * FR O M tableA ,tableB W H ER E tableA .Key_N am e = tableB .Key_N am e(+)
9. 結合-2
Right Outer Join
tableA
tableB
・SELEC T * FR O M tableA R IG H T O U TER JO IN tableB O N tableA .Key_N am e = tableB .Key_N am e
・SELEC T * FR O M tableA ,tableB W H ER E tableA .Key_N am e(+) = tableB .Key_N am e
NULL
NULL
tableA
NULL
tableB
Full Outer Join
・SELEC T * FR O M tableA FU LL O U TER JO IN tableB O N tableA .Key_N am e = tableB .Key_N am e
・SELEC T * FR O M tableA ,tableB W H ER E tableA .Key_N am e = tableB .Key_N am e(+)
U N IO N
SELEC T * FR O M tableA ,tableB W H ER E tableA .Key_N am e(+) = tableB .Key_N am e
10. 結合-3
11. 演算(和演算)
■ 和演算 ( union )
表A
表B
SQ L
SELEC T * FR O M TA B LEA
U N IO N
SELEC T * FR O M TA B LEB
TA B LEA
TA B LEB
12. 演算(差演算)
■ 差演算 ( difference )
表A
表B
SQ L
SELEC T * FR O M TA B LEA
M IN U S
SELEC T * FR O M TA B LEB
TA B LEA
TA B LEB
13. 演算(積演算)
■ 積演算 ( intersection )
表A
表B
SQ L
SELEC T * FR O M TA B LEA
IN TER SEC T
SELEC T * FR O M TA B LEB
TA B LEA
TA B LEB
14. サブクエリー
■ サブクエリー
SELEC T
V A LU E
FR O M
サブクエリーの
TA B LEC C
結果が条件
W H ER E
C .KEY_N A M E IN (
SELEC T KEY_N A M E
※1
FR O M TA B LEA W H ER E
V A LU E IN ('O N E','TW O ','TEN ')
)
※1 サブクエリーのSELEC T の結果は、
単一列でなければならない。
ただし、EXISTS述語の場合を除く
TA B LEC
TA B LEA
15. 相関サブクエリー
■ 相関サブクエリー
SELEC T
V A LU E
① 1行づづ処理する
FR O M
TA B LEC C
W H ER E
C .KEY_N A M E = (
②サブクエリー
SELEC T KEY_N A M E
の結果が条件
FR O M TA B LEA W H ER E
KEY_N A M E = C .KEY_N A M E
)
TA B LEC
TA B LEA
16. 最適化
オプティマイザ
ユーザが発行したSQLを解析し、最も効率よくデータベースを走査するにはどうすればよいかを判断した上で、
実行計画を決定する。開発者はオプティマイザの実行計画を確認しながら、効率的なSQL文を記述することができる。
SQL 処理の概要
ユーザ
結果
SQL問い合わせ
パーサー
ディクショナリ
ルールベース・
オプティマイザ
RBO
オプティマイザ
モード？
CBO
コストベース・
オプティマイザ
問い合わせ計画
行ソース
ジェネレータ
SQL実行
17. ルールベースオプティマイザ
SQLの記述内容に応じて、実行計画を決定する手法
SQLの構文と、用意されているインデックスにしたがってアクセスパス(作業手順)が決定される。
アクセスパスのランク
アクセスパスの選定
アクセスパスの洗い出し
1. ROWIDによる単一行
→ アクセスパスのランク付け
2. クラスタ結合による単一行
3. 一意／主キーを持つハッシュ・クラスタ・キーによる単一行
→ 一番ランクの高いアクセスパスを使用
4. 一意／主キーによる単一行
5. クラスタ結合
6. ハッシュ・クラスタ・キー
7. 索引付のクラスタ・キー
8. 複合検索
9. 単一列検索
10. 索引列の境界付の範囲検索
全表走査
11. 索引列の境界なしの範囲検索
通常全表走査はもっとも時間のかかる検索方法です。しかし、小さな表
を検索する場合には、インデックスリーフを検索するオーバーヘッドが発
生しない為、全表走査の方が高速になる場合もあります。
12. ソート／マージ結合
13. 索引付の列のMAXまたはMIN
14. 索引付の列のORDER BY
15. 全表走査
一般にWHERE句の検索条件に合致する行が表の総数の20 ～ 30 % 以
上になる場合、全表走査の方が高速。
18. コストベースオプティマイザ
統計情報(データ量、分布状況)を元に、アクセスコストを見積もり、もっともコストの低いアクセスパスを
採択する。
コストベースを採用する場合には、統計情報をあらかじめ取得しておく必要がある。
統計情報を取得する為には、
ANALYZE { INDEX <インデックス名> | TABLE <テーブル名> } COMPUTE STSTISTICS
を発行する。
オプティマイザヒント
ある問合せに対しては、特定の索引を選択する方がよい場合もあります。この情報に基づいて、アプリケーション設計者がオプティ
マイザよりも効率的に実行計画を選択することができます。その場合は、ヒントを使用して、オプティマイザが最適な実行計画を使
用するように強制することができます。
・ ALL_ROWS
最高のスループットを目標とする(合計リソース使用率を最小)
・ FIRST_ROWS
応答が高速なるようにする
・ CHOOSE
オプティマイザはSQL 文にルールベースのアプローチとコストベースのアプローチのどちらかを選択
選択の基準は、文がアクセスする表に対する統計の存在
・ RULE
ルールベース
例
SELECT /*+ RULE */
employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;
19. インデックス作成の指針
(1) 表の結合条件として使用する列に対しては、インデックスを作成する。
結合条件を WHERＥ句に指定する為。
(2) 小さな表に対して、インデックスを作成しても、パフォーマンスは改善されない。
インデックスリーフを参照するより、テーブルスキャンの方が高速となることが多い。
(3) 多くの異なる値が格納されている列に対して、インデックスを作成すると、大幅に
パフォーマンスが向上する。
(4) 表の総行数の 10 ～ 15 % 未満の結果を返すクエリーで、効果を発揮する。
(5) インデックスを作成すると、検索のパフォーマンスは向上するが、更新のパフォーマンスは低下する。
20. SQLの最適化 -1
SQLのパフォーマンスを最適化するためには、適切なインデックスが使用されるようにする
コストベースで運用されていたとしても、構築時点でルールベースで正しくインデックスが利用されてい
ることを確認する。
インデックスが使用されない場合
(1) NULL を検索条件に指定
インデックスにはNULL値は格納されない為、全表走査となる。
SELECT * FROM
TABLE_A WHERE COL_1 IS NULL
全表走査
(2) NOT EQUAL( != 、 <> 、 ^= ) による検索
SELECT * FROM TABLE_A WHERE COL_1 <> 100
全表走査
SELECT * FROM TABLE_A WHERE COL_1 > 100 OR COL_1 < 100
索引走査
21. SQLの最適化 -2
(3) インデックス列に計算式を使用
SELECT * FROM TABLE_A WHERE COL_1 * 2 > 100
全表走査
SELECT * FROM TABLE_A WHERE COL_1 > 100 / 2
索引走査
(4) 暗黙のデータ変換が発生する場合
SELECT * FROM TABLE_A WHERE COL_2 = 100
全表走査
SELECT * FROM TABLE_A WHERE COL_2 = '100‘
索引走査
※ COL_2 は CHAR とする
(5) LIKE 句の使用
SELECT * FROM TABLE_A WHERE COL_2 LIKE ‘A%’
索引走査
SELECT * FROM TABLE_A WHERE COL_2 LIKE 'A_‘
索引走査
SELECT * FROM TABLE_A WHERE COL_2 LIKE '%A‘
全表走査
SELECT * FROM TABLE_A WHERE COL_2 LIKE '_A‘
全表走査
22. SQLの最適化 -3
(6) 複合インデックス
※ [COL_1 , COL_2] が複合インデックスの場合
SELECT * FROM TABLE_A WHERE COL_1 = 100 AND COL_2 = ‘200‘ 索引走査
SELECT * FROM TABLE_A WHERE COL_2 = ‘200’ AND COL_1 = 100 索引走査
SELECT * FROM TABLE_A WHERE COL_1 = 100
全表走査
SELECT * FROM TABLE_A WHERE COL_2 = ‘200‘
全表走査
(7) ORDER BY 句の使用
ORDER BY の対象となる列に NOT NULL 制約が設定されている場合にのみ、
インデックスが利用される。
(8) GROUP BY 句の使用
常に全表走査となる。
(9) OR 検索
OR 検索を行う場合、使用する列全てに対して、インデックスが付与されていないと、
全表走査となる。
23. SQLの最適化 -4
その他
(1) 表の指定順序
FROM 句の後は、データの多い順に記述する。
RULE ベースで、NESTED LOOP の場合、後に記述したほうが、外側のループとなる。
※ NESTED LOOPでは、外側のループが小さいほうがよい。