oracle的並行管道函式

並行管道函式
這個例子中要使用兩個表：T1和T2。T1是先讀的表，T2表用來插入這個資訊。我們要用的兩個表如下：

sys@JINGYONG> create table t1
  2  as
  3  select object_id id,object_name text
  4  from all_objects;

表已建立。

sys@JINGYONG> begin
  2  dbms_stats.set_table_stats
  3  (user,'T1',numrows=>100000000,numblks=>100000);
  4  end;
  5  /

PL/SQL 過程已成功完成。

sys@JINGYONG> create table t2
  2  as
  3  select t1.*,0 session_id
  4  from t1
  5  where 1=0;

表已建立。

這裡使用DBMS_STATS來騙過最佳化器，讓它以為輸入表中有10,000,000行，而且佔用了100,000個資料庫塊。在此模擬 一個大表。第二個表T2是第一個表的一個副本，只是在結構中增加了一個SESSION_ID列。可以透過它具體看到是否發生了並行化。接下來，需要建立管道函式返回的物件型別。在這個例子中，物件型別類似於T2：

sys@JINGYONG> create or replace type t2_type
  2  as object
  3  (
  4  id number,
  5  text varchar2(30),
  6  session_id number
  7  );
  8  /

型別已建立。

sys@JINGYONG> create or replace type t2_tab_type as table of t2_type;
  2  /

型別已建立。

現在這個過程是一個生成行的函式。它接收資料作為輸入，並在一個引用遊標（ref cursor）中處理。這個函式返回一個 T2_TAB_TYPE，這就是我們剛才建立的物件型別。這是一個PARALLEL_ENABLED（啟用子並行）的管道函式。在此使用了分割槽 （partition）子句，這就告訴Oracle：以任何最合適的方式劃分或分解資料。我們不需要對資料的順序做任何假設。

在此，我們只想劃分資料。資料如何劃分對於我們的處理並不重要，所以定義如下：

sys@JINGYONG> create or replace function parallel_pipelined(l_cursor in sys_refcursor)
  2  return t2_tab_type
  3  pipelined
  4  parallel_enable(partition l_cursor by any)
  5  is
  6   l_session_id number;
  7   TYPE type_t1_data IS TABLE OF t1%ROWTYPE INDEX BY PLS_INTEGER;
  8   l_t1  type_t1_data;
  9
 10  begin
 11  select sid into l_session_id
 12  from v$mystat
 13  where rownum=1;
 14  loop
 15    fetch l_cursor bulk collect into l_t1;--用bulk collect來一次性獲取資料
 16    exit when l_t1.count=0;
 17    for i in 1 .. l_t1.count loop
 18          pipe row(t2_type(l_t1(i).id,l_t1(i).text,l_session_id));
 19    end loop;
 20    null;
 21  end loop;
 22  close l_cursor;
 23  return;
 24  end;
 25  /

Function created

或者用下面的過程來一行一行來獲取

create or replace
function parallel_pipelined( l_cursor in sys_refcursor )
return t2_tab_type
pipelined
parallel_enable ( partition l_cursor by any )
is
 l_session_id number;
 l_rec t1%rowtype;
begin
 select sid into l_session_id
 from v$mystat
 where rownum =1;
 loop
 fetch l_cursor into l_rec;
 exit when l_cursor%notfound;
 pipe row(t2_type(l_rec.id,l_rec.text,l_session_id));
 end loop;
 close l_cursor;
 return;
end;

這樣就建立了函式。我們準備並行地處理資料，讓Oracle根據可用的資源來確定最合適的並行度：

SQL> insert /*+ append */
  2  into t2(id,text,session_id)
  3   select *
  4   from table(parallel_pipelined
  5   (CURSOR(select /*+ parallel(t1) */ *
  6   from t1 )
  7  ))
  8  ;

50333 rows inserted

SQL> commit;

Commit complete

為了檢視這裡發生了什麼，可以查詢新插入的資料，並按SESSION_ID分組，先來看使用了多少個並行執行伺服器，再看每個並行 執行伺服器處理了多少行：

SQL> select session_id,count(*) from t2 group by session_id;

SESSION_ID   COUNT(*)
---------- ----------
       136      31006
       145      19327

顯然，對於這個並行操作的SELECT部分，我們使用了2個並行執行伺服器,可以看到，Oracle對我們的過程進行了並行化