Doris版本:2.1.6
基表模型:明细模型
建表sql:
CREATE TABLE test (
operate_dt date NULL DEFAULT "1991-01-01" COMMENT "",
sku varchar(512) NULL DEFAULT "" COMMENT "",
send_emp_code varchar(512) NULL DEFAULT "" COMMENT "",
operate_time varchar(300) NULL DEFAULT "" COMMENT "",
num double NULL DEFAULT "0" COMMENT "",
is_verify_exception varchar(30) NULL DEFAULT "0" COMMENT "",
verify_num double NULL DEFAULT "0" COMMENT "",
verify_material_sku varchar(65533) NULL DEFAULT "" COMMENT "",
verify_type varchar(65533) NULL COMMENT "",
INDEX idx_verify_material_sku (verify_material_sku) USING INVERTED,
INDEX idx_is_verify_exception (is_verify_exception) USING INVERTED,
INDEX idx_send_emp_code (send_emp_code) USING INVERTED,
INDEX idx_operate_time (operate_time) USING INVERTED
) ENGINE=OLAP
DUPLICATE KEY(operate_dt, sku, send_emp_code, operate_time)
COMMENT ""
PARTITION BY RANGE(operate_dt)()
DISTRIBUTED BY HASH(sku) BUCKETS 16
PROPERTIES (
"replication_allocation" = "tag.location.default:3",
"bloom_filter_columns" = "send_emp_code, sku",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.start" = "-366",
"dynamic_partition.end" = "30",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "16"
);
物化视图sql:
create materialized view test_view as
select operate_dt, send_emp_code, verify_material_sku, sum(verify_num) as verify_num
from test
where is_verify_exception = 0
group by operate_dt, send_emp_code, verify_material_sku;
查询sql:
select send_emp_code, verify_material_sku, sum(verify_num) as verify_num
from test
where operate_dt >= '2025-12-26' and operate_dt < '2026-01-27' and is_verify_exception = 0
group by send_emp_code, verify_material_sku;
确认物化视图状态为FINISHED,desc test all后也能看到物化视图
问题:查看执行计划expalin发现没有物化视图命中与否的信息,按照官网文档,无论命中与否应该都会展示
explain结果如下:
PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| send_emp_code[#67] |
| verify_material_sku[#68] |
| verify_num[#69] |
| PARTITION: UNPARTITIONED |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| VRESULT SINK |
| MYSQL_PROTOCAL |
| |
| 4:VEXCHANGE |
| offset: 0 |
| distribute expr lists: send_emp_code[#67], verify_material_sku[#68] |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: send_emp_code[#64], verify_material_sku[#65] |
| |
| HAS_COLO_PLAN_NODE: true |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:VAGGREGATE (merge finalize)(249) |
| | output: sum(partial_sum(verify_num)[#66])[#69] |
| | group by: send_emp_code[#64], verify_material_sku[#65] |
| | cardinality=5,810,778 |
| | distribute expr lists: send_emp_code[#64], verify_material_sku[#65] |
| | |
| 2:VEXCHANGE |
| offset: 0 |
| distribute expr lists: |
| |
| PLAN FRAGMENT 2 |
| |
| PARTITION: RANDOM |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: send_emp_code[#64], verify_material_sku[#65] |
| |
| 1:VAGGREGATE (update serialize)(243) |
| | STREAMING |
| | output: partial_sum(verify_num[#63])[#66] |
| | group by: send_emp_code[#61], verify_material_sku[#62] |
| | cardinality=5,810,778 |
| | distribute expr lists: |
| | |
| 0:VOlapScanNode(234) |
| TABLE: test.test(test), PREAGGREGATION: ON |
| PREDICATES: (((operate_dt[#0] >= '2025-12-26') AND (operate_dt[#0] < '2026-01-27')) AND (CAST(is_verify_exception[#50] AS double) = 0)) |
| partitions=32/397 (p20251226,p20251227,p20251228,p20251229,p20251230,p20251231,p20260101,p20260102,p20260103,p20260104,p20260105,p20260106,p20260107,p20260108,p20260109,p20260110,p20260111,p20260112,p20260113,p20260114,p20260115,p20260116,p20260117,p20260118,p20260119,p20260120,p20260121,p20260122,p20260123,p20260124,p20260125,p20260126) |
| tablets=512/512, tabletList=7835771,7835775,7835779 ... |
| cardinality=1272176091, avgRowSize=0.0, numNodes=1 |
| pushAggOp=NONE |
| final projections: send_emp_code[#4], verify_material_sku[#53], verify_num[#52] |
| final project output tuple id: 1 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+