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;
沒有留言:
張貼留言