Re: tune between query/join
Date: Fri, 21 Oct 2011 02:47:53 -0700 (PDT)
Message-ID: <1319190473.24919.YahooMailNeo_at_web65414.mail.ac4.yahoo.com>
I will respectfully disagree; the example used the query originally posted by the OP and, for 8000 records in account and 150 in tmp_account, returned a staggering 52108 rows due to the cartesian join. In actuality only 42 records met the criteria posted by the OP when they were properly matched on account_number.
Why would someone generate 52066 records of unmatched data and consider it valuable? Maybe I'm missing something here but it doesn't seem logical to me. David Fitzjarrell
From: Gints Plivna <gints.plivna_at_gmail.com>
To: oratune_at_yahoo.com
Cc: Oracle-L Group <oracle-l_at_freelists.org>
Sent: Friday, October 21, 2011 2:27 AM
Subject: Re: tune between query/join
2011/10/21 David Fitzjarrell <oratune_at_yahoo.com>:
> The original 'join' produces useless noise along with some valid data buried deep within.
As we don't know the OPs original problem it is hard to say whether it
is useless noise or valuable solution FOR HIM.
Generally join doesn't mean only eaquality, join predicate can be
everything not only (=) meaning equi join, but also any other
condition. How necessary and appropriate it is in this case of course
is another question.
And yes - if you have anything other than equi join, then the only
solution for actual join mechanism remains either nested loops
(generally not good for big datasets) or merge join. Hash joins are
possible only if there is equality and respectively hashes to compare.
Gints Plivna
http://www.gplivna.eu
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 21 2011 - 04:47:53 CDT