有两个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