Hi Mich, Yes, Optimizer just chooses this on its own. The point of concern here is, this optimization does not work good in Large set Vs Small Set case. Nested Join is almost 10 times costlier than Hashed join or union join of 3 conditions. So is this a bug on optimizer or is this a request to add SQL hints?
Regards Ashok On Tue, Apr 5, 2016 at 1:33 AM, Mich Talebzadeh <mich.talebza...@gmail.com> wrote: > Actually this may not be a bug. It just the Optimizer decides to do a > nested loop join over Hash Join when more that two OR joins are involved > > With one equality predicate Hash JOin is chosen > > 4> SELECT COUNT(SALES.PROD_ID) from SALES, SALES2 > 5> WHERE SALES.CUST_ID = SALES2.CUST_ID > 6> go > QUERY PLAN FOR STATEMENT 1 (at line 4). > Optimized using Parallel Mode > > STEP 1 > The type of query is SELECT. > 4 operator(s) under root > |ROOT:EMIT Operator (VA = 4) > | > | |SCALAR AGGREGATE Operator (VA = 3) > | | Evaluate Ungrouped COUNT AGGREGATE. > | | > | | |*HASH JOIN Operator* (VA = 2) (Join Type: Inner Join) > | | | Using Worktable1 for internal storage. > | | | Key Count: 1 > | | | > | | | |SCAN Operator (VA = 0) > | | | | FROM TABLE > | | | | SALES2 > | | | | Table Scan. > | | | | Forward Scan. > | | | | Positioning at start of table. > | | | | Using I/O Size 64 Kbytes for data pages. > | | | | With LRU Buffer Replacement Strategy for data pages. > | | | > | | | |SCAN Operator (VA = 1) > | | | | FROM TABLE > | | | | SALES > | | | | Table Scan. > | | | | Forward Scan. > | | | | Positioning at start of table. > | | | | Using I/O Size 64 Kbytes for data pages. > | | | | With MRU Buffer Replacement Strategy for data pages. > > Total estimated I/O cost for statement 1 (at line 4): 783206. > > Now if I chose two predicates it reverts to Nested Loop Join > > 4> SELECT COUNT(SALES.PROD_ID) from SALES, SALES2 > 5> WHERE SALES.CUST_ID = SALES2.CUST_ID > 6> OR > 7> SALES.TIME_ID = SALES2.TIME_ID > 8> go > > STEP 1 > The type of query is SET OPTION ON. > Total estimated I/O cost for statement 3 (at line 3): 0. > > QUERY PLAN FOR STATEMENT 4 (at line 4). > Optimized using Parallel Mode > > STEP 1 > The type of query is SELECT. > 5 operator(s) under root > |ROOT:EMIT Operator (VA = 5) > | > | |SCALAR AGGREGATE Operator (VA = 4) > | | Evaluate Ungrouped COUNT AGGREGATE. > | | > | | |*NESTED LOOP JOIN* Operator (VA = 3) (Join Type: Inner > Join) > | | | > | | | |SCAN Operator (VA = 0) > | | | | FROM TABLE > | | | | SALES > | | | | Table Scan. > | | | | Forward Scan. > | | | | Positioning at start of table. > | | | | Using I/O Size 64 Kbytes for data pages. > | | | | With MRU Buffer Replacement Strategy for data pages. > | | | > | | | |RESTRICT Operator (VA = 2)(0)(0)(0)(7)(0) > | | | | > | | | | |SCAN Operator (VA = 1) > | | | | | FROM TABLE > | | | | | SALES2 > | | | | | Table Scan. > | | | | | Forward Scan. > | | | | | Positioning at start of table. > | | | | | Using I/O Size 64 Kbytes for data pages. > | | | | | With LRU Buffer Replacement Strategy for data > pages. > > Total estimated I/O cost for statement 4 (at line 4): 2147483647. > > 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 1 April 2016 at 13:19, ashokkumar rajendran < > ashokkumar.rajend...@gmail.com> wrote: > >> I agree with Hemant's comment. But it does not give good results for >> simple usecases like 2 OR conditions. Ultimately we need good results from >> Spark for end users. shall we consider this as a request to support SQL >> hints then? Is there any plan to support SQL hint in Spark in upcoming >> release? >> >> Regards >> Ashok >> >> On Fri, Apr 1, 2016 at 5:04 PM, Robin East <robin.e...@xense.co.uk> >> wrote: >> >>> Yes and even today CBO (e.g. in Oracle) will still require hints in some >>> cases so I think it is more like: >>> >>> RBO -> RBO + Hints -> CBO + Hints. Most relational databases meet >>> significant numbers of corner cases where CBO plans simply don’t do what >>> you would want. I don’t know enough about Spark SQL to comment on whether >>> the same problems would afflict Spark. >>> >>> >>> >>> >>> On 31 Mar 2016, at 15:54, Yong Zhang <java8...@hotmail.com> wrote: >>> >>> I agree that there won't be a generic solution for these kind of cases. >>> >>> Without the CBO from Spark or Hadoop ecosystem in short future, maybe >>> Spark DataFrame/SQL should support more hints from the end user, as in >>> these cases, end users will be smart enough to tell the engine what is the >>> correct way to do. >>> >>> Weren't the relational DBs doing exactly same path? RBO -> RBO + Hints >>> -> CBO? >>> >>> Yong >>> >>> ------------------------------ >>> Date: Thu, 31 Mar 2016 16:07:14 +0530 >>> Subject: Re: SPARK-13900 - Join with simple OR conditions take too long >>> From: hemant9...@gmail.com >>> To: ashokkumar.rajend...@gmail.com >>> CC: user@spark.apache.org >>> >>> Hi Ashok, >>> >>> That's interesting. >>> >>> As I understand, on table A and B, a nested loop join (that will produce >>> m X n rows) is performed and than each row is evaluated to see if any of >>> the condition is met. You are asking that Spark should instead do a >>> BroadcastHashJoin on the equality conditions in parallel and then union the >>> results like you are doing in a different query. >>> >>> If we leave aside parallelism for a moment, theoretically, time taken >>> for nested loop join would vary little when the number of conditions are >>> increased while the time taken for the solution that you are suggesting >>> would increase linearly with number of conditions. So, when number of >>> conditions are too many, nested loop join would be faster than the solution >>> that you suggest. Now the question is, how should Spark decide when to do >>> what? >>> >>> >>> Hemant Bhanawat <https://www.linkedin.com/in/hemant-bhanawat-92a3811> >>> www.snappydata.io >>> >>> On Thu, Mar 31, 2016 at 2:28 PM, 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 >>> >>> >>> >> >
