What are hints in Oracle? Have you used any?

In Oracle, hints are special instructions embedded in SQL queries that guide the optimizer on how to execute the query more efficiently. They influence the choice of execution plan, such as specifying which index to use, what type of join to apply, etc.

Examples of commonly used hints:

• /*+ INDEX(table_name index_name) */ — forces the use of a specified index.
• /*+ FULL(table_name) */ — enforces a full table scan.
• /*+ NO_MERGE */ — prevents merging of subqueries.
• /*+ LEADING(table1 table2) */ — sets the join order of tables.

Example of usage:

SELECT /*+ INDEX(emp emp_dept_idx) */ * FROM emp WHERE dept_id = 10;

Here, the optimizer will try to use the emp_dept_idx index for the emp table.

I used hints to optimize complex queries when the standard plan was inefficient, especially in reports and when working with large volumes of data.