Solution: Navigate many shallow hierachies in parallel

Lets create the indexes we need. There might be better options, but these will do:


create index i1 on tree_emp( empno);
create index i2 on tree_emp( mgr);

Now we define the pipe table package. Of course it is possible to have a more efficient solution, but I want to show the principle.


create or replace package parallel_access
as

TYPE R_REC IS RECORD (empno tree_emp.empno%TYPE,
sum_salaries Number); — result record, change defintion according to your needs
TYPE refcur_t IS REF CURSOR RETURN R_REC;

TYPE result_Tab is TABLE OF R_REC;

FUNCTION passData (p_ref refcur_t) RETURN result_Tab
PIPELINED
PARALLEL_ENABLE(PARTITION p_ref BY ANY); — function will inherit parallelism from ref cursor

END parallel_access;
/

create or replace package body parallel_access
as
FUNCTION passData (p_ref refcur_t) RETURN result_Tab
PIPELINED PARALLEL_ENABLE(PARTITION p_ref BY ANY)
IS
out_rec r_rec;
BEGIN
execute immediate ‘alter session set “_old_connect_by_enabled”=true’;
LOOP — for each parallel process
FETCH p_ref INTO out_rec;
EXIT WHEN p_ref%NOTFOUND;
SELECT sum(sal)
INTO out_rec.sum_salaries
FROM tree_emp
CONNECT BY PRIOR EMPNO = MGR
START WITH mgr = out_rec.empno;

PIPE ROW(out_rec);
END LOOP;
execute immediate ‘alter session set “_old_connect_by_enabled”=false’;
RETURN;
END passData;

END parallel_access;
/

We can use the package as follows. Parallel 4 is just an example, one can choose the parallelism as needed.

SELECT b.* FROM
TABLE(parallel_access.passdata (CURSOR( select /*+ parallel (d 4) */ empno , null from tree_emp where mgr is null))) b;

I tested the example with parallel 16 on my laptop (4 core CPU). The piped table was about twice as fast as the standard plan.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s