It is not so much a question of forcing the optimizer to do an anti-join as one of allowing it to.
Because of the logic of anti-join you must exclude the possibility of NULLs on either side of the join.
For columns or expressions not obviously NULL to the optimizer this is typically done by adding IS NOT NULL conditions to all columns or expressions referenced on BOTH sides of the in the NOT IN.
For example, the first query below does not guarantee that ename and loc are not null, hence hash anti-join is not an option. In the second query we explicitly state that ename and loc are NULL and anti-join results.
Code:Personal Oracle Database 10g Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> SET AUTOTRACE ON EXPLAIN; SQL> SELECT empno 2 FROM emp 3 WHERE ename NOT IN ( 4 SELECT loc 5 FROM dept); EMPNO ---------- 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 14 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=76 Card=13 Bytes 130) 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=55 Card=14 Bytes=140) 3 1 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Bytes=7) SQL> SELECT empno 2 FROM emp 3 WHERE ename IS NOT NULL 4 AND ename NOT IN ( 5 SELECT loc 6 FROM dept 7 WHERE loc IS NOT NULL); EMPNO ---------- 7698 7654 7902 7499 7521 7934 7900 7369 7844 7782 7876 7788 7839 7566 14 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=59 Card=10 Bytes=170) 1 0 HASH JOIN (ANTI) (Cost=59 Card=10 Bytes=170) 2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=55 Card=14 Bytes=140) 3 1 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=4 Bytes=28) SQL>




Reply With Quote