oracle sql语句优化

Question

在Oracle9i Enterprise Edition Release 9.2.0.1.0下执行了一个多小时，但是在Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 下比较快执行计划也不同，但是现场时9i，请各位专家给个优化意见sql语句：
insert into cb_drvbase nologging(.......)
select a.dabh,
       a.sfzmhm,
       b.xm,
       'A' as fzjg,
       a.glbm,
       a.cclzrq as cclzrq1,
       to_char(a.cclzrq, 'yyyy-mm-dd') as cclzrq2,
       nvl(t2.xzqh1, 'ZZZ') as djzsxzqh,
       nvl(t3.fzjg, 'ZZZ') as ccfzjg,
       nvl(t1.ztdm, 'ZZZ') as zt,
       round((sysdate - b.csrq) / 365) as nl,
       trunc((sysdate - a.cclzrq) / 365 + 0.99) as jl,
       pgex_util_public.getZJCXDM(a.zjcx) as zjcx,
       nvl(t5.jxmc, 'ZZZ') as jxmc,
       nvl(t6.jszly, 'ZZZ') as jszly,
       decode(b.xb, '1', '1', '2', '2', 'ZZZ') as xb,
       b.gj,
       a.ljjf,
       1 as rysl,
       decode(a.zt, 'A', 1, 0) as zcsl,
       decode(a.zt, 'A', 0, 1) as jszycsl,
       decode(sign(a.yxqz - sysdate), 1, 0, 1) as cqsl,
       decode(sign(12 - a.ljjf), 1, 0, 1) as lj12fsl,
       decode(instr(a.zt, 'R'), 0, 0, 1) as cqwsy,
       nvl2(t7.dabh, '1', '2') as zdjsr,
       t8.bzsgglbm,
       t8.bzjwglbm,
       nvl(t10.dwbh, 'ZZZ') as JSRGSSQ,
       nvl(t11.BMDM, 'ZZZ') as JSRZRDW,
       nvl(t12.jybh, 'ZZZ') as JSRZRMJ,
       nvl2(t9.dabh, 1, 0) as sdhsl
  from drv_admin.drivinglicense    a,
       drv_admin.person            b,
       dim_jg_jszzt                t1,
       dim_xzqh                    t2,
       dim_jw_fzjg                 t3,
       dim_jg_jx                   t5,
       dim_jg_jszly                t6,
       base_admin.bas_keydrv       t7,
       mid_jg_glbm                 t8,
       base_admin.bas_drvresidence t9,
       base_admin.bas_unit         t10,
       base_admin.bas_codeunit     t11,
       base_admin.bas_user         t12
 where a.sfzmhm = b.sfzmhm
   and substr(a.zt, 1, 1) = t1.ztdm(+)
   and b.djzsxzqh = t2.xzqh1(+)
   and a.ccfzjg = t3.fzjg(+)
   and a.jxmc = t5.jxmc(+)
   and a.ly = t6.jszly(+)
   and a.dabh = t7.dabh(+)
   and a.glbm = t8.jgglbm(+)
   and a.dabh = t9.dabh(+)
   and t9.dwbh = t10.dwbh(+)
   and t10.bmdm = t11.bmdm(+)
   and substr(t10.jybh, 1, 6) = t12.jybh(+)
   and not exists (select 1 from cb_drvbase t where t.dabh = a.dabh);
------------------------------
drv_admin.drivinglicense表：主键dabh，索引：sfzmhm（1179428行记录）
drv_admin.person        表：主键：sfzmhm（1179428行记录）
dim_jg_jszzt            表：主键：ztdm（15行记录）
dim_xzqh                表：主键：xzqh1（3936行记录）
dim_jw_fzjg             表：主键：fzjg（300行记录）
dim_jg_jx               表：主键：jxmc（360行记录）
dim_jg_jszly            表：主键：jszly（6行记录）
base_admin.bas_keydrv   表：主键：dabh（16843行记录）
mid_jg_glbm             表：主键：jgglbm（12行记录）
base_admin.bas_drvresidence表：主键：dabh（555477行记录）
base_admin.bas_unit     表：主键：dwbh（4504行记录）
base_admin.bas_codeunit 表：主键：bmdm（138行记录）
base_admin.bas_user     表：主键：jybh（1021行记录）
cb_drvbase              表：主键：dabh（150506行记录）执行计划---------------------（pl\sql）
description                      对象所有者 对象名称      耗费              基数     字节
SELECT STATEMENT, GOAL = ALL_ROWS       20644773       20621150 8640261850
 FILTER
  NESTED LOOPS OUTER                23623    20621150 8640261850
   NESTED LOOPS OUTER                23623    901274   362312148
    NESTED LOOPS OUTER                23623    220360   84838600
     NESTED LOOPS OUTER                23623    220360   81753560
      NESTED LOOPS OUTER                23623    220360   80651760
       HASH JOIN OUTER                23623    220360   77566720
        HASH JOIN OUTER       21841    220360   68972680
         HASH JOIN OUTER       20218    220360   63463680
          HASH JOIN       16295    220360   58615760
           NESTED LOOPS OUTER                5191    220360   45173800
            NESTED LOOPS OUTER                5191    220360   44072000
             NESTED LOOPS OUTER       5191    220360   29528240
              TABLE ACCESS FULL  DRV_ADMIN  DRIVINGLICENSE   5191    220360   28646800
              INDEX UNIQUE SCAN  OLAPJN     PK_DIM_JW_FZJG             1        4
             INDEX UNIQUE SCAN   OLAPJN     PK_DIM_JG_JX             1        66
            INDEX UNIQUE SCAN    OLAPJN     PK_DIM_JG_JSZLY             1        5
           TABLE ACCESS FULL     DRV_ADMIN  PERSON            2787          2365862  144317582
          TABLE ACCESS FULL       BASE_ADMIN BAS_DRVRESIDENCE  997           845307   18596754
         TABLE ACCESS FULL       OLAPJN     MID_JG_GLBM       2             409      10225
        TABLE ACCESS FULL       BASE_ADMIN BAS_UNIT         9             5963     232557
       INDEX UNIQUE SCAN       BASE_ADMIN PK_BAS_CODEUNIT             1        14
      INDEX UNIQUE SCAN       OLAPJN     PK_DIM_XZQH    1        5
     INDEX UNIQUE SCAN       BASE_ADMIN PK_BAS_KEYDRV             1        14
    INDEX UNIQUE SCAN       OLAPJN     PK_DIM_JG_JDZZT             4        68
   INDEX UNIQUE SCAN       BASE_ADMIN IDX_JYBH             23       391
  INDEX UNIQUE SCAN       OLAPJN     PK_CB_DRVBASE      1    1        8

Answer 1

单纯的看你的sql语句，已经是很简介了，没有任何不规范的地方，我想应该从业务上考虑优化下了 ！

Answer 2

少用外关联,可以用子查询实现相同的功能.以t1表与a的关联为例,可以改写为
select a.dabh, 
      a.sfzmhm, 
      b.xm, 
      (select nvl(ztdm,'ZZZ') from t1 WHERE ztdm=substr(a.zt,1,1)) as zt
   from drv_admin.drivinglicense    a, 
      drv_admin.person            b, 
      dim_jg_jszzt                t1
where a.sfzmhm = b.sfzmhm 
  and not exists (select 1 from cb_drvbase t where t.dabh = a.dabh); 

Answer 3

可以考虑用Merge Insert 代替and not exists (select 1 from cb_drvbase t where t.dabh = a.dabh)
然后还可以用分次的Merge Update减少单次关联表的数量

Answer 4

有没有可以修改oracle9i系统参数来提高性能的

Answer 5

Oracle不喜欢太复杂的SQL,没有必要通过一个SQL来实现。你把它修改成几个sql不就解决了吗。