In SQL queries, access path hints allow developers and database administrators to direct the query optimizer on how to handle specific tables, influencing the data retrieval methods during query execution.
| Hint Type | Description |
|---|---|
DYNAMIC_SAMPLING |
Specifies table-level dynamic sampling. |
FULL |
Instructs the optimizer to 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 for the table. |
PARALLEL |
Specifies the parallelism level for the table. |
USE_DAS |
Instructs the optimizer to use DAS for the specified table. Its reverse operation is NO_USE_DAS. |
NO_USE_DAS |
Instructs the optimizer to 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. |
DYNAMIC_SAMPLING Hint
The DYNAMIC_SAMPLING hint specifies whether to enable dynamic sampling for a query. Enabling dynamic sampling can improve 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. This is an optional parameter.table_name: the name of the table for which to enable dynamic sampling. This is an optional parameter. 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 sample. We recommend that you set this parameter to a value greater than 32.
Examples
-- Enable dynamic sampling for table t1 during query execution.
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 to enable primary key scan for a specified table.
Syntax
/*+ FULL ( [ @queryblock ] tablespec ) */
Examples
In the following example, even though an index exists on the last_name column, the query performs a primary key scan on the employees table. In the FROM clause, an alias e is specified for the employees table, so the hint refers to the alias instead of the actual table name.
First, create the employees table 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 the employees table:
-- Force a primary key scan on the employees table.
SELECT /*+ FULL(e) */ employee_id, last_name
FROM hr.employees e
WHERE last_name LIKE :al;
INDEX Hint
The INDEX hint specifies to enable a specified index for a table. The reverse operation is the NO_INDEX hint, which specifies to disable a specified index for a 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 specified index for scanning and ignores other indexes and full table scans.
- Multiple hints: Multiple
INDEXhints can be specified 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, other indexes and full table scans are not considered.
A single INDEX hint can specify only one index. To provide hints for multiple indexes on the same table, you must write a separate INDEX hint 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 to disable a specified index for a table. The reverse operation is the INDEX hint, which specifies to enable a specified index for a 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 to enable index skip scan for a specified table using a specified index.
Syntax
/*+ INDEX_SS ( [ @ qb_name ] tablespec indexspec) */
Parameters
tablespec: the table object to which the hint applies.indexspec: the index to use for index skip scan.
Examples
In the following example, the query uses the idx1 index for index skip scan. The query plan is as follows. The query range for index skip scan in the query 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 query range for index skip scan cannot be extracted from the index, the query will not use index skip scan. In this case, the INDEX_SS hint only specifies to use 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. Additionally, there is no reverse hint to disable index skip scan for the INDEX_SS hint. 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 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 limits are violated, the PARALLEL hint is ignored.
Syntax
The PARALLEL hint at the table level is specified as follows:
/*+ PARALLEL(table_name n) */
When used with the hint to specify both table-level and global parallelism, 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 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 node in the cluster on any Observer node.
Task-level retry capability: During data access, if certain partitions become unreadable due to reasons such as 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 is the USE_DAS hint.
Syntax
/*+ NO_USE_DAS ( [ @queryblock ] tablespec ) */
Examples
-- The `NO_USE_DAS` hint instructs the optimizer to not 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 specifies to use columnar scanning for table objects. For tables that have not enabled columnar storage, 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 the use of columnar scanning 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 |
+------------------------------------------------------------------+