SQL 回表

回表即为一条SELECT语句，where子句中的字段有索引，首先通过索引定位记录行的rowid，然后通过rowid回到表中读取相关数据。
如：select * from tab_t where id=1，id字段有索引，需要回表取行的所有记录。
针对查询较少列的sql，还可以通过建立联合索引，使sql一次将数据读取出来，不用回表。
select id,name from tab_t where id=1,(id,name)联合索引，这种情况下就不用回报。
下面是简单测试：
[code]SQL> conn c##awen/oracle
SQL> create table in_tt (id number(5),name varchar2(20));

SQL> insert into in_tt select OBJECT_ID,OWNER from dba_objects;

90910 rows created.

SQL> set autotrace traceonly;
SQL> select * from in_tt;

90910 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4059055725

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92228 | 2251K| 68 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| IN_TT | 92228 | 2251K| 68 (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
36 recursive calls
1 db block gets
6408 consistent gets
247 physical reads
184 redo size
2598271 bytes sent via SQL*Net to client
67203 bytes received via SQL*Net from client
6062 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
90910 rows processed


SQL> select id from in_tt where name ='C##AWEN';



Execution Plan
----------------------------------------------------------
Plan hash value: 1741146986

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 50 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| IN_TT | 2 | 50 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IN_TT_ID | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

2 - access("NAME"='C##AWEN')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
594 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed[/code]