SUBQUERY_MAPJOIN HINT - MaxCompute - Alibaba Cloud ドキュメントセンター

MaxComputeはサブクエリ操作をサポートしており、これらのサブクエリの一部は実行中にJOINに変換されることで内部的に最適化されます。サブクエリの実行効率をさらに向上させるために、サブクエリ文内でSUBQUERY_MAPJOIN HINTを使用して、MAPJOINアルゴリズムを明示的に指定できます。このトピックでは、SUBQUERY_MAPJOIN HINTの使用方法について説明します。

制限事項

SCALAR、IN、NOT IN、EXISTS、およびNOT EXISTSサブクエリのみをサポートし、基本サブクエリはサポートしません。サブクエリの詳細については、「サブクエリ」をご参照ください。
SUBQUERY_MAPJOIN HINTを使用する場合、小さなテーブルを手動で指定する必要はありません。システムは、サブクエリ実行の結果をMAPJOINの小さなテーブルとして自動的に使用します。
重要
サブクエリの結果が大きすぎると、MAPJOIN操作中にOut of Memoryエラーが発生する可能性があります。このHINTを使用する前に、サブクエリ結果が小さなテーブルであることを検証することが不可欠です。 MAPJOINの制限については、「制限」をご参照ください。
特定のシナリオでは、サブクエリは実行のためにJOIN操作に変換されません。これらのシナリオでSUBQUERY_MAPJOIN HINTを使用すると、システムは警告を発します。詳細な手順については、「例5: JOINに変換されていないサブクエリがクエリ結果を返すときに警告を出力する」をご参照ください。

使用方法

サブクエリステートメント内でMAPJOINアルゴリズムを実行するには、サブクエリの左括弧の直後にHINT /* + subquery_mapjoin */ を使用して配置する必要があります。使用法を次に示します。

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 and 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);

不正な使用

-- The following code is incorrect because the SUBQUERY_MAPJOIN HINT is not written immediately after the left parenthesis corresponding to the subquery
SELECT * FROM t1 WHERE a IN (SELECT /*+ subquery_mapjoin */ a FROM t2 WHERE b = t1.b);

サンプルデータ

提供される例の理解を容易にするために、サンプルソースデータが利用可能である。

sale_detailテーブルとshop_detailテーブルを作成し、データを入力します。サンプルコマンド:

-- Create a partitioned table 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);

-- Add partitions to the sale_detail table
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');

-- Append data to the sale_detail table
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);

-- Create a shop_detail table and insert data from the 2013 partition of the sale_detail table
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

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

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

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の使用を指定できません

基本サブクエリはsubquery_mapjoin HINTをサポートしていないため、次のクエリは失敗します。

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

クエリは次の結果を返します。

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

例5: JOINに変換されていないサブクエリは、クエリ結果を返すときに警告を出力します

-- Add a column to the shop_detail table and add data
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');

-- This flag allows the system to output warnings. If the default configuration of the project already meets the requirements, there is no need to set it
SET odps.compiler.warning.disable=false;

/** The following query involves partitioned tables. To support partition pruning, the system does not convert the subquery to join.
When using the SUBQUERY_MAPJOIN HINT, the system will output a warning **/
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:[1,47]  subquery_mapjoin hint does not work because the subquery is not converted to join