# 通过主外键约束消除多余的JOIN
本文介绍如何使用主键（PRIMARY KEY）与外键（FOREIGN KEY）之间的约束关系来优化查询计划，消除多余的JOIN操作。
#### 前提条件
- 仅9.1.0.200及以上集群版本支持通过主外键约束消除多余的JOIN。
- 创建FOREIGN KEY前，需开启声明外键约束功能：
  ```
  set info_constraint_options = 'foreign_key';
  ```
  
 
#### 功能介绍
在关系型数据库中，使用JOIN连接可以将多张表数据组合在一起。在大型数据分析场景中，多表JOIN是非常常见的操作。与此同时，JOIN操作SQL执行中也是最为耗时的操作之一。因此JOIN消除是一种能极大提升SQL执行性能的优化技术之一。其中，根据用户定义的表直接的主外键约束信息进行JOIN消除是一种具体的优化方法。这些约束提供了关于表与表之间关系的信息，可以用来减少不必要的JOIN操作，减少查询时间，提高数据库性能。
DWS数据库支持通过设置GUC参数**rewrite_rule**来开启或关闭使用外键约束信息消除多余JOIN操作的功能。具体设置如下：
- 开启JOIN消除优化功能。开启后，系统在SQL执行过程中，会获取表之间已经定义好的主外键约束关系，并根据此信息结合JOIN消除规则，进行JOIN消除。
  ```
  SET rewrite_rule = 'join_elimination';
  ```
  

- 关闭JOIN消除优化功能。
  ```
  SET rewrite_rule = '';
  ```
  
 
#### 操作流程
1. 声明FOREIGN KEY。
2. 应用JOIN消除优化查询。
 
#### 声明FOREIGN KEY
- 新建表时，通过CREATE TABLE创建FOREIGN KEY。
- 对于已存在的表，通过ALTER TABLE创建或删除FOREIGN KEY。
![](https://support.huaweicloud.com/devg-911-dws/public_sys-resources/caution_3.0-zh-cn.png)
- 您需要自行确保主键和外键之间的数据约束关系。DWS不会进行数据的约束检查。如果用户未能保证正确的主键与外键约束关系，开启join消除后可能会造成查询返回的结果集错误问题。
- DROP TABLE命令会自动删除外键，DWS不会进行检验和报错。
- 如需使用JOIN消除优化，外键列须同时带有NOT NULL约束；若无NOT NULL约束，则优化器会自动添加外键列的IS NOT NULL过滤条件来确保外键不为NULL，然后应用JOIN消除优化。
 
1. **通过CREATE TABLE创建FOREIGN KEY。**
   - 创建一个带有主键的表item。
     ```
     CREATE TABLE item(a1 INT PRIMARY KEY, b1 INT) WITH (ORIENTATION=COLUMN) distribute by HASH(a1);
     ```
     
   
   
   
   - 通过CREATE TABLE语句创建带有外键约束的表store, 外键列为a2带有NOT NULL 约束，指向item表的主键列a1。
     ```
     CREATE TABLE store(a2 int NOT NULL REFERENCES item(a1), b2 int);
     ```
     
    
2. **通过ALTER TABLE创建或删除FOREIGN KEY** 。
   - 创建一个带有主键的表item。
     ```
     CREATE TABLE item(a1 INT PRIMARY KEY, b1 INT) WITH (ORIENTATION=COLUMN) distribute by HASH(a1);
     ```
     
   
   
   
   - 通过CREATE TABLE语句创建一张非外键表store。
     ```
     CREATE TABLE store(a2 int NOT NULL , b2 int);
     ```
     
   
   
   
   - 通过ALTER TABLE为store表增加外键。
     ```
     ALTER TABLE store ADD FOREIGN KEY (a2) REFERENCES item(a1);
     ```
     
   
   
   
   - 通过ALTER TABLE 为store表删除外键。
     ```
     ALTER TABLE store DROP CONSTRAINT store_a2_fkey;
     ```
     
    
3. **查看表上的外键约束** 。
   ![](https://support.huaweicloud.com/devg-911-dws/figure/zh-cn_image_0000002166774344.png "点击放大")
   
4. **DWS还提供函数验证外键约束的合法性，用户需手动调用。**
   ```
   SELECT check_foreign_key_constraint('store');
   ```
   ![](https://support.huaweicloud.com/devg-911-dws/figure/zh-cn_image_0000002202140937.png "点击放大")
   
#### 应用JOIN消除优化查询
JOIN消除是在JOIN连接中，利用主外键约束消除不必要的JOIN关系，以简化查询计划并提升查询性能。
前提条件：将join_elimination加入rewrite_rule中。
```
SET rewrite_rule='join_elimination';
```
- **两表内连接主外键消除。**
  test2.a2 为外键，test1.a1为主键。
  ```
  EXPLAIN (COSTS OFF, NODES OFF) SELECT a1, a2 FROM test1, test2 WHERE a1 = a2 ORDER BY 1,2;
               QUERY PLAN              
  -------------------------------------
    id |          operation           
   ----+------------------------------
     1 | ->  Streaming (type: GATHER)
     2 |    ->  Sort                  
     3 |       ->  Seq Scan on test2
  ```
  ```
  EXPLAIN (COSTS OFF, NODES OFF) SELECT a1, a2 FROM test1 inner join test2 on a1 = a2 ORDER BY 1,2;
               QUERY PLAN              
  -------------------------------------
    id |          operation           
   ----+------------------------------
     1 | ->  Streaming (type: GATHER)
     2 |    ->  Sort                  
     3 |       ->  Seq Scan on test2
  ```
  

- **多表内连接消除 vtest2 -\> (vtest1, vtest3)** 。
  vtest2是外键表，vtest1、vtest3是主键表。
  ```
  EXPLAIN (COSTS OFF, NODES OFF)select vtest2.* from vtest2, vtest1, vtest3 where a2 = a1 and b2 = a3;
                    QUERY PLAN                   
  -----------------------------------------------
    id |               operation                
   ----+----------------------------------------
     1 | ->  Row Adapter                        
     2 |    ->  Vector Streaming (type: GATHER)
     3 |       ->  CStore Scan on vtest2
  ```
  

- **多表串联内连接消除 test2 -\> test1 -\> test3。**
  test2是外键表，test1既是外键表也是主键表，test3是主键表。
  ```
  EXPLAIN (COSTS OFF, NODES OFF) SELECT a1, a2 FROM test1, test2, test3 WHERE a1 = a2 and b1 = a3 ORDER BY 1,2;
               QUERY PLAN              
  -------------------------------------
    id |          operation           
   ----+------------------------------
     1 | ->  Streaming (type: GATHER)
     2 |    ->  Sort                  
     3 |       ->  Seq Scan on test2
  ```
  ```
  EXPLAIN (COSTS OFF, NODES OFF) SELECT a1, a2 FROM test2 inner join (select test1.* from test1 inner join test3 on b1 = a3) on a1 = a2;;
               QUERY PLAN              
  -------------------------------------
    id |          operation           
   ----+------------------------------
     1 | ->  Streaming (type: GATHER)
     2 |    ->  Seq Scan on test2
  ```
  

- **无法消除的场景。**
  如果查询中引用了主键表的非主键列，内连接也无法消除 （如下示例需要返回test1.b1，则无法消除）。
  ```
  EXPLAIN (COSTS OFF, NODES OFF)
  SELECT b1, a2 FROM test1, test2 WHERE a1 = a2 ORDER BY 1,2;
                    QUERY PLAN                   
  -----------------------------------------------
    id |             operation             
   ----+-----------------------------------
     1 | ->  Streaming (type: GATHER)      
     2 |    ->  Sort                       
     3 |       ->  Hash Join (4,5)         
     4 |          ->  Seq Scan on test2    
     5 |          ->  Hash                 
     6 |             ->  Seq Scan on test1
   Predicate Information (identified by plan id)
   ---------------------------------------------
     3 --Hash Join (4,5)
           Hash Cond: (test2.a2 = test1.a1)
  ```