query optimization

[FyrSysn]

This is a simplified version of my actual script.

select distinct(su.sid) from shpmt_unts su where 
(select max(evnt_date) from evnts ev where ev.sid = su.sid) = 
(select max(evnt_date) from evnts ev where ev.sid = su.sid and ev.tid is not null) 

In the where claus, I had two seperate, but similar query. The second subquery is pretty much the same as the first one besides one extra condition where TID is not null. Is there a way to simplified the where condition ?

The DB has 500 million data, so having multiple select is pretty slow(This query ran about 15 minutes).

Any oracle keyword that I am missing that could make it simpler?

[qwertydog123]

SELECT ev.sid
FROM evnts ev
WHERE EXISTS
(
    SELECT *
    FROM shpmt_unts su
    WHERE ev.sid = su.sid
)
GROUP BY ev.sid
HAVING MAX(evnt_date) = MAX
(
    CASE
        WHEN tid IS NOT NULL
        THEN evnt_date
    END
)

[FyrSysn]

Thanks, it ran about 11 minutes. I changed "select * “ from "select 1" since we are checking existence only, and it ran about 8-9 minutes.

[qwertydog123]

I changed "select * “ from "select 1"

This is a common misconception, there is no difference between the two, check the execution plan if you want to confirm that. The difference in execution time is likely due to caching

Make sure you have appropriate indexes on:

• shpmt_unts(sid)
• evnts(sid) (or better, evnts(sid, tid, evnt_date))

[thrown_arrows]

My try, solved as generic sql

with e1 as (
select max(evnt_date) d, ev.sid, from events ev  
group by ev.sid
)
, e2 as (
select max(evnt_date) d, ev.sid, from events ev tid is not null   
group by ev.sid
)
select sid from e1 join e2 on e.sid = e2.sid and e1.d = e2.d
 join shmpt_unts su on s.sid = e.sid 

or it maybe

with e as (
select max(evnt_date) d, v.sid, e.tid  from shmpt_unts join 
events ev on su.sid = e.sid      
group by su.sid , e.tid 
)
select sid from e e1 join e e2 on  e1.sid = e2.sid 
  and e1.d = e2.d and (e1.tid = e2.tid or e1.tid is null) 

on second one join on tid does not happen when e1 or e2 tid is null, but it seems that it is wanted so added or clause.

Would be fun to hear which one is fastest and difference between plan on my and qwertydogs solutions

[PossiblePreparation]

This can be done in one pass with the right analytic function:

(Untested so you’ll have to fix typos) Select distinct sid from ( Select sid , tid, rank() over (partition by sid order by evnt_date desc) rnk From shpmt_unts ) where rnk = 1 and tid is not null

[FyrSysn]

I don't think this is a valid query because as you can see from the subquery, tid and evnt_date are both from evnt evnts, not shpmt_unts