rp0428 wrote:
>
According to my notes, the hashing "algorithm" for a simple"hash is integer_column" takes a simply mod / remainder strategy (the mod() identifies the relative block number, the remainder the row within block). I may have published this somewhere, but don't recall where or when.
As a consequence, I would expecte the row with id = 1 to be in the second data block of the table, id = 2 to be in the third, and so on, and id = 5 will be in the first data block of the table.
>
Not sure I follow how a mod/remainder will do that for ids 1-5 if a block can only hold at most one row.
Consider mod(id,5): mod(1,5) = 1, ... mod(5,5) = 0, so id = 5 has to go in the 0th block of the table. Oracle's calculation runs from zero, but since there is no zeroth block in the table I moved everything forward by one block.
As a folllowup where the remainder() bit comes into play. Assume we set size so that we could get two rows per block, and hashkeys to 8 (which which cause Oracle to use 11 internally), then Oracle would use mod(id,11) to calculate a hash value, so the only values to worry about are: 0,1,2,3,4,5,6,7,8,9,10. Rows giving the first two values would be mapped to the first (zeroth) block of theh table, rows giving the second two values would be mapped to (what I call) the second block of the table, and so on.
In a perfect "single table hash cluster" mapping (i.e. no collisions) we could even say which value would map to which row within block - again with the human/computer change in perspective between counting from 1 or 0: In our second example, the value 0 would map to the first rowid in the block, the value 1 would map to the second rowid; the value 3 would map to the first rowid in the second block, the value 4 would map to the second rowid in the second block and so on.
Regards
Jonathan Lewis