code_junkie

Will Oracle optimizer use multiple Hints in the same SELECT?

Nov 13th, 2011
103
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.68 KB | None | 0 0
  1. SELECT /*+ INDEX(i dcf_vol_prospect_ids_idx)*/
  2. /*+ LEADING(i vol) */
  3. /*+ ALL_ROWS */
  4. i.id_number,
  5. ...
  6. FROM i_table i
  7. JOIN vol_table vol on vol.id_number = i.id_number
  8. JOIN to_a_bunch_of_other_tables...
  9. WHERE i.solicitor_id = '123'
  10. AND vol.solicitable_ind = 1;
  11.  
  12. SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk)
  13. USE_MERGE(j) FULL(j) */
  14. e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
  15. FROM employees e1, employees e2, job_history j
  16. WHERE e1.employee_id = e2.manager_id
  17. AND e1.employee_id = j.employee_id
  18. AND e1.hire_date = j.start_date
  19. GROUP BY e1.first_name, e1.last_name, j.job_id ORDER BY total_sal;
Add Comment
Please, Sign In to add comment