Say you are performing an indexed nested loops join. i.e. For each row in table A, lookup matching rows in table B via an index.

Assume the buffer cache is completely empty.

1. Read block #1 in A (call this A1) from disk into the buffer cache.
2. Pick the first row from block A1

Now that we have our 1st row, we are ready to scan the index.
3. Read the root block of the index from disk into the buffer cache
4. Determine the address of the next branch block in the index and read that from disk into the buffer cache. Repeat until you get to a leaf block that points to our 1st matching row in B.
5. Read the block of B from disk into the buffer cache that contains the rowid found in the index
6. Pick the matching row of B out of that block and join it to the A row from Step 2.

Now we have our first row returned; the contents of the buffer cache are:
- The 1st block of A
- The root block of the index on B
- Several branch blocks of the index on B
- A leaf block of the index on B
- One block from table B.

Then Oracle goes on to get another row, it will again scan the index on B, but this time some of the blocks will be cached - they won't need to be read from disk. And if we are lucky, some of the other matching rows in B will be in blocks we have already read into cache.

Since index blocks and table blocks contain many rows, every time we read a single block from disk we get 1 row that we want, and dozens that we don't want (yet). If it turns out that we DO want one of those rows later in the query, it will be there in the buffer cache.Ross Leishman