Difference between ROWID and ROWID BATCHED in Oracle

Hi Dears,

In this article, we will see Difference between ROWID and ROWID BATCHED in Oracle.

TABLE ACCESS BY INDEX ROWID BATCHED is new execution plan operation introduced in oracle 12c to improve performace by make efficient and minimal read writes to the disk.
It is generally used for range queries.
This operation genrally select few ROWIDs from the index and then try to access the rows in blcoks.
It signifiacant reduce the number of access to the block there by improving performace.
In a table access by ROWID, Oracle looks up each selected row of a organized table based on its ROWID, which specifies the data file, the data block within that file, and the location of the row within that block.
The ROWID is obtained either from the WHERE clause predicate or through an index scan.
If execution plan shows TABLE ACCESS BY INDEX ROWID BATCHED it menas that oracle retrieves a bunch of ROWIDs from the index and then try to access rows in blocks to reduce the number of time each block to be accessed.
1st Execution Plan for TABLE ACCESS BY ROWID BATCHED

2nd Execution plan TABLE ACCESS BY ROWID

If you see both the plan u can find there is no change, in CPU cost or processed rows or bytes all are same but you can notice one change the ROWIDs were processed by batched in 1st step where as other one by ROWID.
In my scenario, Optimizer recommends the query, which processed in the database by ROWID. 2nd one is more efficient than 1st one.

In the Normal ROWID (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.

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts