?

Log in

No account? Create an account

Previous Entry | Next Entry

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

Profile

araqnid
Steve Haslam

Latest Month

March 2009
S M T W T F S
1234567
891011121314
15161718192021
22232425262728
293031    
Powered by LiveJournal.com
Designed by Tiffany Chow