In SQL queries, access path-related hints allow developers and database administrators to instruct the query optimizer how to process specific tables. They affect the way data is read during queries.
Hint type |
Description |
|---|---|
DYNAMIC_SAMPLING |
Specifies table-level dynamic sampling. |
FULL |
Instructs the optimizer to perform a full-table scan on the specified table using the primary key. |
INDEX |
Specifies which index to use for a table. Its inverse operation is NO_INDEX. |
NO_INDEX |
Disables a specific index for a table. Its inverse operation is INDEX. |
INDEX_SS |
Specify a table to use a specific index and enable skip scan. |
PARALLEL |
Specifies the table-level parallelism. |
USE_DAS |
The optimizer uses DAS for the specified table. Its reverse operation is NO_USE_DAS. |
NO_USE_DAS |
The optimizer disables DAS for a specified table. Its reverse operation is USE_DAS. |
USE_COLUMN_TABLE |
Specifies to use columnar scan for table objects. Its inverse operation is NO_USE_COLUMN_TABLE. |
NO_USE_COLUMN_TABLE |
Columnar scan is prohibited for table objects. Its reverse operation is USE_COLUMN_TABLE. |
INDEX_MERGE |
Used to control whether a specified table uses an index merge plan.
Notice
|
NO_INDEX_MERGE |
This clause is used to control the generation of an index merge plan for a specified table without using the specified index.
Notice
|
DYNAMIC_SAMPLING Hint
The DYNAMIC_SAMPLING hint controls whether a query uses dynamic sampling. Enabling dynamic sampling can improve the quality of query execution plans, especially when statistics are insufficient or inaccurate.
Syntax
/*+DYNAMIC_SAMPLING( [[ qb_name_option ] table_name ] INTNUM1 [, INTNUM2 ] ) */
Parameters
qb_name_option: Optional. The name of the query block.table_name: Optional. The name of the table for which to enable dynamic sampling. If left empty, dynamic sampling is enabled for the entire query.INTNUM1: Specifies the sampling level. Currently, only 0 or 1 is supported. 1 enables dynamic sampling, and 0 disables it.INTNUM2: Specifies the number of microblocks to sample. It is recommended to set this parameter to at least 32.
Examples
-- To enable dynamic sampling for table t1 during query time
SELECT /*+DYNAMIC_SAMPLING(t1 1)*/ c4 FROM t1 WHERE c1 > 1 AND c2 > 1 AND c3 > 1 GROUP BY c4;
FULL Hint
The FULL hint enables the optimizer to scan a specified table using its primary key.
Syntax
/*+ FULL ( [ @queryblock ] tablespec ) */
Examples
In the query example below, even though an index exists on the last_name column, the query will perform a primary key scan on the employees table. An alias e is used in the FROM clause to refer to the employees table, so the hint refers to the alias rather than the actual table name.
First, create the employees table and the necessary data and indexes:
CREATE TABLE hr.employees (
employee_id INT PRIMARY KEY,
last_name VARCHAR(255),
first_name VARCHAR(255)
);
-- Insert some sample data
INSERT INTO hr.employees (employee_id, last_name, first_name)
VALUES
(1, 'Smith', 'John'),
(2, 'Doe', 'Jane'),
(3, 'Brown', 'Charlie');
-- Create index
CREATE INDEX idx_last_name ON hr.employees(last_name);
Next, use the FULL hint to force a primary key scan on the employees table:
-- Force a scan on the employees table using the primary key
SELECT /*+ FULL(e) */ employee_id, last_name
FROM hr.employees e
WHERE last_name LIKE :al;
INDEX Hint
The INDEX hint enables a specific index for a specified table. Its reverse operation is the NO_INDEX hint, which specifies to disable a specific index for a table.
Syntax
/*+ INDEX ( [ @queryblock ] tablespec indexspec ) */
Behavior
The behavior of the hint depends on the indexspec specification:
- Single index: A hint can specify only one index. In this case, the optimizer will consider only scanning using that index and will not consider other indexes or a full-table scan.
- Multiple hints: You can write multiple index hints for the same table. For example, by using
/*+ INDEX(t idx1) INDEX(t idx2) */, the optimizer will evaluate these specified indexes and choose the one with the lowest cost for scanning. In this case, indexes not listed and a full-table scan will also not be considered.
A single INDEX hint can only reference one index. To provide hints for multiple indexes on the same table, you must write the INDEX hint separately for each index.
Examples
-- Use the INDEX hint to instruct the query optimizer to access the employees table using the 'emp_department_ix' index.
SELECT /*+ INDEX (employees emp_department_ix) */ employee_id, department_id
FROM employees
WHERE department_id > 50;
NO_INDEX Hint
The NO_INDEX hint is used to disable a specific index for a specified table. Its counterpart is the INDEX hint, which specifies to enable a specific index for a table.
Syntax
/*+ NO_INDEX ( [ @queryblock ] tablespec indexspec ) */
Examples
-- Use the NO_INDEX hint to disable the query optimizer from using the 'emp_department_ix' index to access the employees table.
SELECT /*+ NO_INDEX (employees emp_department_ix) */ employee_id, department_id
FROM employees
WHERE department_id > 50;
INDEX_SS Hint
The INDEX_SS hint specifies to use a specific index for an index skip scan on a table object.
Syntax
/*+ INDEX_SS ( [ @ qb_name ] tablespec indexspec) */
Parameter description
tablespec: The table object specified for the hint.indexspec: the index used for index skip scan.
Examples
In the query example below, the index idx1 is specified for an index skip scan to obtain the following query plan. The query range used for the index skip scan is (1,MIN ; 1,MAX), which is derived from the second column c2 of index idx1.
create table t1(c1 int, c2 int ,c3 int);
create index idx1 on t1(c1, c2);
create index idx2 on t1(c2);
explain basic select /*+index_ss(t1 idx1)*/ 1 from t1 where c2 = 1;
+-----------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------+
| ============================= |
| |ID|OPERATOR |NAME | |
| ----------------------------- |
| |0 |TABLE SKIP SCAN|t1(idx1)| |
| ============================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([1]), filter([t1.c2 = 1]), rowset=16 |
| access([t1.c2]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t1.c1], [t1.c2], [t1.__pk_increment]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true |
| prefix_columns_cnt = 1 , skip_scan_range(1,MIN ; 1,MAX) |
+-----------------------------------------------------------------------------------------------------+
Note that even if the INDEX_SS hint is used, if a suffix query range for index skip scan cannot be extracted from the index, the query will ultimately not use index skip scan. In this case, the role of the INDEX_SS hint is merely to specify that the query should use a particular index.
In the following two examples, the first query uses index idx1 to directly retrieve the query range (1,MIN,MIN ; 1,MAX,MAX), which does not support index skip scan. The second query uses index idx2 to retrieve the query range (1,MIN ; 1,MAX), which also does not support index skip scan. Furthermore, the INDEX_SS hint does not have a corresponding reverse hint to prohibit index skip scan. However, you can directly use the INDEX hint to specify an index, which will then explicitly prohibit index skip scan.
explain basic select /*+index_ss(t1 idx1)*/ 1 from t1 where c1 = 1;
+----------------------------------------------------------------------------------------+
| Query Plan |
+----------------------------------------------------------------------------------------+
| ============================== |
| |ID|OPERATOR |NAME | |
| ------------------------------ |
| |0 |TABLE RANGE SCAN|t1(idx1)| |
| ============================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([1]), filter(nil), rowset=16 |
| access(nil), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.c1], [t1.c2], [t1.__pk_increment]), range(1,MIN,MIN ; 1,MAX,MAX), |
| range_cond([t1.c1 = 1]) |
+----------------------------------------------------------------------------------------+
explain basic select /*+index_ss(t1 idx2)*/ 1 from t1 where c2 = 1;
+-----------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------+
| ============================== |
| |ID|OPERATOR |NAME | |
| ------------------------------ |
| |0 |TABLE RANGE SCAN|t1(idx2)| |
| ============================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([1]), filter(nil), rowset=16 |
| access(nil), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.c2], [t1.__pk_increment]), range(1,MIN ; 1,MAX), |
| range_cond([t1.c2 = 1]) |
+-----------------------------------------------------------------------+
PARALLEL Hint
The PARALLEL hint is a statement-level hint that instructs the optimizer to specify the number of parallel threads that can be used for parallel operations. This hint overrides the value of the initialization parameter PARALLEL_DEGREE_POLICY. It can be used as part of SELECT, INSERT, UPDATE, and DELETE statements, as well as in table scans. If any parallel restrictions are violated, the PARALLEL hint is ignored.
Syntax
The table-level PARALLEL hint is used to specify the number of parallel threads. The syntax is as follows:
/*+ PARALLEL(table_name n) */
You can use hints to specify both table-level and global parallelism, with table-level hints having higher priority.
Examples
-- When scanning the table, use a degree of parallelism of 8.
SELECT /*+parallel(4) parallel(emp 8) */ last_name
FROM emp;
USE_DAS Hint
The USE_DAS hint instructs the optimizer to use DAS (Distributed Data Access Service) for the specified table. DAS is a distributed data access service provided by OceanBase AI Database. By enabling the USE_DAS hint, you can achieve the following effects and fully leverage the advantages of OceanBase AI Database:
Partition pruning capability: By maintaining a mapping between data and physical nodes, DAS helps the optimizer calculate the physical location information required during SQL request execution, improving query efficiency and reducing unnecessary data scans.
Efficient basic data access: DAS supports common database operations such as insert, update, delete, lock_row, and scan. It ensures efficient data access across distributed environments, enabling SQL requests to flexibly access data on any node in the cluster from any Observer node.
Task-level retry capability: When accessing data, if partitions become unreadable due to reasons such as replica migration or network partitioning, DAS automatically retries the request from another available replica.
Syntax
/*+ USE_DAS ( [ @queryblock ] tablespec ) */
Examples
SELECT /*+ USE_DAS(t1) */ * FROM t1 WHERE column1 > 100;
NO_USE_DAS Hint
The NO_USE_DAS hint instructs the optimizer not to use DAS when processing queries for the specified table. Its reverse operation is USE_DAS.
Syntax
/*+ NO_USE_DAS ( [ @queryblock ] tablespec ) */
Examples
-- The `NO_USE_DAS` hint instructs the optimizer not to perform dynamic adaptive sampling on table t1, but instead to execute the query based on the existing statistics.
SELECT /*+ NO_USE_DAS(t1) */ * FROM t1 WHERE column1 < 50;
USE_COLUMN_TABLE Hint
The USE_COLUMN_TABLE hint is used to specify columnar scanning for table objects. For tables where columnar storage is not enabled, using the USE_COLUMN_TABLE hint is ineffective.
Syntax
/*+ USE_COLUMN_TABLE ( [ @ qb_name ] tablespec) */
Examples
create table t(c1 int, c2 int ,c3 int)
with column group (all columns, each column);
explain basic select /*+USE_COLUMN_TABLE(t)*/ * from t;
+------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------+
| ================================ |
| |ID|OPERATOR |NAME| |
| -------------------------------- |
| |0 |COLUMN TABLE FULL SCAN|t | |
| ================================ |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t.c1], [t.c2], [t.c3]), filter(nil), rowset=16 |
| access([t.c1], [t.c2], [t.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------+
NO_USE_COLUMN_TABLE Hint
The NO_USE_COLUMN_TABLE hint specifies to prohibit columnar scan on table objects.
Syntax
/*+ NO_USE_COLUMN_TABLE ( [ @ qb_name ] tablespec ) */
Example
create table t(c1 int, c2 int ,c3 int)
with column group (all columns, each column);
explain basic select /*+NO_USE_COLUMN_TABLE(t)*/ * from t;
+------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------+
| ========================= |
| |ID|OPERATOR |NAME| |
| ------------------------- |
| |0 |TABLE FULL SCAN|t | |
| ========================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t.c1], [t.c2], [t.c3]), filter(nil), rowset=16 |
| access([t.c1], [t.c2], [t.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------+
INDEX_MERGE Hint
The INDEX_MERGE hint is used to control whether a specified table uses an index merge plan. If the specified index cannot generate an index merge plan, this hint is ignored. If no index is specified, the system tries to use an index merge plan whenever possible.
Using the INDEX_MERGE hint can generate an index merge plan when the value of the _enable_index_merge parameter is False.
Syntax:
/*+ INDEX_MERGE([@query_block_name] tbl_name [index_name [,index_name]...]) */
Parameter description:
@query_block_name: Optional. Specifies the name of the query block affected by this hint. If omitted, it applies by default to the main query block of the current query.tbl_name: Specifies the name of the table for which you want to enable index merging.index_name: Optional. Specifies the name of the index involved in the index merge. Multiple index names can be specified.- When the
index_nameparameter is not specified after the table name, it controls the specified table to use an index merge plan. - When the
index_nameparameter is specified after the table name, it controls the specified table to use the specified index to generate an index merge plan.
- When the
Examples:
Specify the index name in the
INDEX_MERGEhint.obclient> SELECT /*+ INDEX_MERGE(t1 idx_c2 idx_c3) */ * FROM t1 WHERE c2 = 1 AND c3 = 1 AND c4 = 1;Do not specify the index name in the
INDEX_MERGEhint.obclient> SELECT /*+ INDEX_MERGE(t1)*/ * FROM t1 WHERE c2 = 1 AND c3 = 1 AND c4 = 1;
NO_INDEX_MERGE Hint
The NO_INDEX_MERGE hint is used to control a specified table from using a specified index to generate an index merge plan. If no index is specified, this hint prohibits the table from generating an index merge plan.
When the NO_INDEX_MERGE hint conflicts with the INDEX_MERGE hint, both hints are ignored.
Syntax:
/*+ NO_INDEX_MERGE([@query_block_name] tbl_name [index_name [,index_name]...]) */
Parameter description:
@query_block_name: Optional. The name of the query block to which this hint applies. If omitted, it applies by default to the main query block of the current query.tbl_name: the name of the table.index_name: Optional. The index name. Multiple index names can be specified.- When the
index_nameparameter is absent after the specified table, index merge plan is not used. - When the
index_nameparameter follows a specified table, it indicates that the specified index will not be used to generate the index merge plan.
- When the
Example:
obclient> SELECT /*+ NO_INDEX_MERGE(t1)*/ * FROM t1
WHERE c2 = 1
AND c3 = 1
AND c4 = 1;