RE: Short-Circuiting a MINUS operation

SQL>l
  1* select /*+ MONITOR opt_param('_convert_set_to_join','true') */ count(*) from (select * from hkc_target_1 minus select * from hkc_source_1)
SQL>/   COUNT(*)

         0

1 row selected.

SQL>SELECT DBMS_SQLTUNE.report_sql_monitor(session_id=>userenv('SID'), type => 'TEXT',report_level=>'ALL') AS report FROM dual;

REPORT

---
------------------------------------------------------------------------
----------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ MONITOR opt_param('_convert_set_to_join','true') */ count(*)
from (select * from hkc_target_1 minus select * from hkc_source_1)

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SQL (6162:1331)
 SQL ID              :  4wunv3g1mwrx2
 SQL Execution ID    :  16777216
 Execution Started   :  04/08/2014 20:48:19
 First Refresh Time  :  04/08/2014 20:48:19
 Last Refresh Time   :  04/08/2014 20:48:19
 Duration            :  .096984s
 Module/Action       :  SQL*Plus/-
 Service             :  HEMANT
 Program             :  sqlplus.exe
 Fetch Calls         :  1

Global Stats
======================================


| Elapsed |   Cpu   | Fetch | Buffer |

| Time(s) | Time(s) | Calls |  Gets  |

======================================


|    0.10 |    0.10 |     1 |  25884 |

======================================

SQL Plan Monitoring Details (Plan Hash Value=803388762)
========================================================================
=====================================================================


| Id |        Operation        |     Name     |  Rows   | Cost |   Time

| Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |

|    |                         |              | (Estim) |      |

Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)
|
========================================================================
=====================================================================


|  0 | SELECT STATEMENT        |              |         |      |

1 |     +0 |     1 |        1 |       |          |                 |


|  1 |   SORT AGGREGATE        |              |       1 |      |

1 |     +0 |     1 |        1 |       |          |                 |


|  2 |    VIEW                 |              |       1 | 6772 |

|        |     1 |          |       |          |                 |

|  3 |     HASH UNIQUE         |              |       1 | 6772 |

|        |     1 |          |       |          |                 |

|  4 |      HASH JOIN ANTI     |              |       1 | 6771 |

|        |     1 |          |  169K |          |                 |

|  5 |       TABLE ACCESS FULL | HKC_TARGET_1 |       1 | 4524 |

|        |     1 |          |       |          |                 |

|  6 |       TABLE ACCESS FULL | HKC_SOURCE_1 |    769K | 2244 |

|        |       |          |       |          |                 |

========================================================================
=====================================================================


1 row selected.

SQL>


(Previously I had this :
Global Stats
=================================================


| Elapsed |   Cpu   |  Other   | Fetch | Buffer |

| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |

=================================================


|    1.95 |    1.93 |     0.01 |     1 |  38610 |

=================================================

SQL Plan Monitoring Details (Plan Hash Value=1765823105)
========================================================================
======================================================================


| Id |        Operation        |     Name     |  Rows   | Cost  |   Time

| Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |

|    |                         |              | (Estim) |       |

Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)
|
========================================================================
======================================================================


|  0 | SELECT STATEMENT        |              |         |       |

1 |     +2 |     1 |        1 |       |          |                 |


|  1 |   SORT AGGREGATE        |              |       1 |       |

1 |     +2 |     1 |        1 |       |          |                 |


|  2 |    VIEW                 |              |       1 | 26541 |

|        |     1 |       |  |          |                 |

|  3 |     MINUS               |              |         |       |

1 |     +2 |     1 |        0 |       |          |                 |


|  4 |      SORT UNIQUE        |              |       1 |  4525 |

|        |     1 |       |  |          |                 |

|  5 |       TABLE ACCESS FULL | HKC_TARGET_1 |       1 |  4524 |

1 |     +0 |     1 |        0 |       |    50.00 | Cpu (1)         |


|  6 |      SORT UNIQUE        |              |    769K | 22016 |

2 |     +1 |     1 |     769K |  129M |    50.00 | Cpu (1)         |


|  7 |       TABLE ACCESS FULL | HKC_SOURCE_1 |    769K |  2244 |

1 |     +2 |     1 |     769K |       |          |                 |
========================================================================
======================================================================
)

Hemant K Chitale


From: Sayan Malakshinov [mailto:xt.and.r_at_gmail.com] 
Sent: Tuesday, April 08, 2014 5:33 PM
To: Chitale, Hemant K
Cc: ORACLE-L
Subject: Re: Short-Circuiting a MINUS operation

I think would be helpful hint: opt_param('_convert_set_to_join','true')
Best regards,
Sayan Malakshinov
Senior Oracle performance tuning engineer
PSBANK
http://orasql.org

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html.
--
http://www.freelists.org/webpage/oracle-l