SUBQUERY_MAPJOIN HINT - MaxCompute

MaxCompute支援子查詢操作，部分子查詢在執行過程中會被轉換成JOIN進行計算。您可以在子查詢SUBQUERY語句中使用SUBQUERY_MAPJOIN HINT，以顯式指定使用MAPJOIN演算法，從而提升子查詢的執行效率。本文為您介紹如何使用SUBQUERY_MAPJOIN HINT。

使用限制

僅適用於SCALAR、IN、NOT IN、EXISTS和NOT EXISTS子查詢，不適用於基礎子查詢。子查詢詳情請參見子查詢（SUBQUERY）。
使用SUBQUERY_MAPJOIN HINT時不需要手動指定小表，系統預設以SUBQUERY的計算結果作為MAPJOIN的小表。
重要
若SUBQUERY的計算結果資料量過大，可能會導致MAPJOIN運行時出現記憶體溢出（Out of Memory）的情況，請確認SUBQUERY的計算結果為小表時再使用此HINT。MAPJOIN的使用限制請參見使用限制。
部分情境下，子查詢不會被轉換成JOIN來執行，此時使用SUBQUERY_MAPJOIN HINT時，系統會輸出Warning進行提示。具體用法請參見樣本5。

使用方法

在SUBQUERY語句中，您需要使用HINT提示/*+ subquery_mapjoin */來指定MAPJOIN演算法的執行，且HINT必須緊貼於SUBQUERY對應的左括弧之後書寫。用法如下。

假設t1、t2兩張表的定義如下：

CREATE TABLE t1(a BIGINT, b BIGINT);
CREATE TABLE t2(a BIGINT, b BIGINT);

SCALAR SUBQUERY

SELECT a,
       (/*+ subquery_mapjoin */ SELECT b FROM t2 WHERE a = t1.a)
FROM t1;

IN和NOT IN SUBQUERY

SELECT * FROM t1 WHERE a IN (/*+ subquery_mapjoin */ SELECT a FROM t2 WHERE b = t1.b);
SELECT * FROM t1 WHERE a NOT IN (/*+ subquery_mapjoin */ SELECT a FROM t2 WHERE b = t1.b);

EXISTS和NOT EXISTS SUBQUERY

SELECT * FROM t1 WHERE EXISTS (/*+ subquery_mapjoin */ SELECT * FROM t2 WHERE b = t1.b);
SELECT * FROM t1 WHERE NOT EXISTS (/*+ subquery_mapjoin */ SELECT * FROM t2 WHERE b = t1.b);

錯誤寫法

-- 如下代碼為錯誤格式，SUBQUERY_MAPJOIN HINT沒有緊貼於SUBQUERY對應的左括弧之後書寫
SELECT * FROM t1 WHERE a IN (SELECT /*+ subquery_mapjoin */ a FROM t2 WHERE b = t1.b);

樣本資料

為便於理解，本文為您提供來源資料，基於來源資料提供相關樣本。

建立表sale_detail和shop_detail，並添加資料，命令樣本如下：

-- 建立一張分區表sale_detail
CREATE TABLE if NOT EXISTS sale_detail
(
shop_name     STRING,
customer_id   STRING,
total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

-- 向sale_detail表增加分區
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');

-- 向sale_detail表追加資料
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai') VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);

-- 建立一張shop_detail表，插入sale_detail表分區為2013下的資料
SET odps.sql.allow.fullscan=true;
CREATE TABLE shop_detail AS SELECT shop_name,customer_id,total_price FROM sale_detail WHERE sale_date='2013'AND region='china';

查詢分區表sale_detail中的資料，命令樣本如下：

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail;

返回結果如下：

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

查詢shop_detail表中的資料，命令樣本如下：

SELECT * FROM shop_detail;

返回結果如下：

+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s1         | c1          | 100.1       |
| s2         | c2          | 100.2       |
| s3         | c3          | 100.3       |
+------------+-------------+-------------+

使用樣本

下述樣本均基於樣本資料為您介紹SUBQUERY_MAPJOIN HINT的使用。

樣本1：SCALAR SUBQUERY使用SUBQUERY_MAPJOIN HINT

SET odps.sql.allow.fullscan=true;
SELECT * FROM shop_detail WHERE (/*+ subquery_mapjoin */ SELECT COUNT(*) FROM sale_detail WHERE sale_detail.shop_name = shop_detail.shop_name) >= 1;

返回結果如下：

+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s1         | c1          | 100.1       |
| s2         | c2          | 100.2       |
| s3         | c3          | 100.3       |
+------------+-------------+-------------+

樣本2：IN子 SUBQUERY使用SUBQUERY_MAPJOIN HINT

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail WHERE total_price IN (/*+ subquery_mapjoin */ SELECT total_price FROM shop_detail WHERE customer_id = shop_detail.customer_id);

返回結果如下：

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

樣本3：EXISTS SUBQUERY使用SUBQUERY_MAPJOIN HINT

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail WHERE EXISTS (/*+ subquery_mapjoin */ SELECT * FROM shop_detail WHERE customer_id = sale_detail.customer_id);

返回結果如下：

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

樣本4：不支援基礎子查詢指定使用SUBQUERY_MAPJOIN HINT

如下Query會報錯，因為subquery_mapjoin不支援基礎子查詢。

SET odps.sql.allow.fullscan=true;
SELECT * FROM (/*+ subquery_mapjoin */ SELECT shop_name FROM sale_detail) a;

返回結果如下：

-- 報錯資訊
FAILED: ODPS-0130161:[1,16] Parse exception - invalid subquery_mapjoin hint, should only be used for scalar/in/exists subquery

樣本5：未轉成JOIN的子查詢，系統會在輸出查詢結果的同時，進行報錯提示（即WARNING資訊）

-- 在表shop_detail中增加一列，並添加資料
ALTER TABLE shop_detail ADD columns if not exists(sale_date STRING);
INSERT OVERWRITE TABLE shop_detail VALUES ('s1','c1',100.1,'2013'),('s2','c2',100.2,'2013'),('s3','c3',100.3,'2013');

-- 該flag允許系統輸出warning，假如project的預設配置已經滿足要求，則不需要設定
SET odps.compiler.warning.disable=false;

/** 以下查詢由於涉及到分區表，系統為了支援分區裁剪，沒有把subquery轉成join,
使用SUBQUERY_MAPJOIN HINT時，系統會輸出waring資訊 **/
SELECT * FROM sale_detail WHERE sale_date IN (/*+ subquery_mapjoin */ SELECT sale_date FROM shop_detail);

返回結果如下：

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

WARNING資訊如下：

WARNING:[1,47]  subquery_mapjoin hint does not work because the subquery is not converted to join