0

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)

2 Answers 2

3

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

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

4
  • 1
    Why do you use ancient Oracle join syntax? Modern ANSI joins exist for more than 20 years. Commented Jun 28, 2023 at 9:02
  • Why does everyone assume that ANSI is better? I could give you a dozen reasons why native Oracle syntax is better in many cases. I've been writing Oracle SQL for nearly 30 years and am quite familiar with both methods, and I don't see any supposed superiority to the ANSI syntax that applies in all cases. There are advantages and disadvantages to both and a time to use one and a time to use the other. Why can't folks stopped complaining about this and simply accept that it's a matter of preference?
    – Paul W
    Commented Jun 28, 2023 at 12:19
  • I know only one reason for using the old style: You have to use it for FAST REFRESH materialized views. ANSI join is easier to read (ok, I admit, that's personal preference) more common, i.e. used by almost any other databases, more secure (you cannot forget a join condition) and more flexible (e.g. you cannot use the (+) operator to outer-join a table to itself) Commented Jun 28, 2023 at 13:23
  • As long as we agree it's a personal preference, we can move on without criticizing each other's preferences. As I said, I could write an essay on the advantages of the native syntax over ANSI, but SO isn't the forum for doing so. Newer isn't necessarily better, especially when Oracle only added it to be industry compliant, not to actually make an improvement over their own preferred, native method. Sort of equivalent to Microsoft Windows supporting Java apps. New buzzwords, gotta jump on the bandwagon, marketing demands it.
    – Paul W
    Commented Jun 28, 2023 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.