Oracle hierarchical query in a view, bind «start with» arguments to query

user_13822092 0
Viewed 387

I have a table with the columns

parent_key1, parent_key2, child_key1, child_key2

definig trees through the connection of two pairs of parameters.

The table is quite large, and it contains thousands of root objects, that is parents that do not occur as a child; so to say the table does not contain a tree but rather a forest. This is why the queries here do not work.

I want to get the tree members starting with top_ancestor_key1 and top_ancestor_key2.

For a procedure, I can define the two parameters :top_ancestor_key1 and :top_ancestor_key2, the code

SELECT parent_key1, parent_key2, child_key1, child_key2, level
FROM genealogy
START WITH parent_key1 = :top_ancestor_key1, parent_key2 = :top_ancestor_key2, 
CONNECT BY parent_key1 = PRIOR child_key1 AND parent_key2 = PRIOR child_key2

works well.

Now I would like to create a view «ancestors_resolved» with the columns

top_ancestor_key1, top_ancestor_key2, parent_key1, parent_key2, child_key1, child_key2 [, level]

that I could use the result for joins on top_ancestor_key1 and top_ancestor_key2

I have tried

--CREATE View ancestors_resolved AS
SELECT connect_by_root parent_key1 as top_ancestor_key1, connect_by_root parent_key2 as top_ancestor_key2, parent_key1, parent_key2, child_key1, child_key2, level
FROM genealogy
CONNECT BY parent_key1 = PRIOR child_key1 AND parent_key2 = PRIOR child_key2

however, the enclosing query

SELECT * FROM 
(
SELECT connect_by_root parent_key1 as top_ancestor_key1, connect_by_root parent_key2 as top_ancestor_key2, parent_key1, parent_key2, child_key1, child_key2, level
FROM genealogy
CONNECT BY parent_key1 = PRIOR child_key1 AND parent_key2 = PRIOR child_key2
)
WHERE top_ancestor_key1='grandpa' AND top_ancestor_key2 = 5

runs into timeout; it seems like oracle trying to build all trees before evaluating the Parameters.

I also tried

WITH tmptbl (parent_key1, parent_key2, child_key1, child_key2) as (
SELECT parent_key1, parent_key2, child_key1, child_key2
FROM genealogy
  UNION ALL
  SELECT tmptbl.parent_key1, tmptbl.parent_key2, tmptbl.child_key1, tmptbl.child_key2
  FROM tmptbl
  INNER JOIN genealogy x on x.child_key1 = tmptbl.parent_key1 and x.child_key2 = tmptbl.parent_key2 and x.child_key1 != x.parent_key1 and x.child_key2 != x.parent_key2
)
SELECT *
FROM tmptbl

but it did not work either.

How do I link the parameters top_ancestor_key1, top_ancestor_key2 I use for START WITH clause to a view?

sql-view hierarchical-data oracle
1 Answers

If you are on 19.6 onwards, you can use a SQL Table Macro https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-language-elements.html#GUID-292C3A17-2A4B-4EFB-AD38-68DF6380E5F7 to create an parameterized view. Using your initial query with bind variables (fixing for typos)

create or replace function tm_genealogy (nParentKey1 number, nParentKey2 number)
return varchar2 sql_macro
is
begin
  return 'SELECT parent_key1, parent_key2, child_key1, child_key2, level
FROM genealogy
START WITH parent_key1 = nParentKey1 and parent_key2 = nParentKey2
CONNECT BY parent_key1 = PRIOR child_key1 AND parent_key2 = PRIOR child_key2';
end tm_genealogy;
/
select *
from   tm_genealogy (1,2);

If you haven't patched/upgraded that far then you could DIY it with a pipelined table function, this is a bit more effort:

create or replace package genealogy_pkg 
is
  type udt is record 
  (parent_key1 number
  ,parent_key2 number
  ,child_key1  number
  ,child_key2  number
  ,lvl         number
  );
  type udt_t is table of udt;
  
  function connect_by(nParentKey1 number, nParentKey2 number) return udt_t PIPELINED;
end genealogy_pkg;
/
show err
create or replace package body genealogy_pkg 
is
  function connect_by(nParentKey1 number, nParentKey2 number) return udt_t PIPELINED
  is
    cursor connect_by_cursor (nParentKey1 number, nParentKey2 number) 
    is SELECT parent_key1, parent_key2, child_key1, child_key2, level lvl
       FROM genealogy
       START WITH parent_key1 = nParentKey1 and parent_key2 = nParentKey2
       CONNECT BY parent_key1 = PRIOR child_key1 AND parent_key2 = PRIOR child_key2;
    temp_results   udt;
  begin 
    open connect_by_cursor (nParentKey1 , nParentKey2 ) ;
    loop
      fetch connect_by_cursor
      into  temp_results;
      exit when connect_by_cursor%notfound;
          
      pipe row (temp_results);
    end loop;
    return;
       
  end connect_by;
end genealogy_pkg;
/
show err
select * from table(genealogy_pkg.connect_by(1,2));

You can have a read about pipelined functions here https://oracle-base.com/articles/misc/pipelined-table-functions#:~:text=Pipelined%20Table%20Functions%201%20Table%20Functions.%20Table%20functions,Pipelined%20Table%20Functions.%20...%208%20Transformation%20Pipelines.%20 , essentially they just allow you to pipe out rows from a PL/SQL piece of code. The way I've written it, it's going to fetching from the parameterized cursor one row at a time, you can put in some further effort and use a looped bulk collect with a sensible limit.

This doesn't give you quite the same effect as the SQL Table Macro. The macro can be merged in with the rest of your query and optimized, the pipelined function only exists as the non-merged function.

user_98561760
Related