Re: Short-Circuiting a MINUS operation

From: Sidney <huanshengchen_at_gmail.com>
Date: Thu, 10 Apr 2014 21:25:21 +0800
Message-Id: <94F3E4FD-73B2-480B-A5EF-EC09D2391D8F_at_gmail.com>



http://jonathanlewis.wordpress.com/2014/02/28/empty-hash/

And check this blog also.

--
Sidney

> 在 2014年4月8日,下午9:00,vijay sehgal <vijaysehgal21_at_gmail.com> 写道:
> 
> 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_at_sc.com> 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.
>> --
>> http://www.freelists.org/webpage/oracle-l
> -- http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 10 2014 - 15:25:21 CEST

Original text of this message