物化视图未命中

Viewed 37
mtmv 2.1

请问，为什么会去匹配一个毫无关联的物化视图，而不去找相似的物化视图

CREATE MATERIALIZED VIEW reportview.discounts_test_owner
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 10 MINUTE
DUPLICATE KEY(fld_guid)
COMMENT '新优惠查询'
DISTRIBUTED BY HASH(fld_guid) BUCKETS 8
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
) as SELECT

fee.fld_guid,fee.fld_dq,
fee.fld_ywdy,
fee.fld_company,
fee.fld_xm,
fee.fld_area_name,
fee.fld_owner_name,
fee.fld_project_guid,
fee.fld_project_name,
fee.fld_amount,
fld_adjust_guid,
fld_rebate_type_guid,
r.fld_approve_date
FROM bj_sjzt_db.zhcx_owner_incoming_alldata fee
left join bj_sjzt_db.discounts_test r on fee.fld_guid = r.fld_guid

explain
SELECT

fee.fld_guid,fee.fld_dq,
fee.fld_ywdy,
fee.fld_company,
fee.fld_xm,
fee.fld_area_name,
fee.fld_owner_name,
fee.fld_project_guid,
fee.fld_project_name,
fee.fld_amount,
fld_adjust_guid,
fld_rebate_type_guid,
r.fld_approve_date
FROM bj_sjzt_db.zhcx_owner_incoming_alldata fee
left join bj_sjzt_db.discounts_test r on fee.fld_guid = r.fld_guid

张张张12319
2 Answers

目前的参与改写的物化视图获取逻辑是物化视图使用的表和查询使用的表有交集就会参与改写。

目前看着 discounts_test_owner 没有参与透明改写，可能是此物化视图状态不可用，没参与到改写，物化没有构建完成都是不可用的状态。

// 查看物化视图的状态,看看SyncWithBaseTables 是不是1
SELECT * 
FROM mv_infos('database'='reportview')
WHERE Name = 'discounts_test_owner' \G



*************************** 1. row ***************************
                Id: 139570
              Name: mv11
           JobName: inner_mtmv_139570
             State: NORMAL
SchemaChangeDetail: 
      RefreshState: SUCCESS
       RefreshInfo: BUILD IMMEDIATE REFRESH AUTO ON MANUAL
          QuerySql: SELECT l_shipdate, l_orderkey, O_ORDERDATE, count(*)
FROM lineitem  
LEFT OUTER JOIN orders on l_orderkey = o_orderkey
GROUP BY l_shipdate, l_orderkey, O_ORDERDATE
           EnvInfo: EnvInfo{ctlId='0', dbId='16813'}
      MvProperties: {}
   MvPartitionInfo: MTMVPartitionInfo{partitionType=FOLLOW_BASE_TABLE, relatedTable=lineitem, relatedCol='l_shipdate', partitionCol='l_shipdate'}
SyncWithBaseTables: 1


// 查看物化视图的任务状态，看看物化视图的构建任务是否是完成状态
SELECT * 
FROM tasks("type"="mv")
WHERE
MvDatabaseName = 'reportview' and    
mvName = 'discounts_test_owner'
ORDER BY  CreateTime DESC \G


*************************** 1. row ***************************
               TaskId: 167019363907545
                JobId: 139872
              JobName: inner_mtmv_139570
                 MvId: 139570
               MvName: mv11
         MvDatabaseId: 16813
       MvDatabaseName: regression_test_nereids_rules_p0_mv
               Status: SUCCESS
             ErrorMsg: 
           CreateTime: 2024-06-21 10:31:43
            StartTime: 2024-06-21 10:31:43
           FinishTime: 2024-06-21 10:31:45
           DurationMs: 2466
          TaskContext: {"triggerMode":"SYSTEM","isComplete":false}
          RefreshMode: COMPLETE
NeedRefreshPartitions: ["p_20231023_20231024","p_20231019_20231020","p_20231020_20231021","p_20231027_20231028","p_20231030_20231031","p_20231018_20231019","p_20231024_20231025","p_20231021_20231022","p_20231029_20231030","p_20231028_20231029","p_20231025_20231026","p_20231022_20231023","p_20231031_20231101","p_20231016_20231017","p_20231026_20231027"]
  CompletedPartitions: ["p_20231023_20231024","p_20231019_20231020","p_20231020_20231021","p_20231027_20231028","p_20231030_20231031","p_20231018_20231019","p_20231024_20231025","p_20231021_20231022","p_20231029_20231030","p_20231028_20231029","p_20231025_20231026","p_20231022_20231023","p_20231031_20231101","p_20231016_20231017","p_20231026_20231027"]
             Progress: 100.00% (15/15)
          LastQueryId: fe700ca3d6504521-bb522fc9ccf615e3

看下是不是物化视图没有构建完成，状态是否正常

seawinde116

hi 目标的物化视图没有参与改写导致改写失败，这个问题目前还存在不？如果需要进一步的排查，可以留下你的联系方式，我加你一起看看这个问题

seawinde116