Cannot force antijoin in UPDATE (9.2.0.5.0)

[Jaap W. van Dijk]

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.

[hpuxrac]

Did you check metalink for a bug or change that impacts 9.2.0.5?

[Charles Hooper]

On Jun 15, 7:50 am, "Jaap W. van Dijk" <j.w.vand...@hetnet.nl> wrote:

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.

[Jaap W. van Dijk]

Charles Hooper schreef:

> Maybe the possibility of NULL values is causing the problem?
>
> Charles Hooper

Charles: that's it! I changed the column from NULL to NOT NULL, and
then the UPDATE was performed with an ANTIJOIN.

Thanks a million,

Jaap.