MaxCompute:SUBQUERY_MAPJOIN HINT

Sınırlamalar

• Bu hint yalnızca skaler, IN, NOT IN, EXISTS ve NOT EXISTS alt sorguları için geçerlidir. Temel alt sorguları desteklemez. Alt sorgular hakkında daha fazla bilgi için bkz. alt sorgular.

• SUBQUERY_MAPJOIN HINT kullandığınızda sistem, alt sorgunun sonuç kümesini MAPJOIN işlemi için otomatik olarak küçük tablo olarak kabul eder; bu nedenle manuel olarak belirtmeniz gerekmez.

  Önemli

  Alt sorgunun sonuç kümesi çok büyükse bellek yetersizliği (OOM) hatası oluşabilir. Bu hint'i yalnızca alt sorgu sonucunun küçük bir tablo olarak nitelendirilebileceğinden eminseniz kullanın. MAPJOIN sınırlamaları hakkında daha fazla bilgi için bkz. Sınırlamalar.

• Bazı senaryolarda MaxCompute bir alt sorguyu JOIN işlemine dönüştürmez. Bu durumlarda SUBQUERY_MAPJOIN HINT kullanırsanız sistem bir uyarı mesajı verir. Örnek için bkz. Örnek 5.

Kullanım

Bir alt sorguda MAPJOIN algoritmasını belirtmek için /*+ subquery_mapjoin */ hint'ini kullanın. Hint'i alt sorgunun açılış parantezinin hemen ardına yerleştirin.

t1 ve t2 tablolarının aşağıdaki gibi tanımlandığını varsayalım:

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

• Skaler alt sorgu

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

• IN ve NOT IN alt sorguları

  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 ve NOT EXISTS alt sorguları

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

• Hatalı sözdizimi

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

Örnek veriler

Bu konudaki örnekler aşağıdaki örnek verileri kullanır.

1. sale_detail ve shop_detail tablolarını oluşturun ve bunları verilerle doldurun.

   -- 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. sale_detail tablosundaki verileri sorgulayın.

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

   Aşağıdaki sonuç döndürülür:

   +------------+-------------+-------------+------------+------------+
   | 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 tablosundaki verileri sorgulayın.

   SELECT * FROM shop_detail; 

   Aşağıdaki sonuç döndürülür:

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

Örnekler

Aşağıdaki örneklerde SUBQUERY_MAPJOIN HINT kullanımını göstermek için örnek veriler kullanılmıştır.

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