REWRITE HINT

Updated on 2024-12-13 GMT+08:00

View PDF

This statement can be used together with a SELECT statement to rewrite a query using a specified materialized view, which accelerates the execution of the query statement. A hint must be provided at the beginning of a query. Currently, the following two types of hints are supported:

NOREWRITE
Disables query rewrite. Format: /*+ NOREWRITE */
REWRITE(materialized_view_name ..)
Rewrites a query based on the known materialized view name. You can enter multiple materialized view names and separate them by spaces. A materialized view name must be fully qualified.

Format: /*+ REWRITE(mv1 mv2 ..) */

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)

Parent topic: HetuEngine DQL SQL Syntax

Previous topic: SELECT VIEW CONTENT

Next topic: HetuEngine SQL Functions and Operators