Posted by Serge Rielau on 02/03/06 22:29

Alexander Kuznetsov wrote:
>>why would you not want to macro-ize common combinations of joins you do over and over again?
>
>
> Serge,
>
> In certain cases (not too often) I would rather not deal with a view,
> for instance:
> let's say you have a rather complex frequently used query, much more
> complex than a simple 5 way join. You wrap it up as a view, and try to
> use it like this:
>
> select <columns> from my_view
> where <a very selective SARGEable criteria on an indexed column in
> table1>
>
> It runs slowly, so you look at the plan and see that that very
> selective criteria on an indexed column is not applied first. You try
> to push the condition down manually, instead of
>
> select ... from(
> select ...
> from table1
> ...)
> where <a very selective SARGEable criteria on an indexed column in
> table1>
>
> you write
>
> select ...
> from (select ... from table1
> where <a very selective SARGEable criteria on an indexed column in
> table1>
> ) table1
> ...
>
> and it runs fast. I would not say that's a very common scenario, but it
> does happen from time to time.
>
That is hard to imagine to happen on newer releases of the major
commercial DBMS'. Either way throwing out views due to an isolated bad
plan is throwing out the baby with the bathwater.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Back to original message]