What are Oracle Hints and How do you use them ?

Oracle optimizer is responsible for handling different situations by making appropriate decisions based on database statistics. It will create route path that is most appropriate to execute the code.

Oracle hints are used when there is a need to instruct or guide the Oracle optimizer to make custom decisions instead of its own decisions which are made based on database statistics.

That means by making use of Hints we are basically giving instruction to the optimizer and telling it which route it should follow to execute the code and thereby affecting the outcome of Oracle optimizer's decision.

Some of the Hint types are as follows:
- Access Hints
- Join Order Hints
- Joint Operations Hints
- Parallel Execution Hints
- Optimization Hints

Jonathan Lewis states that "Hints are interception points in the optimizer code path, and must be obeyed."
and he further adds that "Hint affects the optimizers route through its code path."