SQL開発標準書
表紙
SQL 開発標準書
【 0.9 版 】
2003年 10月 7日
ビットワレット株式会社
[Confidential]
- 1 / 35 -
bitWallet, Inc.
SQL開発標準書
変更履歴
年月日
版 数
2003/07/15
0.1版
-
新規作成。
b/W 沼上
2003/09/12
0.9版
-
SQL標準検討会議にて追加・更新
b/W 櫻井
[Confidential]
修 正 項 目
修正/発行履歴
- 2 / 35 -
発行者/修正者
bitWallet, Inc.
SQL開発標準書
Contents
1.SQL開発標準書の目的
・・・・・・・
4
2.Oracle運用標準について
・・・・・・・
5
3-1.記述文字タイプ
・・・・・・・
6
3-2.コーディング規約
・・・・・・・
7
3-3.条件句記述
・・・・・・・
11
3-4.表結合
・・・・・・・
21
3-5.Hint句
・・・・・・・
22
3-6.関数
・・・・・・・
28
4.Traceの取得
・・・・・・・
32
5.実行計画の指針
・・・・・・・
33
3.SQLコーディング規約
[Confidential]
- 3 / 35 -
bitWallet, Inc.
SQL開発標準書
1.SQL開発標準書の目的
EdyシステムDataBase運用規約に準拠したSQL開発標準を本書に示す。
・Edy System DataBase運用規約において、Optimaize ModeはCost Base Optimaizerを採用する。
Cost Base Optimaizerは、統計情報取得タイミングにより、予測不可能な挙動を起こすケースが
散見される。従って、Cost Base Optimaizerにて運用を行う際はこれら予測不能な挙動は
抑止する必要性がある。
本書は、これら予測不能な挙動を抑止する方法をまとめたものであり、開発を行う際は本書に
記載された規約に従い開発を行うものとする。
・Applicationの保守容易性の向上を図るための指針を示す。
・Oracleの動作特性に合致するSQL構文を記述することとする。
・本書に記述されていない機能等については、DBAに相談して使用有無を判断する。
DBAが使用許可した機能等については、本書に追加する事とする。
[Confidential]
- 4 / 35 -
bitWallet, Inc.
SQL開発標準書
2.Oracleの運用標準について
・SQL開発標準に関係するOracleの運用標準を下記に記す。
①Optimize Mode は、コスト・ベース(ALL_ROWS)を使用するが、統計情報の取得タイミングによりアクセス・パスが
変更される事を回避するために、原則として全てのSQL(SELECT、UPDATE、DELETE)にはHINT文により、
アクセス・パスを固定する。
②ALL_ROWS Optimaizerは、Batch System向けOptimaize Modeのため、Online処理等のレスポンスを重視する処理を
行う際は、Hint句【First_Rows 】にてレスポンス速度の向上を計る
③Edy System DBに格納される表・索引は全て統計情報を取得する。この為、SQL文にはHint句は必須とする。
④Traceの取得を任意の時点で行えるよう、ProgramにはTraceを取得するロジックを記述することを必須とする。
⑤単体テストを実施する際は、アクセスパスが適切であるか確認を行うものとする。
⑥SQLを記述する際は、問い合わせ表の特性を考慮すること
[Confidential]
- 5 / 35 -
bitWallet, Inc.
SQL開発標準書
3.SQLの記述方法 【1.記述文字タイプ】
①SQLは全て大文字で記述する。
(Oracleは、大文字/小文字が混在すると実行前に分析を行う。処理時間に影響する範囲では無いが、
最適化を考慮して大文字とする)
②問い合わせを行う際は、「*」にて項目選択行わない事とする。
③問い合わせ表が1表であった場合でも必ず、別名を記述することとする。
(1表でも別名にするのは、Hint句等の記述を容易にする為)
※別名は、「A」・「B」・「C」とアルファベット順にて記述する。
ex)
Select a.Edy_Term_Id
ex)
SELECT A.EDY_TERM_ID FROM BC_LOG_DET A
[Confidential]
from bc_Log_det a
- 6 / 35 -
⇒ 誤
⇒ 正
bitWallet, Inc.
SQL開発標準書
3.SQLの記述方法 【2.コーディング規約】
SQL(SELECT)を記述する際は、保守性の向上を考慮に入れ、以下の形式にのっとり記述を行うこととする。
S
E
L
F
W H
SELECT文
O R D
G R O
S
E
U
U
E
R
P
N
L
F
E
C
T
R O M
E R E
A N D
O R
B Y
B Y
I O N
E C T
R O M
/ * +
H
H
F I E L
F I E L
T A B L
( 条 件 )
( 条 件 )
( 条 件 )
F I E L
F I E L
/ * +
F
T
I E
A B
F O
※DMLコマンドと、次文字列間は必ず3バイトSPACEをあけること。
R
I
I
D
D
E
N T 1
N T 2
名 ,
名
名
別
* /
/ * 列 名 の 説 明 * /
/ * 列 名 の 説 明 * /
名
D 名
D 名
H I N T 1
H I N T 2 * /
L D 名
/ * 列 名 の 説 明 * /
L E 名
別 名
U
P D A
T E
;
※条件句を記述する際は必ずHINT句は必ず記述すること。
※射影対象列には、任意でコメントを追加することができる。
※問い合わせTBLには必ず別名を定義する。
[Confidential]
- 7 / 35 -
bitWallet, Inc.
SQL開発標準書
3.SQLの記述方法 【2.コーディング規約】
SQL(UPDATE)を記述する際は、保守性の向上を考慮に入れ、以下の形式にのっとり記述を行うこととする。
U
P
D
UPDATE文
W
H
A
S
E
T
E
R
E
T
E
/
T
F
(
*
+
A B
I E
条 件
H
H
L
L
)
I
I
E
D
;
N
N
名
名
T
T
1
2
*
/
/
* 列 名 の 説 明 *
/
※DMLコマンドと、次文字列間は必ず3バイトSPACEをあけること。
※更新対象列には、任意でコメントを追加することができる。
※条件句を記述する際は必ずHINT句を記述すること
[Confidential]
- 8 / 35 -
bitWallet, Inc.
SQL開発標準書
3.SQLの記述方法 【2.コーディング規約】
SQL(INSERT)を記述する際は、保守性の向上を考慮に入れ、以下の形式にのっとり記述を行うこととする。
I
N
S
I
E
N
R
T
T
O
INSERT文
V
A
L
U
E
S
/
T
(
(
*
+
A B
F I
F I
値 ,
値 )
H
H
L
E
E
I
I
E
L
L
N T
N T
名
D 名
D 名
;
1
2
*
/
,
)
/
/
* 列 名 の 説 明 *
* 列 名 の 説 明 *
/
/
※DMLコマンドと、次文字列間は必ず3バイトSPACEをあけること。
※VALUES句の挿入項目には必ず、コメントにて列名を記述する。
※挿入先列名は必ず記述すること
[Confidential]
- 9 / 35 -
bitWallet, Inc.
SQL開発標準書
3.SQLの記述方法 【2.コーディング規約】
SQL(DELETE)を記述する際は、保守性の向上を考慮に入れ、以下の形式にのっとり記述を行うこととする。
D
E
DELETE文
W
L
F
H
E
R
E
T
O
R
E
M
E
/
T
(
*
+
A B
条 件
H
H
L
)
I
I
E
;
N
N
名
T
T
1
2
*
/
※DMLコマンドと、次文字列間は必ず3バイトSPACEをあけること。
※条件記述時は必ず、HINT句を記述すること
[Confidential]
- 10 / 35 -
bitWallet, Inc.
SQL開発標準書
3.SQLの記述方法 【3.条件句記述】
SQLにて条件句を記述する際は、以下の通り記述を行うものとする。
①比較演算子(「=」「<」「>」)を記述する際は、以下の条件に則り記述を行うこと。
・否定条件を記述する際は、下記条件を満たしたものに限り、使用を可能とする。
1.否定検索対象列にINDEXが付与されていない場合は、予めINDEXの付与された列にて範囲を限定した結果に対し実施すること
期待する実行計画
SELECT /*+ INDEX (A BC_MST_CARD_P1) */
A.EDY_NO FROM BC_MST_CARD A
WHERE A.EDY_NO >= 1003200100000300 AND A.EDY_NO<= 1003200100000360
AND A.REG_DATE != 20030731
call
count
------- -----Parse
1
Execute
1
Fetch
6
------- -----total
8
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.01
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
21
0
-------- ---------- ---------- ---------- ---------0.00
0.01
0
21
0
rows
---------0
0
61
---------61
Rows
Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT GOAL: CHOOSE
61 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'BC_MST_CARD'
62
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'BC_MST_CARD_P1‘
(UNIQUE)
※絞込み条件列「A.EDY_NO」には、「UNIQUEINDEX BC_MST_CARD_P1」が付与されている。
[Confidential]
- 11 / 35 -
bitWallet, Inc.
3.SQLの記述方法 【3.条件句記述】
SQL開発標準書
2.否定検索対象列にINDEXが付与されており且つHINT句が記述されている場合、否定検索記述を可とする。
期待される実行計画
********************************************************************************
SELECT /*+ INDEX (A BC_MST_CARD_P1) */
A.EDY_NO
FROM BC_MST_CARD A
WHERE A.EDY_NO != 1003200100000354
call
count
cpu
elapsed
disk
query
current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------Parse
1
0.00
0.01
0
0
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch
142552
6.83
6.41
5966
148669
0
2138255
------- ------ -------- ---------- ---------- ---------- ---------- ---------total
142554
6.83
6.42
5966
148669
0
2138255
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 90 (EDYMDSB_OWR1)
Rows
Row Source Operation
------- --------------------------------------------------2138255 INDEX FULL SCAN (object id 174021)
Rows
Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT
GOAL: CHOOSE
2138255 INDEX
GOAL: ANALYZED (FULL SCAN) OF 'BC_MST_CARD_P1' (UNIQUE)
********************************************************************************
※絞込み条件列「A.EDY_NO」には、「UNIQUEINDEX BC_MST_CARD_P1」が付与されている。
[Confidential]
- 12 / 35 -
bitWallet, Inc.
3.SQLの記述方法 【3.条件句記述】
SQL開発標準書
・比較演算子を用いる際は、演算子の左辺に算術記号を用いることを禁止とする。
期待する実行計画
********************************************************************************
SELECT /*+ INDEX (A BC_MST_CARD_P1) */
A.EDY_NO
FROM BC_MST_CARD A
WHERE
A.EDY_NO-4
=1003200100000354
call
count
cpu
elapsed
disk
query
current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------Parse
1
0.00
0.00
0
0
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch
2
3.04
3.06
0
5983
0
1
------- ------ -------- ---------- ---------- ---------- ---------- ---------total
4
3.04
3.06
0
5983
0
1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 90 (EDYMDSB_OWR1)
Rows
Row Source Operation
------- --------------------------------------------------1 INDEX FULL SCAN (object id 174021)
Rows
Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT
GOAL: CHOOSE
1 INDEX
GOAL: ANALYZED (FULL SCAN) OF 'BC_MST_CARD_P1' (UNIQUE)
********************************************************************************
※絞込み条件列「A.EDY_NO」には、「UNIQUEINDEX BC_MST_CARD_P1」が付与されている。
左辺に算術演算子を用いたケース(INDEX HINT)を指定したことにより “INDEX FULLSCAN” にてアクセスパスを決定している。
代替方式として以下のように右辺に算術演算子を用いることにより、INDEXRANGESCAN方式となり、パフォーマンスの向上が
見込める為、Edy System開発標準では左辺に算術演算子を使用することは禁止とする。
[Confidential]
- 13 / 35 -
bitWallet, Inc.
3.SQLの記述方法 【3.条件句記述】
SQL開発標準書
期待する実行計画
********************************************************************************
SELECT /*+ INDEX (A BC_MST_CARD_P1) */
A.EDY_NO
FROM BC_MST_CARD A
WHERE A.EDY_NO=
1003200100000350+4
call
count
cpu
elapsed
disk
query
current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------Parse
1
0.00
0.00
0
0
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch
2
0.00
0.00
0
3
0
1
------- ------ -------- ---------- ---------- ---------- ---------- ---------total
4
0.00
0.00
0
3
0
1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 90 (EDYMDSB_OWR1)
Rows
Row Source Operation
------- --------------------------------------------------1 INDEX UNIQUE SCAN (object id 174021)
Rows
Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT
GOAL: CHOOSE
1 INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'BC_MST_CARD_P1' (UNIQUE)
********************************************************************************
※絞込み条件列「A.EDY_NO」には、「UNIQUEINDEX BC_MST_CARD_P1」が付与されている。
右辺に算術演算子を用いたケースINDEXHINTを指定したことにより “INDEX RANGE SCAN” にてアクセスパスを決定している。
[Confidential]
- 14 / 35 -
bitWallet, Inc.
3.SQLの記述方法 【3.条件句記述】
SQL開発標準書
②論理演算子を記述する場合、以下の条件に則り記述を行うこと。
・AND論理演算子
⇒ 特に制限を設けない。
・OR論理演算子
⇒ OR句における検索条件記述時予めINDEXの付与された列にて範囲を限定した結果に対し実施する場合、
特に問い合わせ制限は設けない。
期待する実行計画
********************************************************************************
SELECT /*+ INDEX (A BC_MST_CARD_P1) */
A.EDY_NO
FROM BC_MST_CARD A
WHERE A.EDY_NO>=1001950200000296 AND A.EDY_NO<=1001950200000400
OR A.EDY_NO=1001950200000255 OR A.EDY_NO=1001950200000300 OR A.EDY_NO=1001950200000399
call
count
cpu
elapsed
disk
query
current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------Parse
1
0.00
0.02
0
42
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch
8
0.00
0.00
0
19
0
104
------- ------ -------- ---------- ---------- ---------- ---------- ---------total
10
0.00
0.02
0
61
0
104
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 90 (EDYMDSB_OWR1)
Rows
Row Source Operation
------- --------------------------------------------------104 CONCATENATION
3 INLIST ITERATOR
3
INDEX RANGE SCAN (object id 174021)
101 INDEX RANGE SCAN (object id 174021)
Rows
Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT GOAL: CHOOSE
104 CONCATENATION
3
INLIST ITERATOR
3
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'BC_MST_CARD_P1'
(UNIQUE)
101
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'BC_MST_CARD_P1'
(UNIQUE)
********************************************************************************
※絞込み条件列「A.EDY_NO」には、「UNIQUEINDEX BC_MST_CARD_P1」が付与されている。
[Confidential]
- 15 / 35 -
bitWallet, Inc.
3.SQLの記述方法 【3.条件句記述】
SQL開発標準書
・前項条件に合致しないケースでは、レスポンスの確保(INDEXの使用)を条件とし、且つOR演算子による問い合わせ数を3項目までに限定する。
期待する実行計画
********************************************************************************
SELECT /*+ INDEX (A BC_MST_CARD_P1) */
A.EDY_NO
FROM BC_MST_CARD A
WHERE A.EDY_NO=1001950200000296 OR A.EDY_NO=1001950200000299 OR A.EDY_NO=1001950200000300
call
count
cpu
elapsed
disk
query
current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------Parse
1
0.00
0.03
0
0
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch
2
0.00
0.00
0
10
0
3
------- ------ -------- ---------- ---------- ---------- ---------- ---------total
4
0.00
0.03
0
10
0
3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 90 (EDYMDSB_OWR1)
Rows
Row Source Operation
------- --------------------------------------------------3 INLIST ITERATOR
3 INDEX RANGE SCAN (object id 174021)
Rows
Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT GOAL: CHOOSE
3 INLIST ITERATOR
3
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'BC_MST_CARD_P1'
(UNIQUE)
********************************************************************************
※絞込み条件列「A.EDY_NO」には、「UNIQUEINDEX BC_MST_CARD_P1」が付与されている。
[Confidential]
- 16 / 35 -
bitWallet, Inc.
3.SQLの記述方法 【3.条件句記述】
SQL開発標準書
・NOT
⇒ 否定条件を記述する際は、下記条件を満たしたものに限り、使用を可能とする。
1.否定検索対象列にINDEXが付与されていない場合は、予めINDEXの付与された列にて範囲を限定した
結果に対し実施すること
期待する実行計画
********************************************************************************
SELECT /*+ INDEX_FFS (A BC_MST_CARD_P1) */
A.EDY_NO
FROM BC_MST_CARD A
WHERE A.EDY_NO NOT IN (1001950200000296,1003200100000354,1001950200000297)
call
count
cpu
elapsed
disk
query
current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------Parse
1
0.00
0.01
0
0
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch 142552
6.31
6.97
1
148521
4
2138253
------- ------ -------- ---------- ---------- ---------- ---------- ---------total 142554
6.31
6.98
1
148521
4
2138253
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 90 (EDYMDSB_OWR1)
Rows
Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT GOAL: CHOOSE
0 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF 'BC_MST_CARD_P1'
(UNIQUE)
********************************************************************************
※絞込み条件列「A.EDY_NO」には、「UNIQUEINDEX BC_MST_CARD_P1」が付与されている。
[Confidential]
- 17 / 35 -
bitWallet, Inc.
3.SQLの記述方法 【3.条件句記述】
SQL開発標準書
③文字演算子
・BETWEENによる範囲検索は禁止とする。範囲検索を行う際は、「>=」・「<=」にて代用することとする。
※BETWEEN・「>=」・「<=」伴に、INDEXは使用されるが、保守時の視認性確保を考慮し、BETWEENの使用は禁止とする。
期待する実行計画
********************************************************************************
SELECT /*+ INDEX (A BC_MST_CARD_P1) */
A.EDY_NO
FROM BC_MST_CARD A
WHERE A.EDY_NO>=1003200100000354 AND A.EDY_NO<=1003200100000400
call
count
cpu
elapsed
disk
query
current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------Parse
1
0.01
0.00
0
0
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch
5
0.00
0.00
0
8
0
47
------- ------ -------- ---------- ---------- ---------- ---------- ---------total
7
0.01
0.00
0
8
0
47
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 90 (EDYMDSB_OWR1)
Rows
Row Source Operation
------- --------------------------------------------------47 INDEX RANGE SCAN (object id 174021)
Rows
Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT GOAL: CHOOSE
47 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'BC_MST_CARD_P1' (UNIQUE)
********************************************************************************
※絞込み条件列「A.EDY_NO」には、「UNIQUEINDEX BC_MST_CARD_P1」が付与されている。
[Confidential]
- 18 / 35 -
bitWallet, Inc.
3.SQLの記述方法 【3.条件句記述】
SQL開発標準書
・LIKEにおける検索を行う際は、原則として以下の条件時のみ使用を可能とする。
(a)「検索文字列%」のような前方一致の問い合わせは使用可とする。
(b)「%検索文字列%」といった中間一致に関する問い合わせは、下記制約(※)を満たしていることを条件に使用を許可する。
(c) 「%検索文字列」といった後方一致に関する問い合わせは、下記制約(※)を満たしていることを条件に使用を許可する。
※ 文字列検索対象列にINDEXが付与されている場合は、中間一致・後方一致検索の使用を許可する。
期待する実行計画(後方一致・中間一致問い合わせ時)
********************************************************************************
SELECT /*+ INDEX (A BC_MST_CARD_P1) */
A.EDY_NO
FROM BC_MST_CARD A
WHERE A.EDY_NO LIKE '%0354'
call
count
cpu
elapsed
disk
query
current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------Parse
1
0.01
0.00
0
0
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch
18
42.25
45.06
0
6015
0
245
------- ------ -------- ---------- ---------- ---------- ---------- ---------total
20
42.26
45.06
0
6015
0
245
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 90 (EDYMDSB_OWR1)
Rows
Row Source Operation
------- --------------------------------------------------245 INDEX FULL SCAN (object id 174021)
Rows
Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT GOAL: CHOOSE
245 INDEX GOAL: ANALYZED (FULL SCAN) OF 'BC_MST_CARD_P1' (UNIQUE)
********************************************************************************
※絞込み条件列「A.EDY_NO」には、「UNIQUEINDEX BC_MST_CARD_P1」が付与されている。
[Confidential]
- 19 / 35 -
bitWallet, Inc.
3.SQLの記述方法 【3.条件句記述】
SQL開発標準書
・IN句における検索を行う際は、原則として以下の条件時のみ使用を可能とする。
⇒ IN句の変数は、最大3項目までとする。
期待する実行計画
SELECT /*+ INDEX (A BC_MST_CARD_P1) */
A.CARD_ID,A.NEG_FLG
FROM BC_MST_CARD A
WHERE A.EDY_NO IN (1003200100000354,1003200100000350,1003200100000400)
call count
cpu elapsed
disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------Parse
1 0.00
0.03
0
0
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch
2 0.00
0.00
0
13
0
3
------- ------ -------- ---------- ---------- ---------- ---------- ---------total
4
0.00
0.03
0
13
0
3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 90 (EDYMDSB_OWR1)
Rows Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT GOAL: CHOOSE
3 INLIST ITERATOR
3 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'BC_MST_CARD'
6 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'BC_MST_CARD_P1'
(UNIQUE)
※絞込み条件列「A.EDY_NO」には、「UNIQUEINDEX BC_MST_CARD_P1」が付与されている。
[Confidential]
- 20 / 35 -
bitWallet, Inc.
SQL開発標準書
3.SQLの記述方法 【4.表結合】
表結合を実施する際は、以下の条件に則り結合を実施すること。
・表結合の対象とする表は最大3表までとする。
・結合キーには、UNIQUE INDEX 又は、絞込みの結果一意となるINDEXが付与されている列にて実施することとする。
・結合順序は、Hint句「ORDERED」を用い、結合順序を固定化すること(From以降の記述順序が結合順序となる)
・結合を行う際、使用INDEXを固定化するため、INDEX Hintの使用を必須とする。
期待する実行計画
******************************************************************************
******************************************************************************
SELECT /*+ ORDERED
------- ---------------------------------------------------
INDEX (A BC_LOG_DET_X3)
1350 NESTED LOOPS
INDEX (B BC_MST_CARD_P1)
271
INDEX (C BC_LOG_CHK_X3)
*/
A.EDY_NO,
NESTED LOOPS
271
INDEX RANGE SCAN (object id 1406154)
540
TABLE ACCESS BY INDEX ROWID BC_MST_CARD
B.CARD_IDM,
540
C.CARD_SEQ
1350
INDEX UNIQUE SCAN (object id 174021)
INDEX RANGE SCAN (object id 1342800)
FROM BC_LOG_DET A,BC_MST_CARD B,BC_LOG_CHK C
Rows
WHERE A.EDY_NO=B.EDY_NO AND B.EDY_NO=C.EDY_NO AND A.EDY_NO=1003200100000354
------- ---------------------------------------------------
call
count
cpu
elapsed
disk
query
current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Execution Plan
0 SELECT STATEMENT
1350
Parse
1
0.01
0.02
0
0
0
0
271
Execute
1
0.00
0.00
0
0
0
0
271
INDEX
91
0.01
0.00
0
2074
0
1350
540
TABLE ACCESS
Fetch
NESTED LOOPS
------- ------ -------- ---------- ---------- ---------- ---------- ---------total
93
0.02
0.02
0
2074
0
1350
Misses in library cache during parse: 1
GOAL: ANALYZED (RANGE SCAN) OF 'BC_LOG_DET_X3'
(UNIQUE)
GOAL: ANALYZED (BY INDEX ROWID) OF
'BC_MST_CARD'
540
1350
Optimizer goal: CHOOSE
GOAL: CHOOSE
NESTED LOOPS
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'BC_MST_CARD_P1' (UNIQUE)
INDEX (RANGE SCAN) OF 'BC_LOG_CHK_X3' (NON-UNIQUE)
********************************************************************************
Parsing user id: 90 (EDYMDSB_OWR1)
Rows
Row Source Operation
********************************************************************************
※絞込み条件列「A.EDY_NO」には、「UNIQUEINDEX BC_MST_CARD_P1」が付与されている。
[Confidential]
- 21 / 35 -
bitWallet, Inc.
3.SQLの記述方法 【5.Hint句】
SQL開発標準書
SQLを記述する際は、保守性の向上を考慮に入れ、以下の形式で記述をすることとする。
① SELECT文を使用する時は必ずヒント文を使用する。(副問い合わせを含む)
② 表結合(インラインビュー、セミジョインを除く)を使用する場合は、必ずORDERDヒントを使用する。
[Confidential]
- 22 / 35 -
bitWallet, Inc.
3.SQLの記述方法 【5.Hint句】
SQL開発標準書
①INDEXヒント
・機能
(指定した)索引を強制的に使用させる。
・記述方法
SELECT
/*+ INDEX(表(別)名またはシノニム名, [索引名]) */
項目名1, 項目名2, ・・・・
FROM
表名
・記述例
書き方1:SELECT /*+ INDEX(EMP, DEPT_IDX) */ DEPT FROM EMP
書き方2:SELECT /*+ INDEX(A, DEPT_IDX) */ DEPT FROM EMP A
書き方3:SELECT /*+ INDEX(A) */ DEPT FROM EMP A
・注意点など
□表(シノニム)名は必ず指定する必要があります。
□書き方2のように表に別名を指定する場合は、ヒント文中の表名も必ず別名を使用します。
例えば、書き方2を以下のように書き換えるとヒント文が無視されます。
SELECT
FROM
/*+ INDEX(EMP, DEPT_IDX) */
DEPT
EMP A
□書き方3は表名のみを指定する記述方法です。
このような記述をした場合は、オプティマイザが『最適と判断した』索引を使用します。
この記述方法を用いた場合は意図した索引を使用するとは限りませんので注意が必要です。
□本標準では、書き方2を推奨し、書き方3は使用禁止とします。
[Confidential]
- 23 / 35 -
bitWallet, Inc.
SQL開発標準書
3.SQLの記述方法 【5.Hint句】
②AND_EQUALヒント
・機能
複数の索引を使用して問い合わせを実行する。
・記述方法
SELECT
/*+ AND_EQUAL(表(別)名またはシノニム名, 索引名1, 索引名2, [索引名3]・・・・) */
項目名1, 項目名2, ・・・・
FROM 表名
・記述例
・注意点など
SELECT /*+ AND_EQUAL(A, DEPT_IDX, JOB_IDX) */ DEPT FROM EMP A
□表(シノニム)名は必ず指定する必要があります。
□索引は5個まで指定する事が出来ます。
・効果が期待できる状況
WHERE文の中にINDEX項目が2個以上あり、それらの条件がANDで結合されていること。
ex)
SELECT ITEM_1, ITEM_2, ITEM_3
[Confidential]
FROM
TABLE_X A
WHERE
ITEM_4='ABC'
AND
ITEM_5='XYZ'
ITEM_4='ABC'
ITEM_5='XYZ'
この重なりが少ないほど効果が
期待できます。
- 24 / 35 -
bitWallet, Inc.
3.SQLの記述方法 【5.Hint句】
SQL開発標準書
③INDEX_FFSヒント
・機能
(指定した)索引に対して高速全索引走査を行う。
・記述方法
SELECT
/*+ INDEX_FFS(表(別)名またはシノニム名, 索引名) */
項目名1, 項目名2, ・・・・
FROM
表名
・記述例
SELECT /*+ INDEX_FFS(A, DEPT_JOB_IDX) */ DEPT FROM EMP A WHERE A.JOB='SALES'
SELECT /*+ INDEX_FFS(A, EMP_P_KEY) */ COUNT(*) FROM EMP A
・注意点など
□表(シノニム)名は必ず指定する必要があります。
・効果が期待できる状況
□複合列索引の先頭以外の項目のみで検索する場合。
□表全体に対してCOUNTを行う場合。(プライマリキーが存在する場合))
□索引列に対して否定検索を行う場合
[Confidential]
- 25 / 35 -
bitWallet, Inc.
3.SQLの記述方法 【5.Hint句】
SQL開発標準書
④FIRST_ROWSヒント
・機能
最高の応答時間を実現するように実行計画を作成する。OLTPに適している。
・記述方法
SELECT
/*+ FIRST_ROWS */
項目名1, 項目名2, ・・・・
FROM
表名
・記述例
SELECT /*+ FIRST_ROWS */ DEPT FROM EMP A WHERE A.JOB='SALES'
・注意点など
□大量のデータを処理するバッチプログラムなどではALL_ROWS(Edyシステムのデフォルト)の方が高速です。
・効果が期待できる状況
□オンラインプログラムで問い合わせのレスポンスを向上させたい時。
[Confidential]
- 26 / 35 -
bitWallet, Inc.
SQL開発標準書
3.SQLの記述方法
⑤ORDEREDヒント
・機能
駆動表、表の結合順序を指定する。
・記述方法
SELECT
/*+ ORDERED */
項目名1, 項目名2, ・・・・
FROM
表名1,
表名2・・・・
・記述例
SELECT
FROM
/*+ ORDERED */ A.EDY_NO
BC_TRN_EDY_NO_CHK B,
BC_MST_CARD A
WHERE
A.EDY_NO = B.EDY_NO
・注意点など
□FROM句の後に記述した順番で結合されます。
□表の大小ではなく、選択される行が少ない表を駆動表にするとレスポンスが向上します。
・効果が期待できる状況
□大きさが大きく異なるテーブルを結合する時。
□多くのテーブルを結合する時。(分析時間が節約できる)
[Confidential]
- 27 / 35 -
bitWallet, Inc.
SQL開発標準書
3.SQLの記述方法
⑦APPENDヒント
・機能
ダイレクトロードインサートを行う。
・記述方法
INSERT
/*+ APPEND */
INTO
表名1
SELECT
項目名1, 項目名2, ・・・・
FROM
表名2
・記述例
INSERT
/*+ APPEND */
INTO
EMP2
SELECT
*
FROM
EMP1
・注意点など
□バッファキャッシュを使用しないで、ダイレクトに データファイルへの書き込みを行うため、INSERTに
かかる時間が短縮されます。
□表に対する排他ロックが取得されるため、INSERTが終了するまでは、 他のトランザクションによる
INSERT/UPDATE/DELETE文は同時に実行できません。
□INSERT~SELECT構文でのみ使用できます。INSERT~VALUES構文では使用できません。
□参照整合性はサポートされません。
・効果が期待できる状況
□既存表から列を選択し、新しく表を作る場合。
[Confidential]
- 28 / 35 -
bitWallet, Inc.
SQL開発標準書
関数を記述する際は、保守性の向上を考慮に入れ、以下の形式にのっとり記述を行うこととする。
①WHERE句の左辺で関数及び演算子をインデックス項目に処理した場合、インデックスが使用されないため、関数及び
演算子は右辺に記述することとする。(インデックス項目以外は、この限りではない)
Ex)
SELECT
FROM
ENAME
EMP
WHERE
TO_CHAR(HIREDATE ,’YYMMDD’) = ‘030901’
SELECT
ENAME
→
誤
→
正
Ex)
FROM
WHERE
EMP
HIREDATE
=
TO_DATE(‘030901’,’YYMMDD’)
②日付関数で使用する、日付書式モデルにより戻り値が異なる場合があるため注意すること。
1) TO_DATE、TO_CHARを使用する場合、日付フォーマットにより結果が異なる。
⇒ 日付フォーマット YYYYMMDDとIYYYMMDDでは結果が異なる場合がある。
(IYYYは原則として禁止する)
2) ROUND、 TRUNCで日付操作を行う場合、使用可能な日付書式モデルに注意が必要である。
[Confidential]
- 29 / 35 -
bitWallet, Inc.
SQL開発標準書
3.SQLの記述方法 【6.関数】
ORACLE 関数例(使用方法等詳細は、oracle社マニュアル(SQLリファレンス)を参照)
区 分
値変更
文字列操作
項番
関 数 名
内
容
備
考
1
LOWER
文字列を小文字にする。
SELECT LOWER('AbCdEf')
FROM DUAL;
2
UPPER
文字列を大文字にする。
SELECT UPPER('AbCdEf')
FROM DUAL;
3
DECODE
式の値に対応する値を返す。
SELECT DECODE(job, ‘CLERK’, ‘事務員’,
'SALESMAN', '営業','その他') FROM EMP;
4
NVL
NULL値を他の値に変換する。
SELECT NVL(TO_CHAR(comm), 'N/A') FROM EMP;
5
TO_CHAR
文字列型に変換する。
SELECT TO_CHAR (SYSDATE, ‘YYYYMMDD')
FROM DUAL;
6
TO_NUMBER
整数型に変換する。
SELECT TO_NUMBER('1,000.00', '99G999D99')
FROM DUAL;
7
CONVERT
文字列を別の文字セットに変換する。
SELECT CONVERT('Ä Ê Í Õ Ø A B C D E ',
'US7ASCII', 'WE8ISO8859P1') FROM DUAL;
8
CONCAT
文字列を連結する。
SELECT CONCAT(CONCAT(‘東京’, ‘都’), ‘品川区')
FROM DUAL;
9
REPLACE
文字列を置換する。
SELECT REPLACE(‘EDY',‘E','ME') FROM DUAL;
10
TRIM
文字列の両端の空白(指定文字)を削除する。
SELECT TRIM(BOTH 'o' FROM 'oo1234oo')
FROM DUAL;
11
RTRIM
文字列の右側の空白(指定文字)を削除する。
SELECT RTRIM(‘EDY
12
LTRIM
文字列の左側の空白(指定文字)を削除する。
SELECT LTRIM(‘
[Confidential]
- 30 / 35 -
') FROM DUAL;
EDY') FROM DUAL;
bitWallet, Inc.
SQL開発標準書
3.SQLの記述方法 【6.関数】
区 分
項番
関 数 名
内
容
備
考
12
SUBSTR
文字数指定により文字列を切り出す。
SELECT SUBSTR('ABCD1234',2,4) FROM DUAL;
13
SUBSTRB
バイト指定により文字列を切り出す。
SELECT SUBSTRB('ABCDEFG',5,4) FROM DUAL;
14
NLS_LOWER
すべての文字を小文字に変換する。
SELECT
NLS_LOWER(‘EDY', 'NLS_SORT = XGerman')
FROM DUAL;
15
NLS_UPPER
すべての文字を大文字に変換する。
SELECT
NLS_LOWER(‘edy', 'NLS_SORT = XGerman')
FROM DUAL;
16
LENGTH
文字列に含まれる文字数を返す。
SELECT LENGTH('ABCあいう') FROMDUAL;
17
LENGTHB
文字列のバイト数を返す。
SELECT LENGTHB('ABCあいう') FROMDUAL;
18
ADD_MONTHS
月を加算する。
SELECT
TO_CHAR(ADD_MONTHS(hiredate,3), 'YYYY-MM-DD')
FROM EMP
文字列操作
検索
日付処理
19
MONTHS_BETWEEN
月の差を計算する。
SELECT
MONTHS_BETWEEN (TO_DATE('2002-10-07',
'YYYY-MM-DD'),
TO_DATE('1965-07-14','YYYY-MM-DD')) FROM DUAL;
20
SYSDATE
システムの現在時刻を取得する。
SELECT SYSDATE FROM DUAL;
21
TO_DATE
日付型に変換する。
SELECT TO_DATE('20021007') FROM DUAL;
22
ROUND
日付を指定した桁数に丸める。
SELECT ROUND(TO_DATE('2002-06-30'), 'YEAR')
FROM DUAL;
23
TRUNC
日付を指定の桁数に切り捨てる。
SELECT TRUNC(TO_DATE('2002-07-01'), 'YEAR')
FROM DUAL
[Confidential]
- 31 / 35 -
bitWallet, Inc.
SQL開発標準書
3.SQLの記述方法 【6.関数】
区 分
項番
関 数 名
内
容
備
考
24
ROUND
四捨五入を行った値を求める。
SELECT ROUND(1.123, 1) FROM DUAL;
25
TRUNC
切り捨てを行った値を求める。
SELECT TRUNC (1.123, 1) FROM DUAL;
26
CEIL
指定の数値以上の最小の整数を求める。
SELECT CEIL(1), FROM DUAL;
27
FLOOR
指定の数値以下の最大の整数を求める。
SELECT FLOOR(0) FROM DUAL;
28
COUNT
行数を求める。
SELECT COUNT(*) FROM EMP
29
MAX
最大値を求める。
SELECT MAX(SAL) FROM EMP
30
MIN
最小値を求める。
SELECT MIN(SAL) FROM EMP
31
AVG
平均値を求める。
SELECT AVG(SAL) FROM EMP
32
SUM
合計値を求める。
SELECT SUM(SAL) FROM EMP
33
USERENV
現行のセッションの情報を返す。
SELECT
USERENV('TERMINAL') FROM DUAL;
34
SQRT
平方根を求める。
SELECT SQRT(16) FROM DUAL;
数値桁処理
集約処理
統計処理
その他
本書に参考として関数を記述したが、他の関数も制限はなく使用可能とする。
[Confidential]
- 32 / 35 -
bitWallet, Inc.
SQL開発標準書
4.Traceの取得
Traceの取得を任意の時点で行えるよう、ProgramにはTraceを取得するロジックを記述することを必須とする。
但し、Traceを記述できないProgramについては、DBAに相談する事とする。
(記述例)
★<iniファイル読み出し設定>
★<iniファイルより取得したフラグを元にトレースのON、OFFを実施>
memset(cSection, '\0', sizeof(cSection));
switch( giTraceFlg )
memset(cKey, '\0', sizeof(cKey));
{
strcpy(cSection, (char*)ORA_TRACE);
case
1:
strcpy(cKey, (char*)TRACE_FLG);
EXEC SQL
memset(cBuffer, '\0', sizeof(cBuffer));
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
★<制御提供の読み出しAPI>
break;
csSetFile.GetInformation(cSection, cKey, cBuffer, sizeof(cBuffer));
case
★<iniファイル取得時のエラーハンドリング>
2:
EXEC
if ( cBuffer[0] == '\0' )
SQL
ALTER SESSION SET SQL_TRACE = TRUE;
{
break;
/*
INIファイル読出しエラーをLOG出力
*/
default:
csLog.Write( (char *)MSG_INIFILE_ERR, cSetupFileName, cSection, cKey );
/*
プログラム異常終了
*/
/*
異常終了LOGの出力
*/
vEnd_proc( CC_ERR );
return CC_NG;
break;
}
<iniファイル記述例>
[ORA_TRACE]
}
; ON=1(レベル12),2(通常トレース) OFF=0
giTraceFlg = atoi(cBuffer);
TRACE=0
[Confidential]
- 33 / 35 -
bitWallet, Inc.
SQL開発標準書
5.実行計画の指針
【問合せ時の実行計画】
問い合わせを実行する時は、以下の実行計画のいずれかが必ず実行されます。①が最も望ましい実行計画であり、以下②、③と
下がるに従い、実行時のレスポンスがデータ量などに左右されやすくなります。
①INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF ユニーク(プライマリ)索引名
索引のユニークスキャンです。この実行計画が出ればベストです。
②INDEX
GOAL: ANALYZED (RANGE SCAN) OF 索引名
索引のレンジスキャンです。選択される行数に注意してください。全体の1/3以上の行が選択されるような
問い合わせの実行時は、さらに条件を追加して選択される行数を絞り込む事が出来ないか検討してください。
全体の1/3以上の行が選択され、かつ、これ以上絞り込むことが出来ないのであれば、全表走査の方が
高速である可能性が高いです。FULLヒントの使用を検討してください。
③INDEX
GOAL: ANALYZED (FAST FULL SCAN) OF 索引名
高速全索引走査を行っています。INDEX_FFSヒントを使用していないにもかかわらず、この実行計画が現れた場合は、ユニークス
キャ
ンやレンジスキャンに変更可能か調査してください。索引列へのORやIN条件を指定している場合は、UNION ALL句(UNION演算子)
又は、USE_CONCATヒントを使用をする事により、ユニークスキャンやレンジスキャンに変更できる場合があります。
④INDEX
GOAL: ANALYZED (FULL SCAN) OF 索引名
全索引走査を行っています。INDEX_FFSヒントを使用すると高速全索引走査に変更できる可能性が高いです。
⑤TABLE ACCESS
GOAL: ANALYZED (FULL) OF 表名
全表走査を行っています。極力避けたい実行計画です。この実行計画が許されるのは、以下の場合のみとします。
・表が小さい(1M以内)
・WHERE句に条件を1つも書く事が出来ない。
・FULLヒントを指定している。
上記の条件に当てはまらない場合は、索引の追加等の対策を検討してください。
[Confidential]
- 34 / 35 -
bitWallet, Inc.
SQL開発標準書
5.実行計画の指針
【表結合時の実行計画】
①HASH JOIN
ハッシュ結合です。対処の必要はありません。
ハッシュ結合は駆動表をメモリに読み込むので、駆動表がハッシュ領域に収まる場合は高速に動作します。しかし、ハッシュ領域に
収まらない場合は、メモリへ読み込みが複数回発生するので、ネステッドループ結合よりも遅くなる事があります。
よって、本標準ではUSE_HASHヒントを用いての強制ハッシュ結合は禁止とします。オプティマイザが自動的に選択した場合は
使用可能です。
②NESTED LOOPS
ネステッドループ結合です。対処の必要はありません。
ネステッドループ結合は、最も基本的な表結合の方法です。駆動表から(可能であれば索引を使用して)行にアクセスし、
もう一方の表に索引を使用してアクセスし、結果セットを作成します。結合時のキーは、駆動表側は索引である必要は
ありませんが、それ以外の表は索引である必要があります。
③SORT JOIN/MERGE JOIN
ソートマージ結合です。非駆動表に索引を追加する事または、駆動表の変更を検討してください。
ソートマージ結合は両方の表にフルテーブルスキャンでアクセスした後、ソートを行い、その結果をマージして結果セットを
作成します。この結合方式は、結合時に使用できる索引が無い時に良く現れます。
ソート/マージ結合が許されるのは、以下の場合のみとします。
・結合する両方の表から殆どの行が選択される。
④MERGE JOIN (CARTESIAN)
ソートマージ結合の一種で、デカルトマージ結合です。通常は、結合キーを指定しないで複数の表に問い合わせを実行した時
(直積結合)に現れる実行計画です。この結合は、表に対してデカルト演算を実行します。データ量が増加するに従い、加速
度的にレスポンスが悪化します。あらゆる手段を用いて、この結合方法を回避してください。例外はありません。
[Confidential]
- 35 / 35 -
bitWallet, Inc.
SQL開発標準書
5.実行計画の指針
【その他】
①SORT UNIQUE
SORTを行い、更に結果セットから重複行を取り除いている事を示しています。
DISTINCT句を使用した場合やUNION演算子を指定した場合に現れます。
DISTINCT句を指定している場合、本当に必要かを確認してください。結果セットに重複行が有り得ないのに、念のために
指定しているような事がないようにしてください。
UNION演算子を指定している場合、結果セットがソートされている必要がある場合と、重複行を取り除く必要がある場合以外は、
UNION_ALL句で代用できます。UNION_ALLに変更した場合、大幅にレスポンスが向上する可能性があります。
②SORT ORDER BY/SORT GROUP BY
ORDER BY句/GROUP BY句を指定した事によりSORTを実行している事を示しています。この実行計画自体に問題はありません。
しかし、複数個出てきた場合は以下の点を確認してください。
□GROUP BYで指定している項目をORDER BYにも指定していないか?
GROUP BY句だけでソートが実行されます。(降順にソートしたい場合を除く。)
□UNION演算子の前後の問い合わせでORDER BY句が指定されていないか?
UNION演算子により、結果セットの1番目の列でソートされます。不要なソートは避けましょう。
[Confidential]
- 36 / 35 -
bitWallet, Inc.
ダウンロード

document