Expert_PL_SQL_Practices-----Do Not Use

1、Row-by-Row Processing

DECLARE
  CURSOR C1 IS
    SELECT Prod_Id, Cust_Id, Time_Id, Amount_Sold
      FROM Sales
     WHERE Amount_Sold > 100;
  C1_Rec            C1%ROWTYPE;
  l_Cust_First_Name Customers.Cust_First_Name%TYPE;
  l_Cust_Last_Name  Customers.Cust_Last_Name%TYPE;
BEGIN
  FOR C1_Rec IN C1 LOOP
    -- Query customer details
    SELECT Cust_First_Name, Cust_Last_Name
      INTO l_Cust_First_Name, l_Cust_Last_Name
      FROM Customers
     WHERE Cust_Id = C1_Rec.Cust_Id;
    --
    -- Insert in to target table
    --
    INSERT INTO Top_Sales_Customers
      (Prod_Id,
       Cust_Id,
       Time_Id,
       Cust_First_Name,
       Cust_Last_Name,
       Amount_Sold)
    VALUES
      (C1_Rec.Prod_Id,
       C1_Rec.Cust_Id,
       C1_Rec.Time_Id,
       l_Cust_First_Name,
       l_Cust_Last_Name,
       C1_Rec.Amount_Sold);
  END LOOP;
  COMMIT;
END;

      花费:00:00:10.93 

      程序声明了游标变量C1,并且使用FOR LOOP游标显式的打开，每一行从游标中检索出来的数据，程序都将查询customer表查询first_name和last_name这两列，随后将向top_sales_customers插入数据。

存在的问题:

      即使LOOP中的语句，进行了很高级别的优化，程序执行的过程中，也可能花费大量的时间，假设查询customers花费01秒，insert语句花费0.1秒，那么每次LOOP循环花费0.2秒。如果游标检索出100000条数据，那么总共花费的时间为100000乘以0.2，20000秒大约是5.5小时。

    另一个问题是:SQL语句在PL/SQ的循环中，所以执行的过程中，将会造成PL/SQL和SQL引擎的相互交互。这种交互被称为上下文交互，上下文交互增加程序的执行时间，并造成了不必要的CUP负担，所以必须减少上下文的交互，通过减少或者消除这两种环境的交互。

   通常你必须避免row-by-row处理数据，如下所示，避免了使用PL/SQL引擎

--
-- Insert in to target table
--
INSERT INTO Top_Sales_Customers
  (Prod_Id, Cust_Id, Time_Id, Cust_First_Name, Cust_Last_Name, Amount_Sold)
  SELECT s.Prod_Id,
         s.Cust_Id,
         s.Time_Id,
         c.Cust_First_Name,
         c.Cust_Last_Name,
         s.Amount_Sold
    FROM Sales s, Customers c
   WHERE s.Cust_Id = c.Cust_Id
     AND s.Amount_Sold > 100;

花费:00:00:00.26

两者花费的时间比较明显，在开发的过程中不推荐一行一行的处理。

2、Nested Row-by-Row Processing

     可以在P/SQL中使用嵌套的游标，将从一级游标中查询出来的数据，放置到二级游标中进行输入的插入，查询出来后，在放置到三级游标中进行数据的过滤，然后在进行数据的更新，假如一级查询出20条数，二级查询出30条数，三级查询出来40条数据，那么总的执行次数就是20*30*40数据。

DECLARE
  CURSOR C1 AS
    SELECT N1 FROM T1;
  CURSOR C2(p_N1) AS
    SELECT N1, N2 FROM T2 WHERE N1 = p_N1;
  CURSOR C3(p_N1, p_N2) AS
    SELECT Text
      FROM T3
     WHERE N1 = p_N1
       AND N2 = p_N2;
BEGIN
  FOR C1_Rec IN C1 LOOP
    FOR C2_Rec IN C2(C1_Rec.N1) LOOP
      FOR C3_Rec IN C3(C2_Rec.N1, C2_Rec.N2) LOOP
        -- execute some sql here; 
        UPDATE … SET ..where n1=c3_rec.n1 AND n2=c3_rec.n2;
      EXCEPTION
    WHEN
    No_Data_Found THEN
       INSERT into… END;
    NULL;
  END LOOP;
END LOOP;
END LOOP; COMMIT; END;

除了性能之外，当在进行数据处理的过程中，假如发现了数据，进行更新，没有发现进行数据的插入，可以使用merge来减少SQL引擎和PL/SQL引擎的负载，merge可以将insert和update进行合并，如果存在数据将进行更新，否则进行插入数据。

可以进行重写上述的过程如下:使用merge进行优化

MERGE INTO Fact1
USING (SELECT DISTINCT C3.N1, C3.N2
         FROM T1, T2, T3
        WHERE T1.N1 = T2.N1
          AND T2.N1 = T3.N1
          AND T2.N2 = T3.N2) t
ON (Fact1.N1 = t.N1 AND Fact1.N2 = t.N2)
WHEN MATCHED THEN
  UPDATE SET ..
WHEN NOT MATCHED THEN
  INSERT ..;
COMMIT;

3、Lookup Queries

Lookup Queries经常被用于变量的值构成和执行数据的校验，执行它可能造成性能上的问题

例如:

DECLARE
  CURSOR C1 IS
    SELECT Prod_Id, Cust_Id, Time_Id, Amount_Sold
      FROM Sales
     WHERE Amount_Sold > 100;
  l_Cust_First_Name Customers.Cust_First_Name%TYPE;
  l_Cust_Last_Name  Customers.Cust_Last_Name%TYPE;
  l_Country_Id      Countries.Country_Id%TYPE;
  l_Country_Name    Countries.Country_Name%TYPE;
BEGIN
  FOR C1_Rec IN C1 LOOP
    -- Query customer details
    SELECT Cust_First_Name, Cust_Last_Name, Country_Id
      INTO l_Cust_First_Name, l_Cust_Last_Name, l_Country_Id
      FROM Customers
     WHERE Cust_Id = C1_Rec.Cust_Id;
    -- Query to get country_name
    SELECT Country_Name
      INTO l_Country_Name
      FROM Countries
     WHERE Country_Id = l_Country_Id;
    --
    -- Insert in to target table
    --
    INSERT INTO Top_Sales_Customers
      (Prod_Id,
       Cust_Id,
       Time_Id,
       Cust_First_Name,
       Cust_Last_Name,
       Amount_Sold,
       Country_Name)
    VALUES
      (C1_Rec.Prod_Id,
       C1_Rec.Cust_Id,
       C1_Rec.Time_Id,
       l_Cust_First_Name,
       l_Cust_Last_Name,
       C1_Rec.Amount_Sold,
       l_Country_Name);
  END LOOP;
  COMMIT;
END;

每次都要进行国家名字的查询，这样子效率很不高，有两种优化方式:

1、使用join进行连接。

2、定义一个联合数据，用来缓存结果和在之后的查询中重复利用array，将(country_id, country_name) 在嵌套表中进行键值对的映射，在查询数据的时候，首先使用集合的EXISTS方法，判断，该国家ID对应的名字是否已经映射到了嵌套表中，否则进行查询，将结果放到嵌套表中。如下:

DECLARE
  CURSOR C1 IS
    SELECT Prod_Id, Cust_Id, Time_Id, Amount_Sold
      FROM Sales
     WHERE Amount_Sold > 100;
  l_Country_Names   Country_Names_Type;
  l_Country_Id      Countries.Country_Id%TYPE;
  l_Country_Name    Countries.Country_Name%TYPE;
  l_Cust_First_Name Customers.Cust_First_Name%TYPE;
  l_Cust_Last_Name  Customers.Cust_Last_Name%TYPE;
  TYPE Country_Names_Type IS TABLE OF VARCHAR2(40) INDEX BY PLS_INTEGER;
  l_Country_Names Country_Names_Type;--定义嵌套表
BEGIN
  FOR C1_Rec IN C1 LOOP
    -- Query customer details
    SELECT Cust_First_Name, Cust_Last_Name, Country_Id
      INTO l_Cust_First_Name, l_Cust_Last_Name, l_Country_Id
      FROM Customers
     WHERE Cust_Id = C1_Rec.Cust_Id;
20     -- Check array first before executing a SQL statement
21     IF (l_Country_Names.Exists(l_Country_Id)) THEN
22       l_Country_Name := l_Country_Names(l_Country_Id);
23     ELSE 
24       SELECT Country_Name
25         INTO l_Country_Name
26         FROM Countries
27        WHERE Country_Id = l_Country_Id;
28       -- Store in the array for further reuse
29       l_Country_Names(l_Country_Id) := l_Country_Name;
30     END IF;
    --判断嵌套表中是否有值
    -- Insert in to target table
    --
    INSERT INTO Top_Sales_Customers
      (Prod_Id,
       Cust_Id,
       Time_Id,
       Cust_First_Name,
       Cust_Last_Name,
       Amount_Sold,
       Country_Name)
    VALUES
      (C1_Rec.Prod_Id,
       C1_Rec.Cust_Id,
       C1_Rec.Time_Id,
       l_Cust_First_Name,
       l_Cust_Last_Name,
       C1_Rec.Amount_Sold,
       l_Country_Name);
  END LOOP;
  COMMIT;
END;

基于嵌套表的技巧可以可以应用到其他的场景中，消除一些不必要的工作。

4、Excessive Access to DUAL

 过量的访问dual表，导致上下文的交互，会对性能产生伤害，在进行一些数字或者日期的运算的时候，不必要借助dual表来实现，因为在PL/SQL中可以直接访问所有的函数，并进行运算，要减少在程序中访问dual表。

5、Populating Master-Detail Rows

在开发的过程中，可以借用returning子句，来返回相应的信息，一般返回的主键ID，

INSERT INTO customers (cust_id, ...)
VALUES (cust_id_seq.nextval,...)
RETURNING cust_id into l_cust_id;
...
INSERT INTO customer_transactions (cust_id, ...)
VALUES (l_cust_id,...)
...

但是，也可以先定义一个变量，例如

v_id = sys_guid();
insert into table1 values(v_id......);
insert into table2 values(sys_guid(),v_id.....)

可以先声明一个变量，在插入数据之前，对ID进行赋值，在进行数据的插入操作。

6、Excessive Function Calls

防止不必要的函数调用，对函数进行优化，可以一次完成的事情，坚决不再两次完成。

7、Costly Function Calls

注意:一般不要在循环中调用函数，假如数据量会造成严重的性能问题。

(这里所说的循环-包括，对一个表中的数据进行查询，包含多行，但是每一行的都要进行函数的调用，所以也造成了循环查询的问题)

可以对函数进行优化，对函数建立索引:例如:

函数:

CREATE OR REPLACE FUNCTION calculate_epoch (d in date)
RETURN NUMBER DETERMINISTIC IS
l_epoch number;
BEGIN
l_epoch := (d - TO_DATE('01-JAN-1970 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))
* 24 *60 *60 ;
RETURN l_epoch;
END calculate_epoch;
/

建立的索引:

CREATE INDEX compute_epoch_fbi ON sales
(calculate_epoch(time_id))
Parallel (degree 4);

通过给函数建立索引，可以提高过程的执行效率，大大减少运行时间。

Oracle 11G提供了一个新的解决方案，

在建立函数的时候，为函数设置缓存

DROP INDEX compute_epoch_fbi;
CREATE OR REPLACE FUNCTION calculate_epoch (d in date)
RETURN NUMBER DETERMINISTIC RESULT_CACHE IS
l_epoch number;
BEGIN
l_epoch := (d - TO_DATE('01-JAN-1970 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))
* 24 *60 *60 ;
RETURN l_epoch;
END calculate_epoch;
/

可以使用result_cache来解决问题，当使用该参数后，效率明显提高。

8、Excessive Commits

频繁的提交会造成会产生更多的redo log(重做日志)，需要写日志进程频繁的刷新缓存数据到日志文件，这样会造成数据的不一致性。应该减少提交的次数，可以使用批量提交，大约1000-5000行提交一次数据。

9、Excessive Parsing

不要在PL/SQL的循环中使用LOOP循环，因为每一次执行都会进行解析，造成解析上的问题，但是，相反的可以使用绑定变量来提高性能，避免这个问题。

Summary

       SQL is a set language and PL/SQL is a procedural language(SQL是一个集合的语言，但是PL/SQL是一个过程化的语言)

在编写PL/SQL的过程中，应该遵守以下的规则:

• Solve query problems using SQL. Think in terms of sets! It’s easier to tune queries written in SQL than to tune, say, PL/SQL programs having nested loops to essentially execute queries using row-at-a-time processing.(解决查询问题，使用SQL，有时候，SQL语句一句话就可以完成，但是在PL/SQL中，需要使用嵌套循环)
• If you must code your program in PL/SQL, try offloading work to the SQL engine as much as possible. (如果需要在PL/SQL中进行编程，尽可能的减少访问SQL引擎，减少PL/SQL引擎和SQL引擎的交互)
• Use bulk processing facilities available in PL/SQL if you must use loop-based processing. Reduce unnecessary work in PL/SQL such as unnecessary execution of functions or excessive access to DUAL (可以使用批量操作，减少循环的处理，减少不必要的函数执行和DUAL表的访问)
• Use single-row, loop-based processing only as a last resort.(对于单行数据，在万不得已的时候，使用循环)

 --