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

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Fri, 23 Oct 2009 13:42:25 +0200
Message-ID: <cd8f74560910230442k761be156sf600db14031c9153_at_mail.gmail.com>

I have to agree with Christian (and have to admit, I was wrong).

I did some fast tests (11gR1) with 2 nearly identical IOTs: In no test I could avoid the sorts.
even a FK from table 1 to table 2 didn't help. not even a MINUS from the identical table:

2009-10-23 13:37:53 BERX_at_CDB111T11 > select * from t2 minus select * from t2;

no rows selected

Execution Plan

---

Plan hash value: 4168632517

---

| Id  | Operation              | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

---

|   0 | SELECT STATEMENT       |          | 13853 |   730K|       |
216  (52)| 00:00:03 |
|   1 |  MINUS                 |          |       |       |       |
        |          |
|   2 |   SORT UNIQUE          |          | 13853 |   365K|   552K|
108   (4)| 00:00:02 |
|   3 |    INDEX FAST FULL SCAN| PK_2_IOT | 13853 |   365K|       |
13   (8)| 00:00:01 |
|   4 |   SORT UNIQUE          |          | 13853 |   365K|   552K|
108   (4)| 00:00:02 |
|   5 |    INDEX FAST FULL SCAN| PK_2_IOT | 13853 |   365K|       |
13   (8)| 00:00:01 |

---

Statistics

---

         1  recursive calls
         0  db block gets
       134  consistent gets

I'm quite sure now, there is no optimization (in 11gR1 optimizer) to avoid SORTs for set operations.

sorry again for my previous, wrong advise.  Martin

On Fri, Oct 23, 2009 at 08:57, Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:

> 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
Martin Berger           martin.a.berger_at_gmail.com
Lederergasse 27/2/14           +43 660 660 83306
1080 Wien                                       http://berx.at/
Sent from Erfurt, Thüringen, Deutschland
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 23 2009 - 06:42:25 CDT