Oracle Scratchpad

June 28, 2013

Illogical Tuning

Filed under: CBO,Execution plans,Oracle,subqueries — Jonathan Lewis @ 6:55 pm BST Jun 28,2013

The title is a bit of a joke, really. It’s mirroring a title I used a little over a year ago “Logical Tuning” and reflects my surprise that a silly little trick that I tried actually worked.

If you don’t want to read the original article, here’s a quick précis – I started with the first query, which the optimizer executed as a filter subquery, and rewrote it as the second query, which the optimizer executed as two anti-joins (reducing the execution time from 95 seconds to 27 seconds):

rem
rem     Script:         not_exists_or.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2012
rem 

select
        *
from
        t2
where
        not exists (
                select
                        null
                from    t3
                where   t3.n3a = t2.n2a
                and     t3.n3b = t2.n2b
                and     t3.id3 in (t2.id2, t2.id2 + 1000)
        )
;

select
        *
from
        t2
where
        not exists (
                select  null
                from    t3
                where   t3.n3a = t2.n2a
                and     t3.n3b = t2.n2b
                and     t3.id3 = t2.id2
        )
and     not exists (
                select  null
                from    t3
                where   t3.n3a = t2.n2a
                and     t3.n3b = t2.n2b
                and     t3.id3 = t2.id2 + 1000
        )
;



One of the little steps I’d written en route to the solution was simply to change the IN LIST to an OR list – though I hadn’t really expected that to make any difference since that’s essentially what Oracle tends to do internally anyway:

select
        *
from
        t2
where
        not exists (
                select
                        null
                from    t3
                where   t3.n3a = t2.n2a
                and     t3.n3b = t2.n2b
                and     (
                            t3.id3 = t2.id2
                         or t3.id3 = t2.id2 + 1000
                        )
        )
;

I’d played around with that, fiddling with the subquery on its own for a bit, and in the course of doing this I’d switch sides on the +1000, ultimately producing the following:


select
        *
from
        t2
where
        not exists (
                select  null
                from    t3
                where   t3.n3a = t2.n2a
                and     t3.n3b = t2.n2b
                and     (
                            t3.id3 = t2.id2
                         or t3.id3 - 1000  = t2.id2
                        )
        )
;

And here’s the execution plan after that little switch:

-------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |  5000 |   776K|    33 |
|*  1 |  HASH JOIN RIGHT ANTI   |         |  5000 |   776K|    33 |
|   2 |   VIEW                  | VW_SQ_1 | 10000 |   380K|     8 |
|   3 |    UNION-ALL            |         |       |       |       |
|   4 |     INDEX FAST FULL SCAN| T3_PK   |  5000 | 60000 |     4 |
|   5 |     INDEX FAST FULL SCAN| T3_PK   |  5000 | 60000 |     4 |
|   6 |   TABLE ACCESS FULL     | T2      |  5000 |   585K|    15 |
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("VW_COL_1"="T2"."ID2" AND "VW_COL_2"="T2"."N2A" AND
              "VW_COL_3"="T2"."N2B")

Compare this plan with the plan I had been hoping to get (and which – allowing for the other bits of the client query – I had achieved on the client site):


----------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost  |
----------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |    50 |  7200 |    29 |
|*  1 |  HASH JOIN RIGHT ANTI  |       |    50 |  7200 |    29 |
|   2 |   INDEX FAST FULL SCAN | T3_PK |  5000 | 60000 |     4 |
|*  3 |   HASH JOIN RIGHT ANTI |       |  4999 |   644K|    22 |
|   4 |    INDEX FAST FULL SCAN| T3_PK |  5000 | 60000 |     4 |
|   5 |    TABLE ACCESS FULL   | T2    |  5000 |   585K|    15 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T3"."N3A"="T2"."N2A" AND "T3"."N3B"="T2"."N2B" AND
              "T3"."ID3"="T2"."ID2")
   3 - access("T3"."N3A"="T2"."N2A" AND "T3"."N3B"="T2"."N2B" AND
              "T3"."ID3"="T2"."ID2"+1000)

With my funny little side-swap in the predicate the optimizer did two index fast full scans and a right hash anti-join; with the more complex (intellectually challenging) rewrite that I produced at the client site the optimizer did two index fast full scans and two right hash anti-joins. It’s too late now to go back to the client and test the effect of changing the predicate on the data – but the quick trick looks as if it would have been even better than the clever rewrite. (On the other hand, this is running on 11.2.0.3, and the client was on 11.2.0.2 so maybe it wouldn’t have worked – it doesn’t work on 11.1.0.7 or 10.2.0.5).

3 Comments »

  1. Hello Jonathan

    On the other hand, this is running on 11.2.0.3, and the client was on 11.2.0.2 so maybe it wouldn’t have worked

    yes it most likely wouldn’t have worked. This is controlled with _optimizer_unnest_disjunctive_subq & is available since 11.2.0.3 (and could cause a side effect as usual).

    Comment by Timur Akhmadeev — July 7, 2013 @ 6:27 pm BST Jul 7,2013 | Reply

  2. […] A couple of fast full scans of the primary key might not be very efficient, but it still might be more efficient than running with a filter subquery. I’d written a note about this change under the whimsically related title of Illogical Tuning. […]

    Pingback by Logical tuning | Oracle Scratchpad — June 10, 2020 @ 10:47 am BST Jun 10,2020 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.