SQL> /* DOC> Use pipelined table function and object type to handle hierachical queries in a DOC> data warehouse environment. DOC> DOC> The end result is that you get all the children of a particular node. this can be easily DOC> done with ORACLE hiearachical query, CONNECT BY - if you are familiar with it. DOC> Most OLAP tools do not support it - they will not submit this SQL to ORACLE. Obviously DOC> it is even more difficult for non-technical front end users to try o formulate this SQL DOC> using a GUI tool. So, we make it easy for the users by letting the users work with 1 table DOC> (the view) only. DOC> DOC> You can use procedure or function to return a refcursor that gives you the linked nodes, DOC> but you can not present it to the end user in the form of a table, this is where the DOC> view comes in. Of course performance will be slower, since you have to populate the DOC> nested table on the fly (compared to a refcursor). But you make it easy for the front end DOC> users, this is is the key issue in a DW environment. DOC> DOC> You can create a nested table column for the table to store the linked nodes. Query DOC> performance will be good. But maintaining it may be difficult. Removing, adding or DOC> update a node may not be too difficult if you only have parent key and child key in DOC> the table, it will not be so easy if you also have their children or parent nodes DOC> sotored. How much time will it table to recalculate the linked nodes for the entire table? DOC> How often does the hierachy change? If the hierachy changes very frequently, it is better DOC> to use the virtual nested table approach. It is fairly static, then one can consider DOC> storing linked nodes as a nested table column. DOC>*/ SQL> SQL> select * from hq_table; CHILD_ID PARENT_ID FACT1 FACT2 ---------- ---------- ---------- ---------- 15 100 100 16 15 100 100 18 16 200 200 19 16 250 350 20 16 400 350 23 18 110 700 26 23 460 100 29 26 500 900 24 19 230 210 27 24 600 110 30 27 100 160 CHILD_ID PARENT_ID FACT1 FACT2 ---------- ---------- ---------- ---------- 25 19 130 800 28 25 120 170 31 100 170 17 15 120 190 17 31 120 190 32 31 10 70 28 32 120 170 18 rows selected. SQL> SQL> create or replace type num_tab_type as 2 table of number; 3 / Type created. SQL> SQL> create or replace type link_node_type as object (link_node number, lvl number); 2 / Type created. SQL> create or replace type node_array as table of link_node_type; 2 / Type created. SQL> create or replace type v_num_tab_type as table of num_tab_type; 2 / Type created. SQL> SQL> create or replace function ParentsList(child_id in number) 2 return node_array pipelined as 3 cursor c1(cid number) is 4 select parent_id, level from hq_table 5 where parent_id is not null 6 start with child_id=cid 7 connect by prior parent_id=child_id; 8 begin 9 for c1rec in c1(child_id) loop 10 pipe row(link_node_type(c1rec.parent_id, c1rec.level)); 11 end loop; 12 return; 13 end; 14 / Function created. SQL> SQL> select * from table(cast(ParentsList(29) as node_array)); LINK_NODE LVL ---------- ---------- 26 1 23 2 18 3 16 4 15 5 5 rows selected. SQL> SQL> create or replace function ChildList(child_id in number) 2 return node_array pipelined as 3 cursor c1(cid number) is 4 select child_id, level from hq_table 5 where child_id !=cid 6 start with child_id=cid 7 connect by prior child_id=parent_id; 8 begin 9 for c1rec in c1(child_id) loop 10 pipe row(link_node_type(c1rec.child_id, c1rec.level)); 11 end loop; 12 return; 13 end; 14 / Function created. SQL> SQL> select * from table(cast(ChildList(19) as node_array)); LINK_NODE LVL ---------- ---------- 24 2 27 3 30 4 25 2 28 3 5 rows selected. SQL> SQL> create or replace view child_vw 2 (p_id, child_id, h_level) as 3 select a.child_id, p.* 4 from hq_table a, 5 TABLE(CAST(ChildList(a.child_id) 6 as node_array)) p; View created. SQL> SQL> -- To get all children SQL> select p_id, child_id, h_level 2 from child_vw 3 where p_id = 19; P_ID CHILD_ID H_LEVEL ---------- ---------- ---------- 19 24 2 19 27 3 19 30 4 19 25 2 19 28 3 5 rows selected. SQL> SQL> -- To get immediate subsidiaries SQL> select p_id, child_id, h_level 2 from child_vw 3 where p_id = 19 and h_level=2; P_ID CHILD_ID H_LEVEL ---------- ---------- ---------- 19 24 2 19 25 2 2 rows selected. SQL> SQL> spool off