As Mich has already noticed, Spark defaults to NL join if there are more than one condition. Oracle is probably doing cost-based optimizations in this scenario. You can call it a bug but in my opinion it is an area where Spark is still evolving.
>> Hemant has mentioned the nested loop time will be very little. I had mentioned that NL time will *vary *little with more number of conditions. What I meant was that instead of 3 conditions if you would have 15 conditions, the NL loop would still take 13-15 mins while the hash join would take more than that. Hemant Hemant Bhanawat <https://www.linkedin.com/in/hemant-bhanawat-92a3811> www.snappydata.io On Fri, Apr 1, 2016 at 3:08 PM, ashokkumar rajendran < ashokkumar.rajend...@gmail.com> wrote: > Hi Mich, > > Thanks for the input. > > Yes, it seems to be a bug. Is it possible to fix this in next release? > > Regards > Ashok > > On Fri, Apr 1, 2016 at 2:06 PM, Mich Talebzadeh <mich.talebza...@gmail.com > > wrote: > >> hm. >> >> Sounds like it ends up in Nested Loop Join (NLJ) as opposed to Hash Join >> (HJ) when OR is used for more than one predicate comparison. >> >> In below I have a table dummy created as ORC with 1 billion rows. Just >> created another one called dummy1 with 60K rows >> >> A simple join results in Hash Join good! >> >> scala> sql("select d.id, d1.id from dummy d, dummy2 d1 where >> d.random_string = d1.random_string").explain(true) >> >> == Physical Plan == >> Project [id#212,id#219] >> >> *+- BroadcastHashJoin [random_string#216], [random_string#223], >> BuildRight* :- ConvertToUnsafe >> : +- HiveTableScan [id#212,random_string#216], MetastoreRelation >> test, dummy, Some(d) >> +- ConvertToUnsafe >> +- HiveTableScan [id#219,random_string#223], MetastoreRelation >> test, dummy2, Some(d1) >> >> When the join is done using OR on other predicates I see it starts doing >> NLJ >> >> scala> sql("select d.id, d1.id from dummy d, dummy2 d1 where >> d.random_string = d1.random_string OR d.small_vc = >> d1.small_vc").explain(true) >> >> == Physical Plan == >> Project [id#241,id#248] >> +- B*roadcastNestedLoopJoin *BuildRight, Inner, Some(((random_string#245 >> = random_string#252) || (small_vc#246 = small_vc#253))) >> :- HiveTableScan [small_vc#246,id#241,random_string#245], >> MetastoreRelation test, dummy, Some(d) >> +- HiveTableScan [id#248,random_string#252,small_vc#253], >> MetastoreRelation test, dummy2, Some(d1) >> >> in contrast the same identical tables in Oracle use Hash Join with OR >> which is expected >> >> scratch...@mydb.mich.LOCAL> select d.id, d1.id from dummy d, dummy2 d1 >> where d.random_string = d1.random_string OR d.small_vc = d1.small_vc; >> >> Execution Plan >> ---------------------------------------------------------- >> Plan hash value: 4163534687 >> >> -------------------------------------------------------------------------------------- >> | Id | Operation | Name | Rows | Bytes |TempSpc| Cost >> (%CPU)| Time | >> >> -------------------------------------------------------------------------------------- >> | 0 | SELECT STATEMENT | | 63207 | 8332K| | 1280K >> (1)| 04:16:05 | >> | 1 | CONCATENATION | | | | | >> | | >> |* 2 | * HASH JOIN *| | 60183 | 7934K| 4632K| 640K >> (1)| 02:08:03 | >> | 3 | TABLE ACCESS FULL| DUMMY2 | 60000 | 3925K| | 157 >> (1)| 00:00:02 | >> | 4 | TABLE ACCESS FULL| DUMMY | 100M| 6484M| | 261K >> (1)| 00:52:13 | >> |* 5 | *HASH JOIN *| | 3024 | 398K| 4632K| 640K >> (1)| 02:08:03 | >> | 6 | TABLE ACCESS FULL| DUMMY2 | 60000 | 3925K| | 157 >> (1)| 00:00:02 | >> | 7 | TABLE ACCESS FULL| DUMMY | 100M| 6484M| | 261K >> (1)| 00:52:13 | >> >> -------------------------------------------------------------------------------------- >> >> So this looks like a bug! >> >> >> >> Dr Mich Talebzadeh >> >> >> >> LinkedIn * >> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* >> >> >> >> http://talebzadehmich.wordpress.com >> >> >> >> On 1 April 2016 at 04:53, ashokkumar rajendran < >> ashokkumar.rajend...@gmail.com> wrote: >> >>> Thanks for the reply everyone. >>> >>> Let me provide more detail on the dataset as well. >>> 1. The big table (A) contains more than 3 billion records in parquet >>> format, which is few TBs. >>> 2. The second table (B) is only of 60K rows which is less than 10MB. >>> 3. The column on which I perform JOIN is mostly on the String datatype >>> columns. >>> 4. I used 20 machines that were of 16 cores each and 120GB RAMs for >>> testing this. >>> >>> The pseudo OR query is as below. >>> >>> sql(Select field1, field2 from A, B where A.dimension1 = B.dimension1 OR >>> A.dimension2 = B.dimension2 OR A.dimension3 = B.dimension3 OR A.dimension4 >>> = B.dimension4).explain(true) >>> >>> >>> Pseudo union query is as below. >>> >>> sql{Select field1, field2 from A, B where A.dimension1 = B.dimension1 >>> UNION ALL >>> Select field1, field2 from A, B where A.dimension2 = B.dimension2 >>> UNION ALL >>> Select field1, field2 from A, B where A.dimension3 = B.dimension3 >>> UNION ALL >>> Select field1, field2 from A, B where A.dimension4 = >>> B.dimension4}.explain(true) >>> >>> You can look at the explain plan in the ticket >>> https://issues.apache.org/jira/browse/SPARK-13900 >>> >>> Hemant has mentioned the nested loop time will be very little. But the >>> time taken by inner query for this kind of join is too long. Union of 3 >>> HashJoins take only 3 minutes (each dimension hashjoin takes 1 minute), >>> nested loop join takes nearly 13 mins. I agree that the performance of >>> HashedJoin on OR condition will be linear but that will be very optimized >>> comparing to the nested loop join. >>> >>> As Yong pointed out, if we can provide hints in SQL engine, it will be >>> awesome for these kind of cases. >>> >>> Any idea on how we can optimize this will be helpful. Please let me know >>> if any other detail is needed to provide input. >>> >>> Regards >>> Ashok >>> >>> On Thu, Mar 31, 2016 at 8:32 PM, Mich Talebzadeh < >>> mich.talebza...@gmail.com> wrote: >>> >>>> a hash join come into play when you are joining a large table with >>>> a small table (large being a relative term)..Hash join by definition works >>>> on equality condition through hash bucketing. >>>> >>>> Can you provide pseudo code for your case please? >>>> >>>> HTH >>>> >>>> Dr Mich Talebzadeh >>>> >>>> >>>> >>>> LinkedIn * >>>> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >>>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* >>>> >>>> >>>> >>>> http://talebzadehmich.wordpress.com >>>> >>>> >>>> >>>> On 31 March 2016 at 09:58, ashokkumar rajendran < >>>> ashokkumar.rajend...@gmail.com> wrote: >>>> >>>>> Hi, >>>>> >>>>> I have filed ticket SPARK-13900. There was an initial reply from a >>>>> developer but did not get any reply on this. How can we do multiple hash >>>>> joins together for OR conditions based joins? Could someone please guide >>>>> on >>>>> how can we fix this? >>>>> >>>>> Regards >>>>> Ashok >>>>> >>>> >>>> >>> >> >
