Optimizer hints

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]... */

Hint parameters

The following table describes the name, syntax, and description of the hint parameters.

QB_NAME

In DML statements, each query block has a query block name indicated by QB_NAME, which can be specified by the user or automatically generated by the system. If you do not use a hint to specify QB_NAME, the system generates the names of SEL$1, SEL$2, UPD$1, and DEL$1 from left to right, which is the operation order of the resolver.

You can use QB_NAME to accurately locate every table and specify the behavior of any query block at one position. QB_NAME in TBL_NAME is used to locate the table, and the first QB_NAME in the hint is used to locate the query_block to which the hint applies.

In the following example, according to the default rules, the t1_c1 path is selected for the t1 table in SEL$1, and primary table access is selected for the t2 table in SEL$2.

obclient> CREATE TABLE t1(c1 INT, c2 INT, KEY t1_c1(c1));
Query OK, 0 rows affected

obclient> CREATE TABLE t2(c1 INT, c2 INT, KEY t2_c1(c1));
Query OK, 0 rows affected

obclient> EXPLAIN SELECT * FROM t1, (SELECT * FROM t2 WHERE c2 = 1 LIMIT 5)
 WHERE t1.c1 = 1;

Query Plan:
=========================================================
|ID|OPERATOR                  |NAME     |EST. ROWS|COST |
---------------------------------------------------------
|0 |NESTED-LOOP JOIN CARTESIAN|         |4950     |28686|
|1 | TABLE SCAN               |t1(t1_c1)|990      |26669|
|2 | MATERIAL                 |         |5        |207  |
|3 |  SUBPLAN SCAN            |         |5        |207  |
|4 |   TABLE SCAN             |t2       |5        |207  |
=========================================================

Outputs & filters:
-------------------------------------
  0 - output([t1.c1], [t1.c2], [.c1], [.c2]), filter(nil),
      conds(nil), nl_params_(nil)
  1 - output([t1.c1], [t1.c2]), filter(nil),
      access([t1.c1], [t1.c2]), partitions(p0)
  2 - output([.c1], [.c2]), filter(nil)
  3 - output([.c1], [.c2]), filter(nil),
      access([.c1], [.c2])
  4 - output([t2.c1], [t2.c2]), filter([t2.c2 = 1]),
      access([t2.c2], [t2.c1]), partitions(p0),
      limit(5), offset(nil)

The following example uses a hint in an SQL statement to specify the access method of the t1 table in SEL$1 to primary table access, and that of the t2 table in SEL$2 to index access.

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|COST |
---------------------------------------------------------
|0 |NESTED-LOOP JOIN CARTESIAN|         |4950     |60030|
|1 | TABLE SCAN               |t1       |990      |40790|
|2 | MATERIAL                 |         |5        |17429|
|3 |  SUBPLAN SCAN            |         |5        |17429|
|4 |   TABLE SCAN             |t2(t2_c1)|5        |17429|
=========================================================

Outputs & filters:
-------------------------------------
  0 - output([t1.c1], [t1.c2], [.c1], [.c2]), filter(nil),
      conds(nil), nl_params_(nil)
  1 - output([t1.c1], [t1.c2]), filter([t1.c1 = 1]),
      access([t1.c1], [t1.c2]), partitions(p0)
  2 - output([.c1], [.c2]), filter(nil)
  3 - output([.c1], [.c2]), filter(nil),
      access([.c1], [.c2])
  4 - output([t2.c1], [t2.c2]), filter([t2.c2 = 1]),
      access([t2.c2], [t2.c1]), partitions(p0),
      limit(5), offset(nil)

In this example, 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;

<==>

SELECT /*+INDEX(t1 PRIMARY)*/ * FROM t1 , (SELECT /*+INDEX(t2 t2_c1)*/ * FROM t2 WHERE c2 = 1 LIMIT 5) WHERE t1.c1 = 1;

<==>

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 the information about this hint in the Outline Data field of the execution result of the EXPLAIN EXTENDED statement.

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|COST |
---------------------------------------------------------
|0 |NESTED-LOOP JOIN CARTESIAN|         |4950     |28686|
|1 | TABLE SCAN               |t1(t1_c1)|990      |26669|
|2 | MATERIAL                 |         |5        |207  |
|3 |  SUBPLAN SCAN            |         |5        |207  |
|4 |   TABLE SCAN             |t2       |5        |207  |
=========================================================
Used Hint:
-------------------------------------
  /*+
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$1" ("test.t1"@"SEL$1" ""@"SEL$1" ))
      USE_NL(@"SEL$1" (""@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" (""@"SEL$1" ) LOCAL LOCAL)
      USE_NL_MATERIALIZATION(@"SEL$1" (""@"SEL$1" ))
      INDEX(@"SEL$1" "test.t1"@"SEL$1" "t1_c1")
      FULL(@"SEL$2" "test.t2"@"SEL$2")
      END_OUTLINE_DATA
  */

Usage rules of hints

Observe the following rules when you use hints:

• A hint applies to the query block where it resides, if no query block is specified.

  Example 1: The hint cannot take effect because t2 resides in query block 2 and cannot be relocated to query block 1 through rewriting.

  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|COST |
  ---------------------------------------------------------
  |0 |NESTED-LOOP JOIN CARTESIAN|         |4950     |28686|
  |1 | TABLE SCAN               |t1(t1_c1)|990      |26669|
  |2 | MATERIAL                 |         |5        |207  |
  |3 |  SUBPLAN SCAN            |         |5        |207  |
  |4 |   TABLE SCAN             |t2       |5        |207  |
  =========================================================
  
  
  Outputs & filters:
  -------------------------------------
    0 - output([t1.c1], [t1.c2], [.c1], [.c2]), filter(nil),
        conds(nil), nl_params_(nil)
    1 - output([t1.c1], [t1.c2]), filter(nil),
        access([t1.c1], [t1.c2]), partitions(p0)
    2 - output([.c1], [.c2]), filter(nil)
    3 - output([.c1], [.c2]), filter(nil),
        access([.c1], [.c2])
    4 - output([t2.c1], [t2.c2]), filter([t2.c2 = 1]),
        access([t2.c2], [t2.c1]), partitions(p0),
        limit(5), offset(nil)
  

  Example 2: The hint takes effect because the SQL statement is rewritten and table t1 is relocated to SEL$1.

  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|COST  |
  ----------------------------------------------------------
  |0 |NESTED-LOOP JOIN CARTESIAN|         |980100   |772448|
  |1 | TABLE SCAN               |t1(t1_c1)|990      |26669 |
  |2 | MATERIAL                 |         |990      |387237|
  |3 |  TABLE SCAN              |t2(t2_c1)|990      |387227|
  ==========================================================
  
  
  Outputs & filters:
  -------------------------------------
    0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil),
        conds(nil), nl_params_(nil)
    1 - output([t1.c1], [t1.c2]), filter(nil),
        access([t1.c1], [t1.c2]), partitions(p0)
    2 - output([t2.c1], [t2.c2]), filter(nil)
    3 - output([t2.c2], [t2.c1]), filter([t2.c2 = 1]),
        access([t2.c2], [t2.c1]), partitions(p0)
  

• If a table is specified but is not found in the query block where the hint resides, or a conflict occurs, the hint is invalid.

  If the table is not found, see Example 1 in the first rule. The following part is an example where two conflicts occur at the same time:

  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|COST  |
  ----------------------------------------------------------
  |0 |NESTED-LOOP JOIN CARTESIAN|         |980100   |411890|
  |1 | TABLE SCAN               |t1(t1_c1)|990      |26669 |
  |2 | MATERIAL                 |         |990      |26679 |
  |3 |  TABLE SCAN              |t1(t1_c1)|990      |26669 |
  ==========================================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([t1.c1], [t1.c2], [t1.c1], [t1.c2]), filter(nil),
        conds(nil), nl_params_(nil)
    1 - output([t1.c1], [t1.c2]), filter(nil),
        access([t1.c1], [t1.c2]), partitions(p0)
    2 - output([t1.c1], [t1.c2]), filter(nil)
    3 - output([t1.c1], [t1.c2]), filter(nil),
        access([t1.c1], [t1.c2]), partitions(p0)
  

• If the table specified in a hint for the join cannot be found, the table is ignored, but other specifications remain effective. If the optimizer cannot generate the specified join method, it selects another method, and the hint is invalid.

• If a table in the join order cannot be found, the hint is invalid.

Common hints

Compared with the behaviors of other databases, the behaviors of the OceanBase Database optimizer are dynamically planned, and all possible optimal paths have been considered. Hints are mainly used to specify the behavior of the optimizer, and SQL queries are executed based on the hints.

INDEX hint

The INDEX hint supports syntaxes in both MySQL and Oracle formats.

• The INDEX hint syntax in Oracle mode is as follows:

SELECT/*+INDEX(table_name index_name) */ * FROM table_name;

• The INDEX hint syntax 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] ...

You can specify only one index for a table in Oracle syntax, but you can specify multiple indexes for a table in MySQL syntax. Although MySQL syntax in OceanBase Database supports specifying multiple indexes for a table, only the first index is used to generate the path when the USE INDEX and FORCE INDEX hints are used, even if the SQL statement does not contain a filter for the index and results in a full scan and table access by index primary key operations. This is because OceanBase Database is designed with an idea that a user who writes a hint knows better than a program about which path is the best. The IGNORE INDEX hint instructs the optimizer to ignore all specified indexes. Essentially, the USE INDEX and FORCE INDEX hints work in the same way as the Oracle hint. That is, the hint does not take effect if the index to use does not exist or is invalid. The IGNORE INDEX hint is invalid if all indexes, including the primary table, are ignored.

The SQL syntax contains table_name [AS] alias. You must specify a table alias for an 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|COST|
---------------------------------------
|0 |TABLE SCAN|T1(IDX1)|1        |5   |
=======================================

Outputs & filters:
-------------------------------------
  0 - output([T1.C1], [T1.C2], [T1.C3]), filter([T1.C2 = 1]), rowset=256,
      access([T1.__pk_increment], [T1.C1], [T1.C2], [T1.C3]), partitions(p0)

1 row in set

-------Index that takes effect
obclient> explain select /*+index(t idx2)*/ * from t1 t where c1 = 1 and c2 = 1;

Query Plan:
======================================
|ID|OPERATOR  |NAME   |EST. ROWS|COST|
--------------------------------------
|0 |TABLE SCAN|T(IDX2)|1        |5   |
======================================

Outputs & filters:
-------------------------------------
  0 - output([T.C1], [T.C2], [T.C3]), filter([T.C1 = 1]), rowset=256,
      access([T.__pk_increment], [T.C1], [T.C2], [T.C3]), partitions(p0)

1 row in set

-------Index that does not take effect
obclient> explain select /*+index(t1 idx2)*/ * from t1 t where c1 = 1 and c2 = 1;

Query Plan:
======================================
|ID|OPERATOR  |NAME   |EST. ROWS|COST|
--------------------------------------
|0 |TABLE SCAN|T(IDX1)|1        |5   |
======================================

Outputs & filters:
-------------------------------------
  0 - output([T.C1], [T.C2], [T.C3]), filter([T.C2 = 1]), rowset=256,
      access([T.__pk_increment], [T.C1], [T.C2], [T.C3]), partitions(p0)

1 row in set

The results in the example show that c1 = 1 achieves better filtering effects than c2 = 1, and the index selection mechanism of the optimizer selects IDX1 when the INDEX hint does not take effect.

FULL hint

The following syntax of the FULL hint specifies to scan the primary table:

/*+ FULL(table_name)*/

This FULL hint is equivalent to the INDEX hint /*+ INDEX(table_name PRIMARY)*/.

ORDERED hint

The ORDERED hint specifies to join tables in the order in which the tables appear in the FROM clause. The syntax is as follows:

 /*+ ORDERED */

If the hint is rewritten, the join is executed in the order of the FROM items in the rewritten stmt. This is because sub_query adds new table items at corresponding positions in the FROM items during rewriting.

LEADING hint

The LEADING hint specifies the order in which tables are joined. The syntax is as follows:

 /*+ LEADING(table_name_list)*/

You can use () in table_name_list to indicate the join priorities of right-side tables to specify a complex join. 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 SCAN               |d   |
|4 |   MATERIAL                 |    |
|5 |    TABLE SCAN              |c   |
|6 |  MATERIAL                  |    |
|7 |   TABLE SCAN               |b   |
|8 | MATERIAL                   |    |
|9 |  TABLE SCAN                |a   |
======================================

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 SCAN                |d   |
|3 |  MATERIAL                  |    |
|4 |   TABLE SCAN               |c   |
|5 | MATERIAL                   |    |
|6 |  NESTED-LOOP JOIN CARTESIAN|    |
|7 |   TABLE SCAN               |b   |
|8 |   MATERIAL                 |    |
|9 |    TABLE SCAN              |a   |
======================================

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 SCAN               |d   |
|4 |   MATERIAL                 |    |
|5 |    TABLE SCAN              |c   |
|6 |  MATERIAL                  |    |
|7 |   TABLE SCAN               |b   |
|8 | MATERIAL                   |    |
|9 |  TABLE SCAN                |a   |
======================================

The LEADING hint is strictly examined to ensure that tables are joined in the order specified by the user. The LEADING hint becomes invalid if table_name specified in the hint does not exist, or duplicate tables are found in the hint. If the optimizer does not find a table in FROM items by table_id during a JOIN operation, the query may have been rewritten. In this case, the join order for this table and tables after this table is invalid. The join order before the table is still 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) */

At present, no conclusion is made about whether A Merge Join B is equivalent to B Merge Join A. Based on the cost model, the left-side table and the right-side table are considered separately during the calculation of the cost of a merge join, which improves the flexibility of a hint. So, the left-side table and the right-side table are discriminated in a merge join. This means that the USE_MERGE hint is valid only for the right-side 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 t1 is 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 t1 and t2 is the right-side table. The join order and method of t1 and t2 are ignored.
• USE_NL(t1 (t2 t3) (t4 t5 t6) ... ): uses the nested loop join algorithm when the table t1, join result of tables t2 and t3, join result of tables t4, t5, and t6, 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 a 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|COST|
-----------------------------------------
|0 |NESTED-LOOP JOIN|    |1        |91  |
|1 | TABLE SCAN     |T0  |1        |46  |
|2 | MATERIAL       |    |1        |46  |
|3 |  TABLE SCAN    |T1  |1        |46  |
=========================================

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|COST|
-----------------------------------------
|0 |NESTED-LOOP JOIN|    |1        |137 |
|1 | TABLE SCAN     |T0  |1        |46  |
|2 | MATERIAL       |    |1        |92  |
|3 |  HASH JOIN     |    |1        |91  |
|4 |   TABLE SCAN   |T1  |1        |46  |
|5 |   TABLE SCAN   |T2  |1        |46  |
=========================================

USE_HASH hint

The USE_HASH hint specifies to use the hash join algorithm for a join by taking the specified table as 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|COST    |
-------------------------------------------
|0 |HASH JOIN  |       |98010000 |58439627|
|1 | TABLE SCAN| t0 |100000   |66272   |
|2 | TABLE SCAN| t1 |100000   |66272   |
===========================================

PARALLEL hint

PARALLEL specifies the degree of parallelism (DOP) of statements. The syntax is as follows:

/*+ PARALLEL(n) */

In the syntax, n is an integer that indicates the DOP.

OceanBase Database also supports table-level PARALLEL hints. The syntax is as follows:

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|COST|
------------------------------------------------
|0 |PX COORDINATOR     |        |1        |16  |
|1 | EXCHANGE OUT DISTR|:EX10000|1        |16  |
|2 |  PX BLOCK ITERATOR|        |1        |16  |
|3 |   TABLE SCAN      |TBL1    |1        |16  |
================================================

Outputs & filters:
-------------------------------------
  0 - output([INTERNAL_FUNCTION(TBL1.COL1)]), filter(nil)
  1 - output([INTERNAL_FUNCTION(TBL1.COL1)]), filter(nil), dop=3
  2 - output([TBL1.COL1]), filter(nil)
  3 - output([TBL1.COL1]), filter(nil),
      access([TBL1.COL1]), partitions(p0)

1 row in set