Re: Views inside views, execution plan & external WHERE clause

AFAIK, simple inline views are merged automatically. (Simple views are defined as those not having aggregate operators, rownums, connect-by, analytics, etc).

select * from employees e,

   departments d,
   employee_security es
where e.dept_no=1 and d.dept_no=e.dept_no and d.dept_no=es.dept_no
and employee_no=1

should be as fast/slow as your unmerged query. Could you please confirm or invalidate this?

"Big Bolt" <big_bolt_at_hotmail.com> wrote in message news:3cf9944b.0306301609.6ea9bbf9_at_posting.google.com...
> Here's my situation:
>
>
> Step 1:
> select * from employees
> where
> dept_no=1
> and employee_no=1
> ;
>
> 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 Mon Jun 30 2003 - 19:41:36 CDT