最近在优化sql语句时，经常看到都是由于 TABLE ACCESS BY INDX...ROWID消耗的资源比较高，导致执行计划太差。什么是 TABLE ACCESS BY INDEX ROWID(回表)那？简单说，通过索引访问得到表的ROWID，然后根据这些ROWID再去访问表中数据行，就称为回表，
如果执行计划里出现table access ….by rowid说明产生了回表。
回表就是扫面了2次数据表。
在现实中怎么去优化那？
测试表如下：
select count(*) from TT_INFO;
COUNT(*)
----------
226803
无索引的执行计划
SQL> explain plan for SELECT a.batch_no FROM TT_INFO a where a.TEL_NO=':1';
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2467334803
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 87 | 1725 (1)| 00:00:21 |
|* 1 | TABLE ACCESS FULL| TT__INFO | 3 | 87 | 1725 (1)| 00:00:21 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("A"."TEL_NO"=':1')
13 rows selected.
创建where条件中tel_no 的索引
SQL> create index telno_inx on TT__INFO (TEL_NO);
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TT_INFO',estimate_percent => 10,method_opt=> 'for all indexed columns') ;
PL/SQL procedure successfully completed.
SQL> explain plan for SELECT a.batch_no FROM TT_INFO a where a.TEL_NO=':1';
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2616420379
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 87 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TT_INFO | 3 | 87 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TELNO_INX | 3 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("A"."TEL_NO"=':1')
14 rows selected.
此时看到执行计划中有TABLE ACCESS BY INDEX ROWID，cost 资源为7-3=4
SQL> drop index telno_inx;
Index dropped.
把select字段中batch_no和tel_no建一个复合索引如下：
SQL> create index telno_inx on TT_INFO (TEL_NO,batch_no);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TT_INFO',estimate_percent => 10,method_opt=> 'for all indexed columns') ;
PL/SQL procedure successfully completed.
SQL> explain plan for SELECT a.batch_no FROM TT_INFO a where a.TEL_NO=':1';
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2615321997
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 87 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TELNO_INX | 3 | 87 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
1 - access("A"."TEL_NO"=':1')
13 rows selected.
此时看到执行计划中有已经无TABLE ACCESS BY INDEX ROWID，cost为3.
总结：
Oracle 在建单列索引时，索引中保存的是where字段的值和该值对应的rowid，
查询根据索引进行查找，索引范围扫描后，就会返回该block的rowid，
然后根据rowid直接去block上batch_n字段的数据，因此就出现了：TABLE ACCESS BY INDEX ROWID
因为还要根据rowid回表的数据块上查询数据，所以cost值比较高，速度也就慢了，为了解决这个问题，可以对where条件和字段建复合索引，这样oracle就不会再重新根据rowid查一次数据。
