The hint mechanism allows the optimizer to generate a specified execution plan.
Generally, the optimizer selects the best execution plan for a user query without the need for hints. However, in certain scenarios, the optimizer's generated execution plan may not meet the user's requirements. In such cases, you can use hints to specify the desired execution plan.
Hint syntax
From a syntax perspective, a hint is a special SQL comment. The difference is that after the left delimiter of the comment (the "/*" symbol), a "+" is added. Since it is a comment, if the server cannot recognize the hint in the SQL statement, the optimizer will ignore the hint and use the default plan generation logic. Additionally, hints only affect the logical structure of the plan generated by the optimizer and do not affect the semantics of the SQL statement.
{ DELETE | INSERT | SELECT | UPDATE | REPLACE } /*+ [hint_text][,hint_text]... */
Notice
If you use the MySQL C client to execute SQL statements with hints, you must log in using the -c option. Otherwise, the MySQL client will remove the hints as comments from the user's SQL statements, preventing the system from receiving the user's hints.
Hint parameters
The following table describes the names, semantics, and syntax of the hint parameters.
Name |
Syntax |
Semantics |
|---|---|---|
| NO_REWRITE | NO_REWRITE | Disables SQL rewriting. |
| READ_CONSISTENCY | READ_CONSISTENCY (WEAK[STRONGFROZEN]) | Specifies the read consistency setting (weak/strong). |
| INDEX_HINT | /*+ INDEX(table_name index_name) */ | Specifies the index for the table. |
| QUERY_TIMEOUT | QUERY_TIMEOUT(INTNUM) | Specifies the timeout period. |
| LOG_LEVEL | LOG_LEVEL([']log_level[']) | Specifies the log level. When you specify the module-level statement, the first single quotation mark (') is used as the start and the second single quotation mark (') as the end. For example, 'DEBUG'. |
| LEADING | LEADING([qb_name] TBL_NAME_LIST) | Specifies the join order. |
| ORDERED | ORDERED | Specifies that the join is performed in the order specified in the SQL statement. |
| FULL | FULL([qb_name] TBL_NAME) | Specifies the table access path as the primary table, which is equivalent to INDEX(TBL_NAME PRIMARY). |
| USE_PLAN_CACHE | USE_PLAN_CACHE(NONE[DEFAULT]) | Specifies whether to use the plan cache:
|
| USE_MERGE | USE_MERGE([qb_name] TBL_NAME_LIST) | Specifies that Merge Join is used when the specified table is used as the right table. |
| USE_HASH | USE_HASH([qb_name] TBL_NAME_LIST) | Specifies that Hash Join is used when the specified table is used as the right table. |
| NO_USE_HASH | NO_USE_HASH([qb_name] TBL_NAME_LIST) | Specifies that Hash Join is not used when the specified table is used as the right table. |
| USE_NL | USE_NL([qb_name] TBL_NAME_LIST) | Specifies that Nested Loop Join is used when the specified table is used as the right table. |
| USE_BNL | USE_BNL([qb_name] TBL_NAME_LIST) | Specifies that Block Nested Loop Join is used when the specified table is used as the right table. |
| USE_HASH_AGGREGATION | USE_HASH_AGGREGATION([qb_name]) | Specifies the aggregation algorithm as Hash, for example, Hash Group By or Hash Distinct. |
| NO_USE_HASH_AGGREGATION | NO_USE_HASH_AGGREGATION([qb_name]) | Specifies that the Aggregate method uses Merge Group By or Merge Distinct instead of Hash Aggregate. |
| USE_LATE_MATERIALIZATION | USE_LATE_MATERIALIZATION | Specifies to use late materialization. |
| NO_USE_LATE_MATERIALIZATION | NO_USE_LATE_MATERIALIZATION | Specifies not to use late materialization. |
| TRACE_LOG | TRACE_LOG | Specifies to collect trace records for display by SHOW TRACE. |
| QB_NAME | QB_NAME( NAME ) | Specifies the name of the query block. |
| PARALLEL | PARALLEL(INTNUM) | Specifies the degree of parallelism for distributed execution. |
| TOPK | TOPK(PRECISION MINIMUM_ROWS) | Specifies the precision and the minimum number of rows for fuzzy queries. Here, PRECISION is an integer in the range [0, 100], indicating the percentage of rows to return in a fuzzy query, and MINIMUM_ROWS specifies the minimum number of rows to return. |
| MAX_CONCURRENT | MAX_CONCURRENT(n) | Limits the concurrency of this SQL statement. |
| PUSH_SUBQ | PUSH_SUBQ[(@qb_name)] | Instructs the optimizer to execute subqueries that have not been rewritten as joins as early as possible. |
| NO_PUSH_SUBQ | NO_PUSH_SUBQ[(@qb_name)] | The opposite of PUSH_SUBQ. It instructs the optimizer to execute subqueries that have not been rewritten as joins at the end. |
| OPT_PARAM | OPT_PARAM(parameter_name [,] parameter_value) | Specifies optimizer-related configurations or system variables at the query level. |
| INDEX_MERGE | INDEX_MERGE([@query_block_name] tbl_name [index_name [,index_name]...]) | Controls whether to use an index merge plan for the specified table.
Notice
|
| NO_INDEX_MERGE | NO_INDEX_MERGE([@query_block_name] tbl_name [index_name [,index_name]...]) | Controls whether to use the specified index to generate an index merge plan for the specified table.
Notice
|
| CACHE | CACHE ([@qb_name] table_name) | Manually enables the cache for the specified table and its indexes. The cache is always enabled during queries on the table. |
| NOCACHE | NOCACHE ( [@qb_name] table_name) | Manually disables the cache for the specified table and its indexes. The cache is always disabled during queries on the table. |
Note
- The syntax for
QB_NAMEis:@NAME - The syntax for
TBL_NAMEis:[db_name.]relation_name [qb_name]
QB_NAME parameter
In DML statements, each query_block has a QB_NAME (Query Block Name), which can be specified by the user or automatically generated by the system. If the user does not specify QB_NAME using hints, the system generates it in the order of SEL$1, SEL$2, UPD$1, and DEL$1 from left to right (which is also the resolution order of the resolver).
QB_NAME can be used to precisely locate a table or to specify the behavior of any query block at a certain point. The QB_NAME in TBL_NAME is used to locate a table, and the first QB_NAME in a hint is used to specify which query_block the hint applies to.
As shown in the following example, according to the default rules, the t1_c1 path is selected for the t1 table in SEL$1, and the primary table access is selected for the t2 table in SEL$2.
obclient> CREATE TABLE t1(c1 INT, c2 INT, KEY t1_c1(c1));
Query OK, 0 rows affected
obclient> CREATE TABLE t2(c1 INT, c2 INT, KEY t2_c1(c1));
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM t1, (SELECT * FROM t2 WHERE c2 = 1 LIMIT 5)
WHERE t1.c1 = 1;
+-----------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------+
| ====================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------------------- |
| |0 |NESTED-LOOP JOIN CARTESIAN | |1 |7 | |
| |1 |├─TABLE RANGE SCAN |t1(t1_c1) |1 |7 | |
| |2 |└─MATERIAL | |1 |3 | |
| |3 | └─SUBPLAN SCAN |ANONYMOUS_VIEW1|1 |3 | |
| |4 | └─TABLE FULL SCAN |t2 |1 |3 | |
| ====================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]), filter(nil), rowset=16 |
| conds(nil), nl_params_(nil), use_batch=false |
| 1 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 |
| access([t1.__pk_increment], [t1.c1], [t1.c2]), partitions(p0) |
| is_index_back=true, is_global_index=false, |
| range_key([t1.c1], [t1.__pk_increment]), range(1,MIN ; 1,MAX), |
| range_cond([t1.c1 = 1]) |
| 2 - output([ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]), filter(nil), rowset=16 |
| 3 - output([ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]), filter(nil), rowset=16 |
| access([ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]) |
| 4 - output([t2.c1], [t2.c2]), filter([t2.c2 = 1]), rowset=16 |
| access([t2.c2], [t2.c1]), partitions(p0) |
| limit(5), offset(nil), is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t2.__pk_increment]), range(MIN ; MAX)always true |
+-----------------------------------------------------------------------------------------------------------+
25 rows in set
If the SQL specifies via hints that the t1 table in SEL$1 should use the primary table and the t2 table in SEL$2 should use the index, the example is as follows:
obclient> EXPLAIN SELECT /*+INDEX(t1 PRIMARY) INDEX(@SEL$2 t2 t2_c1)*/ *
FROM t1 , (SELECT * FROM t2 WHERE c2 = 1 LIMIT 5)
WHERE t1.c1 = 1;
+----------------------------------------------------------------------------------------------------------+
| Query Plan |
+----------------------------------------------------------------------------------------------------------+
| ====================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------------------- |
| |0 |NESTED-LOOP JOIN CARTESIAN | |1 |3 | |
| |1 |├─TABLE FULL SCAN |t1 |1 |3 | |
| |2 |└─MATERIAL | |1 |7 | |
| |3 | └─SUBPLAN SCAN |ANONYMOUS_VIEW1|1 |7 | |
| |4 | └─TABLE FULL SCAN |t2(t2_c1) |1 |7 | |
| ====================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]), filter(nil), rowset=16 |
| conds(nil), nl_params_(nil), use_batch=false |
| 1 - output([t1.c1], [t1.c2]), filter([t1.c1 = 1]), rowset=16 |
| access([t1.c1], [t1.c2]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
| 2 - output([ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]), filter(nil), rowset=16 |
| 3 - output([ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]), filter(nil), rowset=16 |
| access([ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]) |
| 4 - output([t2.c1], [t2.c2]), filter([t2.c2 = 1]), rowset=16 |
| access([t2.__pk_increment], [t2.c2], [t2.c1]), partitions(p0) |
| limit(5), offset(nil), is_index_back=true, is_global_index=false, filter_before_indexback[false], |
| range_key([t2.c1], [t2.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true |
+----------------------------------------------------------------------------------------------------------+
24 rows in set
Note
Since INDEX(t1 PRIMARY) already exists in SEL$1, there is no need to specify the query block the hint acts on.
The SQL in the preceding example can also be written in the following ways:
SELECT /*+INDEX(t1 PRIMARY) INDEX(@SEL$2 t2@SEL$2 t2_c1)*/ * FROM t1 , (SELECT * FROM t2 WHERE c2 = 1 LIMIT 5) WHERE t1.c1 = 1;
or:
SELECT /*+INDEX(t1 PRIMARY)*/ * FROM t1 , (SELECT /*+INDEX(t2 t2_c1)*/ * FROM t2 WHERE c2 = 1 LIMIT 5) WHERE t1.c1 = 1;
or:
SELECT /*+INDEX(@SEL$1 t1 PRIMARY) INDEX(@SEL$2 t2 t2_c1)*/ * FROM t1 , (SELECT * FROM t2 WHERE c2 = 1 LIMIT 5) WHERE t1.c1 = 1;
For this hint, you can view all its information in the Outline Data section of the execution result of the EXPLAIN EXTENDED command.
obclient> EXPLAIN EXTENDED SELECT * FROM t1, (SELECT * FROM t2 WHERE c2 = 1 LIMIT 5)
WHERE t1.c1 = 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------------------- |
| |0 |NESTED-LOOP JOIN CARTESIAN | |1 |7 | |
| |1 |├─TABLE RANGE SCAN |t1(t1_c1) |1 |7 | |
| |2 |└─MATERIAL | |1 |3 | |
| |3 | └─SUBPLAN SCAN |ANONYMOUS_VIEW1|1 |3 | |
| |4 | └─TABLE FULL SCAN |t2 |1 |3 | |
| ====================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1(0x7f20d7035330)], [t1.c2(0x7f20d70358b0)], [ANONYMOUS_VIEW1.c1(0x7f20d7035be0)], [ANONYMOUS_VIEW1.c2(0x7f20d7035f10)]), filter(nil), rowset=16 |
| conds(nil), nl_params_(nil), use_batch=false |
| 1 - output([t1.c1(0x7f20d7035330)], [t1.c2(0x7f20d70358b0)]), filter(nil), rowset=16 |
| access([t1.__pk_increment(0x7f20d7036b10)], [t1.c1(0x7f20d7035330)], [t1.c2(0x7f20d70358b0)]), partitions(p0) |
| is_index_back=true, is_global_index=false, |
| range_key([t1.c1(0x7f20d7035330)], [t1.__pk_increment(0x7f20d7036b10)]), range(1,MIN ; 1,MAX), |
| range_cond([t1.c1(0x7f20d7035330) = 1(0x7f20d7034b70)]) |
| 2 - output([ANONYMOUS_VIEW1.c1(0x7f20d7035be0)], [ANONYMOUS_VIEW1.c2(0x7f20d7035f10)]), filter(nil), rowset=16 |
| 3 - output([ANONYMOUS_VIEW1.c1(0x7f20d7035be0)], [ANONYMOUS_VIEW1.c2(0x7f20d7035f10)]), filter(nil), rowset=16 |
| access([ANONYMOUS_VIEW1.c1(0x7f20d7035be0)], [ANONYMOUS_VIEW1.c2(0x7f20d7035f10)]) |
| 4 - output([t2.c1(0x7f20d7033a10)], [t2.c2(0x7f20d7033490)]), filter([t2.c2(0x7f20d7033490) = 1(0x7f20d7032cd0)]), rowset=16 |
| access([t2.c2(0x7f20d7033490)], [t2.c1(0x7f20d7033a10)]), partitions(p0) |
| limit(5), offset(nil), is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t2.__pk_increment(0x7f20d70365e0)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| stmt_id:2, SEL$2 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| LEADING(@"SEL$1" ("aabb"."t1"@"SEL$1" "ANONYMOUS_VIEW1"@"SEL$1")) |
| USE_NL(@"SEL$1" "ANONYMOUS_VIEW1"@"SEL$1") |
| USE_NL_MATERIALIZATION(@"SEL$1" "ANONYMOUS_VIEW1"@"SEL$1") |
| INDEX(@"SEL$1" "aabb"."t1"@"SEL$1" "t1_c1") |
| FULL(@"SEL$2" "aabb"."t2"@"SEL$2") |
| OPTIMIZER_FEATURES_ENABLE('4.3.0.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:1 |
| dop_method:Table DOP |
| avaiable_index_name:[t1_c1, t1] |
| unstable_index_name:[t1] |
| stats version:0 |
| dynamic sampling level:0 |
| estimation method:[DEFAULT, STORAGE] |
| t2: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:1 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[t2_c1, t2] |
| pruned_index_name:[t2_c1] |
| stats version:0 |
| dynamic sampling level:0 |
| estimation method:[DEFAULT, STORAGE] |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of table property |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
79 rows in set
Hint usage rules
The general usage rules for hints are as follows:
If a hint does not specify a query block, it applies to the current query block.
Example 1: The
t2table is in Query Block 2, and it cannot be rewritten to Query Block 1. Therefore, the hint does not take effect.obclient> EXPLAIN SELECT /*+INDEX(t2 t2_c1)*/ * FROM t1 , (SELECT * FROM t2 WHERE c2 = 1 LIMIT 5) WHERE t1.c1 = 1; +-----------------------------------------------------------------------------------------------------------+ | Query Plan | +-----------------------------------------------------------------------------------------------------------+ | ====================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------------------------- | | |0 |NESTED-LOOP JOIN CARTESIAN | |1 |7 | | | |1 |├─TABLE RANGE SCAN |t1(t1_c1) |1 |7 | | | |2 |└─MATERIAL | |1 |3 | | | |3 | └─SUBPLAN SCAN |ANONYMOUS_VIEW1|1 |3 | | | |4 | └─TABLE FULL SCAN |t2 |1 |3 | | | ====================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.c1], [t1.c2], [ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]), filter(nil), rowset=16 | | conds(nil), nl_params_(nil), use_batch=false | | 1 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 | | access([t1.__pk_increment], [t1.c1], [t1.c2]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t1.c1], [t1.__pk_increment]), range(1,MIN ; 1,MAX), | | range_cond([t1.c1 = 1]) | | 2 - output([ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]), filter(nil), rowset=16 | | 3 - output([ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]), filter(nil), rowset=16 | | access([ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]) | | 4 - output([t2.c1], [t2.c2]), filter([t2.c2 = 1]), rowset=16 | | access([t2.c2], [t2.c1]), partitions(p0) | | limit(5), offset(nil), is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([t2.__pk_increment]), range(MIN ; MAX)always true | +-----------------------------------------------------------------------------------------------------------+ 25 rows in setExample 2: If the optimizer can recombine the subquery with the outer query so that only one query block is used, the hint may take effect.
obclient> EXPLAIN SELECT /*+INDEX(t2 t2_c1)*/ * FROM t1 , (SELECT * FROM t2 WHERE c2 = 1) WHERE t1.c1 = 1; +------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ================================================================ | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------------------- | | |0 |NESTED-LOOP JOIN CARTESIAN | |1 |7 | | | |1 |├─TABLE RANGE SCAN |t1(t1_c1)|1 |7 | | | |2 |└─MATERIAL | |1 |7 | | | |3 | └─TABLE FULL SCAN |t2(t2_c1)|1 |7 | | | ================================================================ | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), rowset=16 | | conds(nil), nl_params_(nil), use_batch=false | | 1 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 | | access([t1.__pk_increment], [t1.c1], [t1.c2]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t1.c1], [t1.__pk_increment]), range(1,MIN ; 1,MAX), | | range_cond([t1.c1 = 1]) | | 2 - output([t2.c1], [t2.c2]), filter(nil), rowset=16 | | 3 - output([t2.c2], [t2.c1]), filter([t2.c2 = 1]), rowset=16 | | access([t2.__pk_increment], [t2.c2], [t2.c1]), partitions(p0) | | is_index_back=true, is_global_index=false, filter_before_indexback[false], | | range_key([t2.c1], [t2.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true | +------------------------------------------------------------------------------------+ 22 rows in set
In the above examples, if the optimizer cannot merge the subquery (SELECT * FROM t2 WHERE c2 = 1) into the outer query due to internal operations within the subquery, the t2 table is still considered part of the subquery. Therefore, the hint in the outer query does not take effect.
In the following example, the outer query and the subquery both reference the same table t1 and attempt to use the PRIMARY index. This may cause conflicts when the optimizer interprets and processes the hint, potentially making the hint ineffective.
obclient> EXPLAIN SELECT /*+INDEX(t1 PRIMARY)*/ *
FROM t1 , (SELECT * FROM t1 WHERE c1 = 1)
WHERE t1.c1 = 1;
+--------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------+
| ================================================================ |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------------- |
| |0 |NESTED-LOOP JOIN CARTESIAN | |1 |7 | |
| |1 |├─TABLE RANGE SCAN |t1(t1_c1)|1 |7 | |
| |2 |└─MATERIAL | |1 |7 | |
| |3 | └─TABLE RANGE SCAN |t1(t1_c1)|1 |7 | |
| ================================================================ |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [t1.c1], [t1.c2]), filter(nil), rowset=16 |
| conds(nil), nl_params_(nil), use_batch=false |
| 1 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 |
| access([t1.__pk_increment], [t1.c1], [t1.c2]), partitions(p0) |
| is_index_back=true, is_global_index=false, |
| range_key([t1.c1], [t1.__pk_increment]), range(1,MIN ; 1,MAX), |
| range_cond([t1.c1 = 1]) |
| 2 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 |
| 3 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 |
| access([t1.__pk_increment], [t1.c1], [t1.c2]), partitions(p0) |
| is_index_back=true, is_global_index=false, |
| range_key([t1.c1], [t1.__pk_increment]), range(1,MIN ; 1,MAX), |
| range_cond([t1.c1 = 1]) |
+--------------------------------------------------------------------------+
23 rows in set
If a table specified in a hint for a join method cannot be found, the hint for that table is ignored, while the hints for other tables still take effect. If the optimizer cannot generate the specified join method, it will choose another method, and the hint will be ineffective.
If a table specified in a hint for a join order cannot be found, the entire hint becomes ineffective.
Common hints and syntax
Compared with other databases, the optimizer of OceanBase AI Database is dynamic programming and has considered all possible optimal paths. Hints mainly specify the behavior of the optimizer and execute SQL queries based on the hints.
INDEX Hint
The syntax of the INDEX hint is as follows.
- The MySQL syntax for the
INDEXhint is as follows:
table_name [[AS] alias] [index_hint_list]
index_hint_list:
index_hint [, index_hint] ...
index_hint:
USE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| IGNORE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
| FORCE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
index_list:
index_name [, index_name] ...
OceanBase AI Database in MySQL-compatible mode supports specifying multiple INDEX hints. However, for the USE and FORCE methods, only the first INDEX is used to generate the path, even if the SQL statement does not include a filter for that INDEX and a full scan with subsequent table access occurs. This is because OceanBase AI Database currently assumes that the writer of the hint knows better than the program which path is preferable. The IGNORE method ignores all specified indexes. If the INDEX specified for this method does not exist or is in the invalid state, the hint is ineffective. For the IGNORE method, if all indexes, including those of the primary table, are ignored, the hint is ineffective.
In SQL statements, if a table name has an alias, written as table_name [AS] alias, the alias must be specified for the INDEX hint to take effect. Here is an example:
obclient> create table t1(c1 int, c2 int, c3 int);
Query OK, 0 rows affected
obclient> create index idx1 on t1(c1);
Query OK, 0 rows affected
obclient> create index idx2 on t1(c2);
Query OK, 0 rows affected
obclient> insert into t1 with recursive cte(n) as (select 1 from dual union all select n+1 from cte where n < 1000) select n, mod(n, 3), n from cte;
Query OK, 1 row affected
obclient> analyze table t1 COMPUTE STATISTICS for all columns size 128;
Query OK, 0 rows affected
obclient> explain select * from t1 where c1 = 1 and c2 = 1;
+-----------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------+
| ==================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------- |
| |0 |TABLE RANGE SCAN|t1(idx1)|1 |7 | |
| ==================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 = 1]), rowset=16 |
| access([t1.__pk_increment], [t1.c1], [t1.c2], [t1.c3]), partitions(p0) |
| is_index_back=true, is_global_index=false, filter_before_indexback[false], |
| range_key([t1.c1], [t1.__pk_increment]), range(1,MIN ; 1,MAX), |
| range_cond([t1.c1 = 1]) |
+-----------------------------------------------------------------------------------+
12 rows in set
-------Effective index
obclient> explain select /*+index(t idx2)*/ * from t1 t where c1 = 1 and c2 = 1;
+-----------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------+
| =================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------- |
| |0 |TABLE RANGE SCAN|t(idx2)|1 |812 | |
| =================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t.c1], [t.c2], [t.c3]), filter([t.c1 = 1]), rowset=16 |
| access([t.__pk_increment], [t.c1], [t.c2], [t.c3]), partitions(p0) |
| is_index_back=true, is_global_index=false, filter_before_indexback[false], |
| range_key([t.c2], [t.__pk_increment]), range(1,MIN ; 1,MAX), |
| range_cond([t.c2 = 1]) |
+-----------------------------------------------------------------------------------+
12 rows in set
-------Invalid index
obclient> explain select /*+index(t1 idx2)*/ * from t1 t where c1 = 1 and c2 = 1;
+-----------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------+
| =================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------- |
| |0 |TABLE RANGE SCAN|t(idx1)|1 |7 | |
| =================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t.c1], [t.c2], [t.c3]), filter([t.c2 = 1]), rowset=16 |
| access([t.__pk_increment], [t.c1], [t.c2], [t.c3]), partitions(p0) |
| is_index_back=true, is_global_index=false, filter_before_indexback[false], |
| range_key([t.c1], [t.__pk_increment]), range(1,MIN ; 1,MAX), |
| range_cond([t.c1 = 1]) |
+-----------------------------------------------------------------------------------+
12 rows in set
In the example, the filter condition c1 = 1 has better selectivity than the filter condition c2 = 1. When the INDEX hint does not take effect, the optimizer uses the index selection mechanism and selects the idx1 index.
FULL Hint
The FULL hint specifies a full table scan for the specified table. The syntax is as follows:
/*+ FULL(table_name)*/
The FULL hint specifies a full table scan for the specified table, which is equivalent to the INDEX hint /*+ INDEX(table_name PRIMARY)*/.
ORDERED Hint
The ORDERED hint specifies the join order based on the order of tables after the FROM keyword. The syntax is as follows:
/*+ ORDERED */
If the SQL statement is rewritten after you specify this hint, the join order follows the From Items of the rewritten stmt, because the sub_query is placed at the corresponding position in the From Items during rewriting.
LEADING Hint
The LEADING hint specifies the join order of tables. The syntax is as follows:
/*+ LEADING(table_name_list)*/
You can use parentheses () in the table_name_list to indicate the join priority of internal tables and specify complex joins. Here is an example:
obclient> EXPLAIN BASIC SELECT /*+LEADING(d c b a)*/ * FROM t1 a, t1 b, t1 c, t1 d;
+---------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------------------------------------+
| ========================================= |
| |ID|OPERATOR |NAME| |
| ----------------------------------------- |
| |0 |NESTED-LOOP JOIN CARTESIAN | | |
| |1 |├─NESTED-LOOP JOIN CARTESIAN | | |
| |2 |│ ├─NESTED-LOOP JOIN CARTESIAN | | |
| |3 |│ │ ├─TABLE FULL SCAN |d | |
| |4 |│ │ └─MATERIAL | | |
| |5 |│ │ └─TABLE FULL SCAN |c | |
| |6 |│ └─MATERIAL | | |
| |7 |│ └─TABLE FULL SCAN |b | |
| |8 |└─MATERIAL | | |
| |9 | └─TABLE FULL SCAN |a | |
| ========================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([a.c1], [a.c2], [a.c3], [b.c1], [b.c2], [b.c3], [c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 1 - output([b.c1], [b.c2], [b.c3], [c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 2 - output([c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 3 - output([d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 |
| access([d.c1], [d.c2], [d.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([d.__pk_increment]), range(MIN ; MAX)always true |
| 4 - output([c.c1], [c.c2], [c.c3]), filter(nil), rowset=256 |
| 5 - output([c.c1], [c.c2], [c.c3]), filter(nil), rowset=256 |
| access([c.c1], [c.c2], [c.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([c.__pk_increment]), range(MIN ; MAX)always true |
| 6 - output([b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 |
| 7 - output([b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 |
| access([b.c1], [b.c2], [b.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([b.__pk_increment]), range(MIN ; MAX)always true |
| 8 - output([a.c1], [a.c2], [a.c3]), filter(nil), rowset=256 |
| 9 - output([a.c1], [a.c2], [a.c3]), filter(nil), rowset=256 |
| access([a.c1], [a.c2], [a.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([a.__pk_increment]), range(MIN ; MAX)always true |
+---------------------------------------------------------------------------------------------------------------------------------------+
41 rows in set
obclient> EXPLAIN BASIC SELECT /*+LEADING((d c) (b a))*/ * FROM t1 a, t1 b, t1 c, t1 d;
+---------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------------------------------------+
| ========================================= |
| |ID|OPERATOR |NAME| |
| ----------------------------------------- |
| |0 |NESTED-LOOP JOIN CARTESIAN | | |
| |1 |├─NESTED-LOOP JOIN CARTESIAN | | |
| |2 |│ ├─TABLE FULL SCAN |d | |
| |3 |│ └─MATERIAL | | |
| |4 |│ └─TABLE FULL SCAN |c | |
| |5 |└─MATERIAL | | |
| |6 | └─NESTED-LOOP JOIN CARTESIAN | | |
| |7 | ├─TABLE FULL SCAN |b | |
| |8 | └─MATERIAL | | |
| |9 | └─TABLE FULL SCAN |a | |
| ========================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([a.c1], [a.c2], [a.c3], [b.c1], [b.c2], [b.c3], [c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 1 - output([c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 2 - output([d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 |
| access([d.c1], [d.c2], [d.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([d.__pk_increment]), range(MIN ; MAX)always true |
| 3 - output([c.c1], [c.c2], [c.c3]), filter(nil), rowset=256 |
| 4 - output([c.c1], [c.c2], [c.c3]), filter(nil), rowset=256 |
| access([c.c1], [c.c2], [c.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([c.__pk_increment]), range(MIN ; MAX)always true |
| 5 - output([a.c1], [a.c2], [a.c3], [b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 |
| 6 - output([a.c1], [a.c2], [a.c3], [b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 7 - output([b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 |
| access([b.c1], [b.c2], [b.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([b.__pk_increment]), range(MIN ; MAX)always true |
| 8 - output([a.c1], [a.c2], [a.c3]), filter(nil), rowset=256 |
| 9 - output([a.c1], [a.c2], [a.c3]), filter(nil), rowset=256 |
| access([a.c1], [a.c2], [a.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([a.__pk_increment]), range(MIN ; MAX)always true |
+---------------------------------------------------------------------------------------------------------------------------------------+
41 rows in set
obclient> EXPLAIN BASIC SELECT /*+LEADING((d c b) a))*/ * FROM t1 a, t1 b, t1 c, t1 d;
+---------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------------------------------------+
| ========================================= |
| |ID|OPERATOR |NAME| |
| ----------------------------------------- |
| |0 |NESTED-LOOP JOIN CARTESIAN | | |
| |1 |├─NESTED-LOOP JOIN CARTESIAN | | |
| |2 |│ ├─NESTED-LOOP JOIN CARTESIAN | | |
| |3 |│ │ ├─TABLE FULL SCAN |d | |
| |4 |│ │ └─MATERIAL | | |
| |5 |│ │ └─TABLE FULL SCAN |c | |
| |6 |│ └─MATERIAL | | |
| |7 |│ └─TABLE FULL SCAN |b | |
| |8 |└─MATERIAL | | |
| |9 | └─TABLE FULL SCAN |a | |
| ========================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([a.c1], [a.c2], [a.c3], [b.c1], [b.c2], [b.c3], [c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 1 - output([b.c1], [b.c2], [b.c3], [c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 2 - output([c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 3 - output([d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 |
| access([d.c1], [d.c2], [d.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([d.__pk_increment]), range(MIN ; MAX)always true |
| 4 - output([c.c1], [c.c2], [c.c3]), filter(nil), rowset=256 |
| 5 - output([c.c1], [c.c2], [c.c3]), filter(nil), rowset=256 |
| access([c.c1], [c.c2], [c.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([c.__pk_increment]), range(MIN ; MAX)always true |
| 6 - output([b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 |
| 7 - output([b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 |
| access([b.c1], [b.c2], [b.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([b.__pk_increment]), range(MIN ; MAX)always true |
| 8 - output([a.c1], [a.c2], [a.c3]), filter(nil), rowset=256 |
| 9 - output([a.c1], [a.c2], [a.c3]), filter(nil), rowset=256 |
| access([a.c1], [a.c2], [a.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([a.__pk_increment]), range(MIN ; MAX)always true |
+---------------------------------------------------------------------------------------------------------------------------------------+
41 rows in set
To ensure the join order specified by the user is followed, the LEADING hint has strict checks. If the specified table_name does not exist, the LEADING hint becomes ineffective. If there are duplicate tables in the hint, the LEADING hint also becomes ineffective. If the optimizer cannot find the corresponding table in the From Items by table_id during the join process, the JOIN order specified for this table and subsequent tables becomes ineffective, while the JOIN order specified for tables before this one remains effective.
USE_MERGE Hint
The USE_MERGE hint specifies that the specified table is used as the right table when the Merge Join algorithm is used. The syntax is as follows:
/*+ USE_MERGE(table_name_list) */
Notice
In OceanBase AI Database, Merge Join requires an equality condition in the join-condition. Therefore, if two tables without an equality condition are joined, the use_merge hint is ineffective.
Currently, there is no definitive conclusion on whether A Merge Join B is equivalent to B Merge Join A. According to the cost model, Merge Join distinguishes between the left and right tables when calculating the cost, which increases the flexibility of the hint. Therefore, Merge Join distinguishes between the left and right tables, and the use_merge hint takes effect only when the table is used as the right table.
USE_NL Hint
The basic structure of hints related to joins is as follows: join_hint_name ( @ qb_name table_name_list) The basic semantics are that when the right table matches the table_name_list, a plan is generated based on the hint semantics. Generally, the LEADING hint is used to specify the join order, making the tables in the table_name_list the right tables. Otherwise, the hint becomes ineffective as the join order changes.
The table_name_list can be in the following forms:
- Single table use_nl ( t1 ): Use Nested Loop Join when t1 is the right table.
- Multiple single tables use_nl ( t1 t2 ... ): Use Nested Loop Join when t1 or t2 is the right table.
- Multiple tables use_nl ( (t1 t2) ): Use Nested Loop Join when t1 join t2 is the right table, ignoring the join order and method of t1 and t2.
- Multiple groups of tables use_nl ( t1 (t2 t3) (t4 t5 t6) ... ): Use Nested Loop Join when t1 / t2 join t3 / t4 join t5 join t6 is the right table.
The USE_NL hint specifies that the specified table is used as the right table when the Nested Loop Join algorithm is used. The syntax is as follows:
/*+ USE_NL(table_name_list) */
Examples:
obclient> CREATE TABLE t0(c1 INT, c2 INT, c3 INT);
obclient> CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
obclient> CREATE TABLE t2(c1 INT, c2 INT, c3 INT);
obclient> EXPLAIN EXTENDED SELECT /*+LEADING(t0 t1) USE_NL(t1)*/ *
FROM t0, t1 WHERE t0.c1 = t1.c1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =================================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------- |
| |0 |NESTED-LOOP JOIN | |1 |3 | |
| |1 |├─TABLE FULL SCAN |t0 |1 |3 | |
| |2 |└─MATERIAL | |1 |3 | |
| |3 | └─TABLE FULL SCAN|t1 |1 |3 | |
| =================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t0.c1(0x7f218dc21640)], [t0.c2(0x7f218dc21f00)], [t0.c3(0x7f218dc22230)], [t1.c1(0x7f218dc21980)], [t1.c2(0x7f218dc22560)], [t1.c3(0x7f218dc22890)]), filter(nil), rowset=16 |
| conds([t0.c1(0x7f218dc21640) = t1.c1(0x7f218dc21980)(0x7f218dc20e80)]), nl_params_(nil), use_batch=false |
| 1 - output([t0.c1(0x7f218dc21640)], [t0.c2(0x7f218dc21f00)], [t0.c3(0x7f218dc22230)]), filter(nil), rowset=16 |
| access([t0.c1(0x7f218dc21640)], [t0.c2(0x7f218dc21f00)], [t0.c3(0x7f218dc22230)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t0.__pk_increment(0x7f218dc230f0)]), range(MIN ; MAX)always true |
| 2 - output([t1.c1(0x7f218dc21980)], [t1.c2(0x7f218dc22560)], [t1.c3(0x7f218dc22890)]), filter(nil), rowset=16 |
| 3 - output([t1.c1(0x7f218dc21980)], [t1.c2(0x7f218dc22560)], [t1.c3(0x7f218dc22890)]), filter(nil), rowset=16 |
| access([t1.c1(0x7f218dc21980)], [t1.c2(0x7f218dc22560)], [t1.c3(0x7f218dc22890)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment(0x7f218dc23420)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| LEADING(("t0" "t1")) |
| USE_NL("t1") |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| LEADING(@"SEL$1" ("cccc"."t0"@"SEL$1" "cccc"."t1"@"SEL$1")) |
| USE_NL(@"SEL$1" "cccc"."t1"@"SEL$1") |
| USE_NL_MATERIALIZATION(@"SEL$1" "cccc"."t1"@"SEL$1") |
| FULL(@"SEL$1" "cccc"."t0"@"SEL$1") |
| FULL(@"SEL$1" "cccc"."t1"@"SEL$1") |
| OPTIMIZER_FEATURES_ENABLE('4.3.0.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| t0: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:1 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[t0] |
| stats version:0 |
| dynamic sampling level:1 |
| estimation method:[DYNAMIC SAMPLING FULL] |
| t1: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:1 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[t1] |
| stats version:0 |
| dynamic sampling level:1 |
| estimation method:[DYNAMIC SAMPLING FULL] |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of table property |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
74 rows in set
obclient> EXPLAIN EXTENDED SELECT /*+LEADING(t0 (t1 t2)) USE_NL((t1 t2))*/ *
FROM t0, t1, t2 WHERE t0.c1 = t1.c1 AND t0.c1 = t2.c1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===================================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------------- |
| |0 |NESTED-LOOP JOIN | |1 |3 | |
| |1 |├─TABLE FULL SCAN |t0 |1 |3 | |
| |2 |└─MATERIAL | |1 |5 | |
| |3 | └─HASH JOIN | |1 |5 | |
| |4 | ├─TABLE FULL SCAN|t1 |1 |3 | |
| |5 | └─TABLE FULL SCAN|t2 |1 |3 | |
| ===================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t0.c1(0x7f217d422a30)], [t0.c2(0x7f217d424700)], [t0.c3(0x7f217d424a30)], [t1.c1(0x7f217d422d70)], [t1.c2(0x7f217d424d60)], [t1.c3(0x7f217d425090)], |
| [t2.c1(0x7f217d424180)], [t2.c2(0x7f217d4253c0)], [t2.c3(0x7f217d4256f0)]), filter(nil), rowset=16 |
| conds([t0.c1(0x7f217d422a30) = t1.c1(0x7f217d422d70)(0x7f217d422270)]), nl_params_(nil), use_batch=false |
| 1 - output([t0.c1(0x7f217d422a30)], [t0.c2(0x7f217d424700)], [t0.c3(0x7f217d424a30)]), filter(nil), rowset=16 |
| access([t0.c1(0x7f217d422a30)], [t0.c2(0x7f217d424700)], [t0.c3(0x7f217d424a30)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t0.__pk_increment(0x7f217d426110)]), range(MIN ; MAX)always true |
| 2 - output([t1.c1(0x7f217d422d70)], [t1.c2(0x7f217d424d60)], [t1.c3(0x7f217d425090)], [t2.c1(0x7f217d424180)], [t2.c2(0x7f217d4253c0)], [t2.c3(0x7f217d4256f0)]), filter(nil), rowset=16 |
| 3 - output([t1.c1(0x7f217d422d70)], [t1.c2(0x7f217d424d60)], [t1.c3(0x7f217d425090)], [t2.c1(0x7f217d424180)], [t2.c2(0x7f217d4253c0)], [t2.c3(0x7f217d4256f0)]), filter(nil), rowset=16 |
| equal_conds([t1.c1(0x7f217d422d70) = t2.c1(0x7f217d424180)(0x7f217d4c2500)]), other_conds(nil) |
| 4 - output([t1.c1(0x7f217d422d70)], [t1.c2(0x7f217d424d60)], [t1.c3(0x7f217d425090)]), filter(nil), rowset=16 |
| access([t1.c1(0x7f217d422d70)], [t1.c2(0x7f217d424d60)], [t1.c3(0x7f217d425090)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment(0x7f217d426440)]), range(MIN ; MAX)always true |
| 5 - output([t2.c1(0x7f217d424180)], [t2.c2(0x7f217d4253c0)], [t2.c3(0x7f217d4256f0)]), filter(nil), rowset=16 |
| access([t2.c1(0x7f217d424180)], [t2.c2(0x7f217d4253c0)], [t2.c3(0x7f217d4256f0)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t2.__pk_increment(0x7f217d426770)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| LEADING(("t0" ("t1" "t2"))) |
| USE_NL(("t1" "t2")) |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| LEADING(@"SEL$1" ("cccc"."t0"@"SEL$1" ("cccc"."t1"@"SEL$1" "cccc"."t2"@"SEL$1"))) |
| USE_NL(@"SEL$1" ("cccc"."t2"@"SEL$1" "cccc"."t1"@"SEL$1")) |
| USE_NL_MATERIALIZATION(@"SEL$1" ("cccc"."t2"@"SEL$1" "cccc"."t1"@"SEL$1")) |
| FULL(@"SEL$1" "cccc"."t0"@"SEL$1") |
| USE_HASH(@"SEL$1" "cccc"."t2"@"SEL$1") |
| FULL(@"SEL$1" "cccc"."t1"@"SEL$1") |
| FULL(@"SEL$1" "cccc"."t2"@"SEL$1") |
| OPTIMIZER_FEATURES_ENABLE('4.3.0.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| t0: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:1 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[t0] |
| stats version:0 |
| dynamic sampling level:1 |
| estimation method:[DYNAMIC SAMPLING FULL] |
| t1: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:1 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[t1] |
| stats version:0 |
| dynamic sampling level:1 |
| estimation method:[DYNAMIC SAMPLING FULL] |
| t2: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:1 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[t2] |
| stats version:0 |
| dynamic sampling level:1 |
| estimation method:[DYNAMIC SAMPLING FULL] |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of table property |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
97 rows in set
USE_HASH Hint
The USE_HASH hint specifies that the specified table is used as the right table when the Hash Join algorithm is used. The syntax is as follows:
/*+ USE_HASH(table_name_list) */
Examples:
obclient> CREATE TABLE t0(c1 INT, c2 INT, c3 INT);
obclient> CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
obclient> EXPLAIN EXTENDED SELECT /*+LEADING(t0 t1) USE_HASH(t1)*/ *
FROM t0, t1 WHERE t0.c1 = t1.c1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |HASH JOIN | |1 |5 | |
| |1 |├─TABLE FULL SCAN|t0 |1 |3 | |
| |2 |└─TABLE FULL SCAN|t1 |1 |3 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t0.c1(0x7f21a8421640)], [t0.c2(0x7f21a8421f00)], [t0.c3(0x7f21a8422230)], [t1.c1(0x7f21a8421980)], [t1.c2(0x7f21a8422560)], [t1.c3(0x7f21a8422890)]), filter(nil), rowset=16 |
| equal_conds([t0.c1(0x7f21a8421640) = t1.c1(0x7f21a8421980)(0x7f21a8420e80)]), other_conds(nil) |
| 1 - output([t0.c1(0x7f21a8421640)], [t0.c2(0x7f21a8421f00)], [t0.c3(0x7f21a8422230)]), filter(nil), rowset=16 |
| access([t0.c1(0x7f21a8421640)], [t0.c2(0x7f21a8421f00)], [t0.c3(0x7f21a8422230)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t0.__pk_increment(0x7f21a84230f0)]), range(MIN ; MAX)always true |
| 2 - output([t1.c1(0x7f21a8421980)], [t1.c2(0x7f21a8422560)], [t1.c3(0x7f21a8422890)]), filter(nil), rowset=16 |
| access([t1.c1(0x7f21a8421980)], [t1.c2(0x7f21a8422560)], [t1.c3(0x7f21a8422890)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment(0x7f21a8423420)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| LEADING(("t0" "t1")) |
| USE_HASH("t1") |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| LEADING(@"SEL$1" ("dddd"."t0"@"SEL$1" "dddd"."t1"@"SEL$1")) |
| USE_HASH(@"SEL$1" "dddd"."t1"@"SEL$1") |
| FULL(@"SEL$1" "dddd"."t0"@"SEL$1") |
| FULL(@"SEL$1" "dddd"."t1"@"SEL$1") |
| OPTIMIZER_FEATURES_ENABLE('4.3.0.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| t0: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:1 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[t0] |
| stats version:0 |
| dynamic sampling level:1 |
| estimation method:[DYNAMIC SAMPLING FULL] |
| t1: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:1 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[t1] |
| stats version:0 |
| dynamic sampling level:1 |
| estimation method:[DYNAMIC SAMPLING FULL] |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of table property |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
71 rows in set
PARALLEL Hint
PARALLEL specifies the degree of parallelism at the statement level. The syntax is as follows:
/*+ PARALLEL(n) */
Here, n is an integer that specifies the degree of parallelism.
OceanBase AI Database also supports the PARALLEL hint at the table level. The syntax is as follows:
/*+ PARALLEL(table_name n) */
If both the global degree of parallelism and the table-level degree of parallelism are specified, the table-level degree of parallelism does not take effect. Here is an example:
obclient> CREATE TABLE tbl1 (col1 INT) PARTITION BY HASH(col1) ;
Query OK, 0 rows affected
obclient> EXPLAIN SELECT /*+ PARALLEL(3) PARALLEL(tbl1 5) */ * FROM tbl1;
+----------------------------------------------------------------------+
| Query Plan |
+----------------------------------------------------------------------+
| ========================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |1 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |1 | |
| |2 | └─PX BLOCK ITERATOR| |1 |1 | |
| |3 | └─TABLE FULL SCAN|tbl1 |1 |1 | |
| ========================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(tbl1.col1)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(tbl1.col1)]), filter(nil), rowset=16 |
| dop=5 |
| 2 - output([tbl1.col1]), filter(nil), rowset=16 |
| 3 - output([tbl1.col1]), filter(nil), rowset=16 |
| access([tbl1.col1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([tbl1.__pk_increment]), range(MIN ; MAX)always true |
+----------------------------------------------------------------------+
18 rows in set
PUSH_SUBQ Hint
The PUSH_SUBQ hint instructs the optimizer to execute subqueries that have not been rewritten as joins as early as possible. Typically, these subqueries are executed after all table joins in the execution plan. If a subquery has a low computational cost and can filter a large amount of data, executing it early may improve plan performance. Note that this hint is invalid if the subquery has been rewritten as a join (/+unnest/). The hint is applicable to the following scenarios:
- The subquery has a low execution cost but can quickly filter out a large amount of data.
- The result of the subquery needs to be used as early as possible to narrow down the data range.
Syntax:
/*+ PUSH_SUBQ[(@qb_name)] */
Parameters:
@qb_name: Optional. Specifies an alias for the subquery (for more information, see the QB_NAME parameter section above). This helps clarify the target of the hint. If omitted, the hint applies to the subquery where it is placed.
Examples:
Use the PUSH_SUBQ hint in a query to instruct the optimizer to execute the subquery early to filter data in the tbl1 table.
SELECT /*+ PUSH_SUBQ(@"SEL$2") */ *
FROM tbl1, tbl2
WHERE tbl1.col1 = (SELECT MAX(tbl3.col1)
FROM tbl3
WHERE tbl3.col2 = tbl2.col2);
NO_PUSH_SUBQ Hint
The NO_PUSH_SUBQ hint is the opposite of PUSH_SUBQ. It instructs the optimizer to execute subqueries that have not been rewritten as joins at the end. It is applicable when the subquery has a high cost or cannot significantly reduce the number of rows. The hint is applicable to the following scenarios:
- The subquery has a high execution cost, or its result has a minimal impact on the data volume.
- The subquery needs to be executed after other filtering conditions take effect to reduce the input data volume.
Syntax:
/*+ NO_PUSH_SUBQ[(@qb_name)] */
Parameters:
@qb_name: An optional parameter that specifies the alias of the subquery.
Examples:
Use the NO_PUSH_SUBQ hint in a query to instruct the optimizer to execute the subquery at the end.
SELECT /*+ NO_PUSH_SUBQ */ *
FROM tbl1, tbl2
WHERE tbl1.col1 = (SELECT MAX(tbl3.col1)
FROM tbl3
WHERE tbl3.col2 = tbl2.col2);
OPT_PARAM Hint
The OPT_PARAM hint specifies optimizer-related configurations or system variables at the query level.
Syntax:
/*+ OPT_PARAM(parameter_name [,] parameter_value) */
Parameters:
parameter_name: The name of the configuration item or system variable.parameter_value: The value of the variable.
The OPT_PARAM hint applies to the following parameters:
rowsets_enabled: Specifies whether to enable vectorization. Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Enclose the value in single quotation marks ('').rowsets_max_rows: Sets the number of rows to be returned in batches (batch_size). Data type:INT. Value range:[0, 65535]. Do not enclose the value in single quotation marks ('').enable_newsort: Specifies whether to enable the newsort optimization in queries. Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Enclose the value in single quotation marks ('').use_part_sort_mgb: Specifies whether to enable part sort merge group by in queries. Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Enclose the value in single quotation marks ('').enable_in_range_optimization: Specifies whether to enable the in optimization in queries. Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Enclose the value in single quotation marks ('').xsolapi_generate_with_clause: Specifies whether to enable CTE extraction rewrite in queries. Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Enclose the value in single quotation marks ('').preserve_order_for_pagination: Specifies whether to add anorder byclause to preserve order in pagination queries or to prohibit adding one. Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Enclose the value in single quotation marks ('').storage_card_estimation: Specifies whether to use storage-layer row estimation. Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Enclose the value in single quotation marks ('').workarea_size_policy: Specifies the policy for manually or automatically adjusting the SQL workarea size. Data type:VARCHAR. Valid values are'MANUAL'for manual adjustment and'AUTO'for automatic adjustment. Enclose the value in single quotation marks ('').enable_rich_vector_format: Specifies whether to enable vectorization 2.0 (a session-level parameter). Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Enclose the value in single quotation marks ('').spill_compression_codec: Specifies the compression algorithm used by operators that require temporary materialization. Data type:VARCHAR. Valid values areNONE,LZ4,SNAPPY,ZLIB, andZSTD, representing different compression algorithms. The default value isNONE, which indicates that no compression is performed.inlist_rewrite_threshold: The threshold number of constants that triggers the rewrite ofinlistintovalues statement. Data type:INT64. Value range:[1, 2147483647].orc_filter_pushdown_level: Specifies the pushdown level for statement-level ORC external table filter conditions. Valid values forparameter_valueare as follows:Note
The
parameter_nameparameter in theOPT_PARAMhint of OceanBase AI Database supports theorc_filter_pushdown_levelvalue.0: Disables filter condition pushdown.1: Pushes down to the file level.2: Pushes down to the stripe level.3: Pushes down to the row index level.4: Pushes down to the encoding level.
parquet_filter_pushdown_level: Specifies the pushdown level for statement-level Parquet external table filter conditions. Valid values forparameter_valueare as follows:Note
The
parameter_nameparameter in theOPT_PARAMhint of OceanBase AI Database supports theparquet_filter_pushdown_levelvalue.0: Disables filter condition pushdown.1: Pushes down to the file level.2: Pushes down to the RowGroup level.3: Pushes down to the page level.4: Pushes down to the encoding level.
Examples:
Control the level to which ORC external table filter conditions are pushed down.
Disable ORC filter condition pushdown.
SELECT /*+opt_param('orc_filter_pushdown_level', '0') */ * FROM ext_orc_tbl WHERE c1 = 1;Push down ORC filter conditions to the file level.
SELECT /*+opt_param('orc_filter_pushdown_level', '1') */ * FROM ext_orc_tbl WHERE c1 = 1;Push down ORC filter conditions to the stripe level.
SELECT /*+opt_param('orc_filter_pushdown_level', '2') */ * FROM ext_orc_tbl WHERE c1 = 1;Push down ORC filter conditions to the row index level.
SELECT /*+opt_param('orc_filter_pushdown_level', '3') */ * FROM ext_orc_tbl WHERE c1 = 1;Push down ORC filter conditions to the encoding level.
SELECT /*+opt_param('orc_filter_pushdown_level', '4') */ * FROM ext_orc_tbl WHERE c1 = 1;
Control the level to which Parquet external table filter conditions are pushed down.
Disable Parquet filter condition pushdown.
SELECT /*+opt_param('parquet_filter_pushdown_level', '0') */ * FROM ext_parquet_tbl WHERE c1 = 1;Push down Parquet filter conditions to the file level.
SELECT /*+opt_param('parquet_filter_pushdown_level', '1') */ * FROM ext_parquet_tbl WHERE c1 = 1;Push down Parquet filter conditions to the RowGroup level.
SELECT /*+opt_param('parquet_filter_pushdown_level', '2') */ * FROM ext_parquet_tbl WHERE c1 = 1;Push down Parquet filter conditions to the page level.
SELECT /*+opt_param('parquet_filter_pushdown_level', '3') */ * FROM ext_parquet_tbl WHERE c1 = 1;Push down Parquet filter conditions to the encoding level.
SELECT /*+opt_param('parquet_filter_pushdown_level', '4') */ * FROM ext_parquet_tbl WHERE c1 = 1;
INDEX_MERGE Hint
The INDEX_MERGE hint controls whether an index merge plan is used for the specified table. If the specified index cannot generate an index merge plan, the hint is ignored. If no index is specified, the system tries to generate an index merge plan as much as possible.
You can use the INDEX_MERGE hint to generate an index merge plan even when the _enable_index_merge configuration item is set to False.
Syntax:
/*+ INDEX_MERGE([@query_block_name] tbl_name [index_name [,index_name]...]) */
Parameters:
@query_block_name: Optional. Specifies the name of the query block to which the hint applies. If omitted, the hint applies to the main query block of the current query.tbl_name: Specifies the table name.index_name: Optional. Specifies the index name. You can specify multiple index names.- When the specified table is not followed by the
index_nameparameter, the specified table uses an index merge plan. - When the specified table is followed by the
index_nameparameter, the specified index is used to generate an index merge plan for the table.
- When the specified table is not followed by the
Examples:
Specify index names in the
INDEX_MERGEhint.obclient> SELECT /*+ INDEX_MERGE(t1 idx_c2 idx_c3) */ * FROM t1 WHERE c2 = 1 AND c3 = 1 AND c4 = 1;Do not specify an index name in the
INDEX_MERGEhint.obclient> SELECT /*+ INDEX_MERGE(t1)*/ * FROM t1 WHERE c2 = 1 AND c3 = 1 AND c4 = 1;
NO_INDEX_MERGE Hint
The NO_INDEX_MERGE hint controls whether an index merge plan is generated by using the specified index for the specified table. If no index is specified, the table is not allowed to generate an index merge plan.
If the NO_INDEX_MERGE hint conflicts with the INDEX_MERGE hint, both hints are ignored.
Syntax:
/*+ NO_INDEX_MERGE([@query_block_name] tbl_name [index_name [,index_name]...]) */
Parameters:
@query_block_name: Optional. Specifies the name of the query block to which the hint applies. If omitted, the hint applies to the main query block of the current query.tbl_name: Specifies the table name.index_name: Optional. Specifies the index name. You can specify multiple index names.- When the specified table is not followed by the
index_nameparameter, an index merge plan is not used. - When the specified table is followed by the
index_nameparameter, the specified index is not used to generate an index merge plan.
- When the specified table is not followed by the
Examples:
obclient> SELECT /*+ NO_INDEX_MERGE(t1)*/ * FROM t1
WHERE c2 = 1
AND c3 = 1
AND c4 = 1;
CACHE Hint
The CACHE hint manually enables the cache for the specified table and its indexes. The cache is always enabled during queries on this table.
Note
If no hint is specified, the cache is controlled using an adaptive strategy.
Syntax:
/*+ CACHE ([@qb_name] table_name) */
Parameters:
@qb_name: Optional. Specifies the name of the query block to which the hint applies. If omitted, the hint applies to the main query block of the current query.table_name: Specifies the table name.
Examples:
Manually enable the cache for
tbl1.obclient> SELECT /*+ CACHE(tbl1) */ * FROM tbl1;Manually enable the cache for
tbl1, andtbl2remains in the automatically enabled default state.obclient> SELECT /*+ CACHE(tbl1) */ * FROM tbl1, tbl2 WHERE tbl1.col1 = tbl2.col1;
NOCACHE Hint
The NOCACHE hint manually disables the cache for the specified table and its indexes. The cache is always disabled during queries on this table.
Syntax:
/*+ NOCACHE ([@qb_name] table_name) */
Parameters:
@qb_name: Optional. Specifies the name of the query block to which the hint applies. If omitted, the hint applies to the main query block of the current query.table_name: Specifies the table name.
Examples:
Manually disable the cache for tbl1.
obclient> SELECT /*+ NOCACHE(tbl1) */ * FROM tbl1;
When multiple CACHE or NOCACHE hints are specified, they are merged. If a CACHE hint conflicts with a NOCACHE hint, that is, they specify the same table, CACHE takes precedence, which means the cache is enabled for the table.
Examples:
The CACHE hint takes effect, manually enabling the cache for tbl1.
obclient> SELECT /*+ CACHE(tbl1) NOCACHE(tbl1) */ * FROM tbl1;