--- title: OceanBase 数据库如何固化视图中 SQL 的执行计划-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 OceanBase 数据库如何固化视图中 SQL 的执行计划相关的常见问题和使用技巧，帮助您快速解决 OceanBase 数据库如何固化视图中 SQL 的执行计划的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # OceanBase 数据库如何固化视图中 SQL 的执行计划 更新时间：2026-05-14 09:21 适用版本： V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x 内容类型：TechNote 对于非物化视图（OceanBase 数据库 V4.0 之前不支持物化视图），优化器会把视图中的 SQL 带入到外层查询中进行 SQL 的重写，可能会导致与外层查询的合并，从而选择与视图中 SQL 不一样的执行计划。本文讲述如何让优化器使用原先视图的执行计划。 ## 适用版本 OceanBase 数据库所有版本。 ## 操作步骤 通过一个简单例子来说明固化视图 SQL 执行计划的方法（以下示例在 MySQL 模式下执行）。 1. 创建一个视图，并查看原视图里定义 SQL 的执行计划，记住 Outline Data。 ``` obclient [db_mysql]> create view v2 as select t11.* from t11 join t12 using(id); Query OK, 0 rows affected (0.144 sec) obclient [db_mysql]> explain extended select t11.* from t11 join t12 using(id); +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ==================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ------------------------------------ |0 |MERGE JOIN | |3 |93 | |1 | TABLE SCAN|t11 |3 |46 | |2 | TABLE SCAN|t12 |4 |46 | ==================================== Outputs & filters: ------------------------------------- 0 - output([t11.id(0x7f2088faf060)], [t11.r1(0x7f2088fafef0)], [t11.r2(0x7f2088fb01e0)]), filter(nil), equal_conds([t11.id(0x7f2088faf060) = t12.id(0x7f2088faf350)(0x7f2088faf640)]), other_conds(nil), merge_directions([ASC]) 1 - output([t11.id(0x7f2088faf060)], [t11.r1(0x7f2088fafef0)], [t11.r2(0x7f2088fb01e0)]), filter(nil), access([t11.id(0x7f2088faf060)], [t11.r1(0x7f2088fafef0)], [t11.r2(0x7f2088fb01e0)]), partitions(p0), is_index_back=false, range_key([t11.id(0x7f2088faf060)]), range(MIN ; MAX)always true 2 - output([t12.id(0x7f2088faf350)]), filter(nil), access([t12.id(0x7f2088faf350)]), partitions(p0), is_index_back=false, range_key([t12.id(0x7f2088faf350)]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("db_mysql.t11"@"SEL$1" "db_mysql.t12"@"SEL$1" )) USE_MERGE(@"SEL$1" ("db_mysql.t12"@"SEL$1" )) PQ_DISTRIBUTE(@"SEL$1" ("db_mysql.t12"@"SEL$1" ) LOCAL LOCAL) FULL(@"SEL$1" "db_mysql.t11"@"SEL$1") FULL(@"SEL$1" "db_mysql.t12"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t11:table_rows:3, physical_range_rows:3, logical_range_rows:3, index_back_rows:0, output_rows:3, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t11], estimation info[table_id:1100611139454045, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:0, version:1-1-9223372036854775807, logical_rc:3, physical_rc:3)] t12:table_rows:4, physical_range_rows:4, logical_range_rows:4, index_back_rows:0, output_rows:4, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t12], estimation info[table_id:1100611139454046, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:0, version:1-1-9223372036854775807, logical_rc:4, physical_rc:4)] Parameters ------------------------------------- | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.028 sec) ``` 2. 查看视图带入外层查询时的执行计划，可以发现视图 SQL 的执行计划已经被优化器改变了，表 t11 和表 t12 不是同级扫描。 ``` obclient [db_mysql]> explain select * from t13 join v2 using(id); +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ====================================== |ID|OPERATOR |NAME|EST. ROWS|COST| -------------------------------------- |0 |MERGE JOIN | |3 |141 | |1 | MERGE JOIN | |3 |94 | |2 | SORT | |3 |47 | |3 | TABLE SCAN|t13 |3 |46 | |4 | TABLE SCAN |t11 |3 |46 | |5 | TABLE SCAN |t12 |4 |46 | ====================================== Outputs & filters: ------------------------------------- 0 - output([t13.id], [t13.r1], [t13.r2], [t11.r1], [t11.r2]), filter(nil), equal_conds([t11.id = t12.id]), other_conds(nil) 1 - output([t13.id], [t13.r1], [t13.r2], [t11.r1], [t11.r2], [t11.id]), filter(nil), equal_conds([t13.id = t11.id]), other_conds(nil) 2 - output([t13.id], [t13.r1], [t13.r2]), filter(nil), sort_keys([t13.id, ASC]) 3 - output([t13.id], [t13.r1], [t13.r2]), filter(nil), access([t13.id], [t13.r1], [t13.r2]), partitions(p0) 4 - output([t11.id], [t11.r1], [t11.r2]), filter(nil), access([t11.id], [t11.r1], [t11.r2]), partitions(p0) 5 - output([t12.id]), filter(nil), access([t12.id]), partitions(p0) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.082 sec) ``` 3. 可以使用之前视图定义里 SQL 的 Outline Data 来固化视图 SQL 的执行计划。 ``` obclient [db_mysql]> explain select /*+ -> BEGIN_OUTLINE_DATA -> LEADING(@"SEL$1" ("db_mysql.t11"@"SEL$1" "db_mysql.t12"@"SEL$1" )) -> USE_MERGE(@"SEL$1" ("db_mysql.t12"@"SEL$1" )) -> PQ_DISTRIBUTE(@"SEL$1" ("db_mysql.t12"@"SEL$1" ) LOCAL LOCAL) -> FULL(@"SEL$1" "db_mysql.t11"@"SEL$1") -> FULL(@"SEL$1" "db_mysql.t12"@"SEL$1") -> END_OUTLINE_DATA -> */ -> * from t13 join v2 using(id); +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ===================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ------------------------------------- |0 |MERGE JOIN | |3 |141 | |1 | MERGE JOIN | |3 |93 | |2 | TABLE SCAN|t11 |3 |46 | |3 | TABLE SCAN|t12 |4 |46 | |4 | SORT | |3 |47 | |5 | TABLE SCAN|t13 |3 |46 | ===================================== Outputs & filters: ------------------------------------- 0 - output([t13.id], [t13.r1], [t13.r2], [t11.r1], [t11.r2]), filter(nil), equal_conds([t13.id = t11.id]), other_conds(nil) 1 - output([t11.r1], [t11.r2], [t11.id]), filter(nil), equal_conds([t11.id = t12.id]), other_conds(nil) 2 - output([t11.id], [t11.r1], [t11.r2]), filter(nil), access([t11.id], [t11.r1], [t11.r2]), partitions(p0) 3 - output([t12.id]), filter(nil), access([t12.id]), partitions(p0) 4 - output([t13.id], [t13.r1], [t13.r2]), filter(nil), sort_keys([t13.id, ASC]) 5 - output([t13.id], [t13.r1], [t13.r2]), filter(nil), access([t13.id], [t13.r1], [t13.r2]), partitions(p0) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.047 sec) ``` #### 注意 OceanBase 优化器可能会对引用视图的 SQL 进行重写，导致 QueryBlock 编号的变化，在使用 hint 时需注意。 Previous [如何查看 OceanBase 数据库所有租户中的查询计划？](https://www.oceanbase.com/knowledge-base/oceanbase-database-20000001045) Next [in expr 中参数较多时生成计划慢](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002396932) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线