Oracle改ページクエリの最適化

改ページの最適化
プログラムでは、改ページSQLの性能に関する問題をまとめます。

最適化されたクエリー列にサブクエリがあるときの最適化効果は明らかに

です。
所要時間(s)
改ページしない
未処理前の改ページ
処理後の改ページ
位を進める
並べ替えなし
0.5
13.9
0.25
98%
並べ替え
0.5
14.25
0.296
97.9%

ページなしSQL

--   0.5s
SELECT ROWNUM ROWINDEX,              
       (select tbzt
          from t_dw_sbbg_bgsblb
         where bgsbid = t.obj_id
           and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
  from T_SB_ZWYC_JJ T
 WHERE 1 = 1
   AND exists (select bgsbid
          from t_dw_sbbg_bgsblb
         where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
           and bgsbid = t.obj_id)

未処理前の改ページ

--   13.9s
select *
  from (SELECT ROWNUM ROWINDEX,              
               (select tbzt
                  from t_dw_sbbg_bgsblb
                 where bgsbid = t.obj_id
                   and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
          from T_SB_ZWYC_JJ T
         WHERE 1 = 1
           AND exists
         (select bgsbid
                  from t_dw_sbbg_bgsblb
                 where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
                   and bgsbid = t.obj_id))
 WHERE ROWINDEX > 20
   AND ROWINDEX <= 40

処理後の改ページ

--   0.25
select *
  from (SELECT ROWNUM ROWINDEX,               
               (select tbzt
                  from t_dw_sbbg_bgsblb
                 where bgsbid = t.obj_id
                   and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
          from T_SB_ZWYC_JJ T
         WHERE 1 = 1
           AND exists
         (select bgsbid
                  from t_dw_sbbg_bgsblb
                 where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
                   and bgsbid = t.obj_id) and ROWNUM<= 40 )
 WHERE ROWINDEX > 20

並べ替え

ページに分けない

-- 0.5
SELECT ROWNUM ROWINDEX,              
       (select tbzt
          from t_dw_sbbg_bgsblb
         where bgsbid = t.obj_id
           and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
  from T_SB_ZWYC_JJ T
 WHERE 1 = 1
   AND exists (select bgsbid
          from t_dw_sbbg_bgsblb
         where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
           and bgsbid = t.obj_id)
           order by obj_id

未処理

--14.25s
select *
  from (select ROWNUM ROWINDEX, tbzt from (SELECT               
               (select tbzt
                  from t_dw_sbbg_bgsblb
                 where bgsbid = t.obj_id
                   and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
          from T_SB_ZWYC_JJ T
         WHERE 1 = 1
           AND exists
         (select bgsbid
                  from t_dw_sbbg_bgsblb
                 where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
                   and bgsbid = t.obj_id) order by OBJ_ID))
 WHERE ROWINDEX > 20
   AND ROWINDEX <= 40

処理後

--0.296s
select *
  from ( select ROWNUM ROWINDEX, tbzt from (SELECT               
               (select tbzt
                  from t_dw_sbbg_bgsblb
                 where bgsbid = t.obj_id
                   and bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb') tbzt
          from T_SB_ZWYC_JJ T
         WHERE 1 = 1
           AND exists
         (select bgsbid
                  from t_dw_sbbg_bgsblb
                 where bgsqdid = '47c3514c-f8ba-4c13-8262-fb3553157aeb'
                   and bgsbid = t.obj_id)) where 1=1 and ROWNUM<= 40 )
 WHERE ROWINDEX > 20

あなたに役に立つならいいですよ。