Oracle Hints

 

Hints

 

Sl. No.	Hint Name	Example	Do/Don’t
1.	FULL	SELECT /*+ FULL(emp) */ * FROM emp;	Do when Table scan is cheaper than index (large result set) Data warehouse / batch queries Index is highly fragmented or low selectivity Don’t do when OLTP queries Highly selective predicates
2.	INDEX	SELECT /*+ INDEX(emp emp_idx1) */ * FROM emp WHERE empno=100;	Do When Optimizer ignores a good index Predicate is highly selective   Don't Do When Index clustering factor is poor Query returns large % of table
3.	INDEX_FFS (Fast Full Scan)	SELECT /*+ INDEX_FFS(emp emp_idx1) */ empno FROM emp;	Do When Index contains all required columns Avoids table access Batch/reporting queries   Don't Do When OLTP row lookup Index selective scan is better
4.	NO_INDEX	SELECT /*+ NO_INDEX(emp emp_idx1) */ * FROM emp;	Do When Bad index is wrongly chosen Index causes excessive random IO
Join Method Hints (HOW tables are joined)       Hint         Best For               Notes ·       USE_NL      Small → Large joins     OLTP ·       USE_HASH    Large ↔ Large           DWH ·       USE_MERGE   Sorted inputs           Rare
5.	USE_NL	SELECT /*+ USE_NL(o c) */ * FROM orders o, customers c WHERE o.cust_id = c.cust_id;	Do When Driving table is small Index exists on joined column   Don't Do When Large datasets No index → CPU disaster
6.	USE_HASH	SELECT /*+ USE_HASH(o c) */ * FROM orders o, customers c;	Do When Large volume joins Data warehouse queries   Don't Do When OLTP Memory constrained systems
7.	LEADING	SELECT /*+ LEADING(o c l) */ ...	Do When Optimizer chooses wrong driving table Join order critical   Don't Do When Tables grow unpredictably
Parallel Execution Hints
8.	PARALLEL	SELECT /*+ PARALLEL(orders 8) */ * FROM orders;	Do When Batch jobs Reporting queries Offline processing   Don't Do When OLTP CPU saturation risk
9.	NOPARALLEL	SELECT /*+ NOPARALLEL */ * FROM orders;	Do When Unexpected parallelism CPU contention scenarios
Subquery & Query Transformation Hints
10.	UNNEST	SELECT /*+ UNEST */ * FROM emp WHERE deptno IN (SELECT deptno FROM dept);	Do When Subquery performs badly Join equivalent is faster
11.	NO_UNNEST	SELECT /*+ NO_UNNEST */ ...	Do When Subquery logic must be preserved Optimizer transforms incorrectly
12.	PUSH_SUBQ		Do When Filter subquery earlier Reduce result set sooner
Aggregation & Grouping
13.	HASH_GROUP_BY	SELECT /*+ HASH_GROUP_BY */ deptno, COUNT(*) FROM emp GROUP BY deptno;	Do When Large aggregations Enough memory available
14.	SORT_GROUP_BY		Do When Small datasets Avoid hash memory usage
Result Cache Hints
15.	RESULT_CACHE	SELECT /*+ RESULT_CACHE */ COUNT(*) FROM country;	Do When Read‑mostly tables Reference data   Don't Do When High DML rate tables
Cursor & Parsing Hints
16.	BIND_AWARE		Do When Data skew exists Bind peeking causes bad plans
17.	CURSOR_SHARING_EXACT		Do When Prevent unwanted cursor sharing SQL stability is critical
Anti‑Hints (Disable Optimizer Features)
18.	NO_MERGE	SELECT /*+ NO_MERGE */ ...	Do When View merge causes bad plans
19.	NO_PARALLEL		Do When Unexpected PX usage
20.	NO_GATHER_OPTIMIZER_STATISTICS		Do When Query stats collection causes overhead

 

 

When SHOULD Use Hints

·       Reproducible bad execution plan

·       Statistics verified as accurate

·       SQL rewrite not feasible

·       Emergency performance fix

·       Plan stability required

 

When You Should NOT Use Hints

·       As first tuning action

·       Without understanding execution plan

·       For dynamic workloads

·       Across application code blindly

·       Instead of fixing SQL design

 

Best‑Practice Strategy (Golden Order)

·       Fix SQL logic

·       Add correct indexes

·       Refresh statistics

·       SQL Profiles

·       SQL Baselines

·       Hints (last resort)

 

 

When to Use Hints

·       Emergency fix

·       Ad‑hoc SQL

·       No control over stats

·       Temporary workaround

 

When to Use SQL Baselines (Recommended)

·       Production applications

·       Long‑term plan stability

·       After tuning complete

·       Upgrade‑safe deployments