You can use hints to make the optimizer generate a specified execution plan.
Generally, the optimizer will select the optimal execution plan for a query and you do not need to use a hint to specify an execution plan. However, in some scenarios, the execution plan generated by the optimizer may not meet your requirements. In this case, you need to use a hint to specify an execution plan to be generated.
Hint syntax
A hint is a special SQL comment in terms of syntax, because a plus sign (+) is added to the opening tag (/*) of the comment. As hints are comments, the optimizer ignores hints and uses the default plan, if the server does not recognize hints in the SQL statement. In addition, hints only affect the logical structure of the plan generated by the optimizer. The semantics of the SQL statement remains unaffected.
{ DELETE | INSERT | SELECT | UPDATE | REPLACE } /*+ [hint_text][,hint_text]... */
Notice
If you want to execute SQL statements containing hints in a MySQL client, you must log in to the client by using the -c option. Otherwise, the MySQL client will remove the hints from the SQL statements as comments, and the system cannot receive the hints.
Note
- The syntax of the
QB_NAMEparameter is@NAME. - The syntax of the
TBL_NAMEparameter is[db_name.]relation_name [qb_name].
QB_NAME parameter
In a DML statement, every query_block has a QB_NAME (query block name), which can be specified by the user or automatically generated by the system. If you do not specify a QB_NAME with a hint, the system generates one for you in the order of SEL$1, SEL$2, UPD$1, and DEL$1 from left to right (which is also the order parsed by the resolver).
You can use the QB_NAME parameter to uniquely identify each table or to specify the behavior of any query block. The QB_NAME in the TBL_NAME parameter is used to identify a table, and the first QB_NAME in a hint is used to identify the query block to which the hint applies.
As the following example shows, the system selects the t1_c1 path for the t1 table in SEL$1 and the primary (Primary) access path for the t2 table in SEL$2 by default.
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 uses a hint to specify that the t1 table in SEL$1 accesses the primary table and the t2 table in SEL$2 accesses the index, an 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 the INDEX(t1 PRIMARY) hint already exists in SEL$1, you do not need to specify the query block to which the hint applies.
The preceding SQL statement can also be written as follows:
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, all its information can be viewed through the Outline Data in 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
Rules for using hints
The general rules for using hints are as follows:
Hints that do not specify a query block are applied to the current query block.
Example 1: Since the
t2table is in query block 2 and cannot be promoted to query block 1 through rewriting, the hint will 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 integrate the subquery with the outer query into a single query block, then 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
For the above example, if the optimizer, due to some reasons (such as internal operations within the subquery preventing promotion and merging), cannot merge the subquery (SELECT * FROM t2 WHERE c2 = 1) into the outer query, then the t2 table is still considered part of the subquery. As a result, the hint in the outer query will be invalid.
In the following example, since both the outer query and the subquery reference the same table t1 and both attempt to use the PRIMARY index, this may cause the optimizer to encounter conflicts when interpreting and processing the hint, potentially rendering 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 the join method hint cannot be found, that table is ignored, but the other specified tables will still take effect. If the optimizer cannot generate the specified join method, it will choose another method, rendering the hint ineffective.
If a table cannot be found in the join order hint, the entire hint becomes invalid.
Common hints and syntax
Compared with other databases, OceanBase Database's optimizer uses dynamic programming and has already considered all possible optimal paths. The primary role of hints is to specify the behavior of the optimizer and ensure that SQL queries are executed according to the hints.
INDEX hint
The syntax of the INDEX hint is supported in both MySQL mode and Oracle mode.
- The syntax of the
INDEXhint in Oracle mode is as follows:
SELECT/*+INDEX(table_name index_name) */ * FROM table_name;
- The syntax of the
INDEXhint in MySQL mode 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] ...
In the syntax of Oracle mode, only one INDEX can be specified for a table, while in the MySQL mode, multiple indexes can be specified. However, in OceanBase Database's MySQL mode, although multiple INDEX hints can be specified, for the USE and FORCE methods, only the first INDEX is used to generate the PATH. Even if there is no filter for that INDEX in the SQL statement, leading to a full scan and table lookup, the current design of OceanBase Database assumes that the person writing the hint knows better which path is optimal. The IGNORE type will ignore all specified INDEX hints. The USE and FORCE methods function the same way as Oracle's hint mechanism. If the specified INDEX does not exist or is in an invalid state, the hint will be invalid. For the IGNORE method, if all INDEX hints, including the primary table, are ignored, the hint will also be invalid.
In SQL statements, if the table name has an alias, such as table_name [AS] alias, the alias must be used for the INDEX hint to take effect. An example is as follows:
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
As shown in the preceding example, the filter condition c1 = 1 is more effective than c2 = 1. If the INDEX hint is ineffective, the optimizer chooses the index IDX1 based on the index selection mechanism.
FULL hint
The syntax for the FULL hint is used to specify a full table scan for a table. The syntax is as follows:
/*+ FULL(table_name) */
The FULL hint is used to select a full table scan for the specified table, equivalent to the INDEX hint /*+ INDEX(table_name PRIMARY) */.
ORDERED hint
The ORDERED hint can specify the join order according to the sequence of tables following the FROM clause. The syntax is as follows:
/*+ ORDERED */
If SQL rewriting occurs after specifying this hint, the join will follow the order of the from items in the rewritten stmt, as subqueries will be replaced with new table items in their corresponding positions during the rewrite.
LEADING hint
The LEADING hint can specify the join order of tables. The syntax is as follows:
/*+ LEADING(table_name_list) */
In table_name_list, () can be used to indicate the join priority among tables, allowing for the specification of complex joins. An example is as follows:
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 that the join is performed in the order specified by the user, the LEADING hint is checked quite strictly. If the table_name specified in the hint does not exist, the LEADING hint becomes invalid. If duplicate tables are found in the hint, the LEADING hint also becomes invalid. During the optimizer's join process, if the table_id cannot be matched to a corresponding table in the From Items, rewriting may occur, causing the specified JOIN order for that table and the tables following it to become invalid. However, the JOIN order for the tables preceding it remains valid.
USE_MERGE hint
The USE_MERGE hint specifies to use the merge join algorithm to join tables, and treats the specified tables as right-side tables. The syntax is as follows:
/*+ USE_MERGE(table_name_list) */
Notice
In OceanBase Database, a merge join requires an equality join condition. When you join two tables without an equality join condition, the USE_MERGE hint is invalid.
Regarding whether the merge join algorithm considers A Merge Join B equivalent to B Merge Join A, there is currently no definitive conclusion. According to the cost model, the cost of a Merge Join is calculated by distinguishing between the left and right tables, which also increases the flexibility of hints. Therefore, the current implementation of Merge Join differentiates between the left and right tables, meaning that use_merge is only effective when the table is used as the right table.
USE_NL hint
The syntax of a hint that uses a join algorithm is join_hint_name ( @ qb_name table_name_list) When the right-side table in the join matches table_name_list, the optimizer generates a plan based on the hint semantics. Generally, you need to use a LEADING hint to specify the join order to make sure that the table in table_name_list is the right-side table. Otherwise, the hint becomes invalid as the join order changes.
table_name_list supports the following forms:
- USE_NL(t1): uses the nested loop join algorithm when the table
t1is the right-side table. - USE_NL(t1 t2 ... ): uses the nested loop join algorithm when the table
t1,t2, or any other one in the list is the right-side table. - USE_NL((t1 t2)): uses the nested loop join algorithm when the join result of tables
t1andt2is the right-side table. The join order and method oft1andt2are ignored. - USE_NL(t1 (t2 t3) (t4 t5 t6) ... ): uses the nested loop join algorithm when the table
t1, join result of tablest2andt3, join result of tablest4,t5, andt6, or any other item in the list is the right-side table.
The USE_NL hint specifies to use the nested loop join algorithm for a join when the specified table is the right-side table. The syntax is as follows:
/*+ USE_NL(table_name_list) */
Here is an example:
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 to use the hash join algorithm for a join when the specified table is the right-side table. The syntax is as follows:
/*+ USE_HASH(table_name_list) */
Here is an example:
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
The PARALLEL hint specifies the degree of parallelism at the statement level. The syntax is as follows:
/*+ PARALLEL(n) */
In the syntax, n is an integer that specifies the degree of parallelism.
OceanBase Database also supports the PARALLEL hint at the table level. The syntax is as follows:
/*+ PARALLEL(table_name n) */
If both the global and table-level PARALLEL hints are specified, the table-level hint will be ignored. 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
UNION_MERGE hint
Note
For OceanBase Database V4.3.5, support for the UNION_MERGE hint was introduced starting from V4.3.5 BP1.
The UNION_MERGE hint is used to specify the execution method of using index merge in the query plan. When a valid UNION_MERGE hint is provided, OceanBase Database will directly select the corresponding Index Merge plan.
The syntax is as follows:
/*+ UNION_MERGE(table_name index_name_list) */
Parameter explanation:
table_name: Specifies the name of the table.index_name_list: Specifies a list of index names, with multiple index names separated by spaces.
Here is an example:
Create a table named
tbl1that contains full-text indexes.CREATE TABLE tbl1 ( col1 INT PRIMARY KEY, col2 INT, col3 VARCHAR(100), col4 VARCHAR(100), FULLTEXT INDEX ftidx3(col3), FULLTEXT INDEX ftidx4(col4) );Use the
UNION_MERGEhint to query the execution plan. Add the hint to the query statement to specify to use index merge for the full-text indexesftidx3andftidx4of thetbl1table.EXPLAIN SELECT /*+UNION_MERGE(tbl1 ftidx3 ftidx4)*/ * FROM tbl1 WHERE col1 = 1 AND (MATCH(col3) AGAINST ("word1") OR MATCH(col4) AGAINST ("word1"));The return result is as follows:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =========================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | --------------------------------------------------------------------------- | | |0 |DISTRIBUTED INDEX MERGE SCAN|tbl1(ftidx3,ftidx4)|1 |45 | | | =========================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([tbl1.col1], [tbl1.col2], [tbl1.col3], [tbl1.col4]), filter([MATCH(tbl1.col3) AGAINST('word1') OR MATCH(tbl1.col4) AGAINST('word1')], [tbl1.col1 | | = 1]), rowset=16 | | access([tbl1.col1], [tbl1.col3], [tbl1.col4], [tbl1.col2]), partitions(p0) | | is_index_back=true, is_global_index=false, keep_ordering=true, use_index_merge=true, filter_before_indexback[false,false], | | index_name: ftidx3, range_cond(nil), filter(nil) | | index_name: ftidx4, range_cond(nil), filter(nil) | | lookup_filter([tbl1.col1 = 1], [MATCH(tbl1.col3) AGAINST('word1') OR MATCH(tbl1.col4) AGAINST('word1')]) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 14 rows in setIn the query result:
DISTRIBUTED INDEX MERGE SCAN: indicates that the index merge scan method is used in a distributed environment.is_index_back=true: indicates that a table access by index key operation is enabled.use_index_merge=true: indicates that the index merge method is enabled.filter_before_indexback[false,false]: indicates that the filtering condition is not applied before the table access by index key operation.
PUSH_SUBQ hint
Note
For OceanBase Database V4.3.5, support for the PUSH_SUBQ hint was introduced starting from V4.3.5 BP2.
The PUSH_SUBQ hint is used to instruct the optimizer to execute subqueries that have not been rewritten into joins as early as possible. Typically, these subqueries, if not rewritten into joins, are executed after all table joins in the execution plan. If the subquery has a low computation cost and can filter out a large amount of data, executing it earlier may improve the performance of the plan. However, note that if the subquery is rewritten into a join (via /*+ unnest */), this hint becomes invalid. Applicable scenarios include:
- The subquery's execution cost is low but can quickly filter out a large amount of data.
- The results of the subquery need to be utilized early to narrow down the data range.
Syntax:
/*+ PUSH_SUBQ[(@qb_name)] */
Parameter explanation:
@qb_name: An optional parameter that specifies the alias of the subquery (for more details, refer to the QB_NAME Parameter section above). This is used to explicitly indicate the target of the hint. If omitted, the hint applies to the subquery where it is located by default.
Example:
In the following query, the PUSH_SUBQ hint is used to instruct the optimizer to execute the subquery early, allowing it to filter data from the tbl1 table in advance:
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
Note
For OceanBase Database V4.3.5, support for the NO_PUSH_SUBQ hint was introduced starting from V4.3.5 BP2.
The NO_PUSH_SUBQ hint is the opposite of the PUSH_SUBQ hint. It instructs the optimizer to execute subqueries that have not been rewritten into joins at the end of the execution plan. This is suitable for scenarios where subquery execution is costly or does not significantly reduce the number of rows. Applicable scenarios include:
- The execution cost of the subquery is high, or its results have little impact on reducing the data volume.
- The subquery should be executed after other filtering conditions have been applied to reduce its input data size.
Syntax:
/*+ NO_PUSH_SUBQ[(@qb_name)] */
Parameter explanation:
@qb_name: An optional parameter that specifies the alias of the subquery.
Example:
In the following query, the NO_PUSH_SUBQ hint is used to instruct the optimizer to delay the execution of the subquery until the end:
SELECT /*+ NO_PUSH_SUBQ */ *
FROM tbl1, tbl2
WHERE tbl1.col1 = (SELECT MAX(tbl3.col1)
FROM tbl3
WHERE tbl3.col2 = tbl2.col2);
References
For more information about hints, see Overview of hints.