日期:2014-05-17  浏览次数:20746 次

请高手指导如何优化该sql,用oracle plsql编写
CREATE OR REPLACE PROCEDURE PP_SETPPORDER (MONUM_1 in VARCHAR2,--订单号
  MTCODE_1 IN VARCHAR2,--整车号
  MOQTYS_1 IN VARCHAR2,
  PSDATE_1 IN VARCHAR2,
  PFDATE_1 IN VARCHAR2,
  CPCODE_1 IN VARCHAR2,
  CPQTYS_1 IN VARCHAR2
)IS
 ISSAVE INT;
 ISCCX INT;
 ISRK INT;
 ORDSTATE INT;
 COMPENTNUM INT;
 ISEDIT VARCHAR2(20);
 BEGIN
  -------------------------首先判断订单头数量
  select count(*) into ISSAVE from PP_MANUORDER p where P.Monum = MONUM_1;
  SELECT COUNT(*) INTO ORDSTATE FROM PP_MANUORDER P WHERE P.Monum = MONUM_1 AND P.PLCODE = 'SMAZZ01' AND P.MOSTA = 'REL';

IF ISSAVE = 0 THEN
  --总装流水线
  insert into pp_manuorder (FCCODE,MONUM,MOSTA,MOTYPE,MTCODE,PLCODE,MOQTYS,PSDATE,PFDATE,CRUSER,CRTIME,COMPQTYS)
  VALUES ('8090',MONUM_1,'CREA','Z001',MTCODE_1,'SMAZZ01',TO_NUMBER(MOQTYS_1),to_char(to_date(PSDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),to_char(to_date(PFDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),'MESSAP',to_char(SYSDATE,'yyyy-MM-DD'),0);
  --存储线
  insert into pp_manuorder (FCCODE,MONUM,MOSTA,MOTYPE,MTCODE,PLCODE,MOQTYS,PSDATE,PFDATE,CRUSER,CRTIME,COMPQTYS)
  VALUES ('8090',MONUM_1,'REL','ZZ00',MTCODE_1,'SMAZZ00',TO_NUMBER(MOQTYS_1),to_char(to_date(PSDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),to_char(to_date(PFDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),'MESSAP',to_char(SYSDATE,'yyyy-MM-DD'),0);
  --检测线
  insert into pp_manuorder
  (FCCODE,MONUM,MOSTA,MOTYPE,MTCODE,PLCODE,MOQTYS,PSDATE,PFDATE,CRUSER,CRTIME,COMPQTYS)
  VALUES ('8090',MONUM_1,'REL','ZZJC',MTCODE_1,'SMAZZ02',TO_NUMBER(MOQTYS_1),to_char(to_date(PSDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),to_char(to_date(PFDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),'MESSAP',to_char(SYSDATE,'yyyy-MM-DD'),0);
  --入库线
  insert into pp_manuorder
  (FCCODE,MONUM,MOSTA,MOTYPE,MTCODE,PLCODE,MOQTYS,PSDATE,PFDATE,CRUSER,CRTIME,COMPQTYS)
  VALUES ('8090',MONUM_1,'REL','ZZRK',MTCODE_1,'SMAZZ03',TO_NUMBER(MOQTYS_1),to_char(to_date(PSDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),to_char(to_date(PFDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),'MESSAP',to_char(SYSDATE,'yyyy-MM-DD'),0);
end if;

  -----------------------处理订单组件
 select COUNT(*) into COMPENTNUM from pp_mocomponent s where s.cpcode = CPCODE_1 and s.MONUM = MONUM_1;
 
 IF COMPENTNUM = 0 THEN --新订单插入组件
  insert into pp_mocomponent(FCCODE,MONUM,CPCODE,CPQTYS,crtime,cruser)
  VALUES('8090',MONUM_1,CPCODE_1,TO_NUMBER(CPQTYS_1*MOQTYS_1),to_char(SYSDATE,'yyyy-MM-DD hh24:mi:ss'),'MESSAP');
 end if;
  
END;

------解决方案--------------------
把用到的表、表结构描述一下。
还有每个表的数据量。
再有就是索引的创建情况。
再有是表的分区情况。

------解决方案--------------------
select count(*) from PP_MANUORDER p where P.Monum = MONUM_1;
SELECT COUNT(*) FROM PP_MANUORDER P WHERE P.Monum = MONUM_1 AND P.PLCODE = 'SMAZZ01' AND P.MOSTA = 'REL';
select COUNT(*) from pp_mocomponent s where s.cpcode = CPCODE_1 and s.MONUM = MONUM_1;
  
这三条查询语句的执行计划发出来看看
------解决方案--------------------
要看是不是走了索引,count(*)很可以能走全表扫描的
------解决方案--------------------