Re: Cannot force antijoin in UPDATE (9.2.0.5.0)

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 15 Jun 2007 05:19:04 -0700
Message-ID: <1181909944.686123.181160@o61g2000hsh.googlegroups.com>

On Jun 15, 7:50 am, "Jaap W. van Dijk" <j.w.vand..._at_hetnet.nl> wrote:
> Hi,
>
> I'm doing the following update:
>
> UPDATE table1 t1
> SET field1 = NULL
> WHERE field2 NOT IN (SELECT /*+ HASH_AJ */ t2.field2 FROM table2 t2)
>
> Instead of doing the ANTIJOIN, The database is performing a FILTER on
> table1 by reading table2. Why doesn't the hint work? For several
> tables other that table1 the hint does work.
>
> Jaap.

A quick test on Oracle 10.2.0.2:

CREATE TABLE T1 (FIELD1 NUMBER(12), FIELD2 NUMBER(12) NOT NULL); CREATE TABLE T2 (FIELD1 NUMBER(12), FIELD2 NUMBER(12) NOT NULL); INSERT INTO
  T1
SELECT
  100,
  ROWNUM*3
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

INSERT INTO
  T2
SELECT
  100,
  ROWNUM*9
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

COMMIT; EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'table_owner_here',TABNAME=>'T1'); EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'table_owner_here',TABNAME=>'T2');

Plan from 10053 trace file for:
UPDATE t1
SET field1 = NULL
WHERE field2 NOT IN (SELECT /*+ HASH_AJ */ t2.field2 FROM t2)

Plan Table

+-----------------------------------+
| Id  | Operation              | Name    | Rows  | Bytes | Cost  |
Time      |
-----------------------------------------
+-----------------------------------+
| 0   | UPDATE STATEMENT       |         |       |       |   307
|           |
| 1   |  UPDATE                | T1      |       |       |
|           |
| 2   |   HASH JOIN RIGHT ANTI |         |   208 |  2496 |   307 |
00:00:04 |
| 3   |    TABLE ACCESS FULL   | T2      |   98K |  488K |    44 |
00:00:01 |
| 4   |    TABLE ACCESS FULL   | T1      |   98K |  684K |    86 |

00:00:02 |

+-----------------------------------+

Now, repeat the test with the following table definitions: CREATE TABLE T1 (FIELD1 NUMBER(12), FIELD2 NUMBER(12)); CREATE TABLE T2 (FIELD1 NUMBER(12), FIELD2 NUMBER(12));

Plan Table

+-----------------------------------+
| Id  | Operation            | Name    | Rows  | Bytes | Cost  |
Time      |
---------------------------------------
+-----------------------------------+
| 0   | UPDATE STATEMENT     |         |       |       | 3886K
|           |
| 1   |  UPDATE              | T1      |       |       |
|           |
| 2   |   FILTER             |         |       |       |
|           |
| 3   |    TABLE ACCESS FULL | T1      |   98K |  684K |    44 |
00:00:01 |
| 4   |    TABLE ACCESS FULL | T2      |     1 |     5 |    45 |

00:00:01 |

+-----------------------------------+

Maybe the possibility of NULL values is causing the problem?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jun 15 2007 - 07:19:04 CDT