oracle hint over several joins with different methods

Question

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

Answer 1

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.