Oracle Scratchpad

February 5, 2014

Minus

Filed under: Execution plans,Oracle,Troubleshooting,Tuning — Jonathan Lewis @ 5:42 pm BST Feb 5,2014

Here’s a little script to demonstrate an observation about a missed opportunity for avoiding work that appeared in my email this morning (that’s morning Denver time):

create table t1
as
select * from all_objects where rownum = 1;

delete from t1;
commit;

create table t2
as
select * from all_objects where rownum <= 100000;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		method_opt	 => 'for all columns size 1'
	);

	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T2',
		method_opt	 => 'for all columns size 1'
	);
end;
/

alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';

prompt  ======================
prompt  And now the test query
prompt  ======================

select * from t1
minus
select * from t2
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
alter session set events '10046 trace name context off';

Clearly the first query block in the test query will return no rows, and since the MINUS operator returns rows from the first result set that do not appear in the second result set there is no need for Oracle to run the second query block. Well, guess what …

The ‘create where rownum = 1′ followed by ‘delete’ is a lazy workaround to avoid side effects of deferred segment creation so that you can run the script on any (recent) version of Oracle. The flush, combined with 10046 trace, allowed me to see waits that showed which objects Oracle scanned and when, and the display_cursor() was just icing on the cake.

I’ve checked 11.2.0.4 and 12.1.0.1, and both of them scan t1 first and then scan t2 unnecessarily.

This surprised me slightly given how smart the optimizer can be, but I guess it’s one of those boundary cases where the optimizer has just one strategy for an entire class of queries. I couldn’t think of any “legal” way to control the effect, but here’s the first dirty trick that came to my mind. If you’re sure that the first subquery is going to be cheap and you’re worried that the second subquery is expensive, you could do the following:

select v2.*
from
	(select * from t1 where rownum = 1)	v1,
	(
		select * from t1
		minus
		select * from t2
	)	v2
;

Introduce a spurious query to return one row from the first subquery and join it do the MINUS query. If the inline view doesn’t return any rows Oracle short-circuits the join, as shown by the following execution path with stats:

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      0 |00:00:00.01 |       2 |     32 |       |       |          |
|   1 |  MERGE JOIN CARTESIAN  |      |      1 |      1 |      0 |00:00:00.01 |       2 |     32 |       |       |          |
|   2 |   VIEW                 |      |      1 |      1 |      0 |00:00:00.01 |       2 |     32 |       |       |          |
|*  3 |    COUNT STOPKEY       |      |      1 |        |      0 |00:00:00.01 |       2 |     32 |       |       |          |
|   4 |     TABLE ACCESS FULL  | T1   |      1 |      1 |      0 |00:00:00.01 |       2 |     32 |       |       |          |
|   5 |   BUFFER SORT          |      |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 73728 | 73728 |          |
|   6 |    VIEW                |      |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   7 |     MINUS              |      |      0 |        |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   8 |      SORT UNIQUE       |      |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 73728 | 73728 |          |
|   9 |       TABLE ACCESS FULL| T1   |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  10 |      SORT UNIQUE       |      |      0 |  70096 |      0 |00:00:00.01 |       0 |      0 | 73728 | 73728 |          |
|  11 |       TABLE ACCESS FULL| T2   |      0 |  70096 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(ROWNUM=1)

The only thing to watch out for is that the “rownum = 1″ doesn’t make the optimizer switch to an unsuitable “first_rows(1)” execution plan.

5 Comments »

  1. the same holds for “intersect”
    replace all occurrencies of “minus” by “intersect” in your article and it’s also true

    Comment by Matthias Rogel — February 5, 2014 @ 7:26 pm BST Feb 5,2014 | Reply

  2. the second trick would probably be

    select * from (select * from t1 minus select * from t2) where exists(select null from t1);
    

    ?
    ( avoiding probable first_rows-side-effects )

    Comment by Matthias Rogel — February 5, 2014 @ 7:41 pm BST Feb 5,2014 | Reply

  3. Jonathan,

    I think it’s just yet another case when set-to-join conversions are more preferrable:

     
    select--+ opt_param('_convert_set_to_join' 'true')
    gather_plan_statistics  * from t1 minus select * from t2
    
    Plan hash value: 2700174625
    
    ----------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
    ----------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |      1 |        |       |   229 (100)|          |      0 |00:00:00.02 |       2 |      2 |       |       |          |
    |   1 |  HASH UNIQUE        |      |      1 |      1 |   249 |   229   (8)| 00:00:01 |      0 |00:00:00.02 |       2 |      2 |   709K|   709K|          |
    |*  2 |   HASH JOIN ANTI    |      |      1 |      1 |   249 |   228   (8)| 00:00:01 |      0 |00:00:00.02 |       2 |      2 |   720K|   720K|51200  (0)|
    |   3 |    TABLE ACCESS FULL| T1   |      1 |      1 |   158 |     3   (0)| 00:00:01 |      0 |00:00:00.02 |       2 |      2 |       |       |          |
    |   4 |    TABLE ACCESS FULL| T2   |      0 |  69800 |  6202K|   222   (7)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
    ----------------------------------------------------------------------------------------------------------------------------------------------------------
    

    Comment by Sayan Malakshinov — February 12, 2014 @ 2:01 pm BST Feb 12,2014 | Reply

  4. […] little while ago I highlighted a special case with the MINUS operator (that one of the commentators extended to include the INTERSECT operator) relating to the way the […]

    Pingback by Empty Hash | Oracle Scratchpad — February 28, 2014 @ 6:45 pm BST Feb 28,2014 | 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,090 other followers