Symfoware Server V10.0.0 アプリケーション開発ガイド(共通編)

3.4.2 ジョイン系アクセスモデル

以下のアクセスモデルについて、アクセスプランの出力例を示します。

• マージジョイン(インデックスを利用してジョインする)

• マージジョイン(ソートテーブルを利用してジョインする)

• フェッチジョイン

• ネスティッドループジョイン

マージジョイン(インデックスを利用してジョインする)

[アクセスプランの出力例]

===============================================================================
Main query
=sno===sectname=====input1==============input2==============output/update======
   1 : M-JOIN      [TBL1_IX1_DSO       ][                 ][                  ]
                   [TBL2_IX1_DSO       ][                 ][APPL              ]
-------------------------------------------------------------------------------
  [  1] MERGE JOIN ELEMENT
        1) table name       SCM1.TBL1
           scan type        INDEX KEY SCAN(1)
           dso name         TBL1_IX1_DSO         [PAGE/SH]
           join key column       1
           condition evaluation  No
           scan record number    100
        2) table name       SCM1.TBL2
           scan type        INDEX KEY SCAN(1)
           dso name         TBL2_IX1_DSO         [PAGE/SH]
           join key column       1
           condition evaluation  No
           scan record number    200
  [  2] OUTPUT ELEMENT
        record length         22

マージジョイン(ソートテーブルを利用してジョインする)

[アクセスプランの出力例]

===============================================================================
Main query
=sno===sectname=====input1==============input2==============output/update======
   1 : SCAN        [TBL1_IX1_DSO      ][                  ][SORT0002          ]
-------------------------------------------------------------------------------
  [  1] SCAN ELEMENT
        table name       SCM1.TBL1
        scan type        INDEX KEY SCAN(1)
        dso name         TBL1_IX1_DSO        [PAGE/SH]
        condition evaluation  No
        scan record number    100
  [  2] INSERT ELEMENT
        table name       SORT0002
        insert record length  12
-------------------------------------------------------------------------------
   2 : SCAN        [SORT0002          ][TBL1_BASE_DSO     ][SORT0001          ]
-------------------------------------------------------------------------------
  [  1] SCAN ELEMENT
        table name       SORT0002
        scan type        TABLE ALL SCAN
        condition evaluation  No
  [  2] SCAN ELEMENT
        table name       SCM1.TBL1
        scan type        TABLE KEY SCAN
        dso name         TBL1_BASE_DSO       [PAGE/SH]
        condition evaluation  No
        scan record number    1
  [  3] INSERT ELEMENT
        table name       SORT0001
        insert record length  88
-------------------------------------------------------------------------------
   3 : M-JOIN      [SORT0001          ][                  ][                  ]
                   [TBL2_IX1_DSO      ][                  ][SORT0003          ]
-------------------------------------------------------------------------------
  [  1] MERGE JOIN ELEMENT
        1) table name       SORT0001
           scan type        TABLE ALL SCAN
           join key column       1
           condition evaluation  No
        2) table name       SCM1.TBL2
           scan type        INDEX ALL SCAN
           dso name         TBL2_IX1_DSO       [DSI/SH]
           join key column       1
           condition evaluation  No
           scan record number    1000
  [  2] INSERT ELEMENT
        table name       SORT0003
        insert record length  100
-------------------------------------------------------------------------------
   4 : SCAN        [SORT0003          ][TBL2_BASE_DSO     ][APPL              ]
-------------------------------------------------------------------------------
  [  1] SCAN ELEMENT
        table name       SORT0003
        scan type        TABLE ALL SCAN
        condition evaluation  No
  [  2] SCAN ELEMENT
        table name       SCM1.TBL2
        scan type        TABLE KEY SCAN
        dso name         TBL2_BASE_DSO       [PAGE/SH]
        condition evaluation  No
        scan record number    1
  [  3] OUTPUT ELEMENT
        record length         182

フェッチジョイン(インデックスを利用してジョインする)

[アクセスプランの出力例]

===============================================================================
Main query 
=sno===sectname=====input1==============input2==============output/update======
   1 : SCAN        [TBL1_IX1_DSO      ][                  ][SORT0001          ]
-------------------------------------------------------------------------------
  [  1] SCAN ELEMENT
        table name       SCM1.TBL1
        scan type        INDEX KEY SCAN(1)
        dso name         TBL1_IX1_DSO        [PAGE/SH]
        condition evaluation  No
        scan record number    5
  [  2] INSERT ELEMENT
        table name       SORT0001
        insert record length  12
-------------------------------------------------------------------------------
   2 : SCAN        [SORT0001          ][                  ][                  ]
       F-JOIN      [TBL1_BASE_DSO     ][                  ][                  ]
                   [TBL2_IX1_DSO      ][TBL2_BASE_DSO     ][APPL              ]
-------------------------------------------------------------------------------
  [  1] SCAN ELEMENT
        table name       SORT0001
        scan type        TABLE ALL SCAN
        condition evaluation  No
  [  2] FETCH JOIN ELEMENT
        1) table name       SCM1.TBL1
           scan type        TABLE KEY SCAN
           dso name         TBL1_BASE_DSO       [PAGE/SH]
           condition evaluation  No
           scan record number    1
        2) table name       SCM1.TBL2
           scan type        INDEX TABLE SCAN(1)
           dso name         TBL2_IX1_DSO        [PAGE/SH]
                            TBL2_BASE_DSO       [PAGE/SH]
           condition evaluation  No
           scan record number    3
  [  3] OUTPUT ELEMENT
        record length         182

ネスティドループジョイン(実表を直接読み込んでジョインする)

[アクセスプランの出力例]

===============================================================================
Main query
=sno===sectname=====input1==============input2==============output/update======
   1 : N-JOIN      [TBL1_BASE_DSO     ][                  ][                  ]
                   [TBL2_BASE_DSO     ][                  ][APPL              ]
-------------------------------------------------------------------------------
  [  1] NESTED LOOP JOIN ELEMENT
        1) table name       SCM1.TBL1
           scan type        TABLE ALL SCAN
           dso name         TBL1_BASE_DSO        [DSI/SH]
           condition evaluation  Yes
           scan record number    1
        2) table name       SCM1.TBL2
           scan type        TABLE ALL SCAN
           dso name         TBL2_BASE_DSO        [DSI/SH]
           condition evaluation  Yes
           scan record number    1
  [  2] OUTPUT ELEMENT
        record length         182