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

That's an interesting idea.
Not that easy to enforce in the front-end code though.

BTW, I found a mistake in the query I posted - forgot that there's an outer-join in the view definition.
If I use REGULAR JOIN, the execution plan does actually acknowledge a parameter from the wrapping WHERE clause. If I use OUTER JOIN in the view, it stops doing that and execution plan collapses, while with the original view's SQL (with employee WHERE clause specified explicitly inside nested views) it runs well.

Is it an Oracle's limitation?

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

select * from view_complex_employee
where employee_id=1
;

janik_at_pobox.sk (Jan) wrote in message news:<81511301.0307010432.6bf03a85_at_posting.google.com>...
> You want to:
> 1) Filter complex data
> 2) Join single results
>
> and it does:
>
> 1) Join complex data
> 2) Filter complex result
>
> You can create a package with some global variable, e.g.:
>
> gv_employee_id NUMBER
>
> and Function
>
> Get_Employee_ID
> Return gv_employe_id;
>
> and a procedure
>
> Set_Employee_ID (p_value IN NUMBER)
>
> In your query, view, you could have a filter in the "deepest" WHERE:
>
> ...
> WHERE employe_id=(SELECT My_Package.Get_Employee_ID FROM DUAL)
>
> Before you execute the query you just set employee id via
>
> Set_Employee_ID
>
> so it does what you want:
>
> 1) Filter complex data
> 2) Join single results
>
> Jan
Received on Tue Jul 01 2003 - 21:43:56 CEST