outline问题中outline_id为-1

OceanBase 技术问题
#1

version:4.3.0.1

mysql> show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` varchar(100) NOT NULL DEFAULT '',
  `c2` varchar(100) DEFAULT NULL,
  KEY `idx_c2` (`c2`) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.00 sec)

创建outline：

mysql> CREATE OUTLINE otl1 ON SELECT/*+ INDEX(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;
Query OK, 0 rows affected (0.08 sec)

查看outline:

mysql>  SELECT * FROM oceanbase.DBA_OB_OUTLINES WHERE OUTLINE_NAME = 'otl1'\G
*************************** 1. row ***************************
      CREATE_TIME: 2025-01-06 19:56:28.894030
      MODIFY_TIME: 2025-01-06 19:56:28.894030
        TENANT_ID: 1002
      DATABASE_ID: 501181
       OUTLINE_ID: 502321
    DATABASE_NAME: paygate
     OUTLINE_NAME: otl1
VISIBLE_SIGNATURE: SELECT * FROM t1 WHERE c2 = ?
         SQL_TEXT: SELECT/*+ INDEX(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1
   OUTLINE_TARGET: 
      OUTLINE_SQL: SELECT /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "paygate"."t1"@"SEL$1") OPTIMIZER_FEATURES_ENABLE('4.3.0.0') END_OUTLINE_DATA*/* FROM t1 WHERE c2 = 1
           SQL_ID: 
  OUTLINE_CONTENT: /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "paygate"."t1"@"SEL$1") OPTIMIZER_FEATURES_ENABLE('4.3.0.0') END_OUTLINE_DATA*/
1 row in set (0.01 sec)

查询：


mysql> SELECT * FROM t1 WHERE c2 = 1;
+----+------+
| c1 | c2   |
+----+------+
|    | 1    |
+----+------+
1 row in set (0.07 sec)

mysql> SELECT/*+ INDEX(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;
+----+------+
| c1 | c2   |
+----+------+
|    | 1    |
+----+------+
1 row in set (0.00 sec)

mysql> SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where statement like '%idx_c2%' \G
*************************** 1. row ***************************
      SQL_ID: 8D3FB65B024DF0963E6E94B8B87818AD
     PLAN_ID: 17413
   STATEMENT: SELECT/*+ INDEX(t1 idx_c2)*/ * FROM t1 WHERE c2 = ?
  OUTLINE_ID: -1
OUTLINE_DATA: /*+BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "paygate"."t1"@"SEL$1" "idx_c2") OPTIMIZER_FEATURES_ENABLE('4.3.0.0') END_OUTLINE_DATA*/

但是上面查询的OUTLINE_ID: -1，为什么不等于oceanbase.DBA_OB_OUTLINES.outline_id呢？
因为官方文档中有这样一句话：
如果outline_id与在DBA_OB_OUTLINES 中查到的outline_id相同，则表示是按绑定的Outline 生成的执行计划，否则不是。

#3

通过gv$outline先查一下是否创建成功outline。
建议使用sql_id方法创建outline，因为使用sql文本方式要求sql不能有任何差异包括空格等。

CREATE OUTLINE outline_name ON sql_id USING HINT /*+ hint_text */;