0

I had two query to my oracle 8i database

First query (execution time :30 msec)

select p.atc_id,a.atc_no,b.buyer_no,b.name||b.department,p.del_date_to,p.popack_id, p.popack_no,
p.buyer_po,sum(pd.quantity),nvl(max((select sum(sd.quantity) from scheduling_details sd,scheduling_master s 
where s.schedule_id=sd.schedule_id and s.popack_id=p.popack_id and (s.deleted='N' or status=1) group by s.popack_id)),0) 
from atc_master a, buyer_master b, popack_master p, popack_details pd where a.atc_id=p.atc_id and a.original='Y' and  
 a.buyer_no=b.buyer_no and p.popack_id=pd.popack_id and  a.atc_id=905 group by p.atc_id,a.atc_no,b.buyer_no,
b.name||b.department,p.del_date_to,p.popack_id,p.popack_no, p.buyer_po

Second Query(execution time :3sec)

select distinct s.popack_id from scheduling_master s ,
 scheduling_details sd, popack_details pd where s.schedule_id=sd.schedule_id and  s.popack_id=pd.popack_id and s.deleted='N' 
 group by s.popack_id,sd.quantity having sum(pd.quantity)<sd.quantity

I want the result in such a way that the first query should return the records not present in second one (Po_packid not in second one)

In my existing application that is acheived via "NOT IN " (execution time :4.3 min)

Is there any way I can do it more faster I had tried the "Minus" but it require equal number of columns in both query resultset

so any better ideas?

Sreenath Ganga
  • 696
  • 4
  • 19
  • 48
  • According to [this answer](http://stackoverflow.com/a/2246793/1048425) Oracle will optimise the join method in the same way as `NOT IN` or `NOT EXISTS`. – GarethD Feb 10 '14 at 09:51

1 Answers1

0

I had done it in a different way I had used the "minus" operator and the "IN" its execution time is (3sec) (very much better than 4.3min)

select p.atc_id,a.atc_no,b.buyer_no,b.name||b.department,p.del_date_to,p.popack_id, p.popack_no,
p.buyer_po,sum(pd.quantity),nvl(max((select sum(sd.quantity) from scheduling_details sd,scheduling_master s 
where s.schedule_id=sd.schedule_id and s.popack_id=p.popack_id and (s.deleted='N' or status=1) group by s.popack_id)),0) 
from atc_master a, buyer_master b, popack_master p, popack_details pd where a.atc_id=p.atc_id and a.original='Y' and  
 a.buyer_no=b.buyer_no and p.popack_id=pd.popack_id and  a.atc_id=905  and p.popack_id IN (select p.popack_id
from atc_master a, buyer_master b, popack_master p, popack_details pd where a.atc_id=p.atc_id and a.original='Y' and  
 a.buyer_no=b.buyer_no and p.popack_id=pd.popack_id and  a.atc_id=905 group by p.atc_id,a.atc_no,b.buyer_no,
b.name||b.department,p.del_date_to,p.popack_id,p.popack_no, p.buyer_po 
minus
select distinct s.popack_id from scheduling_master s ,
 scheduling_details sd, popack_details pd where s.schedule_id=sd.schedule_id and  s.popack_id=pd.popack_id and s.deleted='N' 
 group by s.popack_id,sd.quantity having sum(pd.quantity)<sd.quantity  )  group by p.atc_id,a.atc_no,b.buyer_no,
b.name||b.department,p.del_date_to,p.popack_id,p.popack_no, p.buyer_po

I first do the minus operation for the tables get a resultset then I used it and used the first query with popack_id "IN" result set

Sreenath Ganga
  • 696
  • 4
  • 19
  • 48