Expand my Community achievements bar.

Announcing the launch of new sub-community for Campaign Web UI to cater specifically to the needs of Campaign Web UI users!

Using hints in Oracle

Avatar

Level 2

Good morning,

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

3 Replies

Avatar

Employee Advisor

Hello Klaster,

Can you elaborate?  Is Hints something specific in Oracle?

Avatar

Level 2

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

Avatar

Employee Advisor

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