Re: Short-Circuiting a MINUS operation
- From: Sidney <huanshengchen@xxxxxxxxx>
- To: "vijaysehgal21@xxxxxxxxx" <vijaysehgal21@xxxxxxxxx>
- Date: Thu, 10 Apr 2014 21:25:21 +0800
http://jonathanlewis.wordpress.com/2014/02/28/empty-hash/
And check this blog also.
--
Sidney
> 在 2014年4月8日，下午9:00，vijay sehgal <vijaysehgal21@xxxxxxxxx> 写道：
>
> 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
>
Other related posts:
