RE: Minus conversion into antijoin
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "malakshinovss@xxxxxxxxx" <malakshinovss@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 7 Jun 2013 07:37:45 +0000
It needs a hidden parameter set to true to work by default.
Sample code, using the standard scott emp and dept tables:
set autotrace traceonly explain
prompt ========
prompt Baseline
prompt ========
select
deptno
from dept
minus
select
deptno
from emp
;
prompt ======
prompt Hinted
prompt ======
select
/*+ set_to_join(@set$1) */
deptno
from dept
minus
select
deptno
from emp
/
alter session set "_convert_set_to_join" = true;
prompt ================
prompt Hidden parameter
prompt ================
select
/* unhinted */
deptno
from dept
minus
select
deptno
from emp
;
alter session set "_convert_set_to_join" = false;
set autotrace off
Output (trimmed):
========
Baseline
========
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 54 | 51 |
| 1 | MINUS | | | | |
| 2 | SORT UNIQUE NOSORT| | 4 | 12 | 25 |
| 3 | INDEX FULL SCAN | DEPT_PK | 4 | 12 | 1 |
| 4 | SORT UNIQUE | | 14 | 42 | 26 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 42 | 2 |
---------------------------------------------------------------
======
Hinted
======
--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 4 |
|* 1 | HASH JOIN ANTI | | 1 | 6 | 4 |
| 2 | INDEX FULL SCAN | DEPT_PK | 4 | 12 | 1 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 2 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
================
Hidden parameter
================
--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 4 |
|* 1 | HASH JOIN ANTI | | 1 | 6 | 4 |
| 2 | INDEX FULL SCAN | DEPT_PK | 4 | 12 | 1 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 2 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Sayan Sergeevich Malakshinov [malakshinovss@xxxxxxxxx]
Sent: 07 June 2013 08:23
To: oracle-l@xxxxxxxxxxxxx
Subject: Minus conversion into antijoin
Hi list!
Could anybody show any real example of this transformation? I tried it on
11.2.0.3, but I did not succeed.
Best regards,
Sayan Malakshinov
http://orasql.org
--
//www.freelists.org/webpage/oracle-l--
//www.freelists.org/webpage/oracle-l
Other related posts:
