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 创建两个辅助类型
[sql]
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方式执行，一个以嵌套表方式返回
[sql]
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方式执行(嵌套表方式返回)
[sql]
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
<span style="color:#ff0000;">已用时间: 00: 00: 29.73</span>
SQL> select count(*) from table(TEST_PIPELLINED.W
