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