Everyone knows Tom Kyte’s mantra:

You should do it in a single SQL statement if at all possible.

But we all know that “Every rule has an exception”

There are many different cases when pl/sql with sql can be more efficient than only sql, and i dont want to catalog them. I just want to show a couple examples of such exceptions:

### 1. Running totals by several dimensions

Simple example from forum:

select dt, dim1, dim2, val, sum(val) over(partition by dim1 order by dt) dim1_cumulative_sum, sum(val) over(partition by dim2 order by dt) dim2_cumulative_sum, sum(val) over(partition by dim1, dim2 order by dt) dim1_dim2_cumulative_sum from mg_t order by dt;

This query will be very hard for big data sets, so we can do it efficiently with pl/sql:

create or replace function xf_to_drop return xt2_to_drop pipelined is type tt is table of number index by pls_integer; type tt2 is table of tt index by pls_integer; dim1_c tt; dim2_c tt; dim12_c tt2; begin for r in ( select dt, dim1, dim2, val from mg_t order by dt ) loop dim1_c(r.dim1):=case when dim1_c.exists(r.dim1) then dim1_c(r.dim1) else 0 end + r.val; dim2_c(r.dim1):=case when dim2_c.exists(r.dim1) then dim2_c(r.dim1) else 0 end + r.val; dim12_c(r.dim1)(r.dim2):=case when dim12_c.exists(r.dim1) and dim12_c(r.dim1).exists(r.dim2) then dim12_c(r.dim1)(r.dim2) else 0 end + r.val; pipe row (xt1_to_drop( r.dt ,r.dim1 ,r.dim2 ,r.val ,dim1_c(r.dim1) ,dim1_c(r.dim1) ,dim12_c(r.dim1)(r.dim2) )); end loop; end; /

### 2. Finding connected components

Assume that we have big table with many-to-many relationship:

create table test (clientid NUMBER(10), accountid NUMBER(10));

How we can find all connected groups?

This example also taken from our russian forum and there was very good and simple sql-only solution, but it’s not efficient on big data sets:

select min(group_member_id) as group_max_id, accountid, clientid from (select clientid as group_member_id , connect_by_root accountid as accountid , connect_by_root clientid as clientid from test connect by nocycle decode(accountid, prior accountid, 1, 0) + decode(clientid, prior clientid, 1, 0) = 1 ) a group by accountid, clientid order by group_max_id, accountid /

We can try to remember algorithms courses and adopt one of the several algorithms for connected components:

We can also try even more interesting special algorithms for parallel processing: CONNECTED COMPONENTS ALGORITHMS

FOR MESH-CONNECTED PARALLEL COMPUTERS