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 use a full table scan on the specified table using the primary key. |
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 for the table 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_TABLE |
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 whether the specified table uses an index merge plan.
Notice
|
NO_INDEX_MERGE |
Controls whether the specified table does not use the specified index to generate an index merge plan.
Notice
|
DYNAMIC_SAMPLING Hint
DYNAMIC_SAMPLING Hint specifies whether to use 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: the name of the query block. This parameter is optional.table_name: the name of the table to be dynamically sampled. This parameter is optional. If this parameter is not specified, dynamic sampling is enabled for the entire query.INTNUM1: specifies the sampling level. Only 0 or 1 is supported. 1 indicates that dynamic sampling is enabled. 0 indicates that dynamic sampling is disabled.INTNUM2: specifies the number of microblocks to be sampled. We recommend that you set this parameter to a value greater than 32.
Examples
-- Enable dynamic sampling for table t1 when you query the table.
SELECT /*+DYNAMIC_SAMPLING(t1 1)*/ c4 FROM t1 WHERE c1 > 1 AND c2 > 1 AND c3 > 1 GROUP BY c4;
FULL Hint
FULL Hint specifies that the optimizer scans the specified table by using the primary key.
Syntax
/*+ FULL ( [ @queryblock ] tablespec ) */
Examples
In the following example, even though an index exists on the last_name column, the query scans the primary key of the employees table. In the FROM clause, the table employees is specified by using the alias e. 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);
Next, use the FULL hint to force the optimizer to scan the primary key of the employees table:
-- Force the optimizer to scan the primary key of the employees table.
SELECT /*+ FULL(e) */ employee_id, last_name
FROM hr.employees e
WHERE last_name LIKE :al;
INDEX Hint
INDEX Hint specifies that the optimizer uses a specific index for the specified table. The reverse operation of this hint is NO_INDEX Hint, which specifies that the optimizer disables the use of a specific index for the 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 the use of 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 of 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
NO_INDEX Hint specifies that the optimizer disables the use of a specific index for the specified table. The reverse operation of this hint is 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
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
In the following example, the query uses the index idx1 for index skip scan. The query plan is as follows. The suffix query range for index skip scan in the query is (1,MIN ; 1,MAX), which is extracted from the second column c2 of the 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, the query will not use index skip scan if the suffix query range for index skip scan cannot be extracted from the index. In this case, the INDEX_SS hint only specifies that the query uses a specific index.
In the following two examples, the first query uses the idx1 index and directly 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 of INDEX_SS Hint to disable index skip scan. However, you can directly use the INDEX hint to specify an index, which will 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 for parallel operations. This hint overrides the value of the initialization parameter PARALLEL_DEGREE_POLICY. The 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 used as follows:
/*+ PARALLEL(table_name n) */
When used with other 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 DAS (Distributed Data Access Service) for the specified table. DAS is a distributed data access service provided by OceanBase Database. By enabling the USE_DAS hint, users can fully leverage the advantages of OceanBase Database:
Partition pruning capability: DAS helps the optimizer calculate the physical location information required for SQL requests, improving query efficiency and reducing unnecessary data scanning.
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, allowing SQL requests to flexibly access data on any Observer node.
Task-level retry capability: During data access, if some 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 not to use DAS for queries on the specified table. Its opposite is the USE_DAS hint.
Syntax
/*+ NO_USE_DAS ( [ @queryblock ] tablespec ) */
Examples
-- The `NO_USE_DAS` hint instructs the optimizer to execute the query based on existing statistics instead of dynamically adaptive sampling for table t1.
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 table objects. For tables that are not enabled for 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 table objects.
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.
Using the INDEX_MERGE hint can generate 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. 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: The name of the table for which the index merge plan is to be applied.index_name: Optional. 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 to be used for the specified table. - If an
index_nameparameter is specified after the table name, it means that the index merge plan is to be generated 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. 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: The name of the table.index_name: Optional. 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 to be used. - If an
index_nameparameter is specified after the table name, it means that the index merge plan is not to be generated using the specified index (Index).
- If no
Examples:
obclient> SELECT /*+ NO_INDEX_MERGE(t1)*/ * FROM t1
WHERE c2 = 1
AND c3 = 1
AND c4 = 1;