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

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_at_ywho.com> wrote in message news:<vZEMa.5$eD3.142_at_news.oracle.com>...
> "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 Tue Jul 08 2003 - 02:00:24 CDT