Difference between table access by index rowid BATCHED and table access by index rowid

Question

I'm using EXPLAIN PLAN in an Oracle database on a simple SELECT statement just to find out how it works.

In one output of EXPLAIN PLAN there is mentioned table access by index rowid and in the other there is table access by index rowid BATCHED.

What is the difference between them?

Answer 1

The documentation contains merely one sentence on this topic:
https://docs.oracle.com/database/121/TGSQL/tgsql_optop.htm#GUID-4180BA97-3E2C-41F9-B282-4FB3FF9532CB

The BATCHED access shown in Step 1 means that the database retrieves a few row ids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block.

Consider a below example of the (simplified) index

+-------------+------------------+
| index value | block nbr-rowid  |
+-------------+------------------+
|      1      |   015-000123     |
|      2      |   034-000527     |
|      3      |   088-000285     |
|      4      |   015-000889     |
|      5      |   088-000632     |
........
........

---

In the "normal" (not batched) method Oracle retrieves rows in the order determined by the index:

1. retrieves block 15, then retrieves row 015-000123 from this block
2. retrieves block 34, then retrieves row 034-000527 from this block
3. retrieves block 88, then retrieves row 088-000285 from this block
4. retrieves block 15 (again), then retrieves row 015-000889 from this block
5. retrieves block 88 (again), then retrieves row 088-000632 from this block

---

In the batched method oracle retrieves a few entries from the index, then first sorts them by the number of block, then process entries in the order determined by number of blocks:

1. retrieves block 15, then retrieves rows 015-000123 and 015-000889 from this block
2. retrieves block 34, then retrieves row 034-000527 from this block
3. retrieves block 88, then retrieves rows 088-000285 and 088-000632 from this block

As you see in this example, blocks were fetched only 3 times instead of 5 times, so a number of block reads from the disk has been reduced - some blocks have been read only once instead of two (or more) times.