Help Center/
MapReduce Service/
Component Operation Guide (LTS)/
Using HetuEngine/
Common HetuEngine SQL Syntax/
DQL Syntax/
REWRITE HINT
Updated on 2024-10-25 GMT+08:00
REWRITE HINT
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
- 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: DQL Syntax
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot
