Suppose I run a sql query and DB makes use of index structures to arrive at a ROWID(assuming an INDEX SCAN, like in Oracle), and now DB wants to use it to fetch the actual records.
So how does ROWID helps in fast access of the record? I assume ROWID must be somehow mapped to internal record storage. I understand index is basically combination of B-tree and doubly linked list. But how are actual records stored such that ROWID fetches them fast.
A ROWID is simply a 10 byte physical row address that contains the relative file number, the block number within that file, and the row number within that block. See this succinct explanation:
With that information, Oracle can make an I/O read request of a single block by calculating the block offset byte position in the file and the length of the block. Then it can use the block's internal row map to jump directly to the byte offset within the block of the desired row. It doesn't have to scan through anything.
You can pull a human-readable representation of these three components by using this query against any (heap) table, any row:
SELECT ROWIDTOCHAR(rowid) row_id,
dbms_rowid.rowid_relative_fno(rowid) fno,
dbms_rowid.rowid_block_number(rowid) blockno,
dbms_rowid.rowid_row_number(rowid) rowno
FROM [yourtable]
WHERE ROWNUM = 1 -- pick any row
The fast retrieval is also often aided by the fact that single block reads are frequently bypassed altogether because the block is already in the buffer cache. Or if it is not in Oracle's buffer cache, a single block read from many cooked filesystems, unless disabled by the setting of filesytemio_options, may be cached at the OS level and never go to storage. And if you use a storage appliance, it probably has it's own caching mechanism. All of these caching mechanisms likely give caching preference to small reads over large ones, so single block reads from Oracle are likely to avoid hitting magnetic disk altogether, more so than multiblock reads associated with table scans.
But be careful - just because ROWID is the fastest way to retrieve a single row does not mean it is the fastest way to retrieve many rows. Because of the overhead of a read call, many single calls accumulate a lot of wasted overhead. When pulling large amounts of rows it is frequently more efficient to do a full table scan, especially when Oracle uses direct path reads to do so, than utilize ROWIDs either manually or via indexes.
