Is anti-bonding more effective than left-handed bonding?

In the commentary, this answer notes that anti-federations may have been optimized to be more efficient than external joins in Oracle. I would be interested to know which explanations / evidence can support or refute this statement.

+2
source share
2 answers

When you use "does not exist" or "not in" in your SQL query, you let Oracle choose how to merge with anti-join or hash anti-access.

Quick explanation

, betwen A B ( A join B Ax = Bx) Oracle A B, A.

- Oracle , .

, , A B , .

: HASH_AJ, MERGE_AJ.

:

.

- .

+2

Oracle +, NULL ANTI-, .

create table ttt1 as select mod(rownum,10) id from dual connect by level <= 50000;
insert into ttt1 select 10 from dual;
create table ttt2 as select mod(rownum,10) id from dual connect by level <= 50000;

select ttt1.id
  from ttt1
  left join ttt2
    on ttt1.id = ttt2.id
 where ttt2.id is null;

select * from ttt1 where id not in (select id from ttt2);

Final query after transformations:******* UNPARSED QUERY IS *******

10053, ( "=" , ANTI)

SELECT "TTT1"."ID" "ID" FROM "TTT2" "TTT2","TTT1" "TTT1" WHERE "TTT1"."ID"="TTT2"."ID"

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  HASH JOIN ANTI    |      |
|   2 |   TABLE ACCESS FULL| TTT1 |
|   3 |   TABLE ACCESS FULL| TTT2 |
-----------------------------------

, , ,

select --+ no_query_transformation
       ttt1.id
  from ttt1, ttt2
 where ttt1.id = ttt2.id(+) and ttt2.id is null;

------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|   1 |  FILTER             |      |
|   2 |   HASH JOIN OUTER   |      |
|   3 |    TABLE ACCESS FULL| TTT1 |
|   4 |    TABLE ACCESS FULL| TTT2 |
------------------------------------

.

ANSI , .

select --+ no_query_transformation
       ttt1.id
  from ttt1
  left join ttt2
    on ttt1.id = ttt2.id
 where ttt2.id is null;
select * from table(dbms_xplan.display_cursor(format => 'BASIC'));

--------------------------------------------------
| Id  | Operation              | Name            |
--------------------------------------------------
|   0 | SELECT STATEMENT       |                 |
|   1 |  VIEW                  |                 |
|   2 |   FILTER               |                 |
|   3 |    MERGE JOIN OUTER    |                 |
|   4 |     TABLE ACCESS FULL  | TTT1            |
|   5 |     BUFFER SORT        |                 |
|   6 |      VIEW              | VW_LAT_2131DCCF |
|   7 |       TABLE ACCESS FULL| TTT2            |
--------------------------------------------------

, , Oracle ANTI-, , . "- + rule", CBO , .

PS. SEMI , +, CBO.

0

Source: https://habr.com/ru/post/1686269/


All Articles