ORACLE 优化---回表（TABLE ACCESS BY INDEX ROWID)

当对一个列创建索引之后，索引会包含该列的键值以及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表一般是单块读，回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描了，应该直接走全表扫描。在进行SQL优化的时候，一定要注意回表次数！特别是要注意回表的物理I/O次数！SQL> select * from test where owner...

梦想家DBA

12551人浏览 · 2019-12-14 16:24:19

梦想家DBA · 2019-12-14 16:24:19 发布

当对一个列创建索引之后，索引会包含该列的键值以及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表一般是单块读，回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描了，应该直接走全表扫描。

在进行SQL优化的时候，一定要注意回表次数！特别是要注意回表的物理I/O次数！

SQL> select * from test where owner = 'SYS';

30812 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3932013684

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 236K| 73 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2499 | 236K| 73 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OWNER | 2499 | | 6 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SYS')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4927 consistent gets
69 physical reads
0 redo size
3502852 bytes sent via SQL*Net to client
23117 bytes received via SQL*Net from client
2056 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30812 rows processed

SQL>

执行计划中加粗部分（TABLE ACCESS BY INDEX ROWID）就是回表。索引返回多少行数据。回表就要回多少次，每次回表都是单块读（因为一个rowid对应一个数据块）该SQL返回了30812行数据，那么回表一共就需要30812次。

请思考：上面执行计划的性能是耗费在索引扫描中还是耗费在回表中？

为了得到答案，请大家在SQLPLUS中进行实验。为了消除arraysize 参数对逻辑读的影响，设置arraysize=5000。arraysize表示Oracle服务器每次传输多少行数据到客户端，默认为15。如果一个块有150行数据，那么这个块就会被读10次，因为每次只传输15行数据到客户端，逻辑读会被放大。设置了arraysize=5000之后，就不会发生一个块被读n次的问题了。

SQL> set pagesize 100
SQL> set line 200
SQL> set arraysize 5000
SQL> set autot trace
SQL> select owner from test where owner='SYS';

30812 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1086061979

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30812 | 180K| 48 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_OWNER | 30812 | 180K| 48 (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='SYS')

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
186 consistent gets
0 physical reads
0 redo size
155727 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30812 rows processed

SQL>

从上面的实验可见，索引扫描只耗费了186个逻辑读。

SQL> set autot off
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from test where owner = 'SYS';

BLOCKS
----------
798

SQL>

该SQL的性能确实绝大部分损失在回表中。

更糟糕的是：假设30812 条数据都在不同的数据块中，表也没有被缓存在buffer cache中，那么回表一共需要耗费30812个物理I/O，这太可怕了。

在无法避免回表的情况下，走索引如果返回数据量太多，必然会导致回表的次数太多，从而导致性能严重性能严重下降。

什么样的SQL必须要回表？

select * from table where ...

这样的SQL就必须回表，所以我们必须严禁使用select * 。那什么样的SQL不需要回表？

select count(*) from table