Question

Oracle Insert Into select 非常慢

INSERT /*+ APPEND*/ INTO EF_EFEE_3_EFEL(EFLE_ID,EFLE_ESCO_ID,EFLE_EFEE_ID,EFLE_EFEL_ID)
SELECT SEQ_EF_EFEE_3_EFEL.NEXTVAL,EFEE_ESCO_ID,EFEE_ID,EFEL_ID FROM
(SELECT EFEE.EFEE_ESCO_ID,EFEE.EFEE_ID,EFEL.EFEL_ID FROM EF_EFEP_2_EFEL EFEE
LEFT JOIN EF_ESTIMATE_LARGE EFEL ON EFEL.EFEL_ESCO_ID = EFEE.EFEE_ESCO_ID
AND EFEL.EFEL_SEGMENT2 = EFEE.EFEE_DEPT_SEG_CODE
AND EFEL.EFEL_SEGMENT3 = EFEE.EFEE_EBCG_CODE
AND EFEL.EFEL_SEGMENT4 = EFEE.EFEE_EBSC_CODE
AND EFEL.EFEL_SEGMENT5 = EFEE.EFEE_EBCU_CDH_CODE
AND EFEL.EFEL_SEGMENT6 = EFEE.EFEE_EBOT_CODE
AND EFEL.EFEL_SEGMENT7 = EFEE.EFEE_EBPJ_PROJECT_CODE
AND EFEL.EFEL_ATTRIBUTE2 = EFEE.EFEE_DP_NUMBER
AND EFEL.EFEL_SUBSTR3 = EFEE.EFEE_IS_RP
AND EFEL.EFEL_REFERENCE_2 =
(case when EFEE.EFEE_IS_RP = 1 THEN EFEE.EFEE_EBCU_CDH_ACODE
when EFEE.EFEE_IS_RP = -1 THEN EFEE.EFEE_EBCU_CDH_SCODE end)
WHERE EFEE.EFEE_ESCO_ID = 5012001
AND EFEE.EFEE_EBAP_CODE = 'Aug-2013'
AND EFEE.EFEE_EFEL_ID IS NULL)
EFLE where EFLE.EFEL_ID IS NOT NULL

Answer 1

给条件的字段加上索引能提高这个查询效率，如EFEE_ESCO_ID，EFEE_EBAP_CODE。
这样的批处理问题，如果数据量比较大的情况下，考虑创建分区也行，数据量不是很大的话，直接加索引就行，

Answer 2

1）估计性能瓶颈在两张表的连接上，这个要靠你自己优化了
2)考虑加nologging(append与nologging联用才有效果）

Answer 3

自己优化写SQL