RE: Execution plan for queries using minus/union/intersect
From: Kenneth Naim <kennaim_at_gmail.com>
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.
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.
Ken
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
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.
-- Regards GBA -- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 22 2009 - 16:06:32 CDT