Find hierarchical or parent-child column values from a single table oracle

Question

I have a table name tree and it have two columns one is p and second one is ch.

p       ch 
-------------
1       2                   
1       3 
1       4 
2       5 
2       6 
7       8 
9       10 
11      12 
6       13 
13      14 
14      15 
14      16

Output I want is all the linked child to a parent. If I give "1" as input for which I have to find all parent and child elements for example , in this case 1 is parent for 2,3,4 and 2 is parent for 5 and so on...in this case I need every linked child element and parent itself as mentioned below:

ParentChilds
------------
1
2
3
4
5
6
13
14
15
16

Below is the query I wrote and I want to confirm it is the best possible solution or we can do it better way, because i have large data in my table :

with LinkedAccounts (p, ch, orig_recur, dest_recur, lvl) as (
            select n.p
    , n.ch
    , 1 orig_recur
     , case n.p
           when 1 then ch
                   else p
        end dest_recur
     , 1 lvl
   from tree n
 where n.p = 1
   or n.ch = 1 union all
select n.p
     , n.ch
    , LinkedAccounts.dest_recur orig_recur
     , case n.p
         when LinkedAccounts.dest_recur then n.ch
                           else n.p
       end dest_recur
     , LinkedAccounts.lvl + 1 lvl
  from LinkedAccounts
  join tree n
        on (n.p = LinkedAccounts.dest_recur and n.ch != LinkedAccounts.orig_recur)
        or (n.ch = LinkedAccounts.dest_recur and n.p != LinkedAccounts.orig_recur)
)
 search breadth first by orig_recur, dest_recur set ordering
cycle ch,
 p set is_cycle to '1' default '0'   select distinct p    from LinkedAccounts    union     Select Distinct ch    from LinkedAccounts;

Answer 1

Use a hierarchical query:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TREE ( p, ch ) AS
          SELECT  1,  2 FROM DUAL
UNION ALL SELECT  1,  3 FROM DUAL
UNION ALL SELECT  1,  4 FROM DUAL
UNION ALL SELECT  2,  5 FROM DUAL
UNION ALL SELECT  2,  6 FROM DUAL
UNION ALL SELECT  7,  8 FROM DUAL
UNION ALL SELECT  9, 10 FROM DUAL
UNION ALL SELECT 11, 12 FROM DUAL
UNION ALL SELECT  6, 13 FROM DUAL
UNION ALL SELECT 13, 14 FROM DUAL
UNION ALL SELECT 14, 15 FROM DUAL
UNION ALL SELECT 14, 16 FROM DUAL
UNION ALL SELECT  2,  1 FROM DUAL;

Query 1:

SELECT     p AS ParentChilds
FROM       TREE
START WITH p = 1
CONNECT BY NOCYCLE PRIOR ch = p
UNION
SELECT     ch
FROM       TREE
START WITH p = 1
CONNECT BY NOCYCLE PRIOR ch = p
UNION
SELECT     p
FROM       TREE
START WITH p = 1
CONNECT BY NOCYCLE ch = PRIOR p

Results:

| PARENTCHILDS |
|--------------|
|            1 |
|            2 |
|            3 |
|            4 |
|            5 |
|            6 |
|           13 |
|           14 |
|           15 |
|           16 |

Query 2:

SELECT     p AS ParentChilds
FROM       TREE
START WITH p = 6
CONNECT BY NOCYCLE PRIOR ch = p
UNION
SELECT     ch
FROM       TREE
START WITH p = 6
CONNECT BY NOCYCLE PRIOR ch = p
UNION
SELECT     p
FROM       TREE
START WITH p = 6
CONNECT BY NOCYCLE ch = PRIOR p

Results:

| PARENTCHILDS |
|--------------|
|            1 |
|            2 |
|            6 |
|           13 |
|           14 |
|           15 |
|           16 |

Answer 2

This approach may be slightly easier to read as it uses a more traditional recursive cte approach, by starting at the root and joining each parent to its children. Root nodes are identified as those nodes which themselves have no parents (where not exists).

with LinkedAccounts(topRoot, parent, child, lvl)
as 
    (
        select r.p as topRoot, r.p as parent, r.ch as child, 1 as lvl
            from tree r
            where not exists
            (
                select 1 from tree t where r.p = t.ch
            )

        union all

        select la.topRoot
            , ch.p
            , ch.ch
            , la.lvl + 1
        from LinkedAccounts la
            inner join tree ch
            on ch.p = la.child
    ),
    ParentAndChild as
    (
        select topRoot, parent as node, lvl from LinkedAccounts

        union all

        select topRoot, child as node, lvl from LinkedAccounts
    )
    select distinct node
        from ParentAndChild
        where topRoot = 1
        order by node ASC;

SqlFiddle here

As an aside, the naming convention in your hierarchy is unusual - ch would typically be nodeid (but parent would be parent / parentid) - this would then model a node allowing for additional columns on the node. At first glance it appears that you are modelling the connection relationship itself, not a node?