In SQL queries, access path hints allow developers and database administrators to instruct the query optimizer on how to handle specific tables, influencing the way data is read during query execution.
Hint Type |
Description |
|---|---|
DYNAMIC_SAMPLING |
Specifies table-level dynamic sampling. |
FULL |
Indicates that the optimizer should perform a full table scan using the primary key for the specified table. |
INDEX |
Specifies the use of a specific index for the table. Its reverse operation is NO_INDEX. |
NO_INDEX |
Specifies the disablement of a specific index for the table. Its reverse operation is INDEX. |
INDEX_SS |
Specifies the use of a specific index with skip scan. |
PARALLEL |
Specifies the parallelism level for the table. |
USE_DAS |
Indicates that the optimizer should use DAS for the specified table. Its reverse operation is NO_USE_DAS. |
NO_USE_DAS |
Indicates that the optimizer should disable DAS for the specified table. Its reverse operation is USE_DAS. |
USE_COLUMN_TABLES |
Specifies the use of columnar scanning for the table object. Its reverse operation is NO_USE_COLUMN_TABLE. |
NO_USE_COLUMN_TABLE |
Prohibits the use of columnar scanning for the table object. Its reverse operation is USE_COLUMN_TABLE. |
INDEX_MERGE |
Controls the use of index merge plans for the specified table.
Notice
|
NO_INDEX_MERGE |
Controls the exclusion of index merge plans for the specified table.
Notice
|
DYNAMIC_SAMPLING Hint
The DYNAMIC_SAMPLING Hint specifies whether to use dynamic sampling for a query. Enabling dynamic sampling improves the quality of the query execution plan, especially when statistics are insufficient or inaccurate.
Syntax
/*+DYNAMIC_SAMPLING( [[ qb_name_option ] table_name ] INTNUM1 [, INTNUM2 ] ) */
Parameters
qb_name_option: the name of the query block. Optional.table_name: the name of the table for which to enable dynamic sampling. Optional. If not specified, dynamic sampling is enabled for the entire query.INTNUM1: specifies the sampling level. Valid values are 0 and 1. 1 indicates that dynamic sampling is enabled. 0 indicates that dynamic sampling is disabled.INTNUM2: specifies the number of microblocks to sample. We recommend that you set this parameter to a value greater than 32.
Examples
-- Enable dynamic sampling for table t1 in the query.
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 specifies that the optimizer uses a primary key scan for a specified table.
Syntax
/*+ FULL ( [ @queryblock ] tablespec ) */
Examples
In the following example, even though an index exists on column last_name, the query performs a primary key scan on table employees. In the FROM clause, the alias e is specified for table employees, so the Hint references the alias instead of the actual table name.
First, create table employees and insert some sample data and create an index:
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 an index
CREATE INDEX idx_last_name ON hr.employees(last_name);
Next, use the FULL Hint to force a primary key scan on table employees:
-- Force a primary key scan on table employees.
SELECT /*+ FULL(e) */ employee_id, last_name
FROM hr.employees e
WHERE last_name LIKE :al;
INDEX Hint
The INDEX Hint specifies that the optimizer uses a specific index for a specified table. The reverse operation is the NO_INDEX Hint, which specifies that the optimizer does not use a specific index for a specified table.
Syntax
/*+ INDEX ( [ @queryblock ] tablespec indexspec ) */
Behavior
The behavior of the Hint depends on the specification of indexspec:
- Single index: A Hint can specify only one index. The optimizer considers only this index for scanning, and ignores other indexes and full table scans.
- Multiple Hints: You can write multiple INDEX Hints for the same table. For example, by using
/*+ INDEX(t idx1) INDEX(t idx2) */, the optimizer evaluates the specified indexes and selects the one with the lowest cost for scanning. In this case, indexes not listed and full table scans are also ignored.
A single INDEX Hint can specify only one index. To provide hints for multiple indexes on the same table, you must write separate INDEX Hints for each index.
Examples
-- Use the INDEX Hint to specify that the query optimizer uses the 'emp_department_ix' index to access the employees table.
SELECT /*+ INDEX (employees emp_department_ix) */ employee_id, department_id
FROM employees
WHERE department_id > 50;
NO_INDEX Hint
The NO_INDEX Hint specifies that the optimizer does not use a specific index for a specified table. The reverse operation is the INDEX Hint, which specifies that the optimizer uses a specific index for a specified table.
Syntax
/*+ NO_INDEX ( [ @queryblock ] tablespec indexspec ) */
Examples
-- Use the NO_INDEX Hint to specify that the query optimizer does not use 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 that the optimizer uses a specific index for an index skip scan on a table.
Syntax
/*+ INDEX_SS ( [ @ qb_name ] tablespec indexspec) */
Parameters
tablespec: the table object specified by the Hint.indexspec: the index used for the index skip scan.
Examples
In the following example, the query uses index idx1 for an index skip scan. The query plan is as follows. The query range for the index skip scan is (1,MIN ; 1,MAX), which is extracted 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 you use the INDEX_SS Hint, if the query range for the index skip scan cannot be extracted from the specified index, the query does not use the index skip scan. In this case, the INDEX_SS Hint only specifies that the query uses the specified index.
In the following two examples, the first query uses index idx1 and extracts the query range (1,MIN,MIN ; 1,MAX,MAX), which cannot be used for an index skip scan. The second query uses index idx2 and extracts the query range (1,MIN ; 1,MAX), which also cannot be used for an index skip scan. Additionally, there is no reverse Hint for INDEX_SS that disables the index skip scan. However, you can directly use the INDEX Hint to specify an index, which will disable the 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
PARALLEL Hint is a statement-level hint that instructs the optimizer to specify the number of parallel threads available for parallel operations. This hint overrides the value of the initialization parameter PARALLEL_DEGREE_POLICY. The PARALLEL hint can be used in SELECT, INSERT, UPDATE, and DELETE statements, as well as in table scans. If any parallel limitations are violated, the PARALLEL hint is ignored.
Syntax
The table-level PARALLEL hint is specified as follows:
/*+ PARALLEL(table_name n) */
When used in conjunction with other hints, the table-level PARALLEL hint takes precedence over global parallelism settings.
Examples
-- Scan the emp table with a parallel degree 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 Database. Enabling the USE_DAS hint allows users to leverage the following features of OceanBase Database:
Partition pruning capability: DAS maintains the mapping between data and physical nodes, enabling the optimizer to calculate the required physical location information for SQL requests. This improves query efficiency and reduces unnecessary data scans.
Efficient base data access: DAS supports common database operations such as insert, update, delete, lock_row, and scan. It ensures efficient data access across distributed environments, allowing SQL requests to flexibly access data on any Observer node within the cluster.
Task-level retry capability: During data access, if certain partitions become unreadable due to replica migration or network partitioning, DAS automatically retries the request from other available replicas.
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 to not use DAS for queries on the specified table. It is the opposite of the USE_DAS hint.
Syntax
/*+ NO_USE_DAS ( [ @queryblock ] tablespec ) */
Examples
-- The `NO_USE_DAS` hint tells the optimizer not to perform dynamic adaptive sampling on table t1, but instead to execute the query based on existing statistics.
SELECT /*+ NO_USE_DAS(t1) */ * FROM t1 WHERE column1 < 50;
USE_COLUMN_TABLE Hint
The USE_COLUMN_TABLE hint specifies that columnar scanning should be used for the table object. For tables that have not enabled columnar storage, this hint has no effect.
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 that columnar scanning should not be used for the table object.
Syntax
/*+ NO_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 /*+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 an index merge plan is used for the specified table. If the specified index cannot generate an index merge plan, the hint is ignored. If no index is specified, the optimizer will attempt to generate an index merge plan as much as possible.
Using the INDEX_MERGE hint allows the generation of an index merge plan even when the _enable_index_merge configuration parameter is set to False.
Syntax:
/*+ INDEX_MERGE([@query_block_name] tbl_name [index_name [,index_name]...]) */
Parameters:
@query_block_name: Optional. Specifies the name of the query block (query block) to which this hint applies. If omitted, the default is to apply the hint to the main query block of the current query.tbl_name: Specifies the name of the table to which the index merge plan is applied.index_name: Optional. Specifies the name of the index to be used in the index merge plan. Multiple index names can be specified.- If no
index_nameparameter is specified after the table name, it means that the index merge plan is controlled for the specified table. - If an
index_nameparameter is specified after the table name, it means that the index merge plan is controlled for the specified table using the specified index (Index).
- If no
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 whether an index merge plan is generated using the specified index for the specified table. If no index is specified, the optimizer will not generate an index merge plan for the table.
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]...]) */
Parameters:
@query_block_name: Optional. Specifies the name of the query block (query block) to which this hint applies. If omitted, the default is to apply the hint to the main query block of the current query.tbl_name: Specifies the name of the table.index_name: Optional. Specifies the name of the index. Multiple index names can be specified.- If no
index_nameparameter is specified after the table name, it means that the index merge plan is not used. - If an
index_nameparameter is specified after the table name, it means that the index merge plan is not used for the specified index (Index).
- If no
Examples:
obclient> SELECT /*+ NO_INDEX_MERGE(t1)*/ * FROM t1
WHERE c2 = 1
AND c3 = 1
AND c4 = 1;