Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Using hints in Oracle

Avatar

Avatar
Boost 1
Level 1
Klaster
Level 1

Like

1 like

Total Posts

5 posts

Correct Reply

0 solutions
Top badges earned
Boost 1
View profile

Avatar
Boost 1
Level 1
Klaster
Level 1

Like

1 like

Total Posts

5 posts

Correct Reply

0 solutions
Top badges earned
Boost 1
View profile
Klaster
Level 1

14-11-2019

Good morning,

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

Accepted Solutions (0)

Answers (3)

Answers (3)

Avatar

Avatar
Give Back 10
Employee
craigthonis
Employee

Likes

167 likes

Total Posts

258 posts

Correct Reply

152 solutions
Top badges earned
Give Back 10
Give Back 5
Give Back 3
Give Back
Boost 50
View profile

Avatar
Give Back 10
Employee
craigthonis
Employee

Likes

167 likes

Total Posts

258 posts

Correct Reply

152 solutions
Top badges earned
Give Back 10
Give Back 5
Give Back 3
Give Back
Boost 50
View profile
craigthonis
Employee

18-11-2019

Hello Klaster,

Can you elaborate?  Is Hints something specific in Oracle?

Avatar

Avatar
Give Back 10
Employee
craigthonis
Employee

Likes

167 likes

Total Posts

258 posts

Correct Reply

152 solutions
Top badges earned
Give Back 10
Give Back 5
Give Back 3
Give Back
Boost 50
View profile

Avatar
Give Back 10
Employee
craigthonis
Employee

Likes

167 likes

Total Posts

258 posts

Correct Reply

152 solutions
Top badges earned
Give Back 10
Give Back 5
Give Back 3
Give Back
Boost 50
View profile
craigthonis
Employee

15-06-2020

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

Avatar

Avatar
Boost 1
Level 1
Klaster
Level 1

Like

1 like

Total Posts

5 posts

Correct Reply

0 solutions
Top badges earned
Boost 1
View profile

Avatar
Boost 1
Level 1
Klaster
Level 1

Like

1 like

Total Posts

5 posts

Correct Reply

0 solutions
Top badges earned
Boost 1
View profile
Klaster
Level 1

18-11-2019

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