mysql.outline
system table for you to add plan binding rules and the cdb_opt_outline_enabled
switch for you to enable/disable the outline feature.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));
Parameter | Effective Immediately | Type | Default Value | Valid Values | Description |
cdb_opt_outline_enabled | yes | bool | false | true/false | Whether to enable the outline feature. |
outline "sql" set outline_info "outline";
. Note that the string after outline_info
must start with "OUTLINE:"
, which is followed by the SQL statement with the hint information added. For example, you can add the index in column a
to table t2
in the SQL statement select *from t1, t2 where t1.a = t2.a
as follows: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";
. Note that the string after outline_info
must start with "OPT:"
, which is followed by the optimizer hint information to be added. For example, you can specify SEMIJOIN
of MATERIALIZATION/DUPSWEEDOUT
for the SQL statement select *from t1 where t1.a in (select b from t2)
as follows: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)";
OPT
keyword must follow "."OPT:1#max_execution_time(1000)"
).outline "sql" set outline_info "outline";
. Note that the string after outline_info
must start with "INDEX:"
, which is followed by the index hint information to be added.
For example, you can add the index idx1
of USE INDEX
in FOR JOIN
type to the table t1
in the database test
in query block 3 for the SQL statement 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))
as follows: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";
INDEX
keyword must follow ".index_type
has three valid values (0: INDEX_HINT_IGNORE; 1: INDEX_HINT_USE; 2: INDEX_HINT_FORCE), and clause
also has three valid values (1: FOR JOIN; 2: FOR ORDER BY; 3: FOR GROUP BY), which must be separated by "#" (e.g., "INDEX:2#test#t2#idx2#1#1"
, indicating to bind the index idx1
in USE INDEX FOR JOIN
type to the table test.t2
in the second query block).outline reset "sql";
. For example, to delete the outline information from select *from t1, t2 where t1.a = t2.a
, run the following statement: outline reset "select* from t1, t2 where t1.a = t2.a";
.outline reset all
, and the execution statement is outline reset all;
.
Was this page helpful?