sql - 优化存储过程循环以提高性能

Question

产品表:

PID START_DATE  END_DATE    PRODUCT_SET_ID  CREATED_DATE    UPDATED_DATE    PRODUCT_NAME    COMPANY_NM  PRICE
    1   04/01/11    06/30/11    12              09/06/16        09/06/16        Apple           ABC         50
    2   04/01/10    06/30/10    12              09/06/16        09/06/16        Toothpaste      PQR         80
    3   07/01/11    09/30/11    12              09/06/16        09/06/16        Soap            DOVE        53
    4   04/01/12    06/30/12    12              09/06/16        09/06/16        TV              ONIDA       50000

订购表:

OID PID PRODUCT_ID  SEQ_ID  TYPE_ID CREATED_DATE    UPDATED_DATE    NUMBER_VALUE    TEXT_VALUE
11  1   1           1       1       09/06/16        09/06/16        5
12  1   2           1       6       09/06/16        09/06/16        50  
13  2   1           1       3       09/06/16        09/06/16        3
14  2   2           1       7       09/06/16        09/06/16        80  

VA表:

Product_ID  Product_Name
1           Apple
2           Orange
3           Toothpaste
4           Soap
5           TV
6           ABC
7           PQR
8           DOVE
9           ONIDA

VA_IN_TB

TB_NM       COL_NM     PRODUCT_ID   SEQ_ID
Product_TB  Apple      1           1
Product_TB  Orange     2           1
Product_TB  Toothpaste 3           1
Product_TB  Soap       4           1
Product_TB  TV         5           1
Product_TB  ABC        6           1
Product_TB  PQR        7           1
Product_TB  DOVE       8           1
Product_TB  ONIDA      9           1

我还在产品表中添加了索引:

CREATE INDEX INDX_PID ON PRODUCT_TABLE(PID);

生成订单表的存储过程:

REC_COUNT:= SELECT COUNT(*) FROM PRODUCT_TABLE;
Loop_CT :=( REC_COUNT/2000000) +1;

SELECT MIN(PID) INTO LOWER_LIMIT FROM PRODUCT_TABLE;
UPPER_LIMIT := LOWER_LIMIT +2000000;

FOR i in 1..LOOP_COUNT LOOP
Create Table Temp_1 Nologging as
SELECT  ORDER_SEQ.NEXTVAL OID,
        A.PID,A.PRODUCT_ID,
        A.SEQ_ID,
        A.VALUE,
        B.TYPE_ID 
FROM 
    (
    SELECT  A.PID,
            A.VA_ID,
            A.SEQ_ID,
            A.VALUE,
            B.TYPE_NAME 
    FROM
        (
        select  A.PID, 
                B.PRODUCT_ID,
                B.SEQ_ID, 
                PRODUCT_NAME VALUE 
        from PRODUCT_TABLE A 
        JOIN VA_IN_TB B 
            ON REPLACE(A.PRODUCT_NAME,'''','')=REPLACE(B.COL_NM,'''','') 
            AND A.PID BETWEEN LOWER_LIMIT AND UPPER_LIMIT
        union all
        select  A.PID,
                B.PRODUCT_ID,
                B.SEQ_ID, 
                PRICE VALUE 
        from PRODUCT_TABLE A 
        JOIN VA_IN_TB B 
            ON REPLACE(A.COMPANY_N,'''','')=REPLACE(B.COL_NM,'''','')
            AND A.PID BETWEEN LOWER_LIMIT AND UPPER_LIMIT
        ) A 
    LEFT JOIN VA_TB B 
        ON A.PRODUCT_ID=B.PRODUCT_ID
    ) A 
LEFT JOIN VA_TB B 
    ON A.TYPE_NAME=B.PRODUCT_NAME

Insert INTO ORDER_TB
SELECT * FROM TEMP_1;
Commit;

DROP TABLE TEMP_1;
LOWER_LIMIT := UPPER_LIMIT + 1;
UPPER_LIMIT := UPPER_LIMIT + 2000000;


End LOOP;

我们在产品表中有 2000 万条记录，我需要使用上述存储过程根据产品表生成订单表。我已经尝试尽可能多地优化，但是在 Oracle Standard One 上执行仍然需要 8 多个小时。如何优化此代码？

Answer 1

您将数据选择到临时表中，然后将临时表的内容复制到表中。为什么不直接选择数据入表？

Insert INTO ORDER_TB
select order_seq.nextval oid
      ,a.pid
      ,a.product_id
      ,a.seq_id
      ,a.value
      ,b.type_id
  from (select a.pid
              ,a.va_id
              ,a.seq_id
              ,a.value
              ,b.type_name
          from (select a.pid
                      ,b.product_id
                      ,b.seq_id
                      ,product_name value
                  from product_table a
                  join va_in_tb b
                    on replace(a.product_name, '''', '') = replace(b.col_nm, '''', '')
                   and a.pid between lower_limit and upper_limit
                union all
                select a.pid
                      ,b.product_id
                      ,b.seq_id
                      ,price value
                  from product_table a
                  join va_in_tb b
                    on replace(a.company_n, '''', '') = replace(b.col_nm, '''', '')
                   and a.pid between lower_limit and upper_limit) a
          left join va_tb b
            on a.product_id = b.product_id) a
  left join va_tb b
    on a.type_name = b.product_name