If there is a query that is actually composed of a series of
selects that are grouped together with UNIONS and within each
major select there is a subquery something like this:

SELECT DISTINCT EMPLOYEEID FROM EMPLOYEE_ADDR

would that clause be executed separately with each UNION part or
would it be executed just a single time and the result would be
passed to each part of the union?

I hope it is the latter.

Any comments?

I think seperatly wich each union
maby you can use such like

Select distinct a
from (select distinct a
from b
union
select distinct a
from c
)


"kstahl" <ktsahl (AT) yahoo (DOT) com> wrote

Sjoerd Bosma wrote:
Oh well. I wish it could have been otherwise. The code I'm
working with is horribly written but I'm really not at liberty to
re-write it at this point. What seems to be obvious is that the
author has primary experience with MSSQL and decided to write an
Oracle stored procedure in the same manner as SQL programmers are
accustomed to writing and I suspect that there may either be at
least subtle, if not obvious, differences in the manner that the
optimizers handle such things

You can enclose union - query in in-line view
and than join it with the table which is selected distinctly.
This makes the optimizer to scan the distincted table once and
combine two row sources vie hash join:

select *
from
(
select *
from t t1
where f = 1

union

select *
from t t2
where f = 2
) v, (select distinct id from employee) e
where v.cd_a = e.id

Execution plan:

SELECT STATEMENT, GOAL = CHOOSE
HASH JOIN
VIEW
SORT UNIQUE
TABLE ACCESS FULL EMPLOYEE
VIEW
SORT UNIQUE
UNION-ALL
TABLE ACCESS FULL T
TABLE ACCESS FULL T