Oracle PipeLined方式究竟能提升多少性能
最新回答
何必纠缠
2026-03-22 04:13:30
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 PIPELINEDAS
--TYPE rowidcur IS REF CURSOR;
--retTab performance_e_TABLE := performance_e_TABLE();--out_rec performance_e_v:=performance_e_v(0,'','','','',0,'');BEGIN
--如果嵌套表里的数据有的话，直接从嵌套表里捞数据送往PIPELINEDIF 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_TABLEAS
--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.comSQL*Plus: Release 10.2.0.4.0 - Production on 星期四 4月 11 16:30:24 2013Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> 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.WITH_NOPIPELINED());COUNT(*)
----------
500000
<span style="color:#ff0000;">已用时间: 00: 00: 29.75</span>
SQL> quit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options 断开3.2 采用PIPELINED方式执行
[sql]
C:\Documents and Settings\Administrator>sqlplus tpcctest/******@102.comSQL*Plus: Release 10.2.0.4.0 - Production on 星期四 4月 11 16:40:03 2013Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> set timing on
SQL> select count(*) from table(TEST_PIPELLINED.WITH_PIPELINED());COUNT(*)
----------
500000
<span style="color:#ff0000;">已用时间: 00: 00: 01.37</span>
SQL> select count(*) from table(TEST_PIPELLINED.WITH_PIPELINED());COUNT(*)
----------
500000
<span style="color:#ff0000;">已用时间: 00: 00: 00.25</span>
SQL> select count(*) from table(TEST_PIPELLINED.WITH_PIPELINED());COUNT(*)
----------
500000
<span style="color:#ff0000;">已用时间: 00: 00: 00.23</span>
SQL> quit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options 断开
热门标签
