1 / 36

SQL 開発標準書 【 0.9 版 】

表紙. SQL 開発標準書 【 0.9 版 】. ２００３年 １０月 ７日 　　ビットワレット株式会社. 変更履歴. Contents. １． SQL 開発標準書の目的 ・・・・・・・ ４ ２． Oracle 運用標準について ・・・・・・・ ５ ３． SQL コーディング規約 3-1 ．記述文字タイプ ・・・・・・・ ６ 3-2 ．コーディング規約 ・・・・・・・ ７ 3-3 ．条件句記述 ・・・・・・・ １１ 3-4 ．表結合 ・・・・・・・ ２１ 3-5 ． Hint 句 ・・・・・・・ ２２

keiran
Download Presentation

SQL 開発標準書 【 0.9 版 】

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 表紙 SQL 開発標準書 【0.9 版 】 ２００３年 １０月 ７日 　　ビットワレット株式会社

  2. 変更履歴

  3. Contents １．SQL開発標準書の目的 ・・・・・・・ ４ ２．Oracle運用標準について ・・・・・・・ ５ ３．SQLコーディング規約 3-1．記述文字タイプ ・・・・・・・ ６ 3-2．コーディング規約 ・・・・・・・ ７ 3-3．条件句記述 ・・・・・・・ １１ 3-4．表結合 ・・・・・・・ ２１ 3-5．Hint句 ・・・・・・・ ２２ 3-6．関数 ・・・・・・・ ２８ ４．Traceの取得 ・・・・・・・ ３２ ５．実行計画の指針 ・・・・・・・ ３３

  4. １．SQL開発標準書の目的 EdyシステムDataBase運用規約に準拠したSQL開発標準を本書に示す。 ・Edy System DataBase運用規約において、Optimaize ModeはCost Base Optimaizerを採用する。 Cost Base Optimaizerは、統計情報取得タイミングにより、予測不可能な挙動を起こすケースが 　散見される。従って、Cost Base Optimaizerにて運用を行う際はこれら予測不能な挙動は 　抑止する必要性がある。 　本書は、これら予測不能な挙動を抑止する方法をまとめたものであり、開発を行う際は本書に 　記載された規約に従い開発を行うものとする。 ・Applicationの保守容易性の向上を図るための指針を示す。 ・Oracleの動作特性に合致するSQL構文を記述することとする。 ・本書に記述されていない機能等については、DBAに相談して使用有無を判断する。 DBAが使用許可した機能等については、本書に追加する事とする。

  5. ２．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を記述する際は、問い合わせ表の特性を考慮すること

  6. ３．SQLの記述方法　【１．記述文字タイプ】 ①SQLは全て大文字で記述する。 　　（Oracleは、大文字／小文字が混在すると実行前に分析を行う。処理時間に影響する範囲では無いが、 　　　最適化を考慮して大文字とする） ②問い合わせを行う際は、「＊」にて項目選択行わない事とする。 ③問い合わせ表が１表であった場合でも必ず、別名を記述することとする。 　　　（１表でも別名にするのは、Hint句等の記述を容易にする為） ※別名は、「A」・「B」・「C」とアルファベット順にて記述する。 ex)Selecta.Edy_Term_Idfrombc_Log_deta　 ⇒　誤 ex)SELECTA.EDY_TERM_ID FROMBC_LOG_DETA ⇒　正

  7. ３．SQLの記述方法　【２．コーディング規約】３．SQLの記述方法　【２．コーディング規約】 SQL（SELECT）を記述する際は、保守性の向上を考慮に入れ、以下の形式にのっとり記述を行うこととする。 ※DMLコマンドと、次文字列間は必ず３バイトSPACEをあけること。　　　　 ※条件句を記述する際は必ずHINT句は必ず記述すること。 ※射影対象列には、任意でコメントを追加することができる。 ※問い合わせTBLには必ず別名を定義する。

  8. ３．SQLの記述方法　【２．コーディング規約】３．SQLの記述方法　【２．コーディング規約】 SQL（UPDATE）を記述する際は、保守性の向上を考慮に入れ、以下の形式にのっとり記述を行うこととする。 ※DMLコマンドと、次文字列間は必ず３バイトSPACEをあけること。 ※更新対象列には、任意でコメントを追加することができる。 ※条件句を記述する際は必ずHINT句を記述すること

  9. ３．SQLの記述方法　【２．コーディング規約】３．SQLの記述方法　【２．コーディング規約】 SQL（INSERT）を記述する際は、保守性の向上を考慮に入れ、以下の形式にのっとり記述を行うこととする。 ※DMLコマンドと、次文字列間は必ず３バイトSPACEをあけること。 ※VALUES句の挿入項目には必ず、コメントにて列名を記述する。 ※挿入先列名は必ず記述すること

  10. ３．SQLの記述方法　【２．コーディング規約】３．SQLの記述方法　【２．コーディング規約】 SQL（DELETE）を記述する際は、保守性の向上を考慮に入れ、以下の形式にのっとり記述を行うこととする。 ※DMLコマンドと、次文字列間は必ず３バイトSPACEをあけること。 ※条件記述時は必ず、HINT句を記述すること

  11. ３．SQLの記述方法　【３．条件句記述】 SQLにて条件句を記述する際は、以下の通り記述を行うものとする。 ①比較演算子（「＝」「＜」「＞」）を記述する際は、以下の条件に則り記述を行うこと。 　・否定条件を記述する際は、下記条件を満たしたものに限り、使用を可能とする。 1.否定検索対象列にINDEXが付与されていない場合は、予めINDEXの付与された列にて範囲を限定した結果に対し実施すること 期待する実行計画 SELECT /*+ INDEX (A BC_MST_CARD_P1) */ A.EDY_NOFROM BC_MST_CARD A WHERE A.EDY_NO >= 1003200100000300 AND A.EDY_NO<= 1003200100000360 AND A.REG_DATE != 20030731 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 6 0.00 0.00 0 21 0 61 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 8 0.00 0.01 0 21 0 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」には、「UNIQUEINDEXBC_MST_CARD_P1」が付与されている。

  12. ３．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」には、「UNIQUEINDEXBC_MST_CARD_P1」が付与されている。

  13. ３．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」には、「UNIQUEINDEXBC_MST_CARD_P1」が付与されている。 左辺に算術演算子を用いたケース（INDEX HINT）を指定したことにより 　“INDEX FULLSCAN”　にてアクセスパスを決定している。 代替方式として以下のように右辺に算術演算子を用いることにより、INDEXRANGESCAN方式となり、パフォーマンスの向上が 見込める為、Edy System開発標準では左辺に算術演算子を使用することは禁止とする。

  14. ３．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」には、「UNIQUEINDEXBC_MST_CARD_P1」が付与されている。 右辺に算術演算子を用いたケースINDEXHINTを指定したことにより　“INDEX RANGE SCAN”　にてアクセスパスを決定している。

  15. ３．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」には、「UNIQUEINDEXBC_MST_CARD_P1」が付与されている。

  16. ３．SQLの記述方法　【３．条件句記述】 　・前項条件に合致しないケースでは、レスポンスの確保（INDEXの使用）を条件とし、且つOR演算子による問い合わせ数を３項目までに限定する。 　　　　　　期待する実行計画 ******************************************************************************** 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」には、「UNIQUEINDEXBC_MST_CARD_P1」が付与されている。

  17. ３．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」には、「UNIQUEINDEXBC_MST_CARD_P1」が付与されている。

  18. ３．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」には、「UNIQUEINDEXBC_MST_CARD_P1」が付与されている。

  19. ３．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」には、「UNIQUEINDEXBC_MST_CARD_P1」が付与されている。

  20. ３．SQLの記述方法　【３．条件句記述】 ・IN句における検索を行う際は、原則として以下の条件時のみ使用を可能とする。 ⇒　IN句の変数は、最大３項目までとする。 期待する実行計画 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」には、「UNIQUEINDEXBC_MST_CARD_P1」が付与されている。

  21. ３．SQLの記述方法　【４．表結合】 表結合を実施する際は、以下の条件に則り結合を実施すること。 　　　・表結合の対象とする表は最大３表までとする。 　　　・結合キーには、UNIQUE INDEX 又は、絞込みの結果一意となるINDEXが付与されている列にて実施することとする。 　　　・結合順序は、Hint句「ORDERED」を用い、結合順序を固定化すること（From以降の記述順序が結合順序となる） 　　　・結合を行う際、使用INDEXを固定化するため、INDEXHintの使用を必須とする。 期待する実行計画 ****************************************************************************** SELECT /*+ ORDERED INDEX (A BC_LOG_DET_X3) INDEX (B BC_MST_CARD_P1) INDEX (C BC_LOG_CHK_X3)　　　　*/ A.EDY_NO, B.CARD_IDM, C.CARD_SEQ FROM BC_LOG_DET A,BC_MST_CARD B,BC_LOG_CHK C 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 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.02 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 91 0.01 0.00 0 2074 0 1350 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 93 0.02 0.02 0 2074 0 1350 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 90 (EDYMDSB_OWR1) Rows Row Source Operation ******************************************************************************** ****************************************************************************** ------- --------------------------------------------------- 1350 NESTED LOOPS 271 NESTED LOOPS 271 INDEX RANGE SCAN (object id 1406154) 540 TABLE ACCESS BY INDEX ROWID BC_MST_CARD 540 INDEX UNIQUE SCAN (object id 174021) 1350 INDEX RANGE SCAN (object id 1342800) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1350 NESTED LOOPS 271 NESTED LOOPS 271 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'BC_LOG_DET_X3' (UNIQUE) 540 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'BC_MST_CARD' 540 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'BC_MST_CARD_P1' (UNIQUE) 1350 INDEX (RANGE SCAN) OF 'BC_LOG_CHK_X3' (NON-UNIQUE) ******************************************************************************** ※絞込み条件列「A.EDY_NO」には、「UNIQUEINDEXBC_MST_CARD_P1」が付与されている。

  22. ３．SQLの記述方法　【５．Hint句】 SQLを記述する際は、保守性の向上を考慮に入れ、以下の形式で記述をすることとする。 ①SELECT文を使用する時は必ずヒント文を使用する。（副問い合わせを含む） ②　表結合（インラインビュー、セミジョインを除く）を使用する場合は、必ずORDERDヒントを使用する。

  23. ３．SQLの記述方法　【５．Hint句】 ①INDEXヒント ・機能 （指定した）索引を強制的に使用させる。 ・記述方法 SELECT /*+ INDEX(表(別)名またはシノニム名, [索引名]) */ 　　　　　項目名１, 項目名２, ・・・・　 FROM　　　表名 ・記述例 書き方１：SELECT /*+ INDEX(EMP, DEPT_IDX) */ DEPT FROM EMP 書き方２：SELECT /*+ INDEX(A, DEPT_IDX) */ DEPT FROM EMP A 書き方３：SELECT /*+ INDEX(A) */ DEPT FROM EMP A ・注意点など □表（シノニム)名は必ず指定する必要があります。 □書き方２のように表に別名を指定する場合は、ヒント文中の表名も必ず別名を使用します。 　例えば、書き方２を以下のように書き換えるとヒント文が無視されます。 SELECT /*+ INDEX(EMP, DEPT_IDX) */ DEPT FROM EMP A □書き方３は表名のみを指定する記述方法です。 　このような記述をした場合は、オプティマイザが『最適と判断した』索引を使用します。 　この記述方法を用いた場合は意図した索引を使用するとは限りませんので注意が必要です。 □本標準では、書き方２を推奨し、書き方３は使用禁止とします。

  24. ３．SQLの記述方法　【５．Hint句】 ②AND_EQUALヒント ・機能 複数の索引を使用して問い合わせを実行する。 ・記述方法 SELECT /*+ AND_EQUAL(表(別)名またはシノニム名, 索引名１, 索引名２, [索引名３]・・・・) */ 　　　　　　　項目名１, 項目名２, ・・・・　 FROM 　表名 ・記述例 SELECT /*+ AND_EQUAL(A, DEPT_IDX, JOB_IDX) */ DEPT FROM EMP A ・注意点など □表（シノニム)名は必ず指定する必要があります。 □索引は５個まで指定する事が出来ます。 ・効果が期待できる状況 WHERE文の中にINDEX項目が２個以上あり、それらの条件がANDで結合されていること。 ex) SELECT ITEM_1, ITEM_2, ITEM_3 FROM TABLE_X A WHERE ITEM_4='ABC' AND ITEM_5='XYZ' ITEM_5='XYZ' ITEM_4='ABC' この重なりが少ないほど効果が 期待できます。

  25. ３．SQLの記述方法　【５．Hint句】 ③INDEX_FFSヒント ・機能 （指定した）索引に対して高速全索引走査を行う。 ・記述方法 SELECT /*+ INDEX_FFS(表(別)名またはシノニム名, 索引名) */ 　　　　 項目名１, 項目名２, ・・・・　 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を行う場合。（プライマリキーが存在する場合)） □索引列に対して否定検索を行う場合

  26. ３．SQLの記述方法　【５．Hint句】 ④FIRST_ROWSヒント ・機能 最高の応答時間を実現するように実行計画を作成する。OLTPに適している。 ・記述方法 SELECT /*+ FIRST_ROWS */ 　　　 　項目名１, 項目名２, ・・・・　 FROM　 表名 ・記述例 SELECT /*+ FIRST_ROWS */ DEPT FROM EMP A WHERE A.JOB='SALES' ・注意点など □大量のデータを処理するバッチプログラムなどではALL_ROWS（Ｅｄｙシステムのデフォルト）の方が高速です。 ・効果が期待できる状況 □オンラインプログラムで問い合わせのレスポンスを向上させたい時。

  27. ３．SQLの記述方法 ⑤ORDEREDヒント ・機能 駆動表、表の結合順序を指定する。 ・記述方法 SELECT /*+ ORDERED */ 　　　　　　項目名１, 項目名２, ・・・・　 FROM　 　表名１， 　　　　　 表名２・・・・ ・記述例 SELECT /*+ ORDERED */ A.EDY_NO FROMBC_TRN_EDY_NO_CHK B, BC_MST_CARD A WHERE A.EDY_NO = B.EDY_NO ・注意点など □FROM句の後に記述した順番で結合されます。 □表の大小ではなく、選択される行が少ない表を駆動表にするとレスポンスが向上します。 ・効果が期待できる状況 □大きさが大きく異なるテーブルを結合する時。 □多くのテーブルを結合する時。（分析時間が節約できる）

  28. ３．SQLの記述方法 ⑦APPENDヒント ・機能 ダイレクトロードインサートを行う。 ・記述方法 INSERT /*+ APPEND */ INTO 表名１ SELECT 項目名１, 項目名２, ・・・・　 FROM　 表名２ ・記述例 INSERT /*+ APPEND */ INTO EMP2 SELECT * FROM EMP1 ・注意点など □バッファキャッシュを使用しないで、ダイレクトに データファイルへの書き込みを行うため、INSERTに 　かかる時間が短縮されます。 □表に対する排他ロックが取得されるため、INSERTが終了するまでは、 他のトランザクションによる INSERT/UPDATE/DELETE文は同時に実行できません。 □INSERT～SELECT構文でのみ使用できます。INSERT～VALUES構文では使用できません。 □参照整合性はサポートされません。 ・効果が期待できる状況 □既存表から列を選択し、新しく表を作る場合。

  29. 関数を記述する際は、保守性の向上を考慮に入れ、以下の形式にのっとり記述を行うこととする。関数を記述する際は、保守性の向上を考慮に入れ、以下の形式にのっとり記述を行うこととする。 ①WHERE句の左辺で関数及び演算子をインデックス項目に処理した場合、インデックスが使用されないため、関数及び 　　演算子は右辺に記述することとする。（インデックス項目以外は、この限りではない） Ex) SELECTENAME FROMEMP WHERETO_CHAR(HIREDATE ,’YYMMDD’)= 　‘030901’ 　 →　　　誤 Ex) SELECTENAME FROM EMP WHEREHIREDATE=TO_DATE(‘030901’,’YYMMDD’) 　→　　　正 ②日付関数で使用する、日付書式モデルにより戻り値が異なる場合があるため注意すること。 1) TO_DATE、TO_CHARを使用する場合、日付フォーマットにより結果が異なる。 　　　⇒　日付フォーマット　YYYYMMDDとIYYYMMDDでは結果が異なる場合がある。 （IYYYは原則として禁止する） 2) ROUND、 TRUNCで日付操作を行う場合、使用可能な日付書式モデルに注意が必要である。

  30. ３．SQLの記述方法　【６．関数】 ORACLE　関数例（使用方法等詳細は、oracle社マニュアル（SQLリファレンス）を参照）

  31. ３．SQLの記述方法　【６．関数】

  32. ３．SQLの記述方法　【６．関数】 本書に参考として関数を記述したが、他の関数も制限はなく使用可能とする。

  33. ４．Traceの取得 Traceの取得を任意の時点で行えるよう、ProgramにはTraceを取得するロジックを記述することを必須とする。 但し、Traceを記述できないProgramについては、DBAに相談する事とする。 （記述例） ★＜iniファイル読み出し設定＞ memset(cSection, '\0', sizeof(cSection)); memset(cKey, '\0', sizeof(cKey)); strcpy(cSection, (char*)ORA_TRACE); strcpy(cKey, (char*)TRACE_FLG); memset(cBuffer, '\0', sizeof(cBuffer)); ★＜制御提供の読み出しＡＰＩ＞ csSetFile.GetInformation(cSection, cKey, cBuffer, sizeof(cBuffer)); ★＜iniファイル取得時のエラーハンドリング＞ if ( cBuffer[0] == '\0' ) { /*INIファイル読出しエラーをLOG出力 */ csLog.Write( (char *)MSG_INIFILE_ERR, cSetupFileName, cSection, cKey ); /*　　　　　プログラム異常終了 */ /*　　　　　異常終了LOGの出力 */ vEnd_proc( CC_ERR ); return CC_NG; } giTraceFlg = atoi(cBuffer); ★＜iniファイルより取得したフラグを元にトレースのＯＮ、ＯＦＦを実施＞ switch( giTraceFlg ) { case 1: EXEC SQL ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; break; case 2: EXEC SQL ALTER SESSION SET SQL_TRACE = TRUE; break; default: break; } ＜iniファイル記述例＞ [ORA_TRACE] ; ON=1(レベル12),2（通常トレース） OFF=0 TRACE=0

  34. ５．実行計画の指針 【問合せ時の実行計画】 問い合わせを実行する時は、以下の実行計画のいずれかが必ず実行されます。①が最も望ましい実行計画であり、以下②、③と 下がるに従い、実行時のレスポンスがデータ量などに左右されやすくなります。 ①INDEX GOAL: ANALYZED (UNIQUE SCAN) OF ユニーク（プライマリ）索引名 索引のユニークスキャンです。この実行計画が出ればベストです。 ②INDEX GOAL: ANALYZED (RANGE SCAN) OF 索引名 索引のレンジスキャンです。選択される行数に注意してください。全体の１／３以上の行が選択されるような 問い合わせの実行時は、さらに条件を追加して選択される行数を絞り込む事が出来ないか検討してください。 全体の１／３以上の行が選択され、かつ、これ以上絞り込むことが出来ないのであれば、全表走査の方が 高速である可能性が高いです。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 表名 全表走査を行っています。極力避けたい実行計画です。この実行計画が許されるのは、以下の場合のみとします。 　　・表が小さい（１M以内） 　　・WHERE句に条件を１つも書く事が出来ない。 　　・FULLヒントを指定している。 上記の条件に当てはまらない場合は、索引の追加等の対策を検討してください。

  35. ５．実行計画の指針 【表結合時の実行計画】 ①HASH JOIN ハッシュ結合です。対処の必要はありません。 ハッシュ結合は駆動表をメモリに読み込むので、駆動表がハッシュ領域に収まる場合は高速に動作します。しかし、ハッシュ領域に 収まらない場合は、メモリへ読み込みが複数回発生するので、ネステッドループ結合よりも遅くなる事があります。 よって、本標準ではUSE_HASHヒントを用いての強制ハッシュ結合は禁止とします。オプティマイザが自動的に選択した場合は 使用可能です。 ②NESTED LOOPS ネステッドループ結合です。対処の必要はありません。 ネステッドループ結合は、最も基本的な表結合の方法です。駆動表から（可能であれば索引を使用して）行にアクセスし、 もう一方の表に索引を使用してアクセスし、結果セットを作成します。結合時のキーは、駆動表側は索引である必要は ありませんが、それ以外の表は索引である必要があります。 ③SORT JOIN／MERGE JOIN ソートマージ結合です。非駆動表に索引を追加する事または、駆動表の変更を検討してください。 ソートマージ結合は両方の表にフルテーブルスキャンでアクセスした後、ソートを行い、その結果をマージして結果セットを 作成します。この結合方式は、結合時に使用できる索引が無い時に良く現れます。 ソート／マージ結合が許されるのは、以下の場合のみとします。 　　　・結合する両方の表から殆どの行が選択される。 ④MERGE JOIN (CARTESIAN) ソートマージ結合の一種で、デカルトマージ結合です。通常は、結合キーを指定しないで複数の表に問い合わせを実行した時 （直積結合）に現れる実行計画です。この結合は、表に対してデカルト演算を実行します。データ量が増加するに従い、加速度的にレスポンスが悪化します。あらゆる手段を用いて、この結合方法を回避してください。例外はありません。

  36. ５．実行計画の指針 【その他】 ①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演算子により、結果セットの１番目の列でソートされます。不要なソートは避けましょう。

More Related