...
To enable hash lookup join, user only needs specify a new hint (SHUFFLE_HASH) in select clause in query, just like which is similar with spark[2] sql.
| Code Block |
|---|
|
SELECT /*+ SHUFFLE_HASH('Orders', 'Customers') */ o.order_id, o.total, c.country, c.zip
FROM Orders AS o
JOIN Customers FOR SYSTEM_TIME AS OF o.proc_time AS c
ON o.customer_id = c.id; |
...
| Code Block |
|---|
|
SELECT /*+ USE_HASH('Orders', 'Customers') */ o.order_id, o.total, c.country, c.zip
FROM Orders AS o
JOIN Customers FOR SYSTEM_TIME AS OF o.proc_time AS c
ON o.customer_id = c.id; |
SQL Server[34] uses keyword 'hash' instead of query hint, it's not a good choise for use, so we ignore this.
...
Anyway, the difference between the two solution is only about the internal implementation and has no impact on the user.
Reference
[1] Oracle Hash hint syntaxUSE_Hash hint
| Code Block |
|---|
|
SELECT /*+ USE_HASH(l h) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 3500; |
[2] Spark Hash hint syntax[3] SQL Server Hash Keyword syntaxSHUFFLE_HASH hint
| Code Block |
|---|
|
SELECT /*+ SHUFFLE_HASH(t1) */ * FROM t1 INNER JOIN t2 ON t1.key = t2.key; |
[3] IMPALA SHUFFLE hint
| Code Block |
|---|
|
SELECT straight_join weather.wind_velocity, geospatial.altitude
FROM weather JOIN /* +SHUFFLE */ geospatial
ON weather.lat = geospatial.lat AND weather.long = geospatial.long; |
[4] SQL Server Hash Keyword
| Code Block |
|---|
|
SELECT p.Name, pr.ProductReviewID FROM Production.Product AS p LEFT OUTER HASH JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID ORDER BY ProductReviewID DESC; |