MaxCompute puede convertir ciertas subconsultas en operaciones JOIN durante la ejecución. Utilice el SUBQUERY_MAPJOIN HINT con estas subconsultas para forzar el algoritmo MAPJOIN y mejorar el rendimiento de las consultas.
Límites
Este hint solo aplica a subconsultas escalares, IN, NOT IN, EXISTS y NOT EXISTS. No es compatible con subconsultas básicas. Para obtener más información sobre subconsultas, consulte subqueries.
-
Al utilizar el SUBQUERY_MAPJOIN HINT, el sistema trata automáticamente el conjunto de resultados de la subconsulta como la tabla pequeña para la operación MAPJOIN, por lo que no necesita especificarla manualmente.
ImportanteSi el conjunto de resultados de la subconsulta es demasiado grande, puede ocurrir un error de memoria insuficiente (OOM). Utilice este hint únicamente cuando tenga la certeza de que el resultado de la subconsulta califica como tabla pequeña. Para obtener más información sobre los límites de MAPJOIN, consulte Limits.
En algunos escenarios, MaxCompute no convierte una subconsulta en una operación JOIN. Si utiliza el SUBQUERY_MAPJOIN HINT en estos casos, el sistema emite un mensaje de advertencia. Para ver un ejemplo, consulte Example 5.
Uso
En una subconsulta, utilice el hint /*+ subquery_mapjoin */ para especificar el algoritmo MAPJOIN. El hint debe colocarse inmediatamente después del paréntesis de apertura de la subconsulta.
Suponga que las tablas t1 y t2 se definen de la siguiente manera:
CREATE TABLE t1(a BIGINT, b BIGINT);
CREATE TABLE t2(a BIGINT, b BIGINT);
-
Subconsulta escalar
SELECT a, (/*+ subquery_mapjoin */ SELECT b FROM t2 WHERE a = t1.a) FROM t1; -
Subconsultas IN y 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); -
Subconsultas EXISTS y 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); -
Sintaxis incorrecta
-- 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);
Datos de muestra
Los ejemplos en este tema utilizan los siguientes datos de muestra.
-
Cree las tablas
sale_detailyshop_detail, y llénelas con datos.-- 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'; -
Consulte los datos en la tabla
sale_detail.SET odps.sql.allow.fullscan=true; SELECT * FROM sale_detail;Se devuelve el siguiente resultado:
+------------+-------------+-------------+------------+------------+ | 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 | +------------+-------------+-------------+------------+------------+ -
Consulte los datos en la tabla
shop_detail.SELECT * FROM shop_detail;Se devuelve el siguiente resultado:
+------------+-------------+-------------+ | shop_name | customer_id | total_price | +------------+-------------+-------------+ | s1 | c1 | 100.1 | | s2 | c2 | 100.2 | | s3 | c3 | 100.3 | +------------+-------------+-------------+
Ejemplos
A continuación se presentan ejemplos que utilizan sample data para demostrar cómo usar el SUBQUERY_MAPJOIN HINT.
Ejemplo 1: Subconsulta escalar
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;
Se devuelve el siguiente resultado:
+------------+-------------+-------------+
| shop_name | customer_id | total_price |
+------------+-------------+-------------+
| s1 | c1 | 100.1 |
| s2 | c2 | 100.2 |
| s3 | c3 | 100.3 |
+------------+-------------+-------------+
Ejemplo 2: Subconsulta IN
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);
Se devuelve el siguiente resultado:
+------------+-------------+-------------+------------+------------+
| 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 |
+------------+-------------+-------------+------------+------------+
Ejemplo 3: Subconsulta EXISTS
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);
Se devuelve el siguiente resultado:
+------------+-------------+-------------+------------+------------+
| 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 |
+------------+-------------+-------------+------------+------------+
Ejemplo 4: Las subconsultas básicas no son compatibles
La siguiente consulta falla porque el SUBQUERY_MAPJOIN HINT no admite subconsultas básicas.
SET odps.sql.allow.fullscan=true;
SELECT * FROM (/*+ subquery_mapjoin */ SELECT shop_name FROM sale_detail) a;
Se devuelve el siguiente mensaje de error:
-- error message
FAILED: ODPS-0130161:[1,16] Parse exception - invalid subquery_mapjoin hint, should only be used for scalar/in/exists subquery
Ejemplo 5: Subconsulta no convertida a JOIN
-- 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);
Se devuelve el siguiente resultado:
+------------+-------------+-------------+------------+------------+
| 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 |
+------------+-------------+-------------+------------+------------+
Se devuelve el siguiente mensaje de advertencia:
WARNING:[1,47] subquery_mapjoin hint does not work because the subquery is not converted to join