Steve Haslam (araqnid) wrote,
Steve Haslam
araqnid

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

  • Reading Half-Marathon 2009

    Reading Half-Marathon 2009 Total 02:03:39 Position = 7626 out of ~17,000? The day didn't get off to too good a start: 6am on…

  • Getting back into training

    I guess it won't come as a surprise to those who know me that I've resolved to try and get fitter during the coming year. Which I've pretty much…

  • everyone's having a go...

    I see Mesh have a "God Rest Ye Merry Gentlemen" ditty on their Myspace page... Heading up to the parental home soon. Disorganised, as is…

  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 0 comments