I've been reading some Oracle documentation about Hash Join but I am not clear what Oracle is based on when it splits tables into partitions after full table scans. Say, if I have EMP and DEPT table, when Oracle partitions them, is it true a partition for EMP table actually corresponds to a partition for DEPT table, or it splits them simply based on the size?
Oracle scans the smaller table(u need to verify this that it does that) , creates a
hash function based on join condition and places this table in hash_area_size
then the second is joined to the first retrieving all matching records using the hash function(table).
when this table does not fit into the hash_area_size , then the hash table is divided into partitions.
the larger table is also partitioned but is not one to one but based on the match to the join hash function
the rows which are matched are returned , rest is kept for next hash table partition.
hash_area_size if not set defaults to twice sort_area_size and hash_...io_count
* db_block_size should not be more than the physical i/o supported by OS.
There is a saturaton level at beyond which if u raise this value there is not benefit.