Re: Short-Circuiting a MINUS operation
- From: vijay sehgal <vijaysehgal21@xxxxxxxxx>
- To: Hemant-K.Chitale@xxxxxx
- Date: Tue, 8 Apr 2014 18:30:25 +0530
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:
