Views inside views, execution plan & external WHERE clause

Step 1:
select * from employees
where
  dept_no=1
  and employee_no=1
;

[Quoted] works pretty fast, since it falls on fast index

Step 2:
create or replace view view_employees as select * from employees
where
  dept_no=1
;

select * from view_employees
where employee_no=1
;

catches the same execution plan and rocks

Step3:
create or replace view view_complex_employee as select * from
  (
  select * from employees
  where dept_no=1
  ) s1,
  (
  select * from departments d, employee_security es   where dept_no=1
    and d.dept_no=es.dept_no
  ) s2
where
  s1.dept_no=s2.dept_no
;

select * from view_complex_employee
where employee_id=1
;

In the last case execution plan changes and the response time goes down, since internal views do not sense the incoming employee_id from a wrapping query.

Is there any way to affect the execution plan of inline views by making them take into account that external where clause? The actual queries are way more complex, I used the employee case as a rudimentary example. This means that the main concept of having a massive join with a bunch of inline views and a wrapper will remain.

I tried some new hints in 8i, but to no avail yet. Received on Tue Jul 01 2003 - 02:09:29 CEST