Use SQL hints in queries|V4.1.0| docs|Distributed Database

Use SQL hints in queries

Last Updated：2023-08-01 06:02:28 Updated

When you use OceanBase Database for the first time, you may use large tables to test queries. You can use SQL hints in SQL queries to control parameter settings. For example, you can specify the consistency level and SQL query timeout period.

Overview of SQL hints

Execution plans involve the table join method, query conditions, and table indexes and affect the SQL statement execution performance of OceanBase Database. This is the internal logic of the SQL engine of OceanBase Database. You can add hint comments to SQL statements to change execution plans and thereby improve the SQL statement execution performance.

A statement can contain only one hint comment, and this comment must follow the SELECT, UPDATE, INSERT, REPLACE, or DELETE keyword.

Syntax for hints in statement comments:

{DELETE|INSERT|SELECT|UPDATE|REPLACE} /*+ [hint_text]... */

General SQL hints include:

READ_CONSISTENCY hint: specifies the consistency level for reading tables. If WEAK is specified, the related SQL statement reads tables in weak mode. If STRONG is specified, the related SQL statement reads tables in strong mode.

Note

READ_CONSISTENCY hint: specifies the consistency level for the current query. For complete read/write splitting, routing and other parameters must be specified. The read/write splitting feature of OceanBase Database depends on the logical data center (LDC) feature of OceanBase Database Proxy (ODP). If you want to use the read/write splitting feature, we recommend that you configure the LDC first. For more information about read/write splitting, see Read/write splitting.
INDEX hint: instructs the optimizer to use an index scan for the specified table.
QUERY_TIMEOUT hint: instructs the server to set the execution timeout period for an SQL statement, in us.

For more information about other general SQL hints, see Overview of comments.

Examples

SQL hints are frequently used with SQL statements and are not limited to use with SQL queries. The following code shows the syntax for an SQL query that uses hints:

SELECT /*+ hint_text [, hint_text] */ select_items FROM table_name;

Note

You can use multiple SQL hints in a statement. Make sure that the features do not conflict with each other.
By default, in the CLI environment of obclient, the comment syntax is ignored, and SQL hints do not work. In this case, you must add the -c parameter when you start obclient.

Example 1: Set the table read mode of an SQL statement to weak consistency

Query data whose id is 1011 in the dws_dy table.

obclient> SELECT /*+ READ_CONSISTENCY(WEAK) */ *
                    FROM dws_ny
                    WHERE dws_ny.id = 1011;
+------+------+--------+-------+-----------+
| ID   | AGE  | NAME   | SAL   | C_DATE    |
+------+------+--------+-------+-----------+
| 1011 |   22 | John Doe   | 13.11 | 09-SEP-19 |
+------+------+--------+-------+-----------+
1 row in set

Display the execution plan.

obclient> EXPLAIN EXTENDED SELECT /*+ READ_CONSISTENCY(WEAK) */ *
          FROM dws_ny
          WHERE dws_ny.id = 1011\G
*************************** 1. row ***************************
Query Plan: ====================================
|ID|OPERATOR |NAME  |EST. ROWS|COST|
------------------------------------
|0 |TABLE GET|DWS_NY|1        |46  |
====================================

Outputs & filters:
-------------------------------------
  0 - output([DWS_NY.ID(0x7f9d9cfe02e0)], [DWS_NY.USER_ID(0x7f9d9cfe0690)], [DWS_NY.NAME(0x7f9d9cfe0980)], [DWS_NY.SAL(0x7f9d9cfe0c70)], [DWS_NY.C_DATE(0x7f9d9cfe0f60)]), filter(nil),
      access([DWS_NY.ID(0x7f9d9cfe02e0)], [DWS_NY.USER_ID(0x7f9d9cfe0690)], [DWS_NY.NAME(0x7f9d9cfe0980)], [DWS_NY.SAL(0x7f9d9cfe0c70)], [DWS_NY.C_DATE(0x7f9d9cfe0f60)]), partitions(p0),
      is_index_back=false,
      range_key([DWS_NY.ID(0x7f9d9cfe02e0)]), range[1011 ; 1011],
      range_cond([DWS_NY.ID(0x7f9d9cfe02e0) = 1011(0x7f9d9cfdfbc0)])

Used Hint:
-------------------------------------
  /*+
      READ_CONSISTENCY("WEAK")
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "SYS.DWS_NY"@"SEL$1")
      READ_CONSISTENCY("WEAK")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
LOCAL

Optimization Info:
-------------------------------------

DWS_NY:table_rows:9, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback

Parameters
-------------------------------------

1 row in set

Example 2: Specify to use indexes in an SQL statement

Specify to use indexes in an SQL statement to query data that meet the condition id > 1018.

obclient> SELECT /*+ INDEX (dws_ny idx7)*/ id, age
               FROM dws_ny
               WHERE id > 1018;
+------+------+
| ID   | AGE  |
+------+------+
| 1027 |   22 |
| 1022 |   22 |
| 1033 |   22 |
| 1035 |   22 |
| 1021 |   22 |
| 1026 |   22 |
+------+------+
6 rows in set

Display the execution plan.

obclient> EXPLAIN EXTENDED SELECT /*+ INDEX (dws_ny idx7)*/ id, age
                   FROM dws_ny
                   WHERE id > 1018\G
*************************** 1. row ***************************
Query Plan: ===========================================
|ID|OPERATOR  |NAME        |EST. ROWS|COST|
-------------------------------------------
|0 |TABLE SCAN|DWS_NY(IDX7)|1        |92  |
===========================================

Outputs & filters:
-------------------------------------
  0 - output([DWS_NY.ID(0x7f9de45e0a90)], [DWS_NY.AGE(0x7f9de45e1480)]), filter([DWS_NY.ID(0x7f9de45e0a90) > 1018(0x7f9de45e0370)]),
      access([DWS_NY.ID(0x7f9de45e0a90)], [DWS_NY.AGE(0x7f9de45e1480)]), partitions(p0),
      is_index_back=true, filter_before_indexback[true],
      range_key([DWS_NY.C_DATE(0x7f9de4621fb0)], [DWS_NY.ID(0x7f9de45e0a90)]), range(MIN,MIN ; MAX,MAX)always true

Used Hint:
-------------------------------------
  /*+
      INDEX(@"SEL$1" "SYS.DWS_NY"@"SEL$1" "IDX7")
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "SYS.DWS_NY"@"SEL$1" "IDX7")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
LOCAL

Optimization Info:
-------------------------------------

DWS_NY:table_rows:9, physical_range_rows:9, logical_range_rows:9, index_back_rows:0, output_rows:0, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[IDX7], pruned_index_name[DWS_NY], estimation info[table_id:1100611139453852, (table_type:1, version:0-1654452010335323-1654452010335323, logical_rc:0, physical_rc:0), (table_type:0, version:1654452010335323-1654452010335323-9223372036854775807, logical_rc:9, physical_rc:9)]
Parameters
-------------------------------------

1 row in set

Example 3: Specify to join tables based on the sequence of the table names in the FROM clause

Use a table join to query data that has the same id in tables dws_ny and dws_yn.

obclient> SELECT /*+ ORDERED */ *
                FROM dws_ny a,dws_yn b
                WHERE a.id = b.id;
+------+------+--------+-------+-----------+------+------+--------+-------+-----------+
| ID   | AGE  | NAME   | SAL   | C_DATE    | ID   | AGE  | NAME   | SAL   | C_DATE    |
+------+------+--------+-------+-----------+------+------+--------+-------+-----------+
| 1011 |   22 | John Doe   | 13.11 | 09-SEP-19 | 1011 |   22 | John Doe   | 13.11 | 09-SEP-19 |
| 1014 |   22 | John Doe   | 22.21 | 08-AUG-20 | 1014 |   22 | John Doe   | 22.21 | 08-AUG-20 |
| 1022 |   22 | Joe Blow   | 23.34 | 06-JUN-19 | 1022 |   22 | Joe Blow   | 23.34 | 06-JUN-19 |
| 1027 |   22 | Joe Blow   | 12.22 | 05-MAY-19 | 1027 |   22 | Joe Blow   | 12.22 | 05-MAY-19 |
+------+------+--------+-------+-----------+------+------+--------+-------+-----------+
4 rows in set

Display the execution plan.

obclient> EXPLAIN EXTENDED SELECT /*+ ORDERED */ *
                    FROM dws_ny a,dws_yn b
                    WHERE a.id = b.id\G
*************************** 1. row ***************************
Query Plan: ====================================
|ID|OPERATOR   |NAME|EST. ROWS|COST|
------------------------------------
|0 |MERGE JOIN |    |9        |96  |
|1 | TABLE SCAN|A   |9        |46  |
|2 | TABLE SCAN|B   |9        |46  |
====================================

Outputs & filters:
-------------------------------------
  0 - output([A.ID(0x7f9de45e0970)], [A.AGE(0x7f9de45e1010)], [A.NAME(0x7f9de45e1300)], [A.SAL(0x7f9de45e15f0)], [A.C_DATE(0x7f9de45e18e0)], [B.ID(0x7f9de45e0c60)], [B.AGE(0x7f9de45e1bd0)], [B.NAME(0x7f9de45e1ec0)], [B.SAL(0x7f9de45e21b0)], [B.C_DATE(0x7f9de45e24a0)]), filter(nil),
      equal_conds([A.ID(0x7f9de45e0970) = B.ID(0x7f9de45e0c60)(0x7f9de45e0250)]), other_conds(nil),
      merge_directions([ASC])
  1 - output([A.ID(0x7f9de45e0970)], [A.AGE(0x7f9de45e1010)], [A.NAME(0x7f9de45e1300)], [A.SAL(0x7f9de45e15f0)], [A.C_DATE(0x7f9de45e18e0)]), filter(nil),
      access([A.ID(0x7f9de45e0970)], [A.AGE(0x7f9de45e1010)], [A.NAME(0x7f9de45e1300)], [A.SAL(0x7f9de45e15f0)], [A.C_DATE(0x7f9de45e18e0)]), partitions(p0),
      is_index_back=false,
      range_key([A.ID(0x7f9de45e0970)]), range(MIN ; MAX)always true
  2 - output([B.ID(0x7f9de45e0c60)], [B.AGE(0x7f9de45e1bd0)], [B.NAME(0x7f9de45e1ec0)], [B.SAL(0x7f9de45e21b0)], [B.C_DATE(0x7f9de45e24a0)]), filter(nil),
      access([B.ID(0x7f9de45e0c60)], [B.AGE(0x7f9de45e1bd0)], [B.NAME(0x7f9de45e1ec0)], [B.SAL(0x7f9de45e21b0)], [B.C_DATE(0x7f9de45e24a0)]), partitions(p0),
      is_index_back=false,
      range_key([B.ID(0x7f9de45e0c60)]), range(MIN ; MAX)always true

Used Hint:
-------------------------------------
  /*+
      ORDERED
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$1" ("SYS.A"@"SEL$1" "SYS.B"@"SEL$1" ))
      USE_MERGE(@"SEL$1" ("SYS.B"@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" ("SYS.B"@"SEL$1" ) LOCAL LOCAL)
      FULL(@"SEL$1" "SYS.A"@"SEL$1")
      FULL(@"SEL$1" "SYS.B"@"SEL$1")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
LOCAL

Optimization Info:
-------------------------------------


A:table_rows:9, physical_range_rows:9, logical_range_rows:9, index_back_rows:0, output_rows:9, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[DWS_NY], pruned_index_name[IDX7], estimation info[table_id:1100611139453851, (table_type:1, version:0-1654452010335323-1654452010335323, logical_rc:0, physical_rc:0), (table_type:0, version:1654452010335323-1654452010335323-9223372036854775807, logical_rc:9, physical_rc:9)]

B:table_rows:9, physical_range_rows:9, logical_range_rows:9, index_back_rows:0, output_rows:9, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[DWS_YN], estimation info[table_id:1100611139453853, (table_type:1, version:0-1654452010335323-1654452010335323, logical_rc:0, physical_rc:0), (table_type:0, version:1654452010335323-1654452010335323-9223372036854775807, logical_rc:9, physical_rc:9)]
Parameters
-------------------------------------

1 row in set

Example 4: Use a SQL hint to set the query timeout period to 10 seconds

Use a SQL hint to set the timeout period for queries on the dws_ny table to 10 seconds.

obclient> SELECT /*+ query_timeout(10000000) */ id,age,name,sal
               FROM dws_ny
               WHERE id=1011 and age=22 AND sal=13.11;
+------+------+--------+-------+
| ID   | AGE  | NAME   | SAL   |
+------+------+--------+-------+
| 1011 |   22 | John Doe   | 13.11 |
+------+------+--------+-------+
1 row in set

Display the execution plan.

obclient> EXPLAIN EXTENDED SELECT /*+ query_timeout(10000000) */ id,age,name,sal
                    FROM dws_ny
                    WHERE id=1011 and age=22 AND sal=13.11;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================
|ID|OPERATOR |NAME  |EST. ROWS|COST|
------------------------------------
|0 |TABLE GET|DWS_NY|1        |46  |
====================================

Outputs & filters:
-------------------------------------
  0 - output([DWS_NY.ID(0x7f9de45e1b80)], [DWS_NY.AGE(0x7f9de45e2c20)], [DWS_NY.NAME(0x7f9de45e49d0)], [DWS_NY.SAL(0x7f9de45e3cc0)]), filter([DWS_NY.AGE(0x7f9de45e2c20) = 22(0x7f9de45e2500)], [DWS_NY.SAL(0x7f9de45e3cc0) = 13.11(0x7f9de45e35a0)]),
      access([DWS_NY.ID(0x7f9de45e1b80)], [DWS_NY.AGE(0x7f9de45e2c20)], [DWS_NY.SAL(0x7f9de45e3cc0)], [DWS_NY.NAME(0x7f9de45e49d0)]), partitions(p0),
      is_index_back=false, filter_before_indexback[false,false],
      range_key([DWS_NY.ID(0x7f9de45e1b80)]), range[1011 ; 1011],
      range_cond([DWS_NY.ID(0x7f9de45e1b80) = 1011(0x7f9de45e1460)])

Used Hint:
-------------------------------------
  /*+
      QUERY_TIMEOUT(10000000)
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "SYS.DWS_NY"@"SEL$1")
      QUERY_TIMEOUT(10000000)
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
LOCAL

Optimization Info:
-------------------------------------

DWS_NY:table_rows:9, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback

Parameters
-------------------------------------
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set