Search

Top 60 Oracle Blogs

Recent comments

Illogical Tuning

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