使用pipeline和并行的存储过程，记录一下。
Parallel Query应该满足两个条件
1。大的，运行时间很长的查询
2。有足够的系统资源
parallel query is essentially nonscalable.如果有很多用户执行pq，那么实际效果会很差，因为
很多用户会造成系统资源的竞争。比如在OLTP里面，qc就不合适。
Parallel DML
必须显式指定 alter session enable parallel dml;否则不会发生
Parallel DDL
可以并行的DDL(9i)
create index
create table as select
alter index rebuild
alter table move
alter table split|coalesce partition
alter index split partition
------------------------------------------------------------------------
---改写串行存储过程，使用管道函数和并行 ------------------------------------------
------------------------------------------------------------------------
使用的表结构如下
test@DW> desc t1
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
X NUMBER
Y VARCHAR2(128)
Z VARCHAR2(19)
test@DW> desc t2
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
X NUMBER
Y VARCHAR2(128)
Z VARCHAR2(19)
SID NUMBER
test@DW> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);
test@DW> select count(1) from t1;
COUNT(1)
----------
200000
--======================================
原始sp如下，一个标准的串行sp
CREATE OR REPLACE PROCEDURE p1 AS
v_x NUMBER;
v_sid NUMBER;
BEGIN
SELECT sid INTO v_sid FROM v$mystat WHERE rownum = 1;
FOR c IN (SELECT x, y, z FROM t1) LOOP
v_x := (c.x + c.x - c.x + c.x - c.x + c.x - c.x + c.x - c.x + c.x - c.x);
INSERT INTO t2 (x, y, z, sid) VALUES (v_x, c.y, c.z, v_sid);
END LOOP;
COMMIT;
END;
test@DW> truncate table t2;
Table truncated.
test@DW> exec p1;
PL/SQL procedure successfully completed.
Elapsed: 00:00:19.45
test@DW> select sid,count(1) from t2 group by sid;
SID COUNT(1)
---------- ----------
145 200000
--========================================
建立一个type
test@DW> CREATE OR REPLACE TYPE t2_type AS OBJECT
2 (
3 x NUMBER,
4 y VARCHAR2(120),
5 z VARCHAR2(19),
6 sid NUMBER
7 )
8 /
Type created.
test@DW> create or replace type t2_tab as table of t2_type
2 /
Type created.
--建立pipeline的函数
CREATE OR REPLACE FUNCTION f2(l_cur IN SYS_REFCURSOR) RETURN t2_tab
PIPELINED
PARALLEL_ENABLE(PARTITION l_cur BY ANY) IS
v_x NUMBER;
v_sid NUMBER;
v_rec t1%ROWTYPE;
BEGIN
SELECT sid INTO v_sid FROM v$mystat WHERE rownum = 1;
LOOP
FETCH l_cur
INTO v_rec;
EXIT WHEN l_cur%NOTFOUND;
v_x := (v_rec.x + v_rec.x - v_rec.x + v_rec.x - v_rec.x + v_rec.x -
v_rec.x + v_rec.x - v_rec.x + v_rec.x - v_rec.x);
PIPE ROW(t2_type(v_x, v_rec.y, v_rec.z, v_sid));
END LOOP;
CLOSE l_cur;
RETURN;
END;
--直接使用
--从测试结果来看，即使不使用并行调用，pipeline函数在执行时间上也比原来的方法快100%（9s vs 19s）
test@DW> truncate table t2;
Table truncated.
test@DW> insert into t2(x,y,z,sid) select * from table(f2(cursor(select * from t1)));
200000 rows created.
Elapsed: 00:00:08.45
test@DW> select sid,count(1) from t2 group by sid;
SID COUNT(1)
---------- ----------
145 200000
--并行使用
--使用并行后（从结果的sid可以看出degree是2），时间有一点减少(7s vs 9s)
test@DW> truncate table t2;
Table truncated.
test@DW> insert into t2(x,y,z,sid) select * from table(f2(cursor(select /*+parallel(t1)*/ * from t1)));
200000 rows created.
Elapsed: 00:00:06.92
test@DW> select sid,count(1) from t2 group by sid;
SID COUNT(1)
---------- ----------
143 101947
137 98053
------------------------------------------------------------------------------------------
今天看到一个使用pipelined的例子，纪录下
create or replace type str2tblType as table of varchar2(30);
/
create or replace
function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' )
return str2tblType
PIPELINED
as
l_str long default p_str || p_delim;
l_n number;
begin
loop
l_n := instr( l_str, p_delim );
exit when (nvl(l_n,0) = 0);
pipe row ( ltrim(rtrim(substr(l_str,1,l_n-1))) );
l_str := substr( l_str, l_n+1 );
end loop;
return;
end;
/