合 一个insert插入语句很慢的优化
记录日期: 2014-07-30 14:25:27
原sql语句：
INSERT INTO RISKREPT.BASE_FMLG (BATCH_DATE, DATE_STAMP_ST, TIME_STAMP_ST, ORG, ACCT, CARD_NBR, CARD_SEQ, MER_ORG, MER_NBR, REQUEST_TYPE_ID, LOGO, SYSTEM_ACTION, FINAL_ACTION, ACTION_REASON, REVERSAL_REASON, AVAIL_CR, CASH_AVAIL_CR, ACCT_CURR_BAL, ACCT_CURR_BAL_CASH, B007_GMT_DATE_TIME, B018_MER_TYPE, B019_CNTRY_CODE, B032_ACQ_ID, B033_FWD_ID, AUTH_CODE, B039_RESP_CODE, B041_CRD_ACCPT_TERM, B042_TERMTYPE2_MER_ID, B043_CRD_ACCPT_NAM, B043_CRD_ACCPT_CITY, B043_CRD_ACCPT_ST_CTRY, MESSAGE_TYPE_ID, RECORD_TYPE, SALES_AMT_RMB, TOTAL_SALES_AMT, B049_CURR_CODE, BLING_AMT, POS_ENTRY_MODE, PIN_ENTRY_MODE, TRADE_INTERNET, SALES_CTRY, SALES_CTRY_NAME, SALES_CITY, SALES_CITY_NAME, SALES_LINK, MER_CODE, MER_MCC, MER_NAM, ACQ_NAME, REVCODE, AUTH_TYPE, REF_NBR, VI_B011_SYS_AUDT_TRCE, CARD_TYPE, STAGE_TYPE, STAGE_NUM, OVERSEA_FLAG, RISK_FCD, RISK_LCD) SELECT BATCH_DATE, DATE_STAMP_ST, TIME_STAMP_ST, ORG, ACCT, CARD_NBR, CARD_SEQ, MER_ORG, MER_NBR, REQUEST_TYPE_ID, LOGO, SYSTEM_ACTION, FINAL_ACTION, ACTION_REASON, REVERSAL_REASON, AVAIL_CR, CASH_AVAIL_CR, ACCT_CURR_BAL, ACCT_CURR_BAL_CASH, B007_GMT_DATE_TIME, B018_MER_TYPE, B019_CNTRY_CODE, B032_ACQ_ID, B033_FWD_ID, AUTH_CODE, B039_RESP_CODE, B041_CRD_ACCPT_TERM, B042_TERMTYPE2_MER_ID, B043_CRD_ACCPT_NAM, B043_CRD_ACCPT_CITY, B043_CRD_ACCPT_ST_CTRY, MESSAGE_TYPE_ID, RECORD_TYPE, SALES_AMT_RMB, TOTAL_SALES_AMT, B049_CURR_CODE, BLING_AMT, POS_ENTRY_MODE, PIN_ENTRY_MODE, TRADE_INTERNET, SALES_CTRY, SALES_CTRY_NAME, SALES_CITY, SALES_CITY_NAME, SALES_LINK, MER_CODE, MER_MCC, MER_NAM, ACQ_NAME, REVCODE, AUTH_TYPE, REF_NBR, VI_B011_SYS_AUDT_TRCE, CARD_TYPE, STAGE_TYPE, STAGE_NUM, OVERSEA_FLAG, SYSDATE, SYSDATE FROM TEMP_FMLG_PURGE
原sql执行计划非常简单：
该语句是job中的语句，每天都需要跑的，其历史执行时间如下图，可以看出执行时间非常长的都是user_io_wait等待比较严重的一些：
查看一下相关表的属性和数据量：
SELECT v.OWNER,
v.TABLE_NAME,
v.partitioned,
v.LAST_ANALYZED,
v.NUM_ROWS,
v.table_size2 ,
v.EMPTY_BLOCKS
FROM vw_table_lhr v
WHERE v.TABLE_NAME IN ('BASE_FMLG',
'TEMP_FMLG_PURGE');
BASE_FMLG有15亿的数据量，是个分区表，每次从TEMP_FMLG_PURGE中取数，TEMP_FMLG_PURGE大约有234W的数据量，
索引信息：
SELECT v.index_owner,
v.index_name,
v.index_type,
v.partitioned,
v.索引列,
v.index_size,
v.num_rows
FROM vw_table_index_lhr v
WHERE v.TABLE_NAME IN ('BASE_FMLG',
'TEMP_FMLG_PURGE');
被插入的表有5个索引，且都是分区索引，不涉及全局索引，涉及到的分区索引：
select v.index_owner,
v.index_name,
v.index_type,
v.索引列,
v.partition_size,
v.num_rows
from vw_table_index_part2_lhr V
where V.TABLE_NAME='BASE_FMLG'
and v.PARTITION_NAME='P201407' ;
查一下数据来源：
SELECT t.BATCH_DATE,
COUNT(1)
