RE: Execution plan for queries using minus/union/intersect

> Is there any way to avoid the sort unique step (for each table) that
> takes place before the execution of the set operation?

For INTERSECT and MINUS operations I don't see how it would be possible to execute them without having two (or more) set of data sorted in exactly the same way. Hence, it's not possible to avoid that. The only special case would be when every set of data is based on the same tables accessed by *exactly* the same operations. In such a case, MINUS would return no rows and INTERSECT all rows from the first set. But, in practice, this is not something relevant... Probably for this reason the query optimizer does not recognize these two cases.

For UNION operations the SORT UNIQUE step takes place *after* the set operation, not *before*. That said, I also do not see a way to avoid it (except than specifying UNION ALL, of course...).

HTH
Chris Antognini

Troubleshooting Oracle Performance, Apress 2008 http://top.antognini.ch

--
http://www.freelists.org/webpage/oracle-l