如何优化表连接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_IDMS_PARAGON_FEED    Cost=28    Cardinality=3299    Bytes=65980
     HASH JOIN RIGHT OUTER            Cost=2481    Cardinality=511638    Bytes=86978460
      TABLE ACCESS FULL    Object owner=DATACORE    Object name=MDR_CUST    Cost=3    Cardinality=395    Bytes=9085
      TABLE ACCESS FULL    Object owner=DATACORE    Object name=GL_RMS_LAGTEST_TV    Cost=2473    Cardinality=511638    Bytes=75210786

h今天还是周末,估计是人不多啊??
我在加班,明天这些存储过程都要上UAT了,目前正在修改中,如果有
高手在,请一定给点帮助.

SQL code

insert into gl_rms_lagtest_tv (
          data_date,
          ... (字段很多,这里不全部列出来了)         
          counterparty)
  select  t.data_date,
          ...
          t.counterparty , a.trade_version
     FROM v_brz_rms_exodus_pipe t, v_brz_rms_exodus_pipe a
where t.system_id = a.system_id and a.trade_version = (select max(trade_version) from v_brz_rms_exodus_pipe b where b.system_id = a.system_id)

insert into gl_rms_lagtest_tv (
          data_date,
          ... (字段很多,这里不全部列出来了)         
          counterparty)
  select  t.data_date,
          ...
          t.counterparty , a.trade_version
     FROM v_brz_rms_exodus_pipe t, v_brz_rms_exodus_pipe a
where t.system_id = a.system_id and not exists (select 1 from v_brz_rms_exodus_pipe b where b.system_id = a.system_id and b.trade_version > a.trade_version)

不知道你是否需要更换为如下:
SQL code

insert into gl_rms_lagtest_tv (
          data_date,
          ... (字段很多,这里不全部列出来了)         
          counterparty)
  select  t.data_date,
          ...
          t.counterparty , a.trade_version
     FROM v_brz_rms_exodus_pipe t, v_brz_rms_exodus_pipe a
where t.system_id = a.system_id and t.trade_version = a.trade_version and a.trade_version = (select max(trade_version) from v_brz_rms_exodus_pipe b where b.system_id = a.system_id)

insert into gl_rms_lagtest_tv (
          data_date,
          ... (字段很多,这里不全部列出来了)         
          counterparty)
  select  t.data_date,
          ...
          t.counterparty , a.trade_version
     FROM v_brz_rms_exodus_pipe t, v_brz_rms_exodus_pipe a
where t.system_id = a.system_id and t.trade_version = a.trade_version not exists (select 1 from v_brz_rms_exodus_pipe b where b.system_id = a.system_id and b.trade_version > a.trade_version)

我看不懂你的SQL,不过估计是要求最大列.
我写个大概,你自己参考更改.

假设A,B表,通过ID连接,去B表对应ID的最大时间列date所在行的数据.

select a.* , t.* from a , b t where a.id = t.id and t.date = (select max(date) from b where id = t.id)

select a.* , t.* from a , b t where a.id = t.id and not exists (select 1 from b where id = t.id and date > t.date)

--如果是多个字段关联,例如:a.id1 = t.id1 , a.id2 = t.id2
则如下:
select a.* , t.* from a , b t where a.id1 = t.id1 and a.id2 = t.id2 and t.date = (select max(date) from b where id1 = t.id1 and id2 = t.id2)

select a.* , t.* from a , b t where a.id1 = t.id1 and a.id2 = t.id2 and not exists (select 1 from b where id1 = t.id1 and id2 = t.id2 and date > t.date)