Re: tune between query/join

  • From: David Fitzjarrell <oratune@xxxxxxxxx>
  • To: "gints.plivna@xxxxxxxxx" <gints.plivna@xxxxxxxxx>
  • Date: Fri, 21 Oct 2011 02:47:53 -0700 (PDT)

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 
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@xxxxxxxxx>
To: oratune@xxxxxxxxx
Cc: Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, October 21, 2011 2:27 AM
Subject: Re: tune between query/join

2011/10/21 David Fitzjarrell <oratune@xxxxxxxxx>:
> 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

Other related posts: