Shulou(Shulou.com)06/01 Report--

At the beginning of Oracle12c, we often see the "TABLE ACCESS BY INDEX ROWID BATCHED" operation when we get the execution plan of the SQL statement, so what exactly does this operation mean? What's the use? Let's first take a look at the official explanation for this operation:

The BATCHED access shown in Step 1 means that the database retrieves a few rowids 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.

This means that in order to get some rowid from the index, the database then tries to access the rows of data in the block in block order in order to improve aggregation and reduce the number of accesses to a block.

That's what the official explanation means, but how do you understand it? Previously, when we went back to the table to get the corresponding data rows through the rowid obtained by the index, we always read a rowid back to the table to get the corresponding data rows, then read another rowid, and then go back to the table to get the corresponding data rows. So that all the required data is read all the time When the data rows corresponding to different rowid are stored in a data block, multiple reads of the same table data block may occur. When the aggregation factor of an index is relatively low, this is also an inevitable result, thus wasting system resources. This new feature in Oracle 12c, by sorting the data block numbers corresponding to rowid, and then going back to the table to read the corresponding data rows, thus avoiding repeated reading of the same table data block, thus improving the performance of SQL statements and reducing resource consumption. This feature is controlled by hiding the parameter "_ optimizer_batch_table_access_by_rowid". The default value is true, which is on.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.