Posted by Alexander Kuznetsov on 02/03/06 20:52

> 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.

[Back to original message]