Views inside views, execution plan & external WHERE clause

[Big Bolt]

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.

[Mikito Harakiri]

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@hotmail.com> wrote in message
news:3cf9944b.0306301609.6ea9bbf9@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.

[Mikito Harakiri]

"Jan" <janik@pobox.sk> wrote in message
news:81511301.0307010432.6bf03a85@posting.google.com... ... 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

Except that:
1. Potential SQL<-->PLSQL context switch per each tuple execution.
2. Optimizer not being able to combine 2 executions together and produce a
good plan.
3. People looking into the code and asking: who designed this mess?

[Big Bolt]

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@pobox.sk (Jan) wrote in message news:<81511301.0307010432.6bf03a85@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

[Jan]

I re-read the original query again and I see that in this example is
not good for using my aproach. Yes, the SELECT is flat, there will be
no benefit from using it, but it works fine (benefit is bigger) for
the SELECT with multi nested subqueries.

Sorry, the link which I had mentioned is pointed to your last
statment:

"2. Optimizer not being able to combine 2 executions together and
produce
a good plan."

In this case, the function is not that one with doing expensive scans
on multi-rows table or called per each row, but there will be just one
touch of DUAL for the whole query (see mentioned link).

jan



"Mikito Harakiri" <mikharakiri@ywho.com> wrote in message news:<vZEMa.5$eD3.142@news.oracle.com>... "Jan" <janik@pobox.sk> wrote in message news:81511301.0307012337.7ab61684@posting.google.com... 1. Just ONE. because there is: SELECT func FROM DUAL Tuple iteration semantics evaluates subquery once per each row in the outer query. see that your understanding is wrong e.g. on: http://asktom.oracle.com/pls/ask/f?p=4950:8:380014022855969596::NO::F4950_P8 _DISPLAYID,F4950_P8_CRITERIA:9808349898392, You can also use SYS_CONTEXT instead of Your_Function. Which of the examples demonstrates that nested subquery is superior to the flat select-project-join form? 2. Why? This I didn`t test, please show us. BTW, if that is true It will be still better since my approach is doing: Filter comlex data - the first, Joins of single result - the second In general, you might have a point, since there actually is optimization called "magic" that pushes idea of applying predicates as early as possible to the estreme. Magic optimization is not implemented by oracle. 2. Optimizer not being able to combine 2 executions together and produce a good plan. Here I was referring to the optimizer not beeng able to cross PL/SQL function call boundary. For example, you can write a PL/SQL function doing scan of EMP table where deptno is corellated to the function argument and call it per each row during DEPT table scan and optimizer would never be able to figure out that the whole thing logically is just a join.