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

From: Kenneth Naim <>
Date: Thu, 22 Oct 2009 17:06:32 -0400
Message-ID: <006801ca535b$893a0210$9bae0630$_at_com>

For union, there is union all that eliminates the sort but the results will contain duplicates if they are in the data. Oracle in general will not perform a sort if it knows it isn't required as in the case of using a unique index, but if it is using a full scan it is still faster to do the full scan and sort than retrieving all the rows via an index.  


From: [] On Behalf Of GBA-DBA
Sent: Thursday, October 22, 2009 4:49 PM To: Oracle Discussion List
Subject: Execution plan for queries using minus/union/intersect  

Hello list,  

Let's say I have select a, b, c from t1 (union/minus/intersect) select a, b , c from t2  

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

A unique index maybe or by having the tables sorted by the columns used in the select statement?  

I don't have a database around to test it, that's why I'm asking.

Thanks for your help.


Received on Thu Oct 22 2009 - 16:06:32 CDT

Original text of this message