RE: Minus conversion into antijoin

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 7 Jun 2013 07:37:45 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90D4D6C_at_exmbx06.thus.corp>

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(_at_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_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Sayan Sergeevich Malakshinov [malakshinovss_at_psbank.ru] Sent: 07 June 2013 08:23
To: oracle-l_at_freelists.org
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

--
http://www.freelists.org/webpage/oracle-l--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 07 2013 - 09:37:45 CEST