rp0428 wrote:For hash clusters if the value of hashkeys is not prime it is increased to the next prime number; so the actual value used in this case will be 5.
Doesn't reproduce for me - I get one CR on the fetch (as expected) when the access path is "table access hash".
See my reply to OP. If my hypothesis is correct then you might need to use ID=2 (or 3 or 4) to find the one 'hash collision' that is occurring. The 'hash' algorithm for your instance might have hashed 'ID=5' to a different value than that used by ID=1.
user12288492 wrote:Effects vary with the number of previous executions and number of times you reconnect.
I ran the query with all the 5 values of id's and the results were all the more confusing.
During first run, I got cr=2 for two key values, for the rest cr=1
During second run, I got cr=2 for one key value , for the rest cr=1
During third run, I got cr=1 for all the key values
On the first block visited, Oracle does a buffer visit to sort out a cleanout SCN (ktugct - get commit time). This cleans out the block and caches the SCN acquired. The rest of the blocks you visit in the same session shouldn't be cleaned out because the session can use the cached SCN to avoid needing a cleanout operation. Eventually all the blocks will have been cleaned out (which means they will be written to disc) and the extra CR will stop happening.
cleanouts only - consistent read gets 1 immediate (CR) block cleanout applications 1 commit txn count during cleanout 1 cleanout - number of ktugct calls 1 Commit SCN cached 1 redo entries 1 redo size 80
rp0428 wrote: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.
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.