Query a table with association to itself

Question

General question here. If a table e.g. Person has a foreign key to itself to model "parent" association and you had to do a query get anyone who is named "Peter" or their parent is named "Peter" you could so something like this:

SELECT * FROM PERSON WHERE NAME = 'PETER'    
UNION
    (SELECT CHILD.* FROM PERSON CHILD, PERSON PARENT 
         WHERE 
         CHILD.PARENT_KEY = PARENT.PK AND PARENT.NAME = 'PETER')

is that the most optimum way?

and roughly if the requirement was just, get all person's from the table with the name peter e.g.

SELECT * FROM PERSON WHERE NAME = 'PETER'    

Can we assume that this is twice as fast because you don't have to iterate twice?

Assume that the colums name, and parent_key are all indexed (b-tree)

Answer 1

I think the fastest query would be this one:

SELECT DISTINCT * 
FROM PERSON 
START WITH NAME = 'PETER'
CONNECT BY PRIOR PK = PARENT_KEY;

----------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |        |     2 |    42 |     3  (34)| 00:00:01 |
|   1 |  HASH UNIQUE                              |        |     2 |    42 |     3  (34)| 00:00:01 |
|*  2 |   CONNECT BY NO FILTERING WITH SW (UNIQUE)|        |       |       |            |          |
|   3 |    TABLE ACCESS STORAGE FULL              | PERSON |     7 |    70 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
 

With this the table is read only once. With a self-join the table must be read two times which should take longer, even with caching.

SELECT child.*
FROM PERSON CHILD
    LEFT OUTER JOIN PERSON PARENT ON child.parent_key = parent.pk
WHERE 'PETER' IN (child.name, parent.name);

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     3 |    54 |     4   (0)| 00:00:01 |
|*  1 |  FILTER                     |        |       |       |            |          |
|*  2 |   HASH JOIN OUTER           |        |     3 |    54 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS STORAGE FULL| PERSON |     7 |    70 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS STORAGE FULL| PERSON |     7 |    56 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

However, if your table is big, then your solution could be the best. Scanning three indexes could be faster than a table scan. Note, this can be used only if you have only one level of parent-child objects.

SELECT * 
FROM PERSON 
WHERE NAME = 'PETER'    
UNION
SELECT CHILD.* 
FROM PERSON CHILD
    JOIN PERSON PARENT ON CHILD.PARENT_KEY = PARENT.PK 
WHERE PARENT.NAME = 'PETER'

--------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |        |     3 |    46 |     6  (17)| 00:00:01 |
|   1 |  SORT UNIQUE                            |        |     3 |    46 |     6  (17)| 00:00:01 |
|   2 |   UNION-ALL                             |        |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED  | PERSON |     1 |    10 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                    | NAME   |     1 |       |     1   (0)| 00:00:01 |
|   5 |    NESTED LOOPS                         |        |     2 |    36 |     3   (0)| 00:00:01 |
|   6 |     NESTED LOOPS                        |        |     2 |    36 |     3   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID BATCHED| PERSON |     1 |     8 |     2   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN                  | NAME   |     1 |       |     1   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN                   | FK     |     1 |       |     0   (0)| 00:00:01 |
|  10 |     TABLE ACCESS BY INDEX ROWID         | PERSON |     1 |    10 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 

Answer 2

If you are only going one level, then a self-join is best. If you must go an undefined number of levels (find people with an ancestor of Peter) then you must use CONNECT BY hierarchical, which is adding a bit more work. But with proper indexing even that's not usually that bad.

In your example, there's no need for UNION ALL. A first name of Peter is going to not be very selective (you're going to find a lot of matches), so a full table scan/hash join is best. In which case, avoid two passes:

SELECT child.*
  FROM person child,
       person parent
 WHERE child.parent_key = parent.pk(+)
   AND (child.name = 'PETER' OR parent.name = 'PETER')

If however your predicates are very selective (e.g., query by SSN), then you want the lookups on both child and parent to be indexed, which being in either of two different columns is best done with UNION ALL as you've done it (or Oracle might rewrite an OR condition and perform a UNION ALL (called OR expansion, which can be forced with the USE_CONCAT hint) under the covers. But being explicit is best and easiest to control).