CODEWITHSUNDEEP
sqloracle-databaseperformance
João Santos
João Santos

Reputation: 3

SQL query - performance and using intra-selects in queries

I am a newbie at SQL and I have a question.

This query works fast and easily, declaring the container_id name in all fields. 

SELECT MASTER_CONTAINER_ID 
FROM CONTAINER_HISTORY A 
WHERE ACTION_TS IN 
  (SELECT MIN(ACTION_TS)
   FROM (SELECT * 
         FROM CONTAINER_HISTORY 
         WHERE CONTAINER_ID = 'abc' 
         AND PUTAWAY_DATE IN 
           (SELECT MIN(PUTAWAY_DATE) 
            FROM CONTAINER_HISTORY 
            WHERE CONTAINER_ID = 'abc' )))
AND CONTAINER_ID = 'abc' 
ORDER BY ACTION_TS DESC;

How can I connect all container_ids in the various selects? Is there a better way to do this?

Upvotes: 0

Views: 53

Answers (2)

Boneist
Boneist

Reputation: 23578

Here's one way, using analytic functions:

SELECT container_id,
       master_container_id,
       putaway_date,
       action_ts
FROM   (SELECT container_id,
               master_container_id,
               putaway_date,
               action_ts,
               MIN(CASE WHEN putaway_date = min_putaway_date THEN action_ts END) OVER (PARTITION BY container_id) min_action_ts_per_min_pa_dt
        FROM   (SELECT container_id,
                       master_container_id,
                       putaway_date,
                       action_ts,
                       MIN(putaway_date) OVER (PARTITION BY container_id) min_putaway_date
                FROM   container_history))
WHERE  action_ts = min_action_ts_per_min_pa_dt;

N.B. untested.

This works by first working out the minimum putaway_date for each container_id across all the rows in the container_history table.

Then, it works out the minimum action_ts when the putaway_date is the earliest.

Finally, it selects those rows that have an action_ts that matches the previously identified minimum action_ts.

N.B. Because the analytic functions are partitioning by container_id, if you want to put a where clause of where container_id = '<val>' on the outer select, Oracle should be able to push the predicate down into the inline views, meaning it only has to run the analytic functions against the specific container_id(s). If your outer query's predicates don't include filtering on container_id, then Oracle will have to run the analytic functions across all the container_ids before it can do any filtering. I mention this because of the performance impact.

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132580

I think this query is equivalent to yours:

select a.master_container_id 
from container_history a 
where action_ts in 
  (select min(b.action_ts)
   from  container_history b 
   where b.container_id = a.container_id
   and b.putaway_date in 
       (select min(c.putaway_date) 
        from container_history c
        where c.container_id = b.container_id))
and a.container_id = 'abc' 
order by a.action_ts desc;

Note that you should use different table aliases when you reference the same table more than once in a query.

(NB I switched to lowercase just because I find it a lot easier on the eye!)

Upvotes: 1

Related Questions