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):

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:

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 the funny little side-swap the optimizer does two index fast full scans and a right hash anti-join; with the more complex (intellectually challenging) rewrite the optimizer does two index fast full scans and two right hash anti-joins. It’s too late now to go back to the client and test it – 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).

2 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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,173 other followers