Oracle PipeLined方式究竟能提升多少性能

最近在使用到了Oracle的表函数，尤其是实现嵌套表的数据按表结构进行返回，发现PIPELINED方式，确实能让性能有非常高的提升。

Oracle PipeLined：

 看了相关的文章，大致是将数据进行按流水线方式进行处理，执行无等待，而不是那种传统的将本次数据全处理完了，再送往下一个处理环节。

最近找了个时间，特意进行了一下测试。

结论：

1 表函数以嵌套表返回时，是非常耗时与耗内存的；

2 PIPELINED方式，能让数据无等待，效率非常高，尤其是应用在Oracle的表函数返回；

不受SQL语句类型的限制，都能有很好的性能提升，尤其是在大数据量的情况下：

1) select * from table(表函数())

2)还是select count(*) from table(表函数())  （本次过程不体现，但是实际应用使用中确实性能较非PIPELINED方式有非常高的性能提升）

3) 还是select * from table(表函数()) where conditions（本次过程不体现，但是应用中确实性能较非PIPELINED方式有非常高的性能提升）

下面来看具体步骤

1 创建两个辅助类型

CREATE OR REPLACE TYPE performance_e_v IS OBJECT
(
  pid          INTEGER ,
  persionid    VARCHAR2(40),
  datefield    DATE,
  name         VARCHAR2(40),
  account      VARCHAR2(20),
  balance      NUMBER,
  securitycode VARCHAR2(60)
);
/
create or replace type performance_TABLE as table of performance_e_v;
/

2 创建一个Oracle Package，里面包含两个函数，一个以PIPELINED方式执行，一个以嵌套表方式返回

create or replace package TEST_PIPELLINED is

  -- Author  : ADMINISTRATOR
  -- Created : 2013-4-11 15:57:21
  -- Purpose : 
  
  -- Public type declarations
  ret_table performance_TABLE := performance_TABLE();
  
  -- Public constant declarations

  -- Public variable declarations
  
  -- Public function and procedure declarations
  -- Test with pipelined
  FUNCTION WITH_Pipelined  RETURN performance_TABLE PIPELINED;
  
  -- test with no pipelined 
  FUNCTION WITH_NOPipelined  RETURN performance_TABLE;

end TEST_PIPELLINED;
/
create or replace package body TEST_PIPELLINED is

  --前后两次执行都以PIPELINED方式返回
  FUNCTION WITH_Pipelined  RETURN performance_TABLE PIPELINED
  AS
      --TYPE rowidcur IS REF CURSOR;
      --retTab performance_e_TABLE := performance_e_TABLE();
      --out_rec performance_e_v:=performance_e_v(0,'','','','',0,'');
  BEGIN
      --如果嵌套表里的数据有的话，直接从嵌套表里捞数据送往PIPELINED
      IF ret_table.COUNT>0 THEN 
         --将嵌套表的记录遍历到pipelined
         FOR j IN 1 .. ret_table.count LOOP
            PIPE ROW(ret_table(j));
         END LOOP;
         RETURN;
      END IF;
      --往嵌套表里插入50万条相同记录
      FOR j IN 1 .. 500000 LOOP
          ret_table.EXTEND(1);
          ret_table(ret_table.LAST) := performance_e_v(1,'',sysdate,'T02JXD9IVGGS5G01J5NDEBQKHBQO8L9V','13834749031',0,'IgGOHBUqOO岯Ex沭YruyXatWgdvlKgju熄眖嶿e缻F侁pG緝VL');
      END LOOP;
      --将嵌套表的记录遍历到pipelined
      FOR j IN 1 .. ret_table.count LOOP
            PIPE ROW(ret_table(j));
      END LOOP;
      
  END WITH_Pipelined;
  
  --两次执行都以嵌套表返回
  FUNCTION WITH_NOPipelined  RETURN performance_TABLE
  AS
      --TYPE rowidcur IS REF CURSOR;
      --retTab performance_e_TABLE := performance_e_TABLE();
      --out_rec performance_e_v:=performance_e_v(0,'','','','',0,'');
  BEGIN
      --如果嵌套表里的数据有的话，直接返回的嵌套表
      IF ret_table.COUNT>0 THEN 
         RETURN ret_table;
      END IF;
      --往嵌套表里插入50万条相同记录(也可以是从一个表里游标遍历过来)
      FOR j IN 1 .. 500000 LOOP
          ret_table.EXTEND(1);
          ret_table(ret_table.LAST) := performance_e_v(1,'',sysdate,'T02JXD9IVGGS5G01J5NDEBQKHBQO8L9V','13834749031',0,'IgGOHBUqOO岯Ex沭YruyXatWgdvlKgju熄眖嶿e缻F侁pG緝VL');
      END LOOP;
      return ret_table;
  END WITH_NOPipelined;
  
end TEST_PIPELLINED;
/

3 下面我们来看测试结果

3.1 采用非PIPELINED方式执行(嵌套表方式返回)

C:\Documents and Settings\Administrator>sqlplus tpcctest/******@102.com

SQL*Plus: Release 10.2.0.4.0 - Production on 星期四 4月 11 16:30:24 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set timing on
SQL> select count(*) from table(TEST_PIPELLINED.WITH_NOPIPELINED());

  COUNT(*)
----------
    500000

已用时间:  00: 00: 29.73
SQL> select count(*) from table(TEST_PIPELLINED.WITH_NOPIPELINED());

  COUNT(*)
----------
    500000

已用时间:  00: 00: 29.75
SQL> quit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options 断开

3.2 采用PIPELINED方式执行

C:\Documents and Settings\Administrator>sqlplus tpcctest/******@102.com

SQL*Plus: Release 10.2.0.4.0 - Production on 星期四 4月 11 16:40:03 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set timing on
SQL> select count(*) from table(TEST_PIPELLINED.WITH_PIPELINED());

  COUNT(*)
----------
    500000

已用时间:  00: 00: 01.37
SQL> select count(*) from table(TEST_PIPELLINED.WITH_PIPELINED());

  COUNT(*)
----------
    500000

已用时间:  00: 00: 00.25
SQL> select count(*) from table(TEST_PIPELLINED.WITH_PIPELINED());

  COUNT(*)
----------
    500000

已用时间:  00: 00: 00.23
SQL> quit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options 断开