HASH ANTI-JOIN, FTS and sequential reads

From: Andreas Piesk <a.piesk_at_gmx.net>
Date: 1 Dec 2006 06:21:17 -0800
Message-ID: <1164982877.199328.241910@j44g2000cwa.googlegroups.com>

Hi,

i've a problem understanding hash anti-join. i have 2 tables:

K, pk on ID, approx. 6M rows
S, non-unique index I0#S on kid, approx. 7M rows

for a cleaning procedure (vendor didn't implement referential integrity for "performance reasons") i need to remove all unreferenced rows from K:

DELETE FROM K WHERE ID NOT IN ( SELECT KID FROM S);

---

| Id  | Operation              |  Name                   | Rows  |

Bytes |TempSpc| Cost |

---

| 0 | DELETE STATEMENT | | 11860 |

625K|       | 18359 |


|   1 |  DELETE                | K                       |       |

 |       |       |
|*  2 |   HASH JOIN ANTI       |                         | 11860 |
625K|   311M| 18359 |


|   3 |    TABLE ACCESS FULL   | K                       |  6054K|

242M|       |  8711 |


|   4 |    INDEX FAST FULL SCAN| I0#S                    |  6961K|

79M|       |  2123 |
------------------------------------------------------------------------------------------

estimated execution time is 7-8h which seems a little bit long now the part i don't understand:

the session spends almost all time on 'db sequential read'. v$session_wait shows the datafile holding table K as P1. v$session_ops shows the session is still executing the hash-join:

SQL> select opname, sofar, totalwork, units, time_remaining, elapsed_seconds, message from v$session_longops where sid=40 and time_remaining > 0;

OPNAME SOFAR TOTALWORK UNITS
TIME_REMAINING ELAPSED_SECONDS

---------- ---------- ---------- --------------------------------

-------------- ---------------
MESSAGE

---

Hash Join       33165      64320 Blocks
   13195           14046

Hash Join: : 33165 out of 64320 Blocks done

why do i see 'db sequential reads' on table K but explain plan shows full table scan? i think it has to do with the way the hash anti-join works but i haven't found a detailed explanation how it works. if someone has some pseudo-code explaining that kind of hash, i would appereciate it.

regards,
-ap Received on Fri Dec 01 2006 - 08:21:17 CST