Optimizing an Oracle SQL Query

Question

Here is a query I have that returns the chain of supervisors for an employee but it uses a bunch of nested SELECT statements. I'd like to know whether this query could be refactored to be more efficient. The query is for an application where 3 levels of management authorize an employee to take a training class. Currently we require 3 levels of approvals, but this could change to 4 or more.

SELECT badge as employee, 
   supervisor_badge as boss1, 
   (select supervisor_badge FROM hr_data level2 WHERE badge = level1.supervisor_badge) as boss2
   (select supervisor_badge FROM hr_data level3 WHERE badge = 
           (select supervisor_badge FROM hr_data level2 WHERE badge = level1.supervisor_badge)) as boss3
   FROM hr_data level1 WHERE BADGE = '123456';

badge = the employee's ID
supervisor_badge = the badge of the employee's supervisor
bothe fields are in the hr_data table

           badge    supervisor_badge 
           123456   111111
           111111   454545
           454545   332211

output

 employee       boss1      boss2      boss3
 123456         111111     454545     332211

Do you want it to run faster or do you want it to be more dynamic? So that your levels can grow without needing to change the select statement? — Niklas
– Niklas, Commented Apr 21, 2011 at 17:27
I guess easy to maintain. The Application Champions may ask me to have 5 levels of approvals. Mikes answer below makes it easy to do this. — jeff
– jeff, Commented Apr 21, 2011 at 18:36

Mike Christian · Accepted Answer · 2011-04-27 19:58:18Z

1

I don't have a database handy, to mock this up, so I'll wing it. I tried to use your same naming conventions, for clarity.

SELECT   level1.badge as employee
        ,level2.badge as boss1
        ,level3.badge as boss2
        ,level3.supervisor_badge as boss3
FROM    hr_data level1 
        INNER JOIN hr_data level2 
            ON level2.badge = level1.supervisor_badge
        INNER JOIN hr_data level3 
            ON level3.badge = level2.supervisor_badge
WHERE       level1.badge = '123456'

IMPORTANT NOTE: This will only return records where data exists at all joins. To return records that have fewer than 3 bosses (i.e. only boss1 and boss2, but no boss3), change the INNER JOIN statements to LEFT JOIN.

edited Apr 27, 2011 at 19:58

answered Apr 21, 2011 at 17:33

Mike Christian

1,5361 gold badge15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jeff Over a year ago

Thanks Mike. Your query worked correctly right out of the box. It makes it easy to add or remove levels.

pickypg · Accepted Answer · 2011-04-21 17:23:44Z

1

Use joins rather than subqueries.

SELECT
    e.badge, b1.badge, b2.badge, b3.badge
FROM
    hr_data e
LEFT JOIN hr_data b1 ON e.badge=b1.badge
LEFT JOIN hr_data b2 ON b1.badge=b2.badge
LEFT JOIN hr_data b3 ON b2.badge=b3.badge
WHERE
    e.badge='123456';

As the level is variable, you may want to consider using a Stored Procedure to internally loop for the prespecified number of levels (until it hits the top, e.g., your CEO).

answered Apr 21, 2011 at 17:23

pickypg

22.4k5 gold badges75 silver badges85 bronze badges

1 Comment

jeff Over a year ago

+1 for reminding me about using JOINS instead of SUBQUERIES. Your query is missing the supervisor badge, though.

Collectives™ on Stack Overflow

Optimizing an Oracle SQL Query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related