Re: Execution plan for queries using minus/union/intersect
Date: Fri, 23 Oct 2009 08:44:06 +0200
Message-ID: <cd8f74560910222344madaac47lbc32794955c81d11_at_mail.gmail.com>
Oracle needs this sort unique step to compare the 2 sets and eliminate some rows (according to your operation).
If you do not want Oracle to do this sort during your operation, you have to provide the information pre-sorted, and make sure it accesses the data using this method, otherwise Oracle will have to sort again.
In your case, you can have an index on t1 (a, b, c) and another on t2 (a, b, c). Or you can create the 2 tables as index organized tables with the same rows as keys in the same order).
Even never tried, putting the tables into the same cluster might be worth trying.
For a simple operation of 2 tables, there might be various methods, if you have to handle 2 result-sets of sub queries, it will become more complex. Also other optimizations like partitions or parallel query will influence your attempts.
hth
Martin
On Thu, Oct 22, 2009 at 22:49, GBA-DBA <gba.oraclel_at_gmail.com> wrote:
> 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.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 23 2009 - 01:44:06 CDT