高手过来看看,100分求优化存储过程,有效果马上给分

Question

回复: bobfang(匆匆过客) 
谢谢,以下是相关表的定义
zccm0003:
-- Create table
create table ZCCM0003
(
  CUSTOMER_PART_DESC   CHAR(40) not null,
  CUSTOMER_PART_NUMBER CHAR(20) not null,
  CUST_CODE            CHAR(5) not null,
  INVOICE_NUMBER       CHAR(10),
  FIFO_DATE            DATE not null,
  LINE_NO              NUMBER(10) not null,
  QUANTITY             FLOAT not null,
  QUANTITY_INVOICED    FLOAT not null,
  SCH_DATE             DATE,
  CREATE_DATE          DATE not null,
  CREATE_BY            CHAR(30) not null,
  PART_RKEY            NUMBER(10) not null,
  CUSTOMER_RKEY        NUMBER(10) not null,
  INVOICE_RKEY         NUMBER(10) not null,
  ADJ_INVOICE          CHAR(10)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table ZCCM0003
  add primary key (CUSTOMER_PART_DESC,CUSTOMER_PART_NUMBER,CUST_CODE,FIFO_DATE,LINE_NO,INVOICE_RKEY)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index D4 on ZCCM0003 (CUSTOMER_PART_DESC,CUSTOMER_PART_NUMBER,CUST_CODE)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index ZCCM0003_IDX1 on ZCCM0003 (INVOICE_RKEY)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select on ZCCM0003 to EPCAPPS;
grant select, insert, update, delete, references, alter, index on ZCCM0003 to JACKYHO with grant option;
grant select on ZCCM0003 to TAPPS;zccm0001:
create table ZCCM0001
(
  CH_INVOICE             CHAR(10) not null,
  CH_DATE                DATE not null,
  CH_CUST_CODE           CHAR(5),
  CH_CONSIGNEE           CHAR(45),
  CH_BILL_ADDRESS_1      CHAR(30),
  CH_BILL_ADDRESS_2      CHAR(30),
  CH_BILL_ADDRESS_3      CHAR(30),
  CH_BILL_ADDRESS_4      CHAR(30),
  CH_STATE               CHAR(3),
  CH_ZIP                 CHAR(10),
  CH_COUNTRY_CODE        CHAR(20),
  CH_COUNTRY_NAME        CHAR(30),
  CH_CUST_SHIP_NAME      CHAR(20),
  CH_SHIP_TO_ADDRESS_1   CHAR(30),
  CH_SHIP_TO_ADDRESS_2   CHAR(30),
  CH_SHIP_TO_ADDRESS_3   CHAR(30),
  CH_SHIP_TO_ADDRESS_4   CHAR(30),
  CH_CONSIGN_ADDRESS_1   CHAR(45),
  CH_CONSIGN_ADDRESS_2   CHAR(45),
  CH_CONSIGN_ADDRESS_3   CHAR(45),
  CH_REF_INVOICE         CHAR(10),
  CH_REF_INV_RKEY        NUMBER(10),
  CH_CURR                CHAR(5),
  CH_EXCH_RATE           NUMBER(20,5),
  CH_TOOLING_CHARGE      NUMBER(20,5),
  CH_DISCOUNT_AMOUNT     NUMBER(20,5),
  CH_INV_AMOUNT          NUMBER(20,5) not null,
  CH_SCH_DATE            DATE,
  CH_DUE_DATE            DATE,
  CH_PO_NUMBER           CHAR(40),
  CH_SHIP_METHOD         CHAR(40),
  CH_PORT_DISCHARGE      CHAR(20),
  CH_COUNTRY_ORIGIN      CHAR(30),
  CH_PORT_DESTINATION    CHAR(20),
  CH_COUNTRY_DESTINATION CHAR(30),
  CH_FORWARDER           CHAR(20),
  CH_INVOICE_NOTE_1      CHAR(70),
  CH_INVOICE_NOTE_2      CHAR(70),
  CH_INVOICE_NOTE        VARCHAR2(4000),
  CH_CUSTOMER_NOTE       VARCHAR2(4000),
  CH_COUNTRY_NOTE        VARCHAR2(4000),
  CH_REMARKS             VARCHAR2(100),
  CH_DRCR                CHAR(1),
  CH_RCD_STATUS          NUMBER,
  CH_PRT_STATUS          NUMBER,
  CH_MODIFY_DATE         DATE,
  CH_MODIFY_BY           CHAR(30),
  CH_CREATE_DATE         DATE,
  CH_CREATE_BY           CHAR(30),
  CH_INV_HEADER          NUMBER,
  CH_CUST_PICK_NOTE      CHAR(20),
  CH_ADJ                 NUMBER(10),
  CH_ADJ_REASON          CHAR(10)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table ZCCM0001
  add primary key (CH_INVOICE)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select on ZCCM0001 to EPCAPPS;
grant select, update on ZCCM0001 to JACKYHO with grant option;
grant select on ZCCM0001 to TAPPS;

Answer 1

接上:
ZFIN0005:
create table ZFIN0005
(
  INVOICE_TYPE         CHAR(2) not null,
  INVOICE_NUMBER       CHAR(10) not null,
  INVOICE_DATE         DATE not null,
  CUST_CODE            CHAR(5) not null,
  CUSTOMER_NAME        CHAR(30),
  ABBR_NAME            CHAR(10),
  BILLING_ADDRESS_1    VARCHAR2(30),
  BILLING_ADDRESS_2    VARCHAR2(30),
  BILLING_ADDRESS_3    VARCHAR2(30),
  BILLING_ADDRESS_4    VARCHAR2(30),
  STATE                VARCHAR2(3),
  ZIP                  VARCHAR2(10),
  BILLING_COUNTRY_CODE VARCHAR2(5),
  BILLING_COUNTRY_NAME VARCHAR2(30),
  PHONE                VARCHAR2(14),
  FAX                  VARCHAR2(14),
  NET_PAY              NUMBER,
  CUST_SHIP_NAME       VARCHAR2(20),
  SHIP_TO_ADDRESS_1    VARCHAR2(30),
  SHIP_TO_ADDRESS_2    VARCHAR2(30),
  SHIP_TO_ADDRESS_3    VARCHAR2(30),
  SHIP_TO_ADDRESS_4    VARCHAR2(30),
  CONSIGNEE            VARCHAR2(45),
  CONSIGN_ADDRESS_1    VARCHAR2(45),
  CONSIGN_ADDRESS_2    VARCHAR2(45),
  CONSIGN_ADDRESS_3    VARCHAR2(45),
  SALES_ORDER          VARCHAR2(10),
  PO_NUMBER            CHAR(40),
  PO_DATE              DATE,
  LINE_NO              NUMBER not null,
  CUSTOMER_PART_NUMBER VARCHAR2(20),
  CUSTOMER_PART_DESC   VARCHAR2(40),
  FOB                  VARCHAR2(20),
  DISCOUNT_SALES_ORDER FLOAT,
  NO_OF_CARTONS        NUMBER,
  NO_OF_PANEL          NUMBER,
  QUANTITY_INVOICED    FLOAT not null,
  PART_PRICE           NUMBER(20,5),
  SO_CURR              VARCHAR2(5),
  SO_EXCH_RATE         NUMBER,
  UNIT_PRICE           NUMBER(20,5) not null,
  TOOLING_CHARGES      NUMBER(20,5),
  TOOLING_INV          VARCHAR2(10),
  DISCOUNT_AMOUNT      NUMBER(20,5),
  INVOICE_CURR         VARCHAR2(5),
  INVOICE_CURR_NAME    VARCHAR2(40),
  INVOICE_EXCH_RATE    FLOAT not null,
  INVOICE_TOTAL        NUMBER(20,5) not null,
  SCH_DATE             DATE,
  DUE_DATE             DATE,
  VEHICLE              VARCHAR2(40),
  PORT_DISCHARGE       VARCHAR2(20),
  COUNTRY_ORIGIN       VARCHAR2(30),
  PORT_DESTINATION     VARCHAR2(20),
  COUNTRY_DESTINATION  VARCHAR2(30),
  FORWARDER            VARCHAR2(20),
  INVOICE_STATUS       NUMBER,
  INVOICE_NOTE_1       VARCHAR2(70),
  INVOICE_NOTE_2       VARCHAR2(70),
  INVOICE_NOTE         VARCHAR2(4000),
  CUSTOMER_NOTE        VARCHAR2(4000),
  COUNTRY_NOTE         VARCHAR2(4000),
  REMARKS              VARCHAR2(30),
  DRCR                 VARCHAR2(1),
  IFC_TYPE             VARCHAR2(5),
  IFC_USER             VARCHAR2(30),
  IFC_DATE             DATE,
  IFC_STATUS           NUMBER not null,
  FILL_DATE            DATE not null,
  FILL_STATUS          NUMBER not null,
  FILL_BY              VARCHAR2(30),
  INVOICE_RKEY         NUMBER(10),
  CUSTOMER_RKEY        NUMBER(10),
  CUST_SHIP_RKEY       NUMBER(10),
  SO_RKEY              NUMBER(10),
  PART_RKEY            NUMBER(10),
  CUSTOMER_PO_RKEY     NUMBER(10),
  SO_SHIP_RKEY         NUMBER(10),
  CR_NOTE_RKEY         NUMBER(10)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table ZFIN0005
  add constraint INVOICE_TYPE unique (INVOICE_TYPE,INVOICE_NUMBER)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index D5 on ZFIN0005 (INVOICE_RKEY)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index D6 on ZFIN0005 (PO_NUMBER)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index ZFIN0003_IDX1 on ZFIN0005 (INVOICE_RKEY,INVOICE_DATE)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select on ZFIN0005 to EPCAPPS;
grant select on ZFIN0005 to TAPPS;

Answer 2

接上:
ZDTA0010:
create table ZDTA0010
(
  RKEY                 NUMBER(10) not null,
  CUST_CODE            CHAR(5),
  CUSTOMER_NAME        CHAR(30),
  ABBR_NAME            CHAR(10),
  BILLING_ADDRESS_1    CHAR(30),
  BILLING_ADDRESS_2    CHAR(30),
  BILLING_ADDRESS_3    CHAR(30),
  BILLING_ADDRESS_4    CHAR(30),
  STATE                CHAR(3),
  ZIP                  CHAR(10),
  BILLING_COUNTRY_CODE CHAR(5),
  BILLING_COUNTRY_NAME CHAR(30),
  PHONE                CHAR(14),
  FAX                  CHAR(14),
  CUSTOMER_TYPE        CHAR(20),
  CUSTOMER_TYPE_DESC   CHAR(30),
  SALESMAN_TYPE        CHAR(30),
  SALESMAN_CODE        CHAR(20),
  SALESMAN_DESC        CHAR(30),
  CONTINENT            CHAR(30),
  COUNTRY_CODE         CHAR(20),
  COUNTRY_DESC         CHAR(30),
  NET_PAY              NUMBER(10),
  CUST_SHIP_NAME       CHAR(20),
  SHIP_TO_ADDRESS_1    CHAR(30),
  SHIP_TO_ADDRESS_2    CHAR(30),
  SHIP_TO_ADDRESS_3    CHAR(30),
  SHIP_TO_ADDRESS_4    CHAR(30),
  CONSIGNEE            CHAR(45),
  CONSIGN_ADDRESS_1    CHAR(45),
  CONSIGN_ADDRESS_2    CHAR(45),
  CONSIGN_ADDRESS_3    CHAR(45),
  FILL_DATE            DATE not null,
  FILL_STATUS          NUMBER not null,
  FILL_BY              CHAR(30)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table ZDTA0010
  add primary key (RKEY)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select on ZDTA0010 to EPCAPPS;
grant select on ZDTA0010 to TAPPS;ZMST0001:
create table ZMST0001
(
  MASTERKEY1   CHAR(50) not null,
  MASTERKEY2   CHAR(100) not null,
  SEQUENCE     NUMBER(10) not null,
  MASTERFIELD1 CHAR(50),
  MASTERFIELD2 CHAR(50),
  DESCRIPTION  VARCHAR2(4000)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table ZMST0001
  add primary key (MASTERKEY1,MASTERKEY2,SEQUENCE)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select on ZMST0001 to EPCAPPS;
grant select, insert, update, delete, references, alter, index on ZMST0001 to JACKYHO with grant option;
grant select on ZMST0001 to TAPPS;ZFIN0007:create table ZFIN0007
(
  PART_RKEY   NUMBER(10) not null,
  VALID_DATE  DATE not null,
  DEF_VALUE   NUMBER(2) not null,
  UNIT_PRICE  NUMBER(19,4) not null,
  VALID_PRICE NUMBER(19,4) not null,
  CRT_DATE    DATE not null,
  CRT_BY      CHAR(50) not null,
  MOD_DATE    DATE,
  MOD_BY      CHAR(50)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table ZFIN0007
  add primary key (PART_RKEY,VALID_DATE)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select on ZFIN0007 to EPCAPPS;
grant select on ZFIN0007 to TAPPS;
HY_CCM003WORK:
create table HY_CCM003WORK
(
  CUST_CODE            VARCHAR2(10),
  CUSTOMER_NAME        VARCHAR2(30),
  CUSTOMER_PART_DESC   VARCHAR2(50),
  CUSTOMER_PART_NUMBER VARCHAR2(30),
  BALANCE_QTY          NUMBER default 0,
  UNIT_PRICE           NUMBER default 0,
  QTY1                 NUMBER default 0,
  QTY2                 NUMBER default 0,
  QTY3                 NUMBER default 0,
  QTY4                 NUMBER default 0,
  QTY5                 NUMBER default 0,
  QTY6                 NUMBER default 0,
  QTY7                 NUMBER default 0
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Answer 3

需要改的：
1、open aging_cur for...处的逻辑似乎有问题。
2、表HY_CCM003WORK必须要增加索引。按过程中的逻辑，可以增加一个按CUST_CODE和CUSTOMER_PART_NUMBER列的主键。
3、表ZDTA0010须加按cust_code的索引。
4、表ZFIN0005需要增加按PART_RKEY和INVOICE_DATE的索引。
5、函数fun_get_partprice中的select count(*) into ...语句的order by没有必要，可以去除。
6、函数fun_get_partprice中的select ... into ... where ... and rownum<2 order by ...语句实际上执行的结果与你想要的不同，需改为select ... into ... from (select ... from ... where ... order by ...) where rownum=1;

Answer 4

非常感谢bobfang(匆匆过客)的热心帮主,
不过有个问题想问问你,
6、函数fun_get_partprice中的select ... into ... where ... and rownum<2 order by ...语句实际上执行的结果与你想要的不同，需改为select ... into ... from (select ... from ... where ... order by ...) where rownum=1
改成后面那种写法以后该条语句就慢了好几倍啊
那种一来岂不是整个存储过程更慢了啊??

Answer 5

非常感谢bobfang(匆匆过客)的热心帮助,在给表增加索引以后,处理速度已经快令我不敢相信了,真的非常感谢,不错你说的第一个地方:open aging_cur for...处的逻辑似乎有问题,我看不出来那里有问题啊?能否指点一下??谢谢

Answer 6

根据你原先所写的中可以明显看出一处的错误，改写后如下：
     SELECT * FROM ZCCM0003 c3
     WHERE (    c3.ADJ_INVOICE is null
            and exists (select f5.INVOICE_RKEY from ZFIN0005 f5
                            where f5.INVOICE_RKEY = c3.INVOICE_RKEY
                              and f5.INVOICE_DATE <= to_date(cut_date,'yyyy-mm-dd')
                              and f5.INVOICE_TYPE = 'PA'
                       )
            and (   c3.INVOICE_NUMBER is null
                 or exists (select CH_INVOICE from ZCCM0001
                                where CH_INVOICE = c3.INVOICE_NUMBER
                                  and CH_RCD_STATUS <> 8
                                  and CH_DATE >  to_date(cut_date,'yyyy-mm-dd')
                           )
                )
           )                       --此处比原先增加一个括号
        or (    c3.ADJ_INVOICE is not null
            and exists (select CH_INVOICE from ZCCM0001
                            where CH_DATE <= to_date(cut_date,'yyyy-mm-dd')
                              and CH_RCD_STATUS <> 8
                              and CH_INVOICE = c3.ADJ_INVOICE
                       )
            and (   c3.INVOICE_NUMBER is null
                 or exists (select CH_INVOICE from ZCCM0001
                                where CH_INVOICE = c3.INVOICE_NUMBER
                                  and CH_RCD_STATUS <> 8
                                  and CH_DATE > to_date(cut_date,'yyyy-mm-dd')
                           )
                )
           );                   --此处比原先减少一个括号
也就是你可能是想要对ADJ_INVOICE是否为null做不同处理。
上面的语句还可以简化为
     SELECT * FROM ZCCM0003 c3
     WHERE（  (    c3.ADJ_INVOICE is null
               and exists (select f5.INVOICE_RKEY from ZFIN0005 f5
                               where f5.INVOICE_RKEY = c3.INVOICE_RKEY
                                 and f5.INVOICE_DATE <= to_date(cut_date,'yyyy-mm-dd')
                                 and f5.INVOICE_TYPE = 'PA'
                          )
              )                       --此处比原先增加一个括号
           or (    c3.ADJ_INVOICE is not null
               and exists (select CH_INVOICE from ZCCM0001
                               where CH_DATE <= to_date(cut_date,'yyyy-mm-dd')
                                 and CH_RCD_STATUS <> 8
                                 and CH_INVOICE = c3.ADJ_INVOICE
                          )
              )
          )
       and(   c3.INVOICE_NUMBER is null
           or exists (select CH_INVOICE from ZCCM0001
                           where CH_INVOICE = c3.INVOICE_NUMBER
                             and CH_RCD_STATUS <> 8
                             and CH_DATE > to_date(cut_date,'yyyy-mm-dd')
                      )
           );                   --此处比原先减少一个括号但上面的语句可能还不一定是你想要做的，你应当先搞清楚自己的逻辑。