2011年6月9日 星期四

Global Hint

CREATE OR REPLACE VIEW v AS
SELECT
    e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1,
        ( SELECT *
          FROM employees e3) 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
     AND e1.salary = ( SELECT
                       max(e2.salary)
                       FROM employees e2
                       WHERE e2.department_id = e1.department_id)
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;
By using the global hint structure, you can avoid the modification of view v with the specification of the index hint in the body of view e2. To force the use of the index emp_job_ix for the table e3, you can use one of the following:
SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */  * 
  FROM v;

SELECT /*+ INDEX(@SEL$2 e2.e3 emp_job_ix) */ * 
  FROM v;

SELECT /*+ INDEX(@SEL$3 e3 emp_job_ix) */ * 
  FROM v;

沒有留言:

張貼留言