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 |
--------------------------------------------------------------------------------------------------