Heman, Jonathan Lewis had an article on this, below URL for your reference. http://jonathanlewis.wordpress.com/2014/02/05/minus/ Warm Regards, Vijay Sehgal. On Tue, Apr 8, 2014 at 2:48 PM, Chitale, Hemant K <Hemant-K.Chitale@xxxxxx>wrote: > > 11.2.0.3 > > In this scenario the HKC_TARGET_1 table has 0 rows and the HKC_SOURCE_1 > table has 769K rows. > If I do a (HKC_TARGET_1 minus HKC_SOURCE_1) Oracle reads all the rows > from HKC_SOURCE_1 inspite of the prior operation reading HKC_TARGET_1 > returning 0 rows. > I understand the optimizer cannot "stop" the query against HKC_SOURCE_1 > because the Execution Plan has already been determined. > > I have been asked "Would Adaptive Execution be able to handle this in > 12c ? " I believe that it is only about Hash versus Nested Loop Join > and cannot short-circuit such a query. > > SQL>select count(*) from hkc_target_1; > > COUNT(*) > ---------- > 0 > > 1 row selected. > > SQL>select count(*) from hkc_source_1; > > COUNT(*) > ---------- > 768863 > > 1 row selected. > > SQL>select /*+ MONITOR */ count(*) from (select * from hkc_target_1 > minus select * from hkc_source_1); > > 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 */ count(*) from (select * from hkc_target_1 minus > select * from hkc_source_1) > > Global Information > ------------------------------ > Status : DONE (ALL ROWS) > Instance ID : 1 > Session : SQL (1439:3467) > SQL ID : 0tcntyk1sg371 > SQL Execution ID : 16777217 > Execution Started : 04/08/2014 17:07:52 > First Refresh Time : 04/08/2014 17:07:52 > Last Refresh Time : 04/08/2014 17:07:54 > Duration : 2s > Module/Action : SQL*Plus/- > Service : HEMANT > Program : sqlplus.exe > Fetch Calls : 1 > > 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 | | | | > ======================================================================== > ====================================================================== > > > 1 row selected. > > SQL> > > > Hemant K Chitale > > > 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 > . > -- > //www.freelists.org/webpage/oracle-l > > >