Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Manjunatha.

Asked: July 18, 2017 - 5:59 pm UTC

Last updated: July 18, 2017 - 11:40 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi ,

I have a table with almost 500+ million records in it.
i need to select distinct record based on the change code indicator , by which i mean ,
in my table for a given set business set key columns there can be many records with "A" , "U" and "D" change code.
I'm interested in latest record which is not D'
Below is my query , this runs for really really long time . I need your help in tuning this .


SELECT DISTINCT * from table A where
Change_CD in ('A','U') order by key_columns
minus
SELECT DISTINCT * from table A where
Change_CD='D' order by key_columns

and Connor said...

You might get benefit from a hash antijoin, ie,

select * from tableA
where changecd in ('A','U')
and (appropriate_primary_key_columns) not in 
  ( select appropriate_primary_key_columns
    from tableA
    where changecd = 'D' )


or some analytics

select *
from (
select t.*,
  row_number() over ( partition by appropriate_cols order by case when changecd in ('A','U') then 1 else 2 end ) as r_au,
  count(case when changecd = 'D' then 1 end) over ( partition by appropriate_cols ) cnt_d,
  count(case when changecd in ('A','U') then 1 end ) over ( partition by appropriate_cols ) cnt_au
from tablea
)
where r_au = 1
and cnt_d = 0
and cnt_au > 0


both of which I havent tested ...because you didnt give us anything to test with :-)

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.