In SQL queries, access path-related hints allow developers and database administrators to instruct the query optimizer on how to handle specific tables. These hints affect how data is read during query execution.
| Hint type | Description |
|---|---|
DYNAMIC_SAMPLING |
Specifies dynamic sampling at the table level. |
FULL |
Instructs the optimizer to perform a full table scan using the primary key on the specified table. |
INDEX |
Specifies to use a specific index on the table. Its reverse operation is NO_INDEX. |
NO_INDEX |
Specifies to disable a specific index on the table. Its reverse operation is INDEX. |
INDEX_SS |
Specifies to use a specific index and perform a skip scan on the table. |
PARALLEL |
Specifies the degree of parallelism at the table level. |
USE_DAS |
Specifies to use DAS for the specified table. Its reverse operation is NO_USE_DAS. |
NO_USE_DAS |
Specifies to disable DAS for the specified table. Its reverse operation is USE_DAS. |
USE_COLUMN_TABLE |
Specifies to use columnar scanning for the table object. Its reverse operation is NO_USE_COLUMN_TABLE. |
NO_USE_COLUMN_TABLE |
Specifies to disable columnar scanning for the table object. Its reverse operation is USE_COLUMN_TABLE. |
DYNAMIC_SAMPLING hint
The DYNAMIC_SAMPLING hint specifies whether to use dynamic sampling for a query. Dynamic sampling improves the quality of the query execution plan, especially when the 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 to which dynamic sampling applies. If this parameter is not specified, dynamic sampling applies to the entire query.INTNUM1: the sampling level. The value can be 0 or 1. The value 1 specifies to enable dynamic sampling, and the value 0 specifies to disable dynamic sampling.INTNUM2: the number of microblocks to sample. We recommend that you set this parameter to 32 or more.
Examples
-- Enable dynamic sampling for the 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 use the primary key for scanning the specified table.
Syntax
/*+ FULL ( [ @queryblock ] tablespec ) */
Example
In the following query example, even though there is an index on the last_name column, the query will perform a primary key scan on the employees table. The table employees is referenced by the alias e in the FROM clause, so the hint refers to the alias rather than the actual table name.
First, create the employees table and add the necessary data and 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 use a specified index for the specified table. Its reverse operation is the NO_INDEX hint, which specifies to disable a specified 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 this index for scanning and ignores other indexes or full-table scans.
- Multiple hints: You can write multiple
INDEXhints for the same table. For example, by using/*+ INDEX(t idx1) INDEX(t idx2) */, the optimizer evaluates these specified indexes and chooses the one with the lowest cost for scanning. In this case, indexes not listed and full-table scans are not considered.
A single INDEX hint can only point to one index. To provide hints for multiple indexes on the same table, you need to write separate INDEX hints for each index.
Example
-- Use the INDEX hint to specify that the query optimizer use 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 disables the use of a specific index for a specified table. Its reverse operation is the INDEX hint, which enables the use of a specific index for the specified table.
Syntax
/*+ NO_INDEX ( [ @queryblock ] tablespec indexspec ) */
Example
-- 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 particular index for an index skip scan on the table object.
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.
Example
In the following query example, the query is specified to use index skip scan with index idx1. 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 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 the index cannot extract the query range for index skip scan, the query will not use index skip scan. In this case, the INDEX_SS hint only specifies to use a particular index for the query.
In the following two examples, the first query uses index idx1 and directly obtains the query range (1,MIN,MIN ; 1,MAX,MAX), which makes index skip scan impossible. The second query uses index idx2 and obtains the query range (1,MIN ; 1,MAX), which also makes index skip scan impossible. Additionally, there is no reverse hint to prohibit index skip scan for the INDEX_SS hint. However, you can directly use the INDEX hint to specify an index, which will directly 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 on the number of parallel threads to use 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 parallelism limits are violated, the PARALLEL hint is ignored.
Syntax
The syntax for a table-level PARALLEL hint is as follows:
/*+ PARALLEL(table_name n) */
When both table-level and global parallelism are specified by hints, the table-level hint takes precedence.
Example
-- When scanning a table, use a 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 Database. By enabling the USE_DAS hint, you can leverage the following benefits of OceanBase Database:
Partition pruning capability: DAS maintains the mapping between data and physical nodes, allowing the optimizer to calculate the required physical locations during SQL request execution. This improves query efficiency and reduces unnecessary data scanning.
Efficient base data access: DAS supports common database operations such as insert, update, delete, row locking, and scan. This ensures efficient data access across distributed environments, allowing SQL requests to flexibly access data on various nodes in the cluster.
Retry capability at the task level: During data access, if some partitions become unreadable due to reasons such as replica migration or network partitioning, DAS automatically retries the request using other available replicas.
Syntax
/*+ USE_DAS ( [ @queryblock ] tablespec ) */
Example
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 ) */
Example
-- 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 to use columnar scanning for the specified table object. If columnar storage is not enabled for the table, this hint is ineffective.
Syntax
/*+ 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 /*+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 scanning for the specified table object.
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 |
+------------------------------------------------------------------+