Many first-time OceanBase users try various queries with large tables. Sometimes they may feel that queries are running slowly and need to use SQL hints in queries.
About SQL hints
The execution performance of OceanBase SQL is related to the SQL execution plan which is related to how the tables are joined, the query conditions, and the indexes in tables. This is usually the internal logic of the SQL engine of a database. You can add comments to SQL statements to change the execution plans and the execution performance of SQL as well. SQL hints are in the following format:
/*+ hint_text */
Common SQL hints include:
read_consistency(weak) : weak consistency read, instructs SQL to read a follower of a table partition.
index ( table_name , index_name ): instructs SQL to use an index of a table to read data.
query_timeout(int_num): specifies the SQL query timeout value in µs.
For more information about other general SQL hints, see the appendix "General SQL hints for OceanBase".
Use SQL hints
SQL hints are commonly used with SQL statements and are not limited to use with SQL queries. Take an SQL query as an example. The basic syntax is:
SELECT /*+ hint_text [, hint_text] */ select_items FROM table_name
Notice
You can use multiple SQL hints in a statement but you must avoid functional conflicts.
In the command line (CLI) environment of OceanBase Client (OBClient), the comment syntax is ignored by default, and SQL hints do not work. Therefore, you need to add the "-c" parameter when starting OBClient.
Example: Using SQL hint in an SQL query where the query timeout is specified as 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 partition (p5)
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 18:57:10 |
+------+--------+--------------+----------+-------------+---------------------+
1 row in set (0.01 sec)
obclient>
obclient> SELECT /*+ parallel(16) */ count(*) FROM big_table;