Use SQL hints in queries

When you use OceanBase Database for the first time, use large tables to test queries. You can use SQL hints in SQL queries to control parameter settings, for example, to set 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 are:

• 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 LDC feature of OBProxy. If you need to use the read/write splitting feature, we recommend that you configure the LDC first.

• 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.

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. Ensure that the features do not conflict with each other.
• In the CLI environment of OBClient, the comment syntax is ignored by default, 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

obclient> SELECT /*+ READ_CONSISTENCY(WEAK) */ *
    FROM employees
    WHERE employees.department_id = 1001;
Query OK, 0 row affected

Example 2: Specify to use indexes in an SQL statement

obclient> SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id
FROM employees
WHERE department_id > 50;

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

obclient> SELECT /*+ ORDERED */ *
    FROM employees t1,department t2
    WHERE t1.department_id = t2.department_id;
Query OK, 0 row affected

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

obclient> SELECT /*+ query_timeout(10000000) */ o_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local,o_entry_d
    FROM ordr   
    WHERE o_w_id=1 and o_d_id=2 AND o_id=2100;
+------+--------+--------------+----------+-------------+------------+
| o_id | o_c_id | o_carrier_id | o_ol_cnt | o_all_local | o_entry_d  |
+------+--------+--------------+----------+-------------+------------+
| 2100 |      8 |            8 |       11 |           1 | 2020-02-15 |
+------+--------+--------------+----------+-------------+------------+
1 row in set