MaxCompute:SUBQUERY_MAPJOIN HINT

制限事項

• このヒントは、スカラー、IN、NOT IN、EXISTS、および NOT EXISTS のサブクエリにのみ適用されます。このヒントは、基本サブクエリをサポートしていません。サブクエリの詳細については、「サブクエリ」をご参照ください。

• SUBQUERY_MAPJOIN HINT を使用すると、システムは自動的にサブクエリの結果セットを MAPJOIN 操作の小さいテーブルとして扱うため、手動で指定する必要はありません。

  重要

  サブクエリの結果セットが大きすぎると、メモリ不足 (OOM) エラーが発生する可能性があります。このヒントは、サブクエリの結果が小さいテーブルの条件を満たすことが確実な場合にのみ使用してください。MAPJOIN の制限事項の詳細については、「制限事項」をご参照ください。

• 一部のシナリオでは、MaxCompute はサブクエリを JOIN 操作に変換しません。このような場合に SUBQUERY_MAPJOIN HINT を使用すると、システムは警告メッセージを発行します。例については、「例 5」をご参照ください。

使用方法

サブクエリで、ヒント /*+ subquery_mapjoin */ を使用して MAPJOIN アルゴリズムを指定します。ヒントは、サブクエリの開き括弧の直後に配置する必要があります。

テーブル t1 と t2 が次のように定義されているとします。

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

• スカラーサブクエリ

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

• IN および NOT IN サブクエリ

  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 サブクエリ

  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 はサブクエリの開き括弧の直後に配置されていません。
  SELECT * FROM t1 WHERE a IN (SELECT /*+ subquery_mapjoin */ a FROM t2 WHERE b = t1.b);

サンプルデータ

このトピックの例では、次のサンプルデータを使用します。

1. 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);
   
   -- sale_detail の 2013 パーティションから shop_detail を作成します。
   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'; 

2. 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   |
   +------------+-------------+-------------+------------+------------+

3. 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 の使用方法を説明します。

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

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

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

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

-- shop_detail に sale_date 列を追加し、データを挿入します。
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');

-- 警告を有効にします。プロジェクトが既に警告を出力するように設定されている場合は、この手順をスキップできます。
SET odps.compiler.warning.disable=false;

/** このクエリでは、サブクエリがパーティションテーブルを対象としています。パーティションプルーニングを有効にするため、
システムはサブクエリを JOIN に変換しません。
その結果、システムは SUBQUERY_MAPJOIN HINT を無視し、警告を発行します。**/
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