RE: Execution plan for queries using minus/union/intersect
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: "GBA-DBA" <gba.oraclel@xxxxxxxxx>
- Date: Fri, 23 Oct 2009 08:57:30 +0200
Hi
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
