OceanBase 数据库如何固化视图中 SQL 的执行计划

对于非物化视图（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)