0

I'm trying to force the oracle database 11g express edition to make the JOINs with two specific methods in a two JOINs SELECT, but it's not working when I try to do it with the USE_MERGE and the USE_NL hints. My SELECT looks like this:

SELECT /*+ ORDERED USE_MERGE(sp) USE_NL(p) FULL(s) FULL(sp) FULL(p) */ DISTINCT s.*
FROM s, sp, p
WHERE s.sn = sp.sn AND
              p.pn = sp.pn AND
              color = 'Rojo';

but the actual explain plan that I'm getting is this one:

explain plan that I'm getting from oracle

I'm really desperate with this and I've been looking at the great oracle documentation but I'm still unable to achieve it. There's this example that looks quite similar but I don't know why's not working with the combination that I'm trying. The next code is the example provided from Oracle:

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;

Thanks in advance for your help :D

Juan Diego Merino Roldán
  • 1
  • For start you should change your conditions to the proper INNER JOIN syntax since oracle recommend it: https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm – Jorge Campos Nov 20 '15 at 17:39
  • by the way, that implicit joins (having two tables in the from clause) is a deprecated syntax, and it's recommended to switch to the modern, explicit, syntax: Learn how to use explicit join syntax. Aaron Bertrand did some [**writting**](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) about it – Juan Carlos Oropeza Nov 20 '15 at 17:41
  • Yes, I know it should not be written that way but I'm forced to it in the requirements, thanks for the advice anyway :D – Juan Diego Merino Roldán Nov 21 '15 at 16:56

1 Answers1

0

Use USE_MERGE(s sp) instead of USE_MERGE(sp). But I can't explain why that works. Hints are tricky, it's best to avoid them as much as possible.

Here's a SQL Fiddle demonstrating the new hint working.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132