在使用绑定 outline 的场景下，会忽略之前查询所使用的 hint。这有可能会导致计划变动。
详细说明
创建视图的时候，使用 hint，之后查询视图，验证 hint 生效。后面对视图查询创建新的 outline，会导致原有的 hint 失效。
测试用例创建如下：
create table t1(c1 int, c2 int, c3 varchar(30), c4 varchar(30));
alter table t1 add index idx_t1_c1_c2(c1,c2);
alter table t1 add index idx_c2(c2);
call dbms_stats.gather_table_stats('test', 't1');
之后，创建视图 v11 查询t1表，并且在 hint 中使用索引 /*+ index(t1 idx_t1_c1_c2) */。
CREATE VIEW v11 AS SELECT /*+ index(t1 idx_t1_c1_c2) */ * FROM t1 WHERE c1 = 3;
执行查询，会获取对应的 sql_id，之后对此 SQL 创建使用另外一个索引的 outline。
select * from v11;
CREATE OUTLINE otl_idx_c2 ON "48ABE2B1242F1CAE5F5FAE1999C9B4B3" USING HINT /*+ parallel(2) index(t1 idx_c2) */;
再次执行之前的查询语句，发现已经使用的新的 hint。
obclient [test]> select * from v11;
Empty set (0.132 sec)
obclient [test]> select dbms_xplan.display_cursor(0,'all');
| ============================================================================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|
------------------------------------------------------------------------------------------------------------
|0 |PX COORDINATOR | |1 |6 |0 |10399 |2937 |10556 |
|1 |└─EXCHANGE OUT DISTR |:EX10000 |1 |5 |0 |7953 |0 |485 |
|2 | └─PX BLOCK ITERATOR| |1 |4 |0 |7953 |0 |1 |
|3 | └─TABLE FULL SCAN|t1(idx_c2)|1 |4 |0 |7953 |0 |142 |
============================================================================================================
Outputs & filters:
-------------------------------------
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4)]), filter(nil), rowset=16
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4)]), filter(nil), rowset=16
dop=2
2 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4]), filter(nil), rowset=16
3 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4]), filter([t1.c1 = 3]), rowset=16
access([t1.__pk_increment], [t1.c1], [t1.c2], [t1.c3], [t1.c4]), partitions(p0)
is_index_back=true, is_global_index=false, filter_before_indexback[false],
range_key([t1.c2], [t1.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true
Used Hint:
-------------------------------------
/*+
INDEX("t1" "idx_c2")
PARALLEL(2)
*/
Qb name trace:
-------------------------------------
stmt_id:0, SEL$1 > SEL$CFEA49FE
stmt_id:1, SEL$2
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
PARALLEL(@"SEL$CFEA49FE" "test"."t1"@"SEL$2" 2)
INDEX(@"SEL$CFEA49FE" "test"."t1"@"SEL$2" "idx_c2")
MERGE(@"SEL$2" > "SEL$1")
PARALLEL(2)
OPTIMIZER_FEATURES_ENABLE('4.2.5.0')
END_OUTLINE_DATA
*/
Optimization Info:
-------------------------------------
t1:
table_rows:1
physical_range_rows:1
logical_range_rows:1
index_back_rows:1
output_rows:1
table_dop:2
dop_method:Global DOP
avaiable_index_name:[idx_t1_c1_c2, idx_c2, t1]
pruned_index_name:[idx_t1_c1_c2, t1]
stats info:[version=2024-12-30 12:24:55.033976, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[OPTIMIZER STATISTICS, STORAGE]
Plan Type:
DISTRIBUTED
Note:
Degree of Parallelism is 2 because of hint
影响租户
影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。
适用版本
OceanBase 数据库 V3.x、V4.x 版本。
