Hints related to access path are INDEX, FULL, USE_COLUMN_TABLE, and NO_USE_COLUMN_TABLE.
INDEX hint
The INDEX hint instructs the optimizer to use an index scan for the specified table. You can use INDEX hints for indexes that are based on functions, domains, B-trees, bitmaps, or bitmap joins.
The syntax of the INDEX hint is as follows:
/*+ INDEX ( [ @queryblock ] tablespec [ indexspec [ indexspec ]... ] ) */
The behavior of a hint depends on the indexspec specification:
If an
INDEXhint specifies a single available index, the database performs a scan on this index. The optimizer does not consider a full table scan or a scan on another index on the table.If an
INDEXhint specifies a list of available indexes, the optimizer considers the cost of a scan on each index in the list and then performs an index scan with the lowest cost. The database chooses to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The database does not perform a full table scan or a scan on an index that is not in the list.If an
INDEXhint does not specify a specific index, the optimizer considers the cost of a scan on each available index on the table and then performs an index scan with the lowest cost. The database chooses to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.
Here is an example of the INDEX hint:
SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id
FROM employees
WHERE department_id > 50;
FULL hint
The FULL hint instructs the optimizer to perform a full table scan for the specified table.
The syntax of the FULL hint is as follows:
/*+ FULL ( [ @queryblock ] tablespec ) */
Here is an example of the FULL hint:
SELECT /*+ FULL(e) */ employee_id, last_name
FROM hr.employees e
WHERE last_name LIKE :al;
In the preceding example, the database performs a full table scan on the employees table to execute this statement, even if an index is defined on the last_name column that is made available by the condition in the WHERE clause.
In the FROM clause, the EMPLOYEES table has an alias e, so the hint must refer to the table by its alias instead of its name. Do not refer to schema names in the hint even if they are specified in the FROM clause.
USE_COLUMN_TABLE
The USE_COLUMN_TABLE hint specifies to perform columnar scan for the specified table object. The USE_COLUMN_TABLE hint is invalid for a table for which columnar storage is not enabled.
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
The NO_USE_COLUMN_TABLE hint specifies to forbid columnar scan for the specified 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 |
+------------------------------------------------------------------+