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_CONSISTENCYhint: specifies the consistency level for reading tables. IfWEAKis specified, the related SQL statement reads tables in weak mode. IfSTRONGis specified, the related SQL statement reads tables in strong mode.Note
READ_CONSISTENCYhint: 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 OBProxy. 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.INDEXhint: instructs the optimizer to use an index scan for the specified table.QUERY_TIMEOUThint: instructs the server to set the execution timeout period for an SQL statement, in us.
For more information about other general SQL hints, see Comments.
Examples of using SQL hints
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-cparameter when you start OBClient.
Specify the consistency level for an SQL statement to read tables to WEAK
obclient> SELECT /*+ READ_CONSISTENCY(WEAK) */ *
FROM employees
WHERE employees.department_id = 1001;
Query OK, 0 row affected
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;
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
Use SQL hints 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