SUBQUERY_MAPJOIN HINT

Limitations

• This hint applies only to scalar, IN, NOT IN, EXISTS, and NOT EXISTS subqueries. This hint does not support basic subqueries. For more information about subqueries, see subqueries.

• When you use the SUBQUERY_MAPJOIN HINT, the system automatically treats the subquery's result set as the small table for the MAPJOIN operation, so you do not need to specify one manually.

  Important

  If the subquery's result set is too large, an out of memory (OOM) error can occur. Use this hint only when you are certain that the subquery result qualifies as a small table. For more information about MAPJOIN limitations, see Limits.

• In some scenarios, MaxCompute does not convert a subquery into a JOIN operation. If you use the SUBQUERY_MAPJOIN HINT in these cases, the system issues a warning message. For an example, see Example 5.

Usage

In a subquery, use the hint /*+ subquery_mapjoin */ to specify the MAPJOIN algorithm. The hint must be placed immediately after the opening parenthesis of the subquery.

Assume that the tables t1 and t2 are defined as follows:

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 subqueries

  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 and NOT EXISTS subqueries

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

• Incorrect syntax

  -- This is incorrect. The SUBQUERY_MAPJOIN HINT is not placed immediately after the opening parenthesis of the subquery.
  SELECT * FROM t1 WHERE a IN (SELECT /*+ subquery_mapjoin */ a FROM t2 WHERE b = t1.b);

Sample data

The examples in this topic use the following sample data.

1. Create the sale_detail and shop_detail tables and populate them with data.

   -- Create a partitioned table named 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');
   
   -- Insert data into 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 shop_detail from the 2013 partition of sale_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. Query the data in the sale_detail table.

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

   The following result is returned:

   +------------+-------------+-------------+------------+------------+
   | 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. Query the data in the shop_detail table.

   SELECT * FROM shop_detail; 

   The following result is returned:

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

Examples

The following examples use the sample data to demonstrate how to use the 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;

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

-- Add the sale_date column to shop_detail and insert 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');

-- Enable warnings. You can skip this step if your project is already configured to output warnings.
SET odps.compiler.warning.disable=false;

/** In this query, the subquery targets a partitioned table. To enable partition pruning, 
the system does not convert the subquery into a JOIN. 
Consequently, the system ignores the SUBQUERY_MAPJOIN HINT and issues 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