oracle 通过索引 rowid BATCHED 访问表和通过索引 rowid 访问表的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36957028/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between table access by index rowid BATCHED and table access by index rowid
提问by T.Poe
I'm using EXPLAIN PLAN in 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 rowidand in the other there is table access by index rowid BATCHED. What is the difference between them?
我在一个简单的 SELECT 语句上在 oracle 数据库中使用 EXPLAIN PLAN 只是为了了解它是如何工作的。在 EXPLAIN PLAN 的一个输出中提到了，table access by index rowid而在另一个输出中提到了table access by index rowid BATCHED. 它们之间有什么区别？
回答by krokodilko
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
该文档仅包含有关此主题的一句话：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 orderto improve the clustering and reduce the number of times that the database must access a block.
Step 1 中的 BATCHED 访问是指数据库从索引中检索一些行 id，然后尝试按块顺序访问行，以提高集群并减少数据库必须访问块的次数。
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:
在“正常”（非批处理）方法中，Oracle 按照索引确定的顺序检索行：
- retrieves block 15, then retrieves row 015-000123 from this block
- retrieves block 34, then retrieves row 034-000527 from this block
- retrieves block 88, then retrieves row 088-000285 from this block
- retrieves block 15 (again), then retrieves row 015-000889 from this block
- retrieves block 88 (again), then retrieves row 088-000632 from this block
- 检索块 15，然后从此块中检索行 015-000123
- 检索块 34，然后从此块中检索行 034-000527
- 检索块 88，然后从此块中检索行 088-000285
- 检索块 15 （再次），然后从此块中检索行 015-000889
- 检索块 88 （再次），然后从此块中检索行 088-000632
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:
在批处理方法中，oracle 从索引中检索一些条目，然后首先按块数对它们进行排序，然后按照块数确定的顺序处理条目：
- retrieves block 15, then retrieves rows 015-000123 and 015-000889 from this block
- retrieves block 34, then retrieves row 034-000527 from this block
- retrieves block 88, then retrieves rows 088-000285 and 088-000632 from this block
- 检索块 15，然后从此块中检索行 015-000123 和 015-000889
- 检索块 34，然后从此块中检索行 034-000527
- 检索块 88，然后从此块中检索行 088-000285 和 088-000632
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.
正如你在这个例子中看到的，块只被读取了 3 次而不是 5 次，所以从磁盘读取块的次数已经减少了 - 有些块只被读取了一次而不是两次（或更多次）。
