更新时间：2024-04-09 GMT+08:00
REWRITE HINT
提示可以与SELECT语句一起提供，用于使用指定的物化视图重写查询，这将优化查询，并有助于更快地执行它们。必须在查询开始时给出提示。目前支持两种类型的提示，如下所示：
- 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)
父主题： DQL 语法
