PARALLEL
Scanning full table with parallel degree of 8. Parallel always assumes full table scan.
SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 8 ) */ last_name FROM employees hr_emp;
PARALLEL_INDEX
The PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans for partitioned indexes.
SELECT /*+ PARALLEL_INDEX(table1, index1, 3) */
INDEX
When you specify an INDEX Hint, the optimizer knows that it has to use the INDEX specified in the hint. In this case, the optimizer does not go for a Full Table Scan nor does it use any other index. In addition, it does not calculate the cost of the Index to be used.
If no INDEX hint is specified the optimizer determines the cost of the each index that could be used to access the table and uses the one with the lower cost.
If there are multiple indexes specified with the Hint then the optimizer has to determine the cost of each index to be used with the specified table. Once that is determined, it uses the Index with the lower cost to access the table. In this case, the optimizer does not do a FULL Table Scan. Also note that, the optimizer may choose to use multiple indexes and then merge the result sets to access the table. This method is used if the cost is low.
select /*+ INDEX(emp_city idx_job_code) */ empname, job_code from emp where job_code = 'T';
INDEX_COMBINE
The INDEX_COMBINE hint instructs the optimizer to use a bitmap access path for the table. If indexspec is omitted from the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of indexes has the best cost estimate for the table. If you specify indexspec, then the optimizer tries to use some Boolean combination of the specified indexes. Each parameter serves the same purpose as in "INDEX Hint". For example:
SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *
FROM employees e
WHERE manager_id = 108
OR department_id = 110;
FULL
You can use the FULL hint to bypass the use of the INDEX. For example if you have a table, which is indexed, and the value you are searching for has a large number of duplicates, then you can go in for a Full Table scan. If an index is used, in this case it will be inefficient. Using FULL hint will bypass the index(es).
select /*+ FULL(emp_status) */ empname, status from emp_status where status = 'P';
NO_INDEX
The NO_INDEX hint explicitly specifies which index cannot be used for the specified table.
select /*+ NO_INDEX(emp_status emp_status) */ empname,
status from emp_status where status = ‘P’;
- If this hint specifies single or multiple available index(es), then the optimizer does not consider a scan on these indexes. Other indexes not specified are still considered.
- If this hint specifies no indexes, then the optimizer does not consider a scan on any index on the table. This is the same as a NO_INDEX hint that specifies a list of all available indexes for the table.
*+ ALL_ROWS */
Explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption)
Hints for Join Operations
NO_USE_NL
The no_use_nl hint causes the CBO to exclude nested loops to join each specified table to another row source, using the specified table as the inner table. With this hint, only the hash join and the sort-merge joins will be considered for the specified tables.
SELECT /*+ NO_USE_NL ( employees ) */
NO_USE_MERGE
This hint causes the CBO to exclude sort-merge to join each specified table to another row source, using the specified table as the inner table.
SELECT /*+ NO_USE_MERGE ( employees dept ) */
NO_USE_HASH
This hint causes the CBO to exclude hash joins to join each specified table to another row source, using the specified table as the inner table.
SELECT /*+ NO_USE_HASH ( employees dept ) */ …
NO_INDEX_FFS
This hint causes the CBO to exclude a fast full-index scan of the specified indexes on the specified table.
SELECT /*+ NO_INDEX_FFS ( tablespec indexspec ) */
CARDINALITY
This hint specifies the estimated cardinality returned by a query or portions of the query. Note that if no table is specified, the cardinality is the total number of rows returned by the entire query.
SELECT /*+ CARDINALITY ( [tablespec] card ) */
DATA WAREHOUSE HINTS
STAR_TRANSFORMATION
The STAR_TRANSFORMATION hint instructs the optimizer to use the best plan in which the transformation has been used. Without the hint, the optimizer could make a query optimization decision to use the best plan generated without the transformation, instead of the best plan for the transformed query. For example:
SELECT /*+ STAR_TRANSFORMATION */ *
FROM sales s, times t, products p, channels c
WHERE s.time_id = t.time_id
AND s.prod_id = p.product_id
AND s.channel_id = c.channel_id
AND p.product_status = 'obsolete';
Even if the hint is specified, there is no guarantee that the transformation will take place. The optimizer generates the subqueries only if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.
UNDOCUMENTED HINTS
CPU_COSTING
This hint turns CPU costing on for the SQL statement. This is the default cost model for the optimizer. The optimizer estimates the number and type of IO operations and the number of CPU cycles the database will perform during execution of the given query. It uses system statistics to convert the number of CPU cycles and number of IO(s) to the estimated query execution time. The CPU_COST column of the PLAN_TABLE stores the CPU cost.
SELECT /*+ CPU_COSTING (tablespec indexspec) */
NO_CPU_COSTING
This hint turns off CPU costing for the SQL statement. CBO then uses the IO cost model, which measures everything in single-block reads and ignores CPU cost.
SELECT /*+ NO_CPU_COSTING */
Mohib Alvi 