求Oracle存储过程优化建议

Question

自己刚开始写存储过程，面对几百万几千万的数据量，执行存储过程速度特别的慢。自己对索引的用法也不是很熟练，通常不知道哪里该加，哪里不该加。求指点。

以下下是存储过程：

CREATE OR REPLACE PROCEDURE PRO_UW_APPLY_INFO_KZ(IN_START_SPAN IN DATE,   --任务开始区间
                                      IN_END_SPAN IN DATE,              --任务结束区间
                                    OUT_MSG_CODE   OUT VARCHAR2,          --0 代表任务执行成功，其他代表失败
                                    OUT_MSG        OUT VARCHAR2         --返回信息
                        ) AS
  BEGIN
  OUT_MSG_CODE := '0';
    OUT_MSG      := '执行成功' || TO_CHAR(IN_START_SPAN, 'yyyy-MM-dd') || TO_CHAR(IN_END_SPAN, 'yyyy-MM-dd');
    --Step 1     ：删除目标表所有数据
    EXECUTE IMMEDIATE 'TRUNCATE TABLE UW_APPLY_INFO_KZ';
    --Setp 2     : 同步源表所有数据
  INSERT INTO UW_APPLY_INFO_KZ(
    APPLY_BAR_CODE ,        
    POLICY_NO ,            
    AGENT_NO ,            
    AGENT_NAME ,          
    AGENT_SEX ,            
    AGENT_HIRE_DATE,        
    BRANCH_CODE ,        
    BRANCK_FULL_NAME ,        
    APPLICANT_NO ,          
    APPLY_STATUS ,          
    CHANNEL_TYPE ,          
    SIGNED_DATE ,          
    FREQUENCY ,            
    PREM_TYPE ,            
    APPLICANT_ADDR_CONTACT ,    
    APPLICANT_ADDR_CONTACT_NAME ,  
    APPLICANT_CONTACT_PHONE ,    
    APPLICANT_CONTACT_PHONE_NAME ,  
    CLIENT_NAME  ,          
    CLIENT_SEX ,          
    CLIENT_ID_TYPE ,        
    CLIENT_ID_NAME ,        
    EFFECT_DATE ,          
    CHNL_DETAIL ,          
    RE_PREM_TYPE ,          
    BANK_CODE ,            
    DESCRIPTION          
  )SELECT
    ai.APPLY_BAR_CODE ,
    ai.POLICY_NO ,
    ai.AGENT_NO ,
    (select EMP_NAME FROM staff_info st where st.emp_no=ai.AGENT_NO),
    (select SEX_DESC FROM SEX s,staff_info st where s.sex_code=st.SEX_CODE and st.emp_no=ai.AGENT_NO),
    (select HIRE_DATE FROM staff_info st where st.emp_no=ai.AGENT_NO),
    ai.BRANCH_CODE ,
    (SELECT BRANCH_FULL_NAME FROM BRANCH_INFO b where b.BRANCH_CODE = ai.BRANCH_CODE),
    ai.APPLICANT_NO ,
    ai.APPLY_STATUS ,
    ai.CHANNEL_TYPE ,
    ai.SIGNED_DATE ,
    ai.FREQUENCY ,
    ai.PREM_TYPE ,
    ai.APPLICANT_ADDR_CONTACT ,
    (SELECT DETAIL_ADDRESS FROM CLIENT_ADDRESS  WHERE ADDRESS_SEQ=ai.APPLICANT_ADDR_CONTACT),
    ai.APPLICANT_CONTACT_PHONE ,
    (SELECT  PHONE_NO FROM CLIENT_PHONE  WHERE PHONE_SEQ=ai.APPLICANT_CONTACT_PHONE),
    (SELECT CLIENT_NAME from CLIENT_INFORMATION c where c.CLIENT_NO=ai.APPLICANT_NO ),
    (SELECT SEX_DESC FROM SEX s,CLIENT_INFORMATION c  where s.sex_code=c.SEX_CODE and c.CLIENT_NO=ai.APPLICANT_NO),
    (select ID_TYPE from CLIENT_INFORMATION  c where c.CLIENT_NO=ai.APPLICANT_NO ),
    (select ID_TYPE_NAME from ID_TYPE i,CLIENT_INFORMATION c  where i.id_type=c.ID_TYPE and c.CLIENT_NO=ai.APPLICANT_NO ),
    ai.EFFECT_DATE ,
    ai.CHNL_DETAIL ,
    ai.RE_PREM_TYPE ,
    ai.BANK_CODE ,
    (select DESCRIPTION from BANK_CATEGORY  b  where b.BANK_CATEGORY=ai.BANK_CODE)
    FROM UW_APPLY_INFO ai;
    --提交
    COMMIT;
    --捕获命令支持异常
  EXCEPTION
    WHEN OTHERS THEN
      OUT_MSG_CODE := SQLCODE;
      OUT_MSG      := SQLERRM;
    --回滚
      ROLLBACK;
      IF OUT_MSG_CODE = '0' THEN
        COMMIT;
    END IF;
  END;

Answer 1

目测，数据库结构设计太教条化了，太学院派了，太遵循范式要求了；很多字段完全可以冗余存下来！！比如EMP_NAME/SEX_DESC/HIRE_DATE等等。甚至SEX_DESC不需要用数据字典，直接char(2)，然后存储成‘男’‘女’‘NA’，存成byte，然后弄个字典，闲的蛋疼啊~就那么几个值，枚举范围的，怎么加索引速度也快不起来。

建议直接优化设计！

Answer 2

先不说加索引. 你这个 insert  into select 后面的语句.

 为什么不弄一个临时表呢.  create or replace table  xxx_temp   as  select  xxx,xxx from  xxx;

然后  你insert  的时候.   直接  insert  into  select *from  xxx_temp. 这不是会快很多.?