outline "sql" set outline_info "outline";
outline reset ""; outline reset all;
outline flush;
create table t1(a int, b int, c int, primary key(a));create table t2(a int, b int, c int, unique key idx2(a));create table t3(a int, b int, c int, unique key idx3(a));
参数名 | 动态 | 类型 | 默认 | 参数值范围 | 说明 |
cdb_opt_outline_enabled | yes | bool | fasle | true/false | 是否打开 outline 功能 |
outline "sql" set outline_info "outline";
,注意 outline_info 后的字符串应该以 "OUTLINE:" 开头,"OUTLINE:" 之后为加入 HINT 之后的 SQL。如给 select *from t1, t2 where t1.a = t2.a
这条 SQL 的 t2 表加上 a 列上的索引。outline "select* from t1, t2 where t1.a = t2.a" set outline_info "OUTLINE:select * from t1, t2 use index(idx2) where t1.a = t2.a";
outline "sql" set outline_info "outline";
,注意 outline_info 后的字符串应该以 "OPT:" 开头,"OPT:" 之后为需要加入的 optimizer hint 信息。如给 select *from t1 where t1.a in (select b from t2)
这条 SQL 指定 MATERIALIZATION/DUPSWEEDOUT 的 SEMIJOIN。outline "select* from t1 where t1.a in (select b from t2)" set outline_info "OPT:2#qb_name(qb2)";outline "select * from t1 where t1.a in (select b from t2)" set outline_info "OPT:1#SEMIJOIN(@qb2 MATERIALIZATION, DUPSWEEDOUT)";
ie. "OPT:1#max_execution_time(1000)"
)。outline "sql" set outline_info "outline";
,注意 outline_info 后的字符串应该以 "INDEX:" 开头,"INDEX:" 之后为需要加入的 index hint 信息。
下面举个例子:给 select *from t1 where t1.a in (select t1.a from t1 where t1.b in (select t1.a from t1 left join t2 on t1.a = t2.a))
这条 SQL 的 query block 3 上数据库 test 下 t1 表增加 USE INDEX 的索引 idx1,类型为 FOR JOIN。outline "select* from t1 where t1.a in (select t1.a from t1 where t1.b in (select t1.a from t1 left join t2 on t1.a = t2.a))" set outline_info "INDEX:3#test#t1#idx1#1#0";
ie. "INDEX:2#test#t2#idx2#1#0"
表示将第2个 query block 中的 test.t2 表中绑定类型为 USE INDEX FOR JOIN 的 idx1 索引)。outline reset "sql";
,如将 select *from t1, t2 where t1.a = t2.a
的 outline 信息删除:outline reset "select* from t1, t2 where t1.a = t2.a";
。outline reset all
,执行语句为:outline reset all;
。字段名 | 说明 |
Id | OUTLINE 设置信息编号 |
Digest | 原始 SQL 语句的哈希值 |
Digest_text | 原始 SQL 语句的指纹信息文本 |
Outline_text | 绑定 OUTLINE 之后的 SQL 语句的指纹信息文本 |
字段名 | 说明 |
origin | 原始 SQL 语句指纹 |
outline | 绑定 OUTLINE 之后的 SQL 语句指纹 |
本页内容是否解决了您的问题?