日期:2014-05-16  浏览次数:21132 次

如何优化表连接SQL,我有两个这样的SQL,运行特别费时间,求优化方法.
有两个SQL:
第一个:
SQL code
insert into gl_rms_lagtest_tv (
          data_date,
          ... (字段很多,这里不全部列出来了)         
          counterparty)
  select  t.data_date,
          ...
          t.counterparty
     FROM v_brz_rms_exodus_pipe t,
  (select a.system_id,
          max(a.trade_version) as trade_version
     from v_brz_rms_exodus_pipe a
    group by a.system_id ) b
    where t.system_id = b.system_id
      and t.trade_version = b.trade_version


v_brz_rms_exodus_pipe数据量:百W级别的,该表字段:181个,由于该SQL运行特别耗费时间,所以请大家帮忙提供优化的方法
-----该表的执行计划:
SQL code
INSERT STATEMENT, GOAL = ALL_ROWS            Cost=58384    Cardinality=499168    Bytes=91347744
 LOAD TABLE CONVENTIONAL    Object owner=DATACORE    Object name=GL_RMS_LAGTEST_TV            
  HASH JOIN            Cost=58384    Cardinality=499168    Bytes=91347744
   VIEW    Object owner=DATACORE        Cost=25834    Cardinality=499168    Bytes=12978368
    SORT GROUP BY            Cost=25834    Cardinality=499168    Bytes=5490848
     TABLE ACCESS FULL    Object owner=DATACORE    Object name=V_BRZ_RMS_EXODUS_PIPE    Cost=22663    Cardinality=1090778    Bytes=11998558
   TABLE ACCESS FULL    Object owner=DATACORE    Object name=V_BRZ_RMS_EXODUS_PIPE    Cost=22903    Cardinality=1090778    Bytes=171252146

 



第二个SQL,类型和第一个一样

SQL code
execute immediate 'truncate table gl_fx_gfis_rs1';
    ------2、GFIS表关联最大Trade Version RMS信息表------------------------------
    insert into gl_fx_gfis_rs1 (exercised_from_src_sys_id,
                                data_date,
                                effective_date,
                                ...
                                counterparty)
   select t3.exercised_from_src_sys_id,
          ...
          t3.trans_id,
          ...
          t3.counterparty 
     FROM DWH_GFIS_ACC_ENTRIES   t1
       -------1、t1 left join t3------------------
LEFT JOIN (  select tb1.*,
                    nvl(tb2.loccpyid,tb3.master_number) as master_num
               from gl_rms_lagtest_tv tb1
          left join v_brz_idms_paragon_feed tb2
                 on tb1.ami_code = tb2.le_code
                and nvl(tb1.high_level_cpty_cdrmnemonic,tb1.cpty_cdrmnemonic) = tb2.cpty_cdr
          left join mdr_cust tb3
                 on tb1.ami_code = tb3.ami_code
                and tb1.counterparty = tb3.cust
           ) t3
       ON t1.linkage_id = to_char(t3.system_id)
    WHERE t1.src_system = 'ACSF'
      AND t1.src_system_ac_number like '042%'
      AND t1.module = 'AGE'
      AND t1.linkage_level = 'CO'


-----这个SQL我已经在src_system .src_system_ac_number.t1.module .linkage_level四个字段上面建了
索引,但似乎运行的时间还是比较多,是不能还是因为表连接比较耗费时间,对于着中类型的表连接SQL,希望大家
多提供一些优化方法.
----执行计划为:
SQL code
INSERT STATEMENT, GOAL = ALL_ROWS            Cost=62983    Cardinality=8752    Bytes=5785072
 LOAD TABLE CONVENTIONAL    Object owner=DATACORE    Object name=GL_FX_GFIS_RS1            
  HASH JOIN OUTER            Cost=62983    Cardinality=8752    Bytes=5785072
   TABLE ACCESS FULL    Object owner=DATACORE    Object name=DWH_GFIS_ACC_ENTRIES    Cost=47709    Cardinality=8688    Bytes=1294512
   VIEW    Object owner=DATACORE        Cost=2515    Cardinality=511638    Bytes=261958656
    HASH JOIN RIGHT OUTER            Cost=2515    Cardinality=511638    Bytes=97211220
     TABLE ACCESS FULL    Object owner=DATACORE    Object name=V_BRZ