REWRITE HINT

Example

• Forcibly execute the original SQL query without rewriting the query.

  /*+ NOREWRITE */ SELECT c1,c2 FROM table;

• Rewrite a query using a specified materialized view.

  SET SESSION materialized_view_rewrite_enabled=true; -- Enable the function of query rewrite using materialized views.
  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');
  -- Create a materialized view for a SQL query and create materialized views for the subqueries contained in the query.
  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;
  -- Rewrite a query using the materialized view of a specified subquery.
  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)
  
  -- Rewrite a query using the materialized view of a specified SQL query.
  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)