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