# REWRITE HINT
提示可以与SELECT语句一起提供，用于使用指定的物化视图重写查询，这将优化查询，并有助于更快地执行它们。必须在查询开始时给出提示。目前支持两种类型的提示，如下所示：
- NOREWRITE 不会进行查询重写。格式为：/\*+ NOREWRITE \*/
  
- REWRITE(materialized_view_name ..) 查询将使用提到的物化视图名称重写。可以提供多个物化视图名称，以空格分隔。物化视图名称应为完全限定名称。
  格式为：/\*+ REWRITE(mv1 mv2 ..) \*/
  
#### 示例
- 强制按原SQL执行，不对SQL进行查询重写
  ```
  /*+ NOREWRITE */ SELECT c1,c2 FROM table;
  ```
  

- 使用指定物化视图进行查询重写
  ```
  SET SESSION materialized_view_rewrite_enabled=true;  --启用物化视图查询改写能力
  CREATE TABLE t1 (id int, c1 varchar);
  INSERT INTO t1 VALUES (1,'abc'), (2,'abc2'), (3,'abc3'), (4,'abc4'), (5,'abc5'),(6,'abc6');
  CREATE TABLE t2 (id1 int, c1 varchar);
  INSERT INTO t2 VALUES (1,'abc'), (2,'abc2'), (3,'abc3'), (4,'abc4'), (5,'abc5'),(6,'abc6');
  -- 创建查询SQL的物化视图，并对SQL包含的子查询也分别创建物化视图
  CREATE MATERIALIZED VIEW mv.tpcds.test7 AS SELECT a.id,b.c1 FROM (SELECT id FROM t1 WHERE id>5) as a,(SELECT id1,c1 FROM t2 WHERE id1>4) AS b WHERE a.id = b.id1;
  CREATE MATERIALIZED VIEW mv.tpcds.test6a AS SELECT id FROM t1 WHERE id>5;
  CREATE MATERIALIZED VIEW mv.tpcds.test6b AS SELECT id1,c1 FROM t2 WHERE id1>4;
  -- 指定使用子查询的物化视图进行查询重写
  EXPLAIN /*+ REWRITE(mv.tpcds.test6a mv.tpcds.test6b) */ SELECT a.id,b.c1 FROM (SELECT id FROM t1 WHERE id>5) AS a,(SELECT id1,c1 FROM t2 WHERE id1>4) AS b WHERE a.id = b.id1;
                                                           Query Plan                                                          
  -----------------------------------------------------------------------------------------------------------------------------
   Output[id, c1]                                                                                                              
   │   Layout: [id:integer, c1:varchar]                                                                                        
   │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                                 
   └─ RemoteExchange[GATHER]                                                                                                   
      │   Layout: [id:integer, c1:varchar]                                                                                     
      │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                              
      └─ InnerJoin[("id" = "id1")][$hashvalue, $hashvalue_22]                                                                  
         │   Layout: [id:integer, c1:varchar]                                                                                  
         │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                           
         │   Distribution: PARTITIONED                                                                                         
         ├─ RemoteExchange[REPARTITION][$hashvalue]                                                                            
         │  │   Layout: [id:integer, $hashvalue:bigint]                                                                        
         │  │   Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: ?}                                                       
         │  └─ ScanProject[table = hive:tpcds:test6a]                                                                          
         │         Layout: [id:integer, $hashvalue_21:bigint]                                                                  
         │         Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: 0B}/{rows: ? (?), cpu: ?, memory: 0B, network: 0B}    
         │         $hashvalue_21 := combine_hash(BIGINT 0, COALESCE($operator$hash_code(id), BIGINT 0))                        
         │         id := id:int:0:REGULAR                                                                                      
         └─ LocalExchange[HASH][$hashvalue_22] ("id1")                                                                         
            │   Layout: [id1:integer, c1:varchar, $hashvalue_22:bigint]                                                        
            │   Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: ?}                                                       
            └─ RemoteExchange[REPARTITION][$hashvalue_23]                                                                      
               │   Layout: [id1:integer, c1:varchar, $hashvalue_23:bigint]                                                     
               │   Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: ?}                                                    
               └─ ScanProject[table = hive:tpcds:test6b]                                                                       
                      Layout: [id1:integer, c1:varchar, $hashvalue_24:bigint]                                                  
                      Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: 0B}/{rows: ? (?), cpu: ?, memory: 0B, network: 0B} 
                      $hashvalue_24 := combine_hash(BIGINT 0, COALESCE($operator$hash_code(id1), BIGINT 0))                    
                      id1 := id1:int:0:REGULAR                                                                                 
                      c1 := c1:string:1:REGULAR                                                                                
  (1 row)
  -- 指定查询SQL整体对应的物化视图进行查询重写
  EXPLAIN SELECT a.id,b.c1 FROM (SELECT id FROM t1 WHERE id>5) AS a,(SELECT id1,c1 FROM t2 WHERE id1>4) AS b WHERE a.id = b.id1;
                               Query Plan                              
  ---------------------------------------------------------------------
   Output[id, c1]                                                      
   │   Layout: [id:integer, c1:varchar]                                
   │   Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: ?}        
   └─ RemoteExchange[GATHER]                                           
      │   Layout: [id:integer, c1:varchar]                             
      │   Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: ?}     
      └─ TableScan[table = hive:tpcds:test7]                           
             Layout: [id:integer, c1:varchar]                          
             Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: 0B} 
             id := id:int:0:REGULAR                                    
             c1 := c1:string:1:REGULAR                                 
  (1 row)
  ```