What are hints and which specific hints have you used?

Hints in databases are special instructions that a developer or administrator can add to an SQL query to influence the execution plan, bypassing or adjusting the optimizer's work. They help improve performance if the optimizer chooses a suboptimal plan.

Examples of commonly used hints:

• In Oracle: /*+ INDEX(table_name index_name) */ — forces the use of a specific index.
• In SQL Server: WITH (NOLOCK) — allows reading data without locks.
• In MySQL: USE INDEX (index_name) — specifies which index to use.

Example in Oracle:

SELECT /*+ INDEX(employees emp_dept_idx) */ * FROM employees WHERE department_id = 10;

Here, we suggest to the optimizer to use the emp_dept_idx index for the employees table.

Using hints requires understanding data structure and optimizer behavior, as incorrect application can degrade performance.