James's Blog

Oracle Hash Join and nested join

- In a nested loop join, the database reads and index, builds a list of ROW ID’s and then probes into the second table for the matching rows.

-In a hash join, the database does a full-scan of the driving table, builds a RAM hash table, and then probes for matching rows in the other table. For certain types of SQL

-The propensity of the SQL optimizer to invoke a hash join is heavily controlled by the setting for the hash_area_size Oracle parameter

posted by James Pei @ 6:05 PM