Home » SQL & PL/SQL » SQL & PL/SQL » difference between "child rows" and "siblings rows " (11g)
difference between "child rows" and "siblings rows " [message #649852] Sat, 09 April 2016 11:09 Go to next message
sivaparam
Messages: 16
Registered: March 2005
Junior Member
Hi There
Could you please tell me what is the difference between "child rows" and "siblings rows " ? I am confused. Thanks.


http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#i2129904

Report message to a moderator

Re: difference between "child rows" and "siblings rows " [message #649853 is a reply to message #649852] Sat, 09 April 2016 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

child: a son or a daughter
sibling: a brother or a sister

But before a new question it would be great you feedback and thank people who spent time and helped you in your previous topics.

[Updated on: Sat, 09 April 2016 14:21]

Report message to a moderator

Re: difference between "child rows" and "siblings rows " [message #649857 is a reply to message #649853] Sat, 09 April 2016 14:58 Go to previous message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
For Oracle's purposes, think of siblings as the same level in a hierarchy and children as the levels below, where the levels in the hierarchies could be compromised of generations in a family or managerial levels in a business or parts and sub-parts in an inventory or various other things.

If you compare Oracle's terminology to families, then siblings include not just brothers and sisters, but also cousins of the same generation and children includes any individual's descendants, such as children, grandchildren, and great grandchildren, and so on.

Please see the example below. Given the original poster's prior post regarding recursive subquery factoring and the documentation section from the link that the original poster provided, I have used recursive subquery factoring and search breadth first to order the rows by siblings and search depth first to order the rows by children.


Suppose you have a table and data like below.
SCOTT@orcl> SELECT * FROM family ORDER BY id
  2  /

        ID NAME             PARENT_ID
---------- --------------- ----------
         1 John
         2 Herman                   1
         3 Anne                     1
         4 Louise                   1
         5 Noel                     2
         6 Karen                    3
         7 Kent                     4
         8 Lorraine                 4
         9 Barbara                  5
        10 Lisa                     5
        11 Christie                 6
        12 Alicia                   6
        13 Sean                    10
        14 Reid                    10

14 rows selected.


You can use recursive subquery factoring to derive the generations (levels in the family hierarchy) as below.
SCOTT@orcl> WITH t (gen, id, name, parent_id) AS
  2    (SELECT 1 gen, id, name, parent_id
  3  	FROM   family
  4  	WHERE  parent_id IS NULL
  5  	UNION ALL
  6  	SELECT t.gen + 1 gen, f.id, f.name, f.parent_id
  7  	FROM   t, family f
  8  	WHERE  t.id = f.parent_id)
  9  SELECT gen generation, id, name, parent_id
 10  FROM   t
 11  /

GENERATION         ID NAME             PARENT_ID
---------- ---------- --------------- ----------
         1          1 John
         2          2 Herman                   1
         2          3 Anne                     1
         2          4 Louise                   1
         3          5 Noel                     2
         3          6 Karen                    3
         3          7 Kent                     4
         3          8 Lorraine                 4
         4          9 Barbara                  5
         4         10 Lisa                     5
         4         11 Christie                 6
         4         12 Alicia                   6
         5         13 Sean                    10
         5         14 Reid                    10

14 rows selected.


If you use breadth first, then you get the sibling rows first, as shown below, with some indentation to make it easier to read.
It displays all of one generation (level of hierarchy), before displaying the next.
 
SCOTT@orcl> COLUMN siblings_first FORMAT A30
SCOTT@orcl> WITH t (gen, id, name, parent_id) AS
  2    (SELECT 1 gen, id, name, parent_id
  3  	FROM   family
  4  	WHERE  parent_id IS NULL
  5  	UNION ALL
  6  	SELECT t.gen + 1 gen, f.id, f.name, f.parent_id
  7  	FROM   t, family f
  8  	WHERE  t.id = f.parent_id)
  9    SEARCH BREADTH FIRST BY id SET siblings
 10  SELECT LPAD(' ',(gen-1)*2) || 'Gen ' || gen || ' ' || id || ' ' || name AS siblings_first,
 11  	    parent_id
 12  FROM   t
 13  ORDER  BY siblings
 14  /

SIBLINGS_FIRST                  PARENT_ID
------------------------------ ----------
Gen 1 1 John
  Gen 2 2 Herman                        1
  Gen 2 3 Anne                          1
  Gen 2 4 Louise                        1
    Gen 3 5 Noel                        2
    Gen 3 6 Karen                       3
    Gen 3 7 Kent                        4
    Gen 3 8 Lorraine                    4
      Gen 4 9 Barbara                   5
      Gen 4 10 Lisa                     5
      Gen 4 11 Christie                 6
      Gen 4 12 Alicia                   6
        Gen 5 13 Sean                  10
        Gen 5 14 Reid                  10

14 rows selected.


If you use depth first, then you get child rows first, as shown below, with some indentation to make it easier to read.
It displays all of each person's descendants before displaying their siblings and their siblings' descendants.
SCOTT@orcl> COLUMN children_first FORMAT A30
SCOTT@orcl> WITH t (gen, id, name, parent_id) AS
  2    (SELECT 1 gen, id, name, parent_id
  3  	FROM   family
  4  	WHERE  parent_id IS NULL
  5  	UNION ALL
  6  	SELECT t.gen + 1 gen, f.id, f.name, f.parent_id
  7  	FROM   t, family f
  8  	WHERE  t.id = f.parent_id)
  9    SEARCH DEPTH FIRST BY id SET children
 10  SELECT LPAD(' ',(gen-1)*2) || 'Gen ' || gen || ' ' || id || ' ' || name AS children_first,
 11  	    parent_id
 12  FROM   t
 13  ORDER  BY children
 14  /

CHILDREN_FIRST                  PARENT_ID
------------------------------ ----------
Gen 1 1 John
  Gen 2 2 Herman                        1
    Gen 3 5 Noel                        2
      Gen 4 9 Barbara                   5
      Gen 4 10 Lisa                     5
        Gen 5 13 Sean                  10
        Gen 5 14 Reid                  10
  Gen 2 3 Anne                          1
    Gen 3 6 Karen                       3
      Gen 4 11 Christie                 6
      Gen 4 12 Alicia                   6
  Gen 2 4 Louise                        1
    Gen 3 7 Kent                        4
    Gen 3 8 Lorraine                    4

14 rows selected.


[Updated on: Sat, 09 April 2016 15:27]

Report message to a moderator

Previous Topic: XMLAGG issue
Next Topic: How to create Dependency in MV
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 18:50:29 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.