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

[Quoted] "Jan" <janik_at_pobox.sk> wrote in message news:81511301.0307012337.7ab61684_at_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. Received on Wed Jul 02 2003 - 19:44:58 CEST