Parallel Execution - Expert Oracle Database Architecture - page 682

EODA@ORA12CR1> create or replace type t2_tab_type
2 as table of t2_type
3 /
Type created.
And now for the pipelined function, which is simply the original PROCESS_DATA procedure rewritten. The
procedure is now a function that produces rows. It accepts as an input the data to process in a ref cursor. The function
returns a T2_TAB_TYPE , the type we just created. It is a pipelined function that is PARALLEL_ENABLED . The partition
clause we are using says to Oracle, “Partition, or slice up, the data by any means that works best. We don't need to
make any assumptions about the order of the data.”
You may also use hash or range partitioning on a specific column in the ref cursor. This would involve using a
strongly typed ref cursor, so the compiler knows what columns are available. Hash partitioning would just send equal
rows to each parallel execution server to process based on a hash of the column supplied. Range partitioning would
send nonoverlapping ranges of data to each parallel execution server, based on the partitioning key. For example, if
you range partitioned on ID , each parallel execution server might get ranges 1...1000, 1001...20000, 20001...30000, and
so on ( ID values in that range).
Here, we just want the data split up. How the data is split up is not relevant to our processing, so our definition
looks like this:
EODA@ORA12CR1> create or replace
2 function parallel_pipelined( l_cursor in sys_refcursor )
3 return t2_tab_type
4 pipelined
5 parallel_enable ( partition l_cursor by any )
We'd like to be able to see what rows were processed by which parallel execution servers, so we'll declare a local
variable L_SESSION_ID and initialize it from V$MYSTAT :
6
7 is
8 l_session_id number;
9 l_rec t1%rowtype;
10 begin
11 select sid into l_session_id
12 from v$mystat
13 where rownum =1;
Now we are ready to process the data. We simply fetch out a row (or rows, as we could certainly use BULK COLLECT
here to array process the ref cursor), perform our complex process on it, and pipe it out. When the ref cursor is
exhausted of data, we close the cursor and return:
14 loop
15 fetch l_cursor into l_rec;
16 exit when l_cursor%notfound;
17 -- complex process here
18 pipe row(t2_type(l_rec.id,l_rec.text,l_session_id));
19 end loop;
20 close l_cursor;
21 return;
22 end;
23 /
Function created.