September 6th, 2002

Yummy Yummy SQL

From this:

SELECT DISTINCT n1.foo, n1.bar
 FROM data_view n1, data_view n2
 WHERE n1.attr = 'XXX' AND n2.attr = 'XXX' AND n1.datestamp = TRUNC(SYSDATE-1)
  AND n1.foo (+) = n2.foo AND n1.bar (+) = n2.bar

to this:

SELECT n2.foo, n2.bar
 FROM (SELECT DISTINCT foo, bar FROM data_view WHERE attr = 'XXX' AND datestamp = TRUNC(SYSDATE-1)) n1,
  (SELECT DISTINCT foo, bar FROM data_view WHERE attr = 'XXX') n2
 WHERE n1.foo (+) = n2.foo AND n1.foo (+) = n2.foo AND n1.foo IS NULL;

Was it all worth it? Yes, a cost decrease from 439 to 42 :) The subject could also be "araqnid discovers hash joins can be better than nested loops" ;)

Update:

yeah yeah, so that's:

SELECT DISTINCT n1.foo, n1.bar 
 FROM data_view n1 LEFT JOIN data_view n2 ON n1.foo = n2.foo AND n1.bar = n2.bar  
 WHERE n1.companyid = 'XXX' AND n2.companyid = 'XXX' 
  AND n1.datestamp = now()-1;
and
SELECT n2.for, n2.bar 
 FROM (SELECT DISTINCT foo, bar FROM data_view WHERE attr = 'XXX' AND datestamp = now()-1) n1
 RIGHT JOIN (SELECT DISTINCT foo, bar FROM data_view WHERE attr = 'XXX') n2 
   ON n1.foo = n2.foo AND n1.bar = n2.bar 
 WHERE n1.foo IS NULL
for you postgresql junkies :)