Using hints in Oracle

[Klaster]

Good morning,

Is possible to use hints for queries generated ? For example those that change join path order.

[Craig_Thonis]

Hello Klaster,

Can you elaborate?  Is Hints something specific in Oracle?

[Klaster]

Probably yes,

It looks like comment - it informs database how process query

for ex.

SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk)

  USE_MERGE(j) FULL(j) */

  e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal

  FROM employees e1, employees e2, job_history j

  WHERE e1.employee_id = e2.manager_id

  AND e1.employee_id = j.employee_id

  AND e1.hire_date = j.start_date

  GROUP BY e1.first_name, e1.last_name, j.job_id

  ORDER BY total_sal;

LEADING - about join order

USE_NL - use nested loops algorithm for ....

INDEX - use index ......

USE_MERGE - merge join algorithm

FULL(j) - full table scan of j

[Craig_Thonis]

Hi Klaster,

I don't see anything on my end where hints is supported.  If it can be deployed to function automatically at the backend DB level then maybe it's still doable, but that said it's not going to reflect back in Campaign so if Hints changes the query and breaks something the only way that you'll be able to troubleshoot would be to review the executed SQL from the DB side directly and making changes on the application side may not have any impact if Oracle is making it's own changes on the fly.

So I'd advise against using Hints personally.

Regards,

Craig