贴上sql（这个sql大家不用太用心看，语句不是问题）：
insert into pms_consump
SELECT DISTINCT
DECODE (nvl(trans.transamt,0),
0, '0.00',
TRUNC (nvl(trans.transamt,0) / 100, 2))
AS transamt,
nvl(trans.transamt,0) AS hiddenTrans,
DECODE (trans.SEARCH_TRANS_CODE,
'0000000200',
'消费',
'2000000200',
'消费撤销',
'2000000400',
'消费撤销冲正',
'0000000400',
'消费冲正')
AS trans_code,
DECODE (trans.CANCELFLAG,
'0',
'正常交易',
'1',
'冲正交易',
'2',
'被冲正交易')
AS CANCELFLAG,
DECODE (
TRANS.CARDNO,
'',
'',
SUBSTR (TRANS.CARDNO, 0, 4) || '****'
|| SUBSTR (TRANS.CARDNO,
LENGTH (TRANS.CARDNO) - 3,
LENGTH (TRANS.CARDNO))
)
AS cardno,
TRANS.CARDNO AS hidenCardno,
TO_CHAR (to_date(trans.transdate||trans.transtime,'yyyymmddhh24miss'), 'yyyy-mm-dd hh24:mi:ss') AS SENDDATE,
trans.pospsn,
trans.id,
trans.routeid,
trans.cancelid,
trans.search_trans_code as TRANSCODE,
MERPOS.POSBUSINESSNO,
merinfo.MERC_NAME,
MERINFO.MERC_ID,
MERINFO.seller_no,
merInfo.mcc_cd,
merInfo.premiumrate,
merInfo.BANKNAME,
POSINFO.SERIALNO,
pri.channel_code as channelCode ,
trans.RESPONSECODE AS channelno,
pri.merchant_id as merchantId ,
MERPOS.POSID,
ai.AGENT_NUMBER,
ai.AGENT_NAME,
di.departmentnumber,
di.departmentname,
DECODE(nvl(trans.transfee1, 0),
0,
'0.000',
TRUNC(nvl(trans.transfee1, 0) / 100, 6)) AS transfee1,
(transamt-transfee1)/100 as transmoney
FROM (select * from POSP_TRANS_INFO trans where trans.search_trans_code not in ('9000100800','9000100820','9000200500')
and to_date(trans.transdate,'yyyy/mm/dd')<=trunc(sysdate)-1
-- and to_date(trans.transdate,'yyyy/mm/dd') >=add_months(trunc(sysdate,'mm'),-1)
and trans.transdate is not null
and trans.merchantcode is not null) trans
LEFT JOIN
PMS_MERCHANT_POS merPos
ON trans.POSID = merPos.id
LEFT JOIN
PMS_POS_INFO posinfo
ON POSINFO.ID = MERPOS.POSID
LEFT JOIN
PMS_MERCHANT_INFO merInfo
ON merInfo.id = MERPOS.MERCHANTID
left join posp_route_info pri
on trans.routeid=pri.id
LEFT JOIN
pms_agent_info ai
ON merinfo.agent_number = ai.agent_number
left join
pms_department_info di
on di.id=merpos.departmentid
ORDER BY senddate ,merc_id, posid;
以上语句，我本地执行时间17s左右，服务器上跑了几个小时！
之后查询服务器数据库执行这条语句的日志，
几个关键的数据是这样的：
BUFFER_GETS ：26907302
CONCURRENCY_WAIT_TIME：41
OPTIMIZER_COS：495638759
CPU_TIME：70572155
ELAPSED_TIME：70572155
OBJECT_STATUS：VALID
需要其他数据我可以提供！摆脱大家了！
检查下索引有没有建立正确，服务器有没有额外的事务或者触发器？
本地數據量和服務器數據量不一樣吧？
比如本地才幾百條數據，服務器上幾千萬條。
可以試試新建個schema，把服務的dump導入到本地，再執行試試。