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, the user 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 C client of MySQL to execute an SQL statement with a hint, you must use the -c option to log in. Otherwise, the MySQL client will remove the hint as a comment from the user's SQL statement, causing the system to not receive the hint.
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 specifying the module-level statement, the first single quotation mark (') is used as the start, and the second single quotation mark (') is used 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 main table, 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 for the specified tables when they are used as right tables. |
| USE_HASH | USE_HASH([qb_name] TBL_NAME_LIST) | Specifies that Hash Join is used for the specified tables when they are used as right tables. |
| NO_USE_HASH | NO_USE_HASH([qb_name] TBL_NAME_LIST) | Specifies that Hash Join is not used for the specified tables when they are used as right tables. |
| USE_NL | USE_NL([qb_name] TBL_NAME_LIST) | Specifies that Nested Loop Join is used for the specified tables when they are used as right tables. |
| USE_BNL | USE_BNL([qb_name] TBL_NAME_LIST) | Specifies that Block Nested Loop Join is used for the specified tables when they are used as right tables. |
| 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 does not use Hash Aggregate, but instead uses Merge Group By or Merge Distinct. |
| 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 SHOW TRACE display. |
| 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 minimum number of rows for fuzzy queries. Here, PRECISION is an integer with a value in the range [0, 100], indicating the percentage of rows for fuzzy queries; and MINIMUM_ROWS specifies the minimum number of rows to return. |
| MAX_CONCURRENT | MAX_CONCURRENT(n) | Specifies the maximum number of concurrent sessions for this SQL statement. |
UNION_MERGE
NoticeFrom OceanBase Database V4.4.1, the |
UNION_MERGE(table_name index_name_list) | Specifies the corresponding index merge plan. |
| PUSH_SUBQ | PUSH_SUBQ[(@qb_name)] | Indicates the optimizer to execute subqueries that are not rewritten as joins as early as possible. |
| NO_PUSH_SUBQ | NO_PUSH_SUBQ[(@qb_name)] | The opposite of PUSH_SUBQ, indicating that the optimizer executes subqueries that are not rewritten as joins at the end. |
| OPT_PARAM | OPT_PARAM(parameter_name [,] parameter_value) | Specifies some optimizer-related configurations or system variables to be updated at the query level. |
| INDEX_MERGE | INDEX_MERGE([@query_block_name] tbl_name [index_name [,index_name]...]) | Controls whether to use the 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 the 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 the query. |
| NOCACHE | NOCACHE ( [@qb_name] table_name) | Manually disables the cache for the specified table and its indexes. The cache is always disabled during the query. |
Note
- The syntax of
QB_NAMEis:@NAME. - The syntax of
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 the QB_NAME using a hint, the system generates them in the order of SEL$1, SEL$2, UPD$1, and DEL$1, from left to right (which is also the parsing order of the resolver).
The QB_NAME can be used to precisely locate each table and to specify the behavior of any query block. In the TBL_NAME, the QB_NAME is used to locate the table, and the QB_NAME at the beginning of the hint is used to specify which query_block the hint applies to.
For example, by default, the t1 table in SEL$1 will use the t1_c1 path, and the t2 table in SEL$2 will use the primary table access.
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 statement specifies that the t1 table in SEL$1 should use the primary table access and the t2 table in SEL$2 should use an index, the statement would look like this:
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, it is not necessary to specify the query block for the hint.
The 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;
You can view all information about this hint by checking the Outline Data section in the 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 reorganize the subquery and the outer query into a single query block, 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 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 INDEX hint is supported in both MySQL and Oracle modes.
- The Oracle syntax of the
INDEXhint is as follows:
SELECT/*+INDEX(table_name index_name) */ * FROM table_name;
- The MySQL syntax of 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] ...
In Oracle mode, only one INDEX can be specified for a table. In MySQL mode, multiple INDEX can be specified. However, in MySQL mode of OceanBase Database, even though multiple INDEX can be specified, only the first INDEX is used to generate the PATH for USE and FORCE methods. This is because OceanBase Database assumes that the writer of the hint is more familiar with the path than the program. If the SQL statement does not include a filter for the specified INDEX, a full table scan and index backtracking will occur. For IGNORE type, all specified INDEX will be ignored. The USE, FORCE, and Oracle hint methods are essentially the same. If the specified INDEX does not exist or is in an invalid state, the hint is ineffective. For IGNORE type, if all INDEX including the main 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 a 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 syntax of the FULL hint is used to specify a full table scan, as shown in the following example:
/*+ 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 specifying this hint, the join order will follow the rewritten stmt from the From Items, as the sub_query will be 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 should be used as the right table when using the Merge Join algorithm. The syntax is as follows:
/*+ USE_MERGE(table_name_list) */
Notice
In OceanBase 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 will be 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 only takes effect 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 will become 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 should be used as the right table when using the Nested Loop Join algorithm. 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 should be used as the right table when using the Hash Join algorithm. 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
The PARALLEL hint specifies the degree of parallelism for the statement. The syntax is as follows:
/*+ PARALLEL(n) */
Here, n is an integer indicating 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 degree of parallelism and the table-level degree of parallelism are specified, the table-level degree of parallelism will 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
UNION_MERGE Hint
Notice
Starting from OceanBase Database V4.4.1, the UNION_MERGE hint is no longer supported.
The UNION_MERGE hint is used to specify the execution method for 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) */
Parameters:
table_name: specifies the name of the table.index_name_list: specifies the list of index names, separated by spaces.
Examples:
Create a table
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 view the execution plan. In the query, use theUNION_MERGEhint to specify the tabletbl1and indexesftidx3andftidx4.EXPLAIN SELECT /*+UNION_MERGE(tbl1 ftidx3 ftidx4)*/ * FROM tbl1 WHERE col1 = 1 AND (MATCH(col3) AGAINST ("word1") OR MATCH(col4) AGAINST ("word1"));The returned 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 setThe returned result indicates the following:
DISTRIBUTED INDEX MERGE SCAN: indicates that the Index Merge scan method is used in a distributed environment.is_index_back=true: indicates that the back-table operation is enabled.use_index_merge=true: indicates that Index Merge is enabled.filter_before_indexback[false,false]: indicates that the filter condition is not applied before the back-table operation.
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 would be 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 the plan's performance. However, if the subquery is rewritten as a join (/+unnest/), this hint is ineffective. The following scenarios are applicable:
- The subquery has a low execution cost but can quickly filter a large amount of data.
- The results of the subquery need to be used early to narrow down the data scope.
Syntax:
/*+ PUSH_SUBQ[(@qb_name)] */
Parameters:
@qb_name: An optional parameter that specifies the alias of the subquery (for more information, see the QB_NAME parameter section above). It 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 following scenarios are applicable:
- 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 a configuration or system variable.parameter_value: the value of the variable. TheOPT_PARAMhint applies to the following parameters:rowsets_enabled: specifies whether to enable vectorization. The data type isVARCHAR, and the value can be'TRUE'or'FALSE'. Enclose the value in single quotation marks (' ').rowsets_max_rows: specifies the number of rows to be returned in batches (batch_size). The data type isINT, and the value ranges from 0 to 65535. Do not enclose the value in single quotation marks (' ').enable_newsort: specifies whether to enable or disable the newsort optimization. The data type isVARCHAR, and the value can be'TRUE'or'FALSE'. Enclose the value in single quotation marks (' ').use_part_sort_mgb: specifies whether to enable or disable the part sort merge group by. The data type isVARCHAR, and the value can be'TRUE'or'FALSE'. Enclose the value in single quotation marks (' ').enable_in_range_optimization: specifies whether to enable or disable the in optimization. The data type isVARCHAR, and the value can be'TRUE'or'FALSE'. Enclose the value in single quotation marks (' ').xsolapi_generate_with_clause: specifies whether to enable or disable the CTE extraction rewrite. The data type isVARCHAR, and the value can be'TRUE'or'FALSE'. Enclose the value in single quotation marks (' ').preserve_order_for_pagination: specifies whether to add anorder byclause to a query for pagination. The data type isVARCHAR, and the value can be'TRUE'or'FALSE'. Enclose the value in single quotation marks (' ').storage_card_estimation: specifies whether to use the storage layer for row estimation. The data type isVARCHAR, and the value can be'TRUE'or'FALSE'. Enclose the value in single quotation marks (' ').workarea_size_policy: specifies the strategy for manually or automatically adjusting the SQL workarea size. The data type isVARCHAR, and the value can be'MANUAL'for manual adjustment or'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). The data type isVARCHAR, and the value can be'TRUE'or'FALSE'. Enclose the value in single quotation marks (' ').spill_compression_codec: specifies the compression algorithm for operators that require temporary materialization. The data type isVARCHAR, and the value can beNONE,LZ4,SNAPPY,ZLIB, orZSTD, representing different compression algorithms. The default value isNONE, indicating no compression.inlist_rewrite_threshold: specifies the maximum number of constants that can trigger the rewrite of aninlistexpression into avalues statement. The data type isINT64, and the value ranges from 1 to 2147483647.orc_filter_pushdown_level: specifies the level to which ORC external table filter conditions are pushed down. Theparameter_valuecan be:Note
Starting from OceanBase Database V4.4.0, the
OPT_PARAMhint supports theorc_filter_pushdown_levelparameter.0: disables filter condition pushdown.1: pushes down filter conditions to the file level.2: pushes down filter conditions to the stripe level.3: pushes down filter conditions to the row index level.4: pushes down filter conditions to the encoding level.
parquet_filter_pushdown_level: specifies the level to which Parquet external table filter conditions are pushed down. Theparameter_valuecan be:Note
Starting from OceanBase Database V4.4.0, the
OPT_PARAMhint supports theparquet_filter_pushdown_levelparameter.0: disables filter condition pushdown.1: pushes down filter conditions to the file level.2: pushes down filter conditions to the RowGroup level.3: pushes down filter conditions to the page level.4: pushes down filter conditions 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 is used to control 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 will try to generate an index merge plan as much as possible.
Using the INDEX_MERGE hint can generate an index merge plan even when the _enable_index_merge configuration 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 (query block) to which the hint applies. If omitted, the default is the main query block of the current query.tbl_name: Specifies the table name.index_name: Optional. Specifies the name of the index. Multiple index names can be specified.- If the
index_nameparameter is not specified after the specified table, it indicates that the system should use an index merge plan for the specified table. - If the
index_nameparameter is specified after the specified table, it indicates that the system should use the specified index to generate an index merge plan.
- If the
Examples:
Specify the index name 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 the 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 is used to control whether an index merge plan is generated using the specified index for the specified table. If no index is specified, the system will not generate an index merge plan for the table.
If there is a conflict between the NO_INDEX_MERGE hint and the INDEX_MERGE hint, both hints will be 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 (query block) to which the hint applies. If omitted, the default is the main query block of the current query.tbl_name: Specifies the table name.index_name: Optional. Specifies the name of the index. Multiple index names can be specified.- If the
index_nameparameter is not specified after the specified table, it indicates that the system should not use an index merge plan. - If the
index_nameparameter is specified after the specified table, it indicates that the system should not use the specified index to generate an index merge plan.
- If the
Examples:
obclient> SELECT /*+ NO_INDEX_MERGE(t1)*/ * FROM t1
WHERE c2 = 1
AND c3 = 1
AND c4 = 1;
CACHE Hint
Note
This hint was introduced in V4.6.0.
The CACHE hint is used to manually enable the cache for the specified table and its indexes. The cache is enabled during the query process for this table.
Note
If no hint is specified, the cache is controlled based on the adaptive strategy.
Syntax:
/*+ CACHE ([@qb_name] table_name) */
Parameters:
@qb_name: Optional. Specifies the name of the query block (query block) to which the hint applies. If omitted, the default is 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, andtbl2is in the default state.obclient> SELECT /*+ CACHE(tbl1) */ * FROM tbl1, tbl2 WHERE tbl1.col1 = tbl2.col1;
NOCACHE Hint
Note
This hint was introduced in V4.6.0.
The NOCACHE hint is used to manually disable the cache for the specified table and its indexes. The cache is disabled during the query process for this table.
Syntax:
/*+ CACHE ([@qb_name] table_name) */
Parameters:
@qb_name: Optional. Specifies the name of the query block (query block) to which the hint applies. If omitted, the default is 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/NOCACHE hints are specified, all hints will be merged. If there is a conflict between CACHE and NOCACHE, that is, they specify the same table, CACHE will take precedence, meaning the cache for this table will be enabled.
CACHE takes effect and manually enables the cache for tbl1.
obclient> SELECT /*+ CACHE(tbl1) NOCACHE(tbl1) */ * FROM tbl1;