Short-Circuiting a MINUS operation
- From: "Chitale, Hemant K" <Hemant-K.Chitale@xxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 8 Apr 2014 17:18:32 +0800
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:
