In SQL queries, access path hints allow developers and database administrators to indicate how the query optimizer should handle specific tables, influencing how 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 that the table should use a specific index. Its opposite is NO_INDEX. |
NO_INDEX |
Specifies that the table should disable a specific index. Its opposite is INDEX. |
INDEX_SS |
Specifies that the table should use a specific index and skip scan. |
PARALLEL |
Specifies the parallelism level for the table. |
USE_DAS |
Specifies that the optimizer should use DAS for the specified table. Its opposite is NO_USE_DAS. |
NO_USE_DAS |
Specifies that the optimizer should disable DAS for the specified table. Its opposite is USE_DAS. |
USE_COLUMN_TABLES |
Specifies that columnar scanning should be used for the table object. Its opposite is NO_USE_COLUMN_TABLE. |
NO_USE_COLUMN_TABLE |
Disables columnar scanning for the table object. Its opposite is USE_COLUMN_TABLE. |
INDEX_MERGE |
Controls whether the specified table should use an index merge plan.
Notice
|
NO_INDEX_MERGE |
Controls whether the specified table should not use a specified index to generate an index merge plan.
Notice
|
DYNAMIC_SAMPLING Hint
The DYNAMIC_SAMPLING Hint specifies whether to use dynamic sampling. Dynamic sampling improves query execution plans, 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. This parameter is optional.table_name: the name of the table to use dynamic sampling. This parameter is optional. If this parameter is not specified, dynamic sampling is used for the entire query.INTNUM1: specifies the sampling level. Currently, only 0 and 1 are supported. 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 when you execute 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 scans the specified table by using the primary key.
Syntax
/*+ FULL ( [ @queryblock ] tablespec ) */
Examples
The following example shows how to force the optimizer to scan the employees table by using the primary key. The query specifies the last_name column, which has an index. However, the optimizer scans the employees table by using the primary key. In the FROM clause, the e alias specifies the employees table. Therefore, the hint refers to the alias instead of the actual table name.
First, create the employees table and insert 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);
Then, use the FULL Hint to force the optimizer to scan the employees table by using the primary key:
-- Force the optimizer to scan the employees table by 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 specifies that the optimizer uses a specific index for the specified table. The reverse operation of this hint is the NO_INDEX Hint, which specifies that the optimizer disables a specific index for the specified 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. The optimizer considers only the specified index for scanning and ignores other indexes and full table scans.
- Multiple hints: You can specify 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. If you want to specify multiple indexes for the same table, you must write separate INDEX Hints for each index.
Examples
-- Use the INDEX Hint to specify that the 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 disables a specific index for the specified table. The reverse operation of this hint is the INDEX Hint, which specifies that the optimizer uses a specific index for the specified table.
Syntax
/*+ NO_INDEX ( [ @queryblock ] tablespec indexspec ) */
Examples
-- Use the NO_INDEX Hint to specify that the 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 index skip scan.
Syntax
/*+ INDEX_SS ( [ @ qb_name ] tablespec indexspec) */
Parameters
tablespec: the table object specified by the hint.indexspec: the index used for index skip scan.
Examples
The following example shows how to specify that the optimizer uses the idx1 index for index skip scan. The query plan is as follows. The query range used for index skip scan is (1,MIN ; 1,MAX), which is extracted from the second column c2 of the idx1 index.
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 optimizer cannot extract the query range for index skip scan from the specified index, the query does not use index skip scan. In this case, the INDEX_SS Hint only specifies that the optimizer uses the specified index.
In the following two examples, the first query uses the idx1 index and extracts the query range (1,MIN,MIN ; 1,MAX,MAX), which cannot be used for index skip scan. The second query uses the idx2 index and extracts the query range (1,MIN ; 1,MAX), which also cannot be used for index skip scan. In addition, there is no reverse hint to disable index skip scan for the INDEX_SS Hint. However, you can use the INDEX Hint to specify an index, which will directly disable 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 with 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 PARALLEL hint at the table level is specified as follows:
/*+ PARALLEL(table_name n) */
When both table-level and global parallelism are specified using hints, the table-level hint has higher priority.
Examples
-- Scan the 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 the Distributed Data Access Service (DAS) for the specified table. DAS is a distributed data access service provided by OceanBase Database. By enabling the USE_DAS hint, users can leverage the following features to fully utilize the advantages of OceanBase Database:
Partition pruning capability: DAS maintains the mapping between data and physical nodes, allowing the optimizer to calculate the required physical location information during SQL execution. 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 from 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 when processing queries on the specified table. Its opposite 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 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 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 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 the use of index merge plans 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 can generate index merge plans 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) affected by the hint. If omitted, the default is to apply to the main query block of the current query.tbl_name: Specifies the name of the table to which the index merge plan should be applied.index_name: Optional. Specifies the name of the index involved in the index merge plan. Multiple index names can be specified.- If no
index_nameparameter is specified after the table name, it indicates that the index merge plan should be used for the specified table. - If an
index_nameparameter is specified after the table name, it indicates that the index merge plan should be generated using the specified 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 the use of the specified index to generate an index merge plan 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 and the INDEX_MERGE hint conflict, 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) affected by the hint. If omitted, the default is to apply 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 indicates that the index merge plan should not be used. - If an
index_nameparameter is specified after the table name, it indicates that the index merge plan should not be generated using the specified index.
- If no
Examples:
obclient> SELECT /*+ NO_INDEX_MERGE(t1)*/ * FROM t1
WHERE c2 = 1
AND c3 = 1
AND c4 = 1;