A Template Script for JDBC Calling of Oracle Procedures with Object Array Parameters

Some time ago I wrote an Oracle database package for a web service. The Java developer for the service told me that it was throwing an error when called from Java, although I had unit tested it from PL/SQL. He gave me a small Java driver script to demonstrate the issue, and this allowed the issue to be quickly identified: As both Java and PL/SQL have boolean data types I had considered that a boolean parameter would make sense to pass a boolean value. However, it turns out that this does not work in JDBC, and so I replaced it with an integer parameter.

It occurred to me then that it would be nice if the database developer was able in general to test JDBC compatibility of his or her procedure as a final step after unit testing. To this end I created a more generic example script based on a simple procedure that I wrote against Oracle’s HR demo schema, the same procedure that I used as an example of a unit testing design pattern Design Patterns for Database Unit Testing 2: Web Service Saving – Code

Update, 4 November 2017: I have made a self-contained project on GitHub with both Java and Oracle code, avoiding dependency on my testing project. JDBC Calling of Oracle Procedures with Object Array Parameters on GitHub. I have also added the Oracle code below.

The code below runs against any Oracle instance in which the standard Oracle HR demo schema has been installed. There is a video demonstration of how to install and run it at the end of this article. The procedure has one input and one output object array parameter, and can easily be extended as desired. It requires one jar file in the classpath, ojdbc6.jar, which is available in Oracle client or database installations, and can be run from an IDE such as Eclipse.

Java Code

package jdbcdemo;
/***************************************************************************************************
Name:        Driver.java
Description: This is a Java driver script for Brendan's HR demo web service procedure. It is
             designed to serve as a template for other web service procedures to allow a database
             developer to do a JDBC integration test easily, and can also be used as a starting point
             for Java development.

             The template procedure takes an input array of objects and has an output array of 
             objects. It is easy to update for any named object and array types, procedure and
             Oracle connection. Any other signature types would need additional changes.

	     See 'A Template Script for JDBC Calling of Oracle Procedures with Object Array Parameters'
             
A Template Script for JDBC Calling of Oracle Procedures with Object Array Parameters
Modification History Who When Which What -------------------- ----------- ----- ------------------------------------------------------------- Brendan Furey 14-May-2016 1.0 Created Brendan Furey 04-Nov-2017 1.1 Put into new GitHub project along with Oracle code ***************************************************************************************************/ import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Array; import java.sql.Struct; import oracle.jdbc.OracleTypes; import oracle.jdbc.OracleCallableStatement; import oracle.jdbc.OracleConnection; public class Driver { // Change section 1/2: Replace these constants with your own values private static final String DB_CONNECTION = "jdbc:oracle:thin:hr/hr@localhost:1521/orclpdb"; private static final String TY_IN_OBJ = "TY_EMP_IN_OBJ"; private static final String TY_IN_ARR = "TY_EMP_IN_ARR"; private static final String TY_OUT_ARR = "TY_EMP_OUT_ARR"; private static final String PROC_NAME = "Emp_WS.AIP_Save_Emps"; private static OracleConnection conn; public static void main(String[] argv) { try { getDBConnection (); prOutArray (callProc (inArray ())); } catch (SQLException e) { System.out.println(e.getMessage()); } } private static Array inArray () throws SQLException { // Change section 2/2: Replace [2] with number of test records, and the arrays with their values Struct[] struct = new Struct[2]; struct[0] = conn.createStruct (TY_IN_OBJ, new Object[] {"LN 1", "EM 1", "IT_PROG", 1000}); struct[1] = conn.createStruct (TY_IN_OBJ, new Object[] {"LN 2", "EM 2", "IT_PROG", 2000}); return conn.createARRAY (TY_IN_ARR, struct); } private static Array callProc (Array objArray) throws SQLException { OracleCallableStatement ocs = (OracleCallableStatement) conn.prepareCall ("BEGIN "+PROC_NAME+"(:1, :2); END;"); ocs.setArray (1, objArray); ocs.registerOutParameter (2, OracleTypes.ARRAY, TY_OUT_ARR); ocs.execute (); return ocs.getARRAY (2); } private static void prOutArray (Array arr) throws SQLException { Object[] objArr = (Object[]) arr.getArray(); int j = 0; for (Object rec : objArr) { Object[] objLis = ((Struct)rec).getAttributes (); int i = 0; String recStr = ""; for (Object fld : objLis) { if (i++ > 0) recStr = recStr + '/'; recStr = recStr + fld.toString(); } System.out.println ("Record "+(++j)+": "+recStr); } } private static void getDBConnection () throws SQLException { conn = (OracleConnection) DriverManager.getConnection (DB_CONNECTION); conn.setAutoCommit (false); System.out.println ("Connected..."); } }

Example output

Connected...
Record 1: 239/TWO HUNDRED THIRTY-NINE
Record 2: 240/TWO HUNDRED FORTY

Google Java Style

Oracle Code

/***************************************************************************************************

Author:      Brendan Furey
Description: Script to create objects to demo JDBC procedure calls with object array parameters

         See 'A Template Script for JDBC Calling of Oracle Procedures with Object Array Parameters'
             
A Template Script for JDBC Calling of Oracle Procedures with Object Array Parameters
Modification History Who When Which What -------------------- ----------- ----- ------------------------------------------------------------- Brendan Furey 04-May-2016 1.0 Created Brendan Furey 04-Nov-2017 1.1 Extracted the JDBC demo code from the unit testing project, and put into new GitHub project along with Java code ***************************************************************************************************/ REM Run this script from Oracle's standard HR schema to create objects to demo JDBC procedure calls COLUMN "Database" FORMAT A20 COLUMN "Time" FORMAT A20 COLUMN "Version" FORMAT A30 COLUMN "Session" FORMAT 9999990 COLUMN "OS User" FORMAT A10 COLUMN "Machine" FORMAT A20 SELECT 'Start: ' || dbs.name "Database", To_Char (SYSDATE,'DD-MON-YYYY HH24:MI:SS') "Time", Replace (Substr(ver.banner, 1, Instr(ver.banner, '64')-4), 'Enterprise Edition Release ', '') "Version" FROM v$database dbs, v$version ver WHERE ver.banner LIKE 'Oracle%'; PROMPT Input types creation DROP TYPE ty_emp_in_arr / CREATE OR REPLACE TYPE ty_emp_in_obj AS OBJECT ( last_name VARCHAR2(25), email VARCHAR2(25), job_id VARCHAR2(10), salary NUMBER ) / CREATE TYPE ty_emp_in_arr AS TABLE OF ty_emp_in_obj / PROMPT Output types creation DROP TYPE ty_emp_out_arr / CREATE OR REPLACE TYPE ty_emp_out_obj AS OBJECT ( employee_id NUMBER, description VARCHAR2(500) ) / CREATE TYPE ty_emp_out_arr AS TABLE OF ty_emp_out_obj / CREATE OR REPLACE PACKAGE Emp_WS AS /*************************************************************************************************** Description: HR demo web service code. Procedure saves new employees list and returns primary key plus same in words, or zero plus error message in output list ***************************************************************************************************/ PROCEDURE AIP_Save_Emps (p_ty_emp_in_lis ty_emp_in_arr, x_ty_emp_out_lis OUT ty_emp_out_arr); END Emp_WS; / CREATE OR REPLACE PACKAGE BODY Emp_WS AS PROCEDURE AIP_Save_Emps (p_ty_emp_in_lis ty_emp_in_arr, -- list of employees to insert x_ty_emp_out_lis OUT ty_emp_out_arr) IS -- list of employee results l_ty_emp_out_lis ty_emp_out_arr; bulk_errors EXCEPTION; PRAGMA EXCEPTION_INIT (bulk_errors, -24381); n_err PLS_INTEGER := 0; BEGIN FORALL i IN 1..p_ty_emp_in_lis.COUNT SAVE EXCEPTIONS INSERT INTO employees ( employee_id, last_name, email, hire_date, job_id, salary ) VALUES ( employees_seq.NEXTVAL, p_ty_emp_in_lis(i).last_name, p_ty_emp_in_lis(i).email, SYSDATE, p_ty_emp_in_lis(i).job_id, p_ty_emp_in_lis(i).salary ) RETURNING ty_emp_out_obj (employee_id, To_Char(To_Date(employee_id,'J'),'JSP')) BULK COLLECT INTO x_ty_emp_out_lis; EXCEPTION WHEN bulk_errors THEN l_ty_emp_out_lis := x_ty_emp_out_lis; FOR i IN 1 .. sql%BULK_EXCEPTIONS.COUNT LOOP IF i > x_ty_emp_out_lis.COUNT THEN x_ty_emp_out_lis.Extend; END IF; x_ty_emp_out_lis (SQL%Bulk_Exceptions (i).Error_Index) := ty_emp_out_obj (0, SQLERRM (- (SQL%Bulk_Exceptions (i).Error_Code))); END LOOP; FOR i IN 1..p_ty_emp_in_lis.COUNT LOOP IF i > x_ty_emp_out_lis.COUNT THEN x_ty_emp_out_lis.Extend; END IF; IF x_ty_emp_out_lis(i).employee_id = 0 THEN n_err := n_err + 1; ELSE x_ty_emp_out_lis(i) := l_ty_emp_out_lis(i - n_err); END IF; END LOOP; END AIP_Save_Emps; END Emp_WS; /

Here is a demo of installing and running the code, Oracle and Java:






 

Recursive SQL for Network Analysis, and Duality

In March 2013 I wrote an article on the use of SQL to group network-structured records into their distinct connected subnetworks, SQL for Network Grouping. I looked at two solution approaches commonly put forward on Oracle forums for these types of problem, using Oracle’s Connect By recursion, and the more recent recursive subquery factoring, and also put forward a new solution of my own using the Model clause. I noted however that SQL solutions are generally very inefficent compared with a good PL/SQL solution, such as I posted here, PL/SQL Pipelined Function for Network Analysis. For the first two methods, I noted:

  1. Non-hierarchical networks have no root nodes, so the traversal needs to be repeated from every node in the network set
  2. Hierarchical queries retrieve all possible routes through a network

I also noted that Connect By is more inefficient than recursive subquery factoring, but did not say why, promising a more detailed explanation at a later date. In this article I illustrate the behaviour of both recursive SQL methods through a series of five elementary networks, followed by a simple combination of the five. I then use the foreign key network from Oracle’s HR demo (v12 version, with OE and PM schemas included) as a final example.

In this article I consider traversal of a single connected network from a given root node (or several if each root node is specified).

It is shown that the behaviour of Connect By can be understood best by considering it to traverse all paths through a network that is dual to the original network.

Dual Networks

Dual network definition

The dual network consists of a set of nodes and links (d-nodes and d-links say) defined thus:

  • the d-nodes correspond to each link in the original network that is adjacent (via a node) to at least one other link, including itself if its start and end nodes are the same
  • the d-links correspond to each pair of adjacent links where the ‘from’ link identifier is alphabetically smaller than that of the ‘to’ link, except for the case of links that are adjacent to themselves where a single d-link has the same ‘from’ and ‘to’ link

Dual network SQL

The d-node identifiers are just the link identifiers, while the d-link identifiers use the adjacency-defining node identifiers with a sequential number (partitioned by node) attached.

WITH dist_links AS (
SELECT	DISTINCT CASE WHEN lin_2.node_fr IN (lin_1.node_fr, lin_1.node_to) THEN lin_2.node_fr ELSE lin_2.node_to END link_node,
        lin_1.id node_fr_d,
	lin_2.id node_to_d
  FROM links lin_1
  JOIN links lin_2
    ON lin_2.node_fr IN (lin_1.node_fr, lin_1.node_to)
    OR lin_2.node_to IN (lin_1.node_fr, lin_1.node_to)
 WHERE lin_2.id >= lin_1.id
   AND (lin_2.id != lin_1.id OR lin_2.node_fr = lin_1.node_to)
)
SELECT Substr (link_node, 1, Length (link_node)-1) || Row_Number () OVER (PARTITION BY link_node
                            ORDER BY node_fr_d, node_to_d) || '-' || Substr (link_node, -1),
       node_fr_d,
       node_to_d
  FROM dist_links

Dual network characteristics

Dual networks defined as above are generally larger than the original networks and are usually more heavily looped, which explains the inferior performance of Connect by compared with recursive subquery factor solutions. The PL/SQL solution mentioned above, while traversing the entire network, does not traverse all possible routes through it and its performance is thus not adversely affected by the degree of looping.

SQL Queries

The recursive SQL queries return all routes through the network from the roots supplied. In my attached script I also have versions that filter out repeated links. The pipelined function query returns a single, exhaustive route through the network, distinguishing a set of tree links from loop-closing links; it also returns all subnetworks without requiring input roots.

Pipelined Function Query (PLF)

See PL/SQL Pipelined Function for Network Analysis for the Pl/SQL function.

SELECT root_node_id             "Network",
       Count (DISTINCT link_id) OVER (PARTITION BY root_node_id) - 1 "#Links",
       Count (DISTINCT node_id) OVER (PARTITION BY root_node_id) "#Nodes",
       LPad (dirn || ' ', 2*node_level, ' ') || node_id || loop_flag "Node",
       link_id || CASE WHEN link_id = 'ROOT' THEN '_' || Substr (root_node_id, -1) END "Link",
       node_level               "Lev"
  FROM TABLE (Net_Pipe.All_Nets)
 ORDER BY line_no

Recursive Subquery Factor Query (RSF)

WITH rsf (node_id, prefix, id, lev) AS (
SELECT node_id, '', 'ROOT_' || Substr (node_id, 4, 1), 0
  FROM nodes_v
 WHERE Substr (node_id, 2, 1) = '1'
 UNION ALL
SELECT CASE WHEN l.node_id_to = r.node_id THEN l.node_id_fr ELSE l.node_id_to END,
       CASE WHEN l.node_id_fr = l.node_id_to THEN '= ' WHEN l.node_id_fr = r.node_id THEN '> ' ELSE '< ' END,
       l.link_id id, lev + 1
  FROM rsf r
  JOIN links_v l
    ON (l.node_id_fr = r.node_id OR l.node_id_to = r.node_id)
   AND l.link_id != Nvl (r.id, '0')
) SEARCH DEPTH FIRST BY node_id SET line_no
CYCLE node_id SET is_cycle TO '*' DEFAULT ' '
SELECT LPad (r.prefix || ' ', 2*r.lev) || r.node_id || is_cycle "Node",
        r.id "Link",
        line_no
  FROM rsf r
 ORDER BY line_no

Connect By Query (CBY)

SELECT node_id_fr || ' > ' || node_id_to  "Nodes",
       LPad (' ', 2 * (LEVEL-1)) || link_id || CASE WHEN CONNECT_BY_ISCYCLE = 1 THEN '*' ELSE ' ' END "Link Path"
  FROM links_v
CONNECT BY NOCYCLE ((node_id_fr = PRIOR node_id_to OR node_id_to = PRIOR node_id_fr OR
                     node_id_fr = PRIOR node_id_fr OR node_id_to = PRIOR node_id_to) /*AND link_id != PRIOR link_id*/)
 START WITH Substr (node_id_fr, 2, 1) = '1' AND Substr (node_id_to, 2, 1) = '2'
 ORDER SIBLINGS BY node_id_to

Five Elementary Networks

Oracle’s two forms of SQL recursion treat cycles differently

Connect By Cycles

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0

Connect By queries do not return loop-closing nodes, and the prior node is marked as the cycle node.

Recursive Subquery Factor Cycles

A row is considered to form a cycle if one of its ancestor rows has the same values for the cycle columns.

Recursive Subquery Factor queries do return loop-closing nodes, and these nodes are marked as the cycle nodes.

We will see this differing behaviour clearly in the following examples. We will also see that the Connect By output on the original network has exactly the same structure as recursive subquery factor output on the dual network if the loop-closing rows are disregarded. Cycle nodes on both definitions are marked with a ‘*’ in the outputs below.

Network 1: 3 nodes in line

Dual Network, 1.3 - net-1

Network 2: Simple fork

Dual Network, 1.3 - net-2

Network 3: 2-node loop

Dual Network, 1.3 - net-3

Network 4: 3-node loop

Dual Network, 1.3 - net-4

Network 5: 2 nodes with a self-loop

Dual Network, 1.3 - net-5

Combination of Elementary Networks

Combination Network 6

Dual Network, 1.3 - net-6

This network has 10 links with 3 loops.

Combination Network 6: PLF Output

Node              Link
----------------- ----------
N1-6
> N2-6            L12-6
  = N2-6*         L22-6
  > N3-6          L23-6
    > N4-6        L34-6
      > N6-6      L46-6
        > N4-6*   L64-6
    > N5-6        L35-6
      > N7-6      L57-6
        > N8-6    L78-6
          < N5-6* L58-6

Combination Network 6: RSF Output

Node              Link
----------------- ----------
N1-6
> N2-6            L12-6
 =  N2-6*         L22-6
 >  N3-6          L23-6
   >  N4-6        L34-6
     <  N6-6      L64-6
       <  N4-6*   L46-6
     >  N6-6      L46-6
       >  N4-6*   L64-6
   >  N5-6        L35-6
     >  N7-6      L57-6
       >  N8-6    L78-6
         <  N5-6* L58-6
     >  N8-6      L58-6
       <  N7-6    L78-6
         <  N5-6* L57-6

Combination Network 6: CBY Output

Nodes           Link Path
--------------- --------------------
N1-6 > N2-6     L12-6*
N2-6 > N2-6       L22-6*
N2-6 > N3-6         L23-6*
N3-6 > N4-6           L34-6*
N6-6 > N4-6             L64-6*
N4-6 > N6-6               L46-6*
N3-6 > N5-6             L35-6*
N5-6 > N7-6               L57-6*
N5-6 > N8-6                 L58-6*
N7-6 > N8-6                   L78-6*
N7-6 > N8-6                 L78-6*
N5-6 > N8-6                   L58-6*
N5-6 > N8-6               L58-6*
N5-6 > N7-6                 L57-6*
N7-6 > N8-6                   L78-6*
N7-6 > N8-6                 L78-6*
N5-6 > N7-6                   L57-6*
N4-6 > N6-6             L46-6*
N6-6 > N4-6               L64-6*
N3-6 > N5-6           L35-6*
N3-6 > N4-6             L34-6*
N6-6 > N4-6               L64-6*
N4-6 > N6-6                 L46-6*
N4-6 > N6-6               L46-6*
N6-6 > N4-6                 L64-6*
N5-6 > N7-6             L57-6*
N5-6 > N8-6               L58-6*
N7-6 > N8-6                 L78-6*
N7-6 > N8-6               L78-6*
N5-6 > N8-6                 L58-6*
N5-6 > N8-6             L58-6*
N5-6 > N7-6               L57-6*
N7-6 > N8-6                 L78-6*
N7-6 > N8-6               L78-6*
N5-6 > N7-6                 L57-6*
N2-6 > N3-6       L23-6*
N2-6 > N2-6         L22-6*
N3-6 > N4-6         L34-6*
N6-6 > N4-6           L64-6*
N4-6 > N6-6             L46-6*
N3-6 > N5-6           L35-6*
N5-6 > N7-6             L57-6*
N5-6 > N8-6               L58-6*
N7-6 > N8-6                 L78-6*
N7-6 > N8-6               L78-6*
N5-6 > N8-6                 L58-6*
N5-6 > N8-6             L58-6*
N5-6 > N7-6               L57-6*
N7-6 > N8-6                 L78-6*
N7-6 > N8-6               L78-6*
N5-6 > N7-6                 L57-6*
N4-6 > N6-6           L46-6*
N6-6 > N4-6             L64-6*
N3-6 > N5-6         L35-6*
N3-6 > N4-6           L34-6*
N6-6 > N4-6             L64-6*
N4-6 > N6-6               L46-6*
N4-6 > N6-6             L46-6*
N6-6 > N4-6               L64-6*
N5-6 > N7-6           L57-6*
N5-6 > N8-6             L58-6*
N7-6 > N8-6               L78-6*
N7-6 > N8-6             L78-6*
N5-6 > N8-6               L58-6*
N5-6 > N8-6           L58-6*
N5-6 > N7-6             L57-6*
N7-6 > N8-6               L78-6*
N7-6 > N8-6             L78-6*
N5-6 > N7-6               L57-6*



Dual Combination Network 6

Dual Network, 1.3 - net-6-D

This network has 15 links with 6 loops, whereas the original had 10 links with 3 loops.

Dual Combination Network 6: PLF Output

Node                      Link
------------------------- ------
L12-6
> L22-6                   N2-1-6
  = L22-6*                N2-3-6
  > L23-6                 N2-4-6
    < L12-6*              N2-2-6
    > L34-6               N3-1-6
      > L35-6             N3-3-6
        < L23-6*          N3-2-6
        > L57-6           N5-1-6
          > L58-6         N5-3-6
            < L35-6*      N5-2-6
            > L78-6       N8-1-6
              < L57-6*    N7-1-6
      > L46-6             N4-1-6
        > L64-6           N6-1-6
          < L34-6*        N4-2-6

Dual Combination Network 6: RSF Output

Node                      Link
------------------------- ------
L12-6
> L22-6                   N2-1-6
 =  L22-6*                N2-3-6
 >  L23-6                 N2-4-6
   <  L12-6*              N2-2-6
   >  L34-6               N3-1-6
     >  L35-6             N3-3-6
       <  L23-6*          N3-2-6
       >  L57-6           N5-1-6
         >  L58-6         N5-3-6
           <  L35-6*      N5-2-6
           >  L78-6       N8-1-6
             <  L57-6*    N7-1-6
         >  L78-6         N7-1-6
           <  L58-6       N8-1-6
             <  L35-6*    N5-2-6
             <  L57-6*    N5-3-6
       >  L58-6           N5-2-6
         <  L57-6         N5-3-6
           <  L35-6*      N5-1-6
           >  L78-6       N7-1-6
             <  L58-6*    N8-1-6
         >  L78-6         N8-1-6
           <  L57-6       N7-1-6
             <  L35-6*    N5-1-6
             >  L58-6*    N5-3-6
     >  L46-6             N4-1-6
       >  L64-6           N6-1-6
         <  L34-6*        N4-2-6
     >  L64-6             N4-2-6
       <  L46-6           N6-1-6
         <  L34-6*        N4-1-6
   >  L35-6               N3-2-6
     <  L34-6             N3-3-6
       <  L23-6*          N3-1-6
       >  L46-6           N4-1-6
         >  L64-6         N6-1-6
           <  L34-6*      N4-2-6
       >  L64-6           N4-2-6
         <  L46-6         N6-1-6
           <  L34-6*      N4-1-6
     >  L57-6             N5-1-6
       >  L58-6           N5-3-6
         <  L35-6*        N5-2-6
         >  L78-6         N8-1-6
           <  L57-6*      N7-1-6
       >  L78-6           N7-1-6
         <  L58-6         N8-1-6
           <  L35-6*      N5-2-6
           <  L57-6*      N5-3-6
     >  L58-6             N5-2-6
       <  L57-6           N5-3-6
         <  L35-6*        N5-1-6
         >  L78-6         N7-1-6
           <  L58-6*      N8-1-6
       >  L78-6           N8-1-6
         <  L57-6         N7-1-6
           <  L35-6*      N5-1-6
           >  L58-6*      N5-3-6
> L23-6                   N2-2-6
 <  L22-6                 N2-4-6
   <  L12-6*              N2-1-6
   =  L22-6*              N2-3-6
 >  L34-6                 N3-1-6
   >  L35-6               N3-3-6
     <  L23-6*            N3-2-6
     >  L57-6             N5-1-6
       >  L58-6           N5-3-6
         <  L35-6*        N5-2-6
         >  L78-6         N8-1-6
           <  L57-6*      N7-1-6
       >  L78-6           N7-1-6
         <  L58-6         N8-1-6
           <  L35-6*      N5-2-6
           <  L57-6*      N5-3-6
     >  L58-6             N5-2-6
       <  L57-6           N5-3-6
         <  L35-6*        N5-1-6
         >  L78-6         N7-1-6
           <  L58-6*      N8-1-6
       >  L78-6           N8-1-6
         <  L57-6         N7-1-6
           <  L35-6*      N5-1-6
           >  L58-6*      N5-3-6
   >  L46-6               N4-1-6
     >  L64-6             N6-1-6
       <  L34-6*          N4-2-6
   >  L64-6               N4-2-6
     <  L46-6             N6-1-6
       <  L34-6*          N4-1-6
 >  L35-6                 N3-2-6
   <  L34-6               N3-3-6
     <  L23-6*            N3-1-6
     >  L46-6             N4-1-6
       >  L64-6           N6-1-6
         <  L34-6*        N4-2-6
     >  L64-6             N4-2-6
       <  L46-6           N6-1-6
         <  L34-6*        N4-1-6
   >  L57-6               N5-1-6
     >  L58-6             N5-3-6
       <  L35-6*          N5-2-6
       >  L78-6           N8-1-6
         <  L57-6*        N7-1-6
     >  L78-6             N7-1-6
       <  L58-6           N8-1-6
         <  L35-6*        N5-2-6
         <  L57-6*        N5-3-6
   >  L58-6               N5-2-6
     <  L57-6             N5-3-6
       <  L35-6*          N5-1-6
       >  L78-6           N7-1-6
         <  L58-6*        N8-1-6
     >  L78-6             N8-1-6
       <  L57-6           N7-1-6
         <  L35-6*        N5-1-6
         >  L58-6*        N5-3-6


Combination Network 6: CBY Original with RSF Dual Output

In the output below I deleted all the loop rows from the RSF output for the dual network and placed the result beside the output for CBY for the original network, using a column-wise copy and paste. It's easy to see then their equivalent structure. Both have 69 rows.

Network 6: CBY                         Dual Network 6: RSF with loop rows deleted
==============                         ==========================================
Nodes           Link Path              Node                      Link
--------------- --------------------   ------------------------- ------
N1-6 > N2-6     L12-6*                 L12-6
N2-6 > N2-6       L22-6*	       > L22-6                   N2-1-6
N2-6 > N3-6         L23-6*	        >  L23-6                 N2-4-6
N3-6 > N4-6           L34-6*	          >  L34-6               N3-1-6
N6-6 > N4-6             L64-6*	            >  L35-6             N3-3-6
N4-6 > N6-6               L46-6*              >  L57-6           N5-1-6
N3-6 > N5-6             L35-6*	                >  L58-6         N5-3-6
N5-6 > N7-6               L57-6*                  >  L78-6       N8-1-6
N5-6 > N8-6                 L58-6*              >  L78-6         N7-1-6
N7-6 > N8-6                   L78-6*              <  L58-6       N8-1-6
N7-6 > N8-6                 L78-6*            >  L58-6           N5-2-6
N5-6 > N8-6                   L58-6*            <  L57-6         N5-3-6
N5-6 > N8-6               L58-6*                  >  L78-6       N7-1-6
N5-6 > N7-6                 L57-6*              >  L78-6         N8-1-6
N7-6 > N8-6                   L78-6*              <  L57-6       N7-1-6
N7-6 > N8-6                 L78-6*          >  L46-6             N4-1-6
N5-6 > N7-6                   L57-6*          >  L64-6           N6-1-6
N4-6 > N6-6             L46-6*	            >  L64-6             N4-2-6
N6-6 > N4-6               L64-6*              <  L46-6           N6-1-6
N3-6 > N5-6           L35-6*	          >  L35-6               N3-2-6
N3-6 > N4-6             L34-6*	            <  L34-6             N3-3-6
N6-6 > N4-6               L64-6*              >  L46-6           N4-1-6
N4-6 > N6-6                 L46-6*              >  L64-6         N6-1-6
N4-6 > N6-6               L46-6*              >  L64-6           N4-2-6
N6-6 > N4-6                 L64-6*              <  L46-6         N6-1-6
N5-6 > N7-6             L57-6*	            >  L57-6             N5-1-6
N5-6 > N8-6               L58-6*              >  L58-6           N5-3-6
N7-6 > N8-6                 L78-6*              >  L78-6         N8-1-6
N7-6 > N8-6               L78-6*              >  L78-6           N7-1-6
N5-6 > N8-6                 L58-6*              <  L58-6         N8-1-6
N5-6 > N8-6             L58-6*	            >  L58-6             N5-2-6
N5-6 > N7-6               L57-6*              <  L57-6           N5-3-6
N7-6 > N8-6                 L78-6*              >  L78-6         N7-1-6
N7-6 > N8-6               L78-6*              >  L78-6           N8-1-6
N5-6 > N7-6                 L57-6*              <  L57-6         N7-1-6
N2-6 > N3-6       L23-6*	       > L23-6                   N2-2-6
N2-6 > N2-6         L22-6*	        <  L22-6                 N2-4-6
N3-6 > N4-6         L34-6*	        >  L34-6                 N3-1-6
N6-6 > N4-6           L64-6*	          >  L35-6               N3-3-6
N4-6 > N6-6             L46-6*	            >  L57-6             N5-1-6
N3-6 > N5-6           L35-6*	              >  L58-6           N5-3-6
N5-6 > N7-6             L57-6*	                >  L78-6         N8-1-6
N5-6 > N8-6               L58-6*              >  L78-6           N7-1-6
N7-6 > N8-6                 L78-6*              <  L58-6         N8-1-6
N7-6 > N8-6               L78-6*            >  L58-6             N5-2-6
N5-6 > N8-6                 L58-6*            <  L57-6           N5-3-6
N5-6 > N8-6             L58-6*	                >  L78-6         N7-1-6
N5-6 > N7-6               L57-6*              >  L78-6           N8-1-6
N7-6 > N8-6                 L78-6*              <  L57-6         N7-1-6
N7-6 > N8-6               L78-6*          >  L46-6               N4-1-6
N5-6 > N7-6                 L57-6*          >  L64-6             N6-1-6
N4-6 > N6-6           L46-6*	          >  L64-6               N4-2-6
N6-6 > N4-6             L64-6*	            <  L46-6             N6-1-6
N3-6 > N5-6         L35-6*	        >  L35-6                 N3-2-6
N3-6 > N4-6           L34-6*	          <  L34-6               N3-3-6
N6-6 > N4-6             L64-6*	            >  L46-6             N4-1-6
N4-6 > N6-6               L46-6*              >  L64-6           N6-1-6
N4-6 > N6-6             L46-6*	            >  L64-6             N4-2-6
N6-6 > N4-6               L64-6*              <  L46-6           N6-1-6
N5-6 > N7-6           L57-6*	          >  L57-6               N5-1-6
N5-6 > N8-6             L58-6*	            >  L58-6             N5-3-6
N7-6 > N8-6               L78-6*              >  L78-6           N8-1-6
N7-6 > N8-6             L78-6*	            >  L78-6             N7-1-6
N5-6 > N8-6               L58-6*              <  L58-6           N8-1-6
N5-6 > N8-6           L58-6*	          >  L58-6               N5-2-6
N5-6 > N7-6             L57-6*	            <  L57-6             N5-3-6
N7-6 > N8-6               L78-6*              >  L78-6           N7-1-6
N7-6 > N8-6             L78-6*	            >  L78-6             N8-1-6
N5-6 > N7-6               L57-6*              <  L57-6           N7-1-6


Dual Combination Network 6: CBY Output

34547 rows selected.

[See attached file if interested in detail.]

Oracle's HR/OE/PM Demo Network

Original Demo Network

Dual Network, 1.3 - HR

This network has 21 links with 6 loops.

Original Demo Network: PLF Output

Node                                          Link                                 Lev
--------------------------------------------- ----------------------------------- ----
COUNTRIES|HR                                  ROOT                                   0
< LOCATIONS|HR                                loc_c_id_fk|HR                         1
  < DEPARTMENTS|HR                            dept_loc_fk|HR                         2
    > EMPLOYEES|HR                            dept_mgr_fk|HR                         3
      < CUSTOMERS|OE                          customers_account_manager_fk|OE        4
        < ORDERS|OE                           orders_customer_id_fk|OE               5
          > EMPLOYEES|HR*                     orders_sales_rep_fk|OE                 6
          < ORDER_ITEMS|OE                    order_items_order_id_fk|OE             6
            > PRODUCT_INFORMATION|OE          order_items_product_id_fk|OE           7
              < INVENTORIES|OE                inventories_product_id_fk|OE           8
                > WAREHOUSES|OE               inventories_warehouses_fk|OE           9
                  > LOCATIONS|HR*             warehouses_location_fk|OE             10
              < ONLINE_MEDIA|PM               loc_c_id_fk|PM                         8
              < PRINT_MEDIA|PM                printmedia_fk|PM                       8
              < PRODUCT_DESCRIPTIONS|OE       pd_product_id_fk|OE                    8
      > DEPARTMENTS|HR*                       emp_dept_fk|HR                         4
      = EMPLOYEES|HR*                         emp_manager_fk|HR                      4
      > JOBS|HR                               emp_job_fk|HR                          4
        < JOB_HISTORY|HR                      jhist_job_fk|HR                        5
          > DEPARTMENTS|HR*                   jhist_dept_fk|HR                       6
          > EMPLOYEES|HR*                     jhist_emp_fk|HR                        6
> REGIONS|HR                                  countr_reg_fk|HR                       1

22 rows selected.

Elapsed: 00:00:00.15



Original Demo Network: RSF Output

Node                                          Link
--------------------------------------------- -----------------------------------
COUNTRIES|HR
< LOCATIONS|HR                                loc_c_id_fk|HR
  < DEPARTMENTS|HR                            dept_loc_fk|HR
    < EMPLOYEES|HR                            emp_dept_fk|HR
      < CUSTOMERS|OE                          customers_account_manager_fk|OE
        < ORDERS|OE                           orders_customer_id_fk|OE
          > EMPLOYEES|HR*                     orders_sales_rep_fk|OE
          < ORDER_ITEMS|OE                    order_items_order_id_fk|OE
            > PRODUCT_INFORMATION|OE          order_items_product_id_fk|OE
              < INVENTORIES|OE                inventories_product_id_fk|OE
                > WAREHOUSES|OE               inventories_warehouses_fk|OE
                  > LOCATIONS|HR*             warehouses_location_fk|OE
              < ONLINE_MEDIA|PM               loc_c_id_fk|PM
              < PRINT_MEDIA|PM                printmedia_fk|PM
              < PRODUCT_DESCRIPTIONS|OE       pd_product_id_fk|OE
      < DEPARTMENTS|HR*                       dept_mgr_fk|HR
      = EMPLOYEES|HR*                         emp_manager_fk|HR
      > JOBS|HR                               emp_job_fk|HR
        < JOB_HISTORY|HR                      jhist_job_fk|HR
          > DEPARTMENTS|HR*                   jhist_dept_fk|HR
          > EMPLOYEES|HR*                     jhist_emp_fk|HR
      < JOB_HISTORY|HR                        jhist_emp_fk|HR
        > DEPARTMENTS|HR*                     jhist_dept_fk|HR
        > JOBS|HR                             jhist_job_fk|HR
          < EMPLOYEES|HR*                     emp_job_fk|HR
      < ORDERS|OE                             orders_sales_rep_fk|OE
        > CUSTOMERS|OE                        orders_customer_id_fk|OE
          > EMPLOYEES|HR*                     customers_account_manager_fk|OE
        < ORDER_ITEMS|OE                      order_items_order_id_fk|OE
          > PRODUCT_INFORMATION|OE            order_items_product_id_fk|OE
            < INVENTORIES|OE                  inventories_product_id_fk|OE
              > WAREHOUSES|OE                 inventories_warehouses_fk|OE
                > LOCATIONS|HR*               warehouses_location_fk|OE
            < ONLINE_MEDIA|PM                 loc_c_id_fk|PM
            < PRINT_MEDIA|PM                  printmedia_fk|PM
            < PRODUCT_DESCRIPTIONS|OE         pd_product_id_fk|OE
    > EMPLOYEES|HR                            dept_mgr_fk|HR
      < CUSTOMERS|OE                          customers_account_manager_fk|OE
        < ORDERS|OE                           orders_customer_id_fk|OE
          > EMPLOYEES|HR*                     orders_sales_rep_fk|OE
          < ORDER_ITEMS|OE                    order_items_order_id_fk|OE
            > PRODUCT_INFORMATION|OE          order_items_product_id_fk|OE
              < INVENTORIES|OE                inventories_product_id_fk|OE
                > WAREHOUSES|OE               inventories_warehouses_fk|OE
                  > LOCATIONS|HR*             warehouses_location_fk|OE
              < ONLINE_MEDIA|PM               loc_c_id_fk|PM
              < PRINT_MEDIA|PM                printmedia_fk|PM
              < PRODUCT_DESCRIPTIONS|OE       pd_product_id_fk|OE
      > DEPARTMENTS|HR*                       emp_dept_fk|HR
      = EMPLOYEES|HR*                         emp_manager_fk|HR
      > JOBS|HR                               emp_job_fk|HR
        < JOB_HISTORY|HR                      jhist_job_fk|HR
          > DEPARTMENTS|HR*                   jhist_dept_fk|HR
          > EMPLOYEES|HR*                     jhist_emp_fk|HR
      < JOB_HISTORY|HR                        jhist_emp_fk|HR
        > DEPARTMENTS|HR*                     jhist_dept_fk|HR
        > JOBS|HR                             jhist_job_fk|HR
          < EMPLOYEES|HR*                     emp_job_fk|HR
      < ORDERS|OE                             orders_sales_rep_fk|OE
        > CUSTOMERS|OE                        orders_customer_id_fk|OE
          > EMPLOYEES|HR*                     customers_account_manager_fk|OE
        < ORDER_ITEMS|OE                      order_items_order_id_fk|OE
          > PRODUCT_INFORMATION|OE            order_items_product_id_fk|OE
            < INVENTORIES|OE                  inventories_product_id_fk|OE
              > WAREHOUSES|OE                 inventories_warehouses_fk|OE
                > LOCATIONS|HR*               warehouses_location_fk|OE
            < ONLINE_MEDIA|PM                 loc_c_id_fk|PM
            < PRINT_MEDIA|PM                  printmedia_fk|PM
            < PRODUCT_DESCRIPTIONS|OE         pd_product_id_fk|OE
    < JOB_HISTORY|HR                          jhist_dept_fk|HR
      > EMPLOYEES|HR                          jhist_emp_fk|HR
        < CUSTOMERS|OE                        customers_account_manager_fk|OE
          < ORDERS|OE                         orders_customer_id_fk|OE
            > EMPLOYEES|HR*                   orders_sales_rep_fk|OE
            < ORDER_ITEMS|OE                  order_items_order_id_fk|OE
              > PRODUCT_INFORMATION|OE        order_items_product_id_fk|OE
                < INVENTORIES|OE              inventories_product_id_fk|OE
                  > WAREHOUSES|OE             inventories_warehouses_fk|OE
                    > LOCATIONS|HR*           warehouses_location_fk|OE
                < ONLINE_MEDIA|PM             loc_c_id_fk|PM
                < PRINT_MEDIA|PM              printmedia_fk|PM
                < PRODUCT_DESCRIPTIONS|OE     pd_product_id_fk|OE
        < DEPARTMENTS|HR*                     dept_mgr_fk|HR
        > DEPARTMENTS|HR*                     emp_dept_fk|HR
        = EMPLOYEES|HR*                       emp_manager_fk|HR
        > JOBS|HR                             emp_job_fk|HR
          < JOB_HISTORY|HR*                   jhist_job_fk|HR
        < ORDERS|OE                           orders_sales_rep_fk|OE
          > CUSTOMERS|OE                      orders_customer_id_fk|OE
            > EMPLOYEES|HR*                   customers_account_manager_fk|OE
          < ORDER_ITEMS|OE                    order_items_order_id_fk|OE
            > PRODUCT_INFORMATION|OE          order_items_product_id_fk|OE
              < INVENTORIES|OE                inventories_product_id_fk|OE
                > WAREHOUSES|OE               inventories_warehouses_fk|OE
                  > LOCATIONS|HR*             warehouses_location_fk|OE
              < ONLINE_MEDIA|PM               loc_c_id_fk|PM
              < PRINT_MEDIA|PM                printmedia_fk|PM
              < PRODUCT_DESCRIPTIONS|OE       pd_product_id_fk|OE
      > JOBS|HR                               jhist_job_fk|HR
        < EMPLOYEES|HR                        emp_job_fk|HR
          < CUSTOMERS|OE                      customers_account_manager_fk|OE
            < ORDERS|OE                       orders_customer_id_fk|OE
              > EMPLOYEES|HR*                 orders_sales_rep_fk|OE
              < ORDER_ITEMS|OE                order_items_order_id_fk|OE
                > PRODUCT_INFORMATION|OE      order_items_product_id_fk|OE
                  < INVENTORIES|OE            inventories_product_id_fk|OE
                    > WAREHOUSES|OE           inventories_warehouses_fk|OE
                      > LOCATIONS|HR*         warehouses_location_fk|OE
                  < ONLINE_MEDIA|PM           loc_c_id_fk|PM
                  < PRINT_MEDIA|PM            printmedia_fk|PM
                  < PRODUCT_DESCRIPTIONS|OE   pd_product_id_fk|OE
          < DEPARTMENTS|HR*                   dept_mgr_fk|HR
          > DEPARTMENTS|HR*                   emp_dept_fk|HR
          = EMPLOYEES|HR*                     emp_manager_fk|HR
          < JOB_HISTORY|HR*                   jhist_emp_fk|HR
          < ORDERS|OE                         orders_sales_rep_fk|OE
            > CUSTOMERS|OE                    orders_customer_id_fk|OE
              > EMPLOYEES|HR*                 customers_account_manager_fk|OE
            < ORDER_ITEMS|OE                  order_items_order_id_fk|OE
              > PRODUCT_INFORMATION|OE        order_items_product_id_fk|OE
                < INVENTORIES|OE              inventories_product_id_fk|OE
                  > WAREHOUSES|OE             inventories_warehouses_fk|OE
                    > LOCATIONS|HR*           warehouses_location_fk|OE
                < ONLINE_MEDIA|PM             loc_c_id_fk|PM
                < PRINT_MEDIA|PM              printmedia_fk|PM
                < PRODUCT_DESCRIPTIONS|OE     pd_product_id_fk|OE
  < WAREHOUSES|OE                             warehouses_location_fk|OE
    < INVENTORIES|OE                          inventories_warehouses_fk|OE
      > PRODUCT_INFORMATION|OE                inventories_product_id_fk|OE
        < ONLINE_MEDIA|PM                     loc_c_id_fk|PM
        < ORDER_ITEMS|OE                      order_items_product_id_fk|OE
          > ORDERS|OE                         order_items_order_id_fk|OE
            > CUSTOMERS|OE                    orders_customer_id_fk|OE
              > EMPLOYEES|HR                  customers_account_manager_fk|OE
                < DEPARTMENTS|HR              dept_mgr_fk|HR
                  < EMPLOYEES|HR*             emp_dept_fk|HR
                  < JOB_HISTORY|HR            jhist_dept_fk|HR
                    > EMPLOYEES|HR*           jhist_emp_fk|HR
                    > JOBS|HR                 jhist_job_fk|HR
                      < EMPLOYEES|HR*         emp_job_fk|HR
                  > LOCATIONS|HR*             dept_loc_fk|HR
                > DEPARTMENTS|HR              emp_dept_fk|HR
                  > EMPLOYEES|HR*             dept_mgr_fk|HR
                  < JOB_HISTORY|HR            jhist_dept_fk|HR
                    > EMPLOYEES|HR*           jhist_emp_fk|HR
                    > JOBS|HR                 jhist_job_fk|HR
                      < EMPLOYEES|HR*         emp_job_fk|HR
                  > LOCATIONS|HR*             dept_loc_fk|HR
                = EMPLOYEES|HR*               emp_manager_fk|HR
                > JOBS|HR                     emp_job_fk|HR
                  < JOB_HISTORY|HR            jhist_job_fk|HR
                    > DEPARTMENTS|HR          jhist_dept_fk|HR
                      < EMPLOYEES|HR*         emp_dept_fk|HR
                      > EMPLOYEES|HR*         dept_mgr_fk|HR
                      > LOCATIONS|HR*         dept_loc_fk|HR
                    > EMPLOYEES|HR*           jhist_emp_fk|HR
                < JOB_HISTORY|HR              jhist_emp_fk|HR
                  > DEPARTMENTS|HR            jhist_dept_fk|HR
                    < EMPLOYEES|HR*           emp_dept_fk|HR
                    > EMPLOYEES|HR*           dept_mgr_fk|HR
                    > LOCATIONS|HR*           dept_loc_fk|HR
                  > JOBS|HR                   jhist_job_fk|HR
                    < EMPLOYEES|HR*           emp_job_fk|HR
                < ORDERS|OE*                  orders_sales_rep_fk|OE
            > EMPLOYEES|HR                    orders_sales_rep_fk|OE
              < CUSTOMERS|OE                  customers_account_manager_fk|OE
                < ORDERS|OE*                  orders_customer_id_fk|OE
              < DEPARTMENTS|HR                dept_mgr_fk|HR
                < EMPLOYEES|HR*               emp_dept_fk|HR
                < JOB_HISTORY|HR              jhist_dept_fk|HR
                  > EMPLOYEES|HR*             jhist_emp_fk|HR
                  > JOBS|HR                   jhist_job_fk|HR
                    < EMPLOYEES|HR*           emp_job_fk|HR
                > LOCATIONS|HR*               dept_loc_fk|HR
              > DEPARTMENTS|HR                emp_dept_fk|HR
                > EMPLOYEES|HR*               dept_mgr_fk|HR
                < JOB_HISTORY|HR              jhist_dept_fk|HR
                  > EMPLOYEES|HR*             jhist_emp_fk|HR
                  > JOBS|HR                   jhist_job_fk|HR
                    < EMPLOYEES|HR*           emp_job_fk|HR
                > LOCATIONS|HR*               dept_loc_fk|HR
              = EMPLOYEES|HR*                 emp_manager_fk|HR
              > JOBS|HR                       emp_job_fk|HR
                < JOB_HISTORY|HR              jhist_job_fk|HR
                  > DEPARTMENTS|HR            jhist_dept_fk|HR
                    < EMPLOYEES|HR*           emp_dept_fk|HR
                    > EMPLOYEES|HR*           dept_mgr_fk|HR
                    > LOCATIONS|HR*           dept_loc_fk|HR
                  > EMPLOYEES|HR*             jhist_emp_fk|HR
              < JOB_HISTORY|HR                jhist_emp_fk|HR
                > DEPARTMENTS|HR              jhist_dept_fk|HR
                  < EMPLOYEES|HR*             emp_dept_fk|HR
                  > EMPLOYEES|HR*             dept_mgr_fk|HR
                  > LOCATIONS|HR*             dept_loc_fk|HR
                > JOBS|HR                     jhist_job_fk|HR
                  < EMPLOYEES|HR*             emp_job_fk|HR
        < PRINT_MEDIA|PM                      printmedia_fk|PM
        < PRODUCT_DESCRIPTIONS|OE             pd_product_id_fk|OE
> REGIONS|HR                                  countr_reg_fk|HR

199 rows selected.

Elapsed: 00:00:00.30

The output above shows that RSF returned 199 rows unfiltered in 0.3s.

Original Demo Network: CBY Output

One tree by Connect By

Nodes                                              Link Path
-------------------------------------------------- ----------------------------------------------------------------------
COUNTRIES|HR > REGIONS|HR                          countr_reg_fk|HR*
LOCATIONS|HR > COUNTRIES|HR                          loc_c_id_fk|HR*
DEPARTMENTS|HR > LOCATIONS|HR                          dept_loc_fk|HR*
EMPLOYEES|HR > DEPARTMENTS|HR                            emp_dept_fk|HR*
JOB_HISTORY|HR > DEPARTMENTS|HR                            jhist_dept_fk|HR*
DEPARTMENTS|HR > EMPLOYEES|HR                                dept_mgr_fk|HR*
EMPLOYEES|HR > EMPLOYEES|HR                                    emp_manager_fk|HR*
CUSTOMERS|OE > EMPLOYEES|HR                                      customers_account_manager_fk|OE*
ORDERS|OE > CUSTOMERS|OE                                           orders_customer_id_fk|OE*
ORDERS|OE > EMPLOYEES|HR                                             orders_sales_rep_fk|OE*
JOB_HISTORY|HR > EMPLOYEES|HR                                          jhist_emp_fk|HR*
EMPLOYEES|HR > JOBS|HR                                                   emp_job_fk|HR*
JOB_HISTORY|HR > JOBS|HR                                                   jhist_job_fk|HR*
JOB_HISTORY|HR > JOBS|HR                                                 jhist_job_fk|HR*
EMPLOYEES|HR > JOBS|HR                                                     emp_job_fk|HR*
EMPLOYEES|HR > JOBS|HR                                                 emp_job_fk|HR*
JOB_HISTORY|HR > EMPLOYEES|HR                                            jhist_emp_fk|HR*
JOB_HISTORY|HR > JOBS|HR                                                   jhist_job_fk|HR*
JOB_HISTORY|HR > JOBS|HR                                                 jhist_job_fk|HR*
JOB_HISTORY|HR > EMPLOYEES|HR                                              jhist_emp_fk|HR*
ORDER_ITEMS|OE > ORDERS|OE                                             order_items_order_id_fk|OE*
ORDER_ITEMS|OE > PRODUCT_INFORMATION|OE                                  order_items_product_id_fk|OE*
INVENTORIES|OE > PRODUCT_INFORMATION|OE                                    inventories_product_id_fk|OE*
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                                      printmedia_fk|PM*
.
.
.
ORDERS|OE > CUSTOMERS|OE                                                           orders_customer_id_fk|OE*
EMPLOYEES|HR > EMPLOYEES|HR                                                        emp_manager_fk|HR*
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                        printmedia_fk|PM*
ONLINE_MEDIA|PM > PRODUCT_INFORMATION|OE                         loc_c_id_fk|PM*
PRODUCT_DESCRIPTIONS|OE > PRODUCT_INFORMATION|OE                   pd_product_id_fk|OE*
PRODUCT_DESCRIPTIONS|OE > PRODUCT_INFORMATION|OE                 pd_product_id_fk|OE*
ONLINE_MEDIA|PM > PRODUCT_INFORMATION|OE                           loc_c_id_fk|PM*
ONLINE_MEDIA|PM > PRODUCT_INFORMATION|OE                       loc_c_id_fk|PM*
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                          printmedia_fk|PM*
PRODUCT_DESCRIPTIONS|OE > PRODUCT_INFORMATION|OE                   pd_product_id_fk|OE*
PRODUCT_DESCRIPTIONS|OE > PRODUCT_INFORMATION|OE                 pd_product_id_fk|OE*
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                            printmedia_fk|PM*
PRODUCT_DESCRIPTIONS|OE > PRODUCT_INFORMATION|OE               pd_product_id_fk|OE*
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                          printmedia_fk|PM*
ONLINE_MEDIA|PM > PRODUCT_INFORMATION|OE                           loc_c_id_fk|PM*
ONLINE_MEDIA|PM > PRODUCT_INFORMATION|OE                         loc_c_id_fk|PM*
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                            printmedia_fk|PM*

4414420 rows selected.

Elapsed: 00:29:33.41

One tree by Connect By filtered

Nodes                                              Link Path                                                              LINK_COUNT
-------------------------------------------------- ---------------------------------------------------------------------- ----------
COUNTRIES|HR > REGIONS|HR                          countr_reg_fk|HR*                                                               1
LOCATIONS|HR > COUNTRIES|HR                          loc_c_id_fk|HR*                                                               1
DEPARTMENTS|HR > LOCATIONS|HR                          dept_loc_fk|HR*                                                        214178
EMPLOYEES|HR > DEPARTMENTS|HR                            emp_dept_fk|HR*                                                      169932
JOB_HISTORY|HR > DEPARTMENTS|HR                            jhist_dept_fk|HR*                                                  272162
DEPARTMENTS|HR > EMPLOYEES|HR                                dept_mgr_fk|HR*                                                  169932
EMPLOYEES|HR > EMPLOYEES|HR                                    emp_manager_fk|HR*                                             207910
CUSTOMERS|OE > EMPLOYEES|HR                                      customers_account_manager_fk|OE*                             132490
ORDERS|OE > CUSTOMERS|OE                                           orders_customer_id_fk|OE*                                   85298
ORDERS|OE > EMPLOYEES|HR                                             orders_sales_rep_fk|OE*                                   72234
JOB_HISTORY|HR > EMPLOYEES|HR                                          jhist_emp_fk|HR*                                       164660
EMPLOYEES|HR > JOBS|HR                                                   emp_job_fk|HR*                                       182784
JOB_HISTORY|HR > JOBS|HR                                                   jhist_job_fk|HR*                                   333192
ORDER_ITEMS|OE > ORDERS|OE                                             order_items_order_id_fk|OE*                             26804
ORDER_ITEMS|OE > PRODUCT_INFORMATION|OE                                  order_items_product_id_fk|OE*                         26804
INVENTORIES|OE > PRODUCT_INFORMATION|OE                                    inventories_product_id_fk|OE*                      428354
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                                      printmedia_fk|PM*                                428384
ONLINE_MEDIA|PM > PRODUCT_INFORMATION|OE                                       loc_c_id_fk|PM*                                428384
PRODUCT_DESCRIPTIONS|OE > PRODUCT_INFORMATION|OE                                 pd_product_id_fk|OE*                         428384
INVENTORIES|OE > WAREHOUSES|OE                                               inventories_warehouses_fk|OE*                    428354
WAREHOUSES|OE > LOCATIONS|HR                                                   warehouses_location_fk|OE*                     214178

21 rows selected.

Elapsed: 00:03:03.16

The output above shows that CBY returned 4,414,420 rows unfiltered in 29m33s. Adding filtering reduced the time to 3m03s.

Dual Demo Network

Dual Network, 1.3 - HR-D

This network has 52 links with 32 loops, whereas the original had 21 links with 6 loops.

Dual Demo Network: PLF Output

Node                                                           Link
-------------------------------------------------------------- -------------------------
countr_reg_fk|HR                                               ROOT
> loc_c_id_fk|HR                                               COUNTRIES|HR-1
  < dept_loc_fk|HR                                             LOCATIONS|HR-1
    > dept_mgr_fk|HR                                           DEPARTMENTS|HR-1
      < customers_account_manager_fk|OE                        EMPLOYEES|HR-1
        > emp_dept_fk|HR                                       EMPLOYEES|HR-2
          < dept_loc_fk|HR*                                    DEPARTMENTS|HR-2
          < dept_mgr_fk|HR*                                    EMPLOYEES|HR-7
          > emp_job_fk|HR                                      EMPLOYEES|HR-12
            < customers_account_manager_fk|OE*                 EMPLOYEES|HR-3
            < dept_mgr_fk|HR*                                  EMPLOYEES|HR-8
            > emp_manager_fk|HR                                EMPLOYEES|HR-16
              < customers_account_manager_fk|OE*               EMPLOYEES|HR-4
              < dept_mgr_fk|HR*                                EMPLOYEES|HR-9
              < emp_dept_fk|HR*                                EMPLOYEES|HR-13
              = emp_manager_fk|HR*                             EMPLOYEES|HR-19
              > jhist_emp_fk|HR                                EMPLOYEES|HR-20
                < customers_account_manager_fk|OE*             EMPLOYEES|HR-5
                < dept_mgr_fk|HR*                              EMPLOYEES|HR-10
                < emp_dept_fk|HR*                              EMPLOYEES|HR-14
                < emp_job_fk|HR*                               EMPLOYEES|HR-17
                < jhist_dept_fk|HR                             JOB_HISTORY|HR-1
                  < dept_loc_fk|HR*                            DEPARTMENTS|HR-3
                  < dept_mgr_fk|HR*                            DEPARTMENTS|HR-4
                  < emp_dept_fk|HR*                            DEPARTMENTS|HR-5
                  > jhist_job_fk|HR                            JOB_HISTORY|HR-2
                    < emp_job_fk|HR*                           JOBS|HR-1
                    < jhist_emp_fk|HR*                         JOB_HISTORY|HR-3
                > orders_sales_rep_fk|OE                       EMPLOYEES|HR-22
                  < customers_account_manager_fk|OE*           EMPLOYEES|HR-6
                  < dept_mgr_fk|HR*                            EMPLOYEES|HR-11
                  < emp_dept_fk|HR*                            EMPLOYEES|HR-15
                  < emp_job_fk|HR*                             EMPLOYEES|HR-18
                  < emp_manager_fk|HR*                         EMPLOYEES|HR-21
                  < order_items_order_id_fk|OE                 ORDERS|OE-2
                    > order_items_product_id_fk|OE             ORDER_ITEMS|OE-1
                      < inventories_product_id_fk|OE           PRODUCT_INFORMATION|OE-2
                        > inventories_warehouses_fk|OE         INVENTORIES|OE-1
                          > warehouses_location_fk|OE          WAREHOUSES|OE-1
                            < dept_loc_fk|HR*                  LOCATIONS|HR-2
                            < loc_c_id_fk|HR*                  LOCATIONS|HR-3
                        > loc_c_id_fk|PM                       PRODUCT_INFORMATION|OE-1
                          > order_items_product_id_fk|OE*      PRODUCT_INFORMATION|OE-5
                          > pd_product_id_fk|OE                PRODUCT_INFORMATION|OE-6
                            < inventories_product_id_fk|OE*    PRODUCT_INFORMATION|OE-3
                            < order_items_product_id_fk|OE*    PRODUCT_INFORMATION|OE-8
                            > printmedia_fk|PM                 PRODUCT_INFORMATION|OE-10
                              < inventories_product_id_fk|OE*  PRODUCT_INFORMATION|OE-4
                              < loc_c_id_fk|PM*                PRODUCT_INFORMATION|OE-7
                              < order_items_product_id_fk|OE*  PRODUCT_INFORMATION|OE-9
                    > orders_customer_id_fk|OE                 ORDERS|OE-1
                      < customers_account_manager_fk|OE*       CUSTOMERS|OE-1
                      > orders_sales_rep_fk|OE*                ORDERS|OE-3

53 rows selected.

Elapsed: 00:00:00.27



Dual Demo Network: RSF and CBY Results

Neither of the two SQL recursion methods completed within a period of an hour and had to be terminated. The result for CBY on the original network suggests that RSF on the dual network should return somewhere above 4,414,420 rows.

Conclusions

  • We have shown by examples how network traversal by the Connect By (CBY) approach in SQL corresponds to traversal of all routes in a type of dual version of the original network
  • This dual version, which has forks converted to loops, tends to be larger and more heavily looped, resulting in worse performance compared with solution by recursive subquery factors (RSF)
  • The examples illustrate the different treatment of loop-closing links between the two types of SQL recursion
  • The RSF solutions on the dual network in the simpler examples where it completes is seen to be equivalent to the CBY solution on the original network, after allowing for the different treatment of loop-closing links
  • On the foreign key network for Oracle's HR/OE/PM demo, which has 21 links, RSF returns 199 rows while CBY returns 4,414,420 rows
  • On the dual version of the foreign key network for Oracle's HR/OE/PM demo, which has 52 links, RSF and CBY fail to complete in reasonable times
  • The pipelined function method returns the solution on both original and dual in a small fraction of a second

SQL files: SQL for network duality
Output files: Output for network duality

Oracle version used: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production






SQL for Continuum and Contiguity Grouping

A question was asked some time ago on Oracle’s SQL&PL/SQL forum (Solution design and performance – SQL or PL/SQL?), the gist of which the poster specified thus:

I have a set of records which keep track of a status at a given date for a range of points on a given road. Basically, I need the current bits to “shine through”.

The thread, despite the open-minded title, gives a nice illustration of the odd preference that people often have for complicated PL/SQL solutions to problems amenable to simpler SQL. I provided an SQL solution in that thread for this problem, and in this article I have taken a simpler, more general form of the problem defined there, and provide SQL solutions with diagrams illustrating how they work.

The class of problem can be characterised as follows:

  • Records have range fields, and we want to aggregate separately at each point within the 1-dimensional range domains (or ‘continuum’), independently by grouping key
  • Within the groups we want to aggregate only the first or last records, ordering by some non-key fields

The first point above can be seen as equivalent to adding in to the main grouping key an implicit field specifying the domain leg, i.e. the region between the break points defined by the record ranges, and splitting the records by leg. We might term this form of aggregation continuum aggregation, or perhaps vertical aggregation if we view the range as distance, the internal ordering as by time, and visualise it graphically as in my diagrams below. Once this vertical aggregation is established, it is natural to think of adding a second aggregation, that might be termed contiguity aggregation, or with the same visualisation, horizontal aggregation:

  • Contiguous legs having the same values for the aggregated attributes will be grouped together

Here is an extract on grouping from Oracle® Database SQL Language Reference:

Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

The aggregate functions MIN, MAX, SUM, AVG, COUNT, VARIANCE, and STDDEV, when followed by the KEEP keyword, can be used in conjunction with the FIRST or LAST function to operate on a set of values from a set of rows that rank as the FIRST or LAST with respect to a given sorting specification. Refer to FIRST for more information.

A simple use-case for the KEEP form of grouping is to list contacts with their most recent telephone number when multiple numbers are stored in a separate table. A few years ago, I realised that in most examples that I see developers write their own code instead of using the built-in constructs, and I wrote an article comparing the performance and complexity of the various alternatives for this requirement (as well as alternatives for SQL pivoting), SQL Pivot and Prune Queries – Keeping an Eye on Performance.

Update, 20 November 2013: Added a Postgres version of the SQL solution.

Test Problem

Data Model

We take for our example problem a very simple model consisting of roads and road events where the events occur over a stretch of road at a given time, and event type forms the horizontal grouping attribute.

Road - ERD2

Test Data

Input data - Roads

ROAD_ID ROAD_DESC       START_POINT  END_POINT
------- --------------- ----------- ----------
      1 The Strand                0        200
      2 Piccadilly                0        100

Input data - road_events

ROAD_ID     REV_ID E_TYPE START_POINT  END_POINT E_DATE
------- ---------- ------ ----------- ---------- ---------
      1          1 OPEN             0         10 01-JAN-07
                 2 OPEN            20         50 01-JAN-08
                 3 CLOSED         130        160 01-FEB-08
                 4 CLOSED          55         85 05-JUN-08
                 5 OTHER           45        115 01-JAN-09
                 6 OPEN            60        100 12-FEB-11
                 7 CLOSED         115        145 12-FEB-12
      2          8 CLOSED          10         30 01-JAN-10
                 9 OPEN            40         50 01-JAN-11
                10 OPEN            50         70 01-JAN-12

10 rows selected.

The following diagrams display the data and solutions for our test problem.

Road - Road 1

Road - Road 2

SQL Solution for ‘Point’ Problem

To start with, here is a standard SQL solution for the ‘zero-dimensional’ problem, where the most recent record is required for each road:

Zero-Dimensional Solution

ROAD_DESC          R_START      R_END    E_START      E_END E_DATE    E_TYPE
--------------- ---------- ---------- ---------- ---------- --------- ------
Piccadilly               0        100         50         70 01-JAN-12 OPEN
The Strand               0        200        115        145 12-FEB-12 CLOSED

  1  SELECT r.road_desc, r.start_point r_start, r.end_point r_end,
  2         Max (e.start_point) KEEP (DENSE_RANK LAST ORDER BY e.event_date) e_start,
  3         Max (e.end_point) KEEP (DENSE_RANK LAST ORDER BY e.event_date) e_end,
  4         Max (e.event_date) KEEP (DENSE_RANK LAST ORDER BY e.event_date) e_date,
  5         Max (e.event_type) KEEP (DENSE_RANK LAST ORDER BY e.event_date) e_type
  6    FROM road_events e
  7    JOIN roads r
  8      ON r.id = e.road_id
  9   GROUP BY r.road_desc, r.start_point, r.end_point
 10*  ORDER BY 1, 2, 3

The SQL is more complicated for the continuum problem, and we provide two versions, that differ in the final stage, of horizontal grouping by contiguous event type. The first uses a differencing method popular in Oracle 11g and earlier versions for this common type of grouping problem; the second uses a new feature from Oracle 12c, row pattern matching. [I have also used another technique for this type of grouping, in an article on contiguity and other range-based grouping problems in June 2011, Forming Range-Based Break Groups With Advanced SQL.]

SQL Solution for ‘Continuum’ Problem, Contiguity Grouping by Differences

SQL (Contiguity by Differences)

WITH breaks AS  (
        SELECT road_id, start_point bp FROM road_events
         UNION
        SELECT road_id, end_point FROM road_events
         UNION
        SELECT id, start_point FROM roads
         UNION
        SELECT id, end_point FROM roads
), legs AS (
        SELECT road_id, bp leg_start, Lead (bp) OVER (PARTITION BY road_id ORDER BY bp) leg_end
          FROM breaks
), latest_events AS ( 
        SELECT l.road_id, l.leg_start, l.leg_end,
               Max (e.id) KEEP (DENSE_RANK LAST ORDER BY e.event_date) event_id,
               Nvl (Max (e.event_type) KEEP (DENSE_RANK LAST ORDER BY e.event_date), '(none)') event_type
          FROM legs l
          LEFT JOIN road_events e
            ON e.road_id = l.road_id
           AND e.start_point <= l.leg_start
	   AND e.end_point >= l.leg_end
         WHERE l.leg_end IS NOT NULL
         GROUP BY l.road_id, l.leg_start, l.leg_end
), latest_events_group AS ( 
        SELECT road_id,
               leg_start,
               leg_end,
               event_id,
               event_type,
               Dense_Rank () OVER (PARTITION BY road_id ORDER BY leg_start, leg_end) -
               Dense_Rank () OVER (PARTITION BY road_id, event_type ORDER BY leg_start, leg_end) group_no
          FROM latest_events
)
SELECT l.road_id, r.road_desc,
       Min (l.leg_start)        sec_start,
       Max (l.leg_end)          sec_end,
       l.event_type             e_type,
       l.group_no
  FROM latest_events_group l
  JOIN roads r
    ON r.id = l.road_id
 GROUP BY l.road_id,
        r.road_desc, 
        l.event_type,
        l.group_no
ORDER BY 1, 2, 3
/

ROAD_ID ROAD_DESC        SEC_START    SEC_END E_TYPE   GROUP_NO
------- --------------- ---------- ---------- ------ ----------
      1 The Strand               0         10 OPEN            0
                                10         20 (none)          1
                                20         45 OPEN            1
                                45         60 OTHER           3
                                60        100 OPEN            4
                               100        115 OTHER           5
                               115        160 CLOSED          9
                               160        200 (none)         11
      2 Piccadilly               0         10 (none)          0
                                10         30 CLOSED          1
                                30         40 (none)          1
                                40         70 OPEN            3
                                70        100 (none)          3

13 rows selected.

Query Structure Diagram (Contiguity by Differences)Road, V1.5 - QSD-DiffExecution Plan (Contiguity by Differences)

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |      1 |        |     13 |00:00:00.01 |      25 |       |       |          |
|   1 |  SORT ORDER BY                  |               |      1 |      2 |     13 |00:00:00.01 |      25 |  2048 |  2048 | 2048  (0)|
|   2 |   HASH GROUP BY                 |               |      1 |      2 |     13 |00:00:00.01 |      25 |   900K|   900K| 1343K (0)|
|   3 |    MERGE JOIN                   |               |      1 |     24 |     19 |00:00:00.01 |      25 |       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID | ROADS         |      1 |      2 |      2 |00:00:00.01 |       2 |       |       |          |
|   5 |      INDEX FULL SCAN            | ROAD_PK       |      1 |      2 |      2 |00:00:00.01 |       1 |       |       |          |
|*  6 |     SORT JOIN                   |               |      2 |     24 |     19 |00:00:00.01 |      23 |  2048 |  2048 | 2048  (0)|
|   7 |      VIEW                       |               |      1 |     24 |     19 |00:00:00.01 |      23 |       |       |          |
|   8 |       WINDOW SORT               |               |      1 |     24 |     19 |00:00:00.01 |      23 |  2048 |  2048 | 2048  (0)|
|   9 |        WINDOW NOSORT            |               |      1 |     24 |     19 |00:00:00.01 |      23 | 73728 | 73728 |          |
|  10 |         SORT GROUP BY           |               |      1 |     24 |     19 |00:00:00.01 |      23 |  4096 |  4096 | 4096  (0)|
|* 11 |          HASH JOIN OUTER        |               |      1 |     24 |     25 |00:00:00.01 |      23 |  1696K|  1696K|  540K (0)|
|* 12 |           VIEW                  |               |      1 |     24 |     19 |00:00:00.01 |      16 |       |       |          |
|  13 |            WINDOW SORT          |               |      1 |     24 |     21 |00:00:00.01 |      16 |  2048 |  2048 | 2048  (0)|
|  14 |             VIEW                |               |      1 |     24 |     21 |00:00:00.01 |      16 |       |       |          |
|  15 |              SORT UNIQUE        |               |      1 |     24 |     21 |00:00:00.01 |      16 |  2048 |  2048 | 2048  (0)|
|  16 |               UNION-ALL         |               |      1 |        |     24 |00:00:00.01 |      16 |       |       |          |
|  17 |                INDEX FULL SCAN  | ROAD_EVENT_N1 |      1 |     10 |     10 |00:00:00.01 |       1 |       |       |          |
|  18 |                INDEX FULL SCAN  | ROAD_EVENT_N3 |      1 |     10 |     10 |00:00:00.01 |       1 |       |       |          |
|  19 |                TABLE ACCESS FULL| ROADS         |      1 |      2 |      2 |00:00:00.01 |       7 |       |       |          |
|  20 |                TABLE ACCESS FULL| ROADS         |      1 |      2 |      2 |00:00:00.01 |       7 |       |       |          |
|  21 |           TABLE ACCESS FULL     | ROAD_EVENTS   |      1 |     10 |     10 |00:00:00.01 |       7 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("R"."ID"="L"."ROAD_ID")
       filter("R"."ID"="L"."ROAD_ID")
  11 - access("E"."ROAD_ID"="L"."ROAD_ID")
       filter(("E"."START_POINT"<="L"."LEG_START" AND "E"."END_POINT">="L"."LEG_END"))
  12 - filter("L"."LEG_END" IS NOT NULL)

SQL Solution for ‘Continuum’ Problem, Contiguity Grouping by 12c Row Pattern Matching

SQL (Contiguity by Pattern Matching)

WITH breaks AS  (
        SELECT road_id, start_point bp FROM road_events
         UNION
        SELECT road_id, end_point FROM road_events
         UNION
        SELECT id, start_point FROM roads
         UNION
        SELECT id, end_point FROM roads
), legs AS (
        SELECT road_id, bp leg_start, Lead (bp) OVER (PARTITION BY road_id ORDER BY bp) leg_end
          FROM breaks
), latest_events AS ( 
        SELECT l.road_id, r.road_desc, l.leg_start, l.leg_end,
               Max (e.id) KEEP (DENSE_RANK LAST ORDER BY e.event_date) event_id,
               Nvl (Max (e.event_type) KEEP (DENSE_RANK LAST ORDER BY e.event_date), '(none)') event_type
          FROM legs l
          JOIN roads r
            ON r.id = l.road_id
          LEFT JOIN road_events e
            ON e.road_id = l.road_id
           AND e.start_point <= l.leg_start
	   AND e.end_point >= l.leg_end
         WHERE l.leg_end IS NOT NULL
         GROUP BY l.road_id, r.road_desc, l.leg_start, l.leg_end
)
SELECT m.road_id, m.road_desc, m.sec_start, m.sec_end, m.event_type e_type
  FROM latest_events
 MATCH_RECOGNIZE (
   PARTITION BY road_id, road_desc
   ORDER BY leg_start, leg_end
   MEASURES FIRST (leg_start) sec_start,
            LAST (leg_end) sec_end,
            LAST (event_type) event_type
   PATTERN (strt sm*)
   DEFINE sm AS PREV(sm.event_type) = sm.event_type
 ) m
ORDER BY 1, 2, 3
/

ROAD_ID ROAD_DESC        SEC_START    SEC_END E_TYPE
------- --------------- ---------- ---------- ------
      1 The Strand               0         10 OPEN
                                10         20 (none)
                                20         45 OPEN
                                45         60 OTHER
                                60        100 OPEN
                               100        115 OTHER
                               115        160 CLOSED
                               160        200 (none)
      2 Piccadilly               0         10 (none)
                                10         30 CLOSED
                                30         40 (none)
                                40         70 OPEN
                                70        100 (none)

13 rows selected.

Query Structure Diagram (Contiguity by Pattern Matching)Road, V1.5 - QSD-MRExecution Plan (Contiguity by Pattern Matching)

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |               |      1 |        |     13 |00:00:00.01 |      25 |       |       |          |
|   1 |  SORT ORDER BY                                   |               |      1 |      2 |     13 |00:00:00.01 |      25 |  2048 |  2048 | 2048  (0)|
|   2 |   VIEW                                           |               |      1 |      2 |     13 |00:00:00.01 |      25 |       |       |          |
|   3 |    MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTO|               |      1 |      2 |     13 |00:00:00.01 |      25 |  2048 |  2048 | 2048  (0)|
|   4 |     VIEW                                         |               |      1 |      2 |     19 |00:00:00.01 |      25 |       |       |          |
|   5 |      SORT GROUP BY                               |               |      1 |      2 |     19 |00:00:00.01 |      25 |  4096 |  4096 | 4096  (0)|
|*  6 |       HASH JOIN OUTER                            |               |      1 |     24 |     25 |00:00:00.01 |      25 |   987K|   987K|  525K (0)|
|   7 |        MERGE JOIN                                |               |      1 |     24 |     19 |00:00:00.01 |      18 |       |       |          |
|   8 |         TABLE ACCESS BY INDEX ROWID              | ROADS         |      1 |      2 |      2 |00:00:00.01 |       2 |       |       |          |
|   9 |          INDEX FULL SCAN                         | ROAD_PK       |      1 |      2 |      2 |00:00:00.01 |       1 |       |       |          |
|* 10 |         SORT JOIN                                |               |      2 |     24 |     19 |00:00:00.01 |      16 |  2048 |  2048 | 2048  (0)|
|* 11 |          VIEW                                    |               |      1 |     24 |     19 |00:00:00.01 |      16 |       |       |          |
|  12 |           WINDOW SORT                            |               |      1 |     24 |     21 |00:00:00.01 |      16 |  2048 |  2048 | 2048  (0)|
|  13 |            VIEW                                  |               |      1 |     24 |     21 |00:00:00.01 |      16 |       |       |          |
|  14 |             SORT UNIQUE                          |               |      1 |     24 |     21 |00:00:00.01 |      16 |  2048 |  2048 | 2048  (0)|
|  15 |              UNION-ALL                           |               |      1 |        |     24 |00:00:00.01 |      16 |       |       |          |
|  16 |               INDEX FULL SCAN                    | ROAD_EVENT_N1 |      1 |     10 |     10 |00:00:00.01 |       1 |       |       |          |
|  17 |               INDEX FULL SCAN                    | ROAD_EVENT_N3 |      1 |     10 |     10 |00:00:00.01 |       1 |       |       |          |
|  18 |               TABLE ACCESS FULL                  | ROADS         |      1 |      2 |      2 |00:00:00.01 |       7 |       |       |          |
|  19 |               TABLE ACCESS FULL                  | ROADS         |      1 |      2 |      2 |00:00:00.01 |       7 |       |       |          |
|  20 |        TABLE ACCESS FULL                         | ROAD_EVENTS   |      1 |     10 |     10 |00:00:00.01 |       7 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("E"."ROAD_ID"="L"."ROAD_ID")
       filter(("E"."START_POINT"<="L"."LEG_START" AND "E"."END_POINT">="L"."LEG_END"))
  10 - access("R"."ID"="L"."ROAD_ID")
       filter("R"."ID"="L"."ROAD_ID")
  11 - filter("L"."LEG_END" IS NOT NULL)

Note
-----
   - this is an adaptive plan

SQL Solution for ‘Continuum’ Problem, Contiguity Grouping – Postgres

The Oracle v11 (and earlier versions) solution, with contiguity by differences, can be converted to work in Postgres, as shown below.

SQL (Continuum by Row_Number, Contiguity by Differences – Postgres)

WITH breaks AS  (
        SELECT road_id, start_point bp FROM road_events
         UNION
        SELECT road_id, end_point FROM road_events
         UNION
        SELECT id, start_point FROM roads
         UNION
        SELECT id, end_point FROM roads
), legs AS (
        SELECT road_id, bp leg_start, Lead (bp) OVER (PARTITION BY road_id ORDER BY bp) leg_end
          FROM breaks
), ranked_events AS ( 
        SELECT l.road_id, l.leg_start, l.leg_end,
               e.id event_id, Coalesce (e.event_type, '(none)') event_type,
               Row_Number() OVER (PARTITION BY l.road_id, l.leg_start ORDER BY e.event_date DESC) rnk
          FROM legs l
          LEFT JOIN road_events e
            ON e.road_id = l.road_id
           AND e.start_point <= l.leg_start            AND e.end_point >= l.leg_end
         WHERE l.leg_end IS NOT NULL
), latest_events_group AS ( 
        SELECT road_id,
               leg_start,
               leg_end,
               event_id,
               event_type,
               Dense_Rank () OVER (PARTITION BY road_id ORDER BY leg_start, leg_end) -
               Dense_Rank () OVER (PARTITION BY road_id, event_type ORDER BY leg_start, leg_end) group_no
          FROM ranked_events
         WHERE rnk = 1
)
SELECT l.road_id, r.road_desc,
       Min (l.leg_start)        sec_start,
       Max (l.leg_end)          sec_end,
       l.event_type             e_type,
       l.group_no
  FROM latest_events_group l
  JOIN roads r
    ON r.id = l.road_id
 GROUP BY l.road_id,
        r.road_desc, 
        l.event_type,
        l.group_no
ORDER BY 1, 2, 3;

Continuum/contiguity Solution with Row_Number...
 road_id | road_desc  | sec_start | sec_end | e_type | group_no 
---------+------------+-----------+---------+--------+----------
       1 | The Strand |         0 |      10 | OPEN   |        0
       1 | The Strand |        10 |      20 | (none) |        1
       1 | The Strand |        20 |      45 | OPEN   |        1
       1 | The Strand |        45 |      60 | OTHER  |        3
       1 | The Strand |        60 |     100 | OPEN   |        4
       1 | The Strand |       100 |     115 | OTHER  |        5
       1 | The Strand |       115 |     160 | CLOSED |        9
       1 | The Strand |       160 |     200 | (none) |       11
       2 | Piccadilly |         0 |      10 | (none) |        0
       2 | Piccadilly |        10 |      30 | CLOSED |        1
       2 | Piccadilly |        30 |      40 | (none) |        1
       2 | Piccadilly |        40 |      70 | OPEN   |        3
       2 | Piccadilly |        70 |     100 | (none) |        3
(13 rows)

SELECT Version();

Postgres version...
                           version                           
-------------------------------------------------------------
 PostgreSQL 9.3.1, compiled by Visual C++ build 1600, 64-bit
(1 row)

Notes on Conversion of SQL to Postgres

  • Postgres does not have an exact equivalent of Oracle’s KEEP/FIRST grouping functionality, but it can be emulated via the analytic function Row_Number within a subquery
  • Coalesce, which is also available in Oracle, replaces Nvl, which does not exist in Postgres
  • Oracle’s execution plans were obtained using DBMS_XPlan after running the queries, while the Postgres version was obtained by running the query prefaced by ‘EXPLAIN ANALYZE ‘
  • There is no Postgres equivalent of Oracle v12’s row pattern matching

Query Structure Diagram (Continuum by Row_Number, Contiguity by Differences – Postgres) Road, V1.5 - QSD-PGExecution Plan (Continuum by Row_Number, Contiguity by Differences – Postgres)

Prefacing the query with ‘EXPLAIN ANALYZE ‘ gives:

Explaining Continuum/contiguity Solution with Row_Number...
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=619.45..619.47 rows=8 width=270) (actual time=0.235..0.235 rows=13 loops=1)
   Sort Key: l.road_id, r.road_desc, (min(l.leg_start))
   Sort Method: quicksort  Memory: 26kB
   CTE breaks
     ->  HashAggregate  (cost=79.50..95.30 rows=1580 width=8) (actual time=0.013..0.020 rows=21 loops=1)
           ->  Append  (cost=0.00..71.60 rows=1580 width=8) (actual time=0.002..0.006 rows=24 loops=1)
                 ->  Seq Scan on road_events  (cost=0.00..14.80 rows=480 width=8) (actual time=0.001..0.003 rows=10 loops=1)
                 ->  Seq Scan on road_events road_events_1  (cost=0.00..14.80 rows=480 width=8) (actual time=0.000..0.002 rows=10 loops=1)
                 ->  Seq Scan on roads  (cost=0.00..13.10 rows=310 width=8) (actual time=0.000..0.001 rows=2 loops=1)
                 ->  Seq Scan on roads roads_1  (cost=0.00..13.10 rows=310 width=8) (actual time=0.000..0.000 rows=2 loops=1)
   CTE legs
     ->  WindowAgg  (cost=115.54..147.14 rows=1580 width=8) (actual time=0.030..0.041 rows=21 loops=1)
           ->  Sort  (cost=115.54..119.49 rows=1580 width=8) (actual time=0.028..0.029 rows=21 loops=1)
                 Sort Key: breaks.road_id, breaks.bp
                 Sort Method: quicksort  Memory: 25kB
                 ->  CTE Scan on breaks  (cost=0.00..31.60 rows=1580 width=8) (actual time=0.014..0.023 rows=21 loops=1)
   CTE ranked_events
     ->  WindowAgg  (cost=290.71..326.08 rows=1572 width=138) (actual time=0.089..0.104 rows=25 loops=1)
           ->  Sort  (cost=290.71..294.64 rows=1572 width=138) (actual time=0.088..0.089 rows=25 loops=1)
                 Sort Key: l_1.road_id, l_1.leg_start, e.event_date
                 Sort Method: quicksort  Memory: 26kB
                 ->  Hash Left Join  (cost=20.80..207.25 rows=1572 width=138) (actual time=0.044..0.079 rows=25 loops=1)
                       Hash Cond: (l_1.road_id = e.road_id)
                       Join Filter: ((e.start_point <= l_1.leg_start) AND (e.end_point >= l_1.leg_end))
                       Rows Removed by Join Filter: 89
                       ->  CTE Scan on legs l_1  (cost=0.00..31.60 rows=1572 width=12) (actual time=0.031..0.048 rows=19 loops=1)
                             Filter: (leg_end IS NOT NULL)
                             Rows Removed by Filter: 2
                       ->  Hash  (cost=14.80..14.80 rows=480 width=138) (actual time=0.005..0.005 rows=10 loops=1)
                             Buckets: 1024  Batches: 1  Memory Usage: 1kB
                             ->  Seq Scan on road_events e  (cost=0.00..14.80 rows=480 width=138) (actual time=0.001..0.002 rows=10 loops=1)
   CTE latest_events_group
     ->  WindowAgg  (cost=35.79..36.01 rows=8 width=48) (actual time=0.165..0.181 rows=19 loops=1)
           ->  Sort  (cost=35.79..35.81 rows=8 width=48) (actual time=0.164..0.165 rows=19 loops=1)
                 Sort Key: ranked_events.road_id, ranked_events.event_type, ranked_events.leg_start, ranked_events.leg_end
                 Sort Method: quicksort  Memory: 26kB
                 ->  WindowAgg  (cost=35.49..35.67 rows=8 width=48) (actual time=0.124..0.138 rows=19 loops=1)
                       ->  Sort  (cost=35.49..35.51 rows=8 width=48) (actual time=0.123..0.124 rows=19 loops=1)
                             Sort Key: ranked_events.road_id, ranked_events.leg_start, ranked_events.leg_end
                             Sort Method: quicksort  Memory: 26kB
                             ->  CTE Scan on ranked_events  (cost=0.00..35.37 rows=8 width=48) (actual time=0.090..0.117 rows=19 loops=1)
                                   Filter: (rnk = 1)
                                   Rows Removed by Filter: 6
   ->  HashAggregate  (cost=14.72..14.80 rows=8 width=270) (actual time=0.218..0.221 rows=13 loops=1)
         ->  Hash Join  (cost=0.26..14.60 rows=8 width=270) (actual time=0.203..0.207 rows=19 loops=1)
               Hash Cond: (r.id = l.road_id)
               ->  Seq Scan on roads r  (cost=0.00..13.10 rows=310 width=222) (actual time=0.002..0.002 rows=2 loops=1)
               ->  Hash  (cost=0.16..0.16 rows=8 width=52) (actual time=0.192..0.192 rows=19 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 2kB
                     ->  CTE Scan on latest_events_group l  (cost=0.00..0.16 rows=8 width=52) (actual time=0.167..0.190 rows=19 loops=1)
 Total runtime: 0.432 ms
(51 rows)

Code

Here is the code: Road-SQL