ORACLE回表



（一）

要写出高效SQL，那必须得清楚SQL执行路径，介绍如何提高SQL性能的文章很多，这里不再赘述，
本人来谈谈如何从 减少SQL回表次数来提高查询性能，因为回表将导致扫描更多的数据块。

   我们大家都知道，数据库表中数据存储都是以块为单位，称为数据块；表中每行数据都有唯一的地址标志ROWID。

  举个例子：

    select a from test_db where b=5

   A、假设b上没有索引

         1、那么该条SQL将进行表扫描，扫描所有该表的数据块

         2、从数据块中找到记录，并且进行过滤

      可想而知，没有索引将会导致扫描该表所有数据块，性能低下

  B、 假设b上有索引

        1、那么该条SQL将进行索引扫描，在索引中找到b=5的位置，一般只需要扫描3个块左右就找到了

        2、获得所有b=5的行的rowid

        3、根据rowid再查询数据(这就是回表)，如果数据量少，那么回表次数就少，如果需要的数据全部在索引中，那么就不会再回表了,例如a也在索引中，如果a不在索引中，那么仍然要回表一次查出a。

   

     经验：如果有可能的话，尽量只在索引上查询，不用回表或者只少量回表。

       

     例如分页需要回表，一般尽量在索引上分页，然后返回rowid，再通过rowid进行回表查询。

    下面是一个常用的分页语句：

   Select * from (select row_number over(order by a) rn,t.* from table t where b=? And c=?) where rn>=1 and rn <=20   
Select * from (select row_number over(order by a) rn,t.* from table t where b=? And c=?) where rn>=1 and rn <=20

 我们分析一下(假设索引是b,c,a)：

   1、先查询内层语句 select * from table t where b=? and c=?,假设返回1000行数据

   2、通过索引找到这1000行数据的rowid，因为索引是连续的，假设这1000行数据的索引分布在5个块中，则差不多为8块读

   3、再根据rowid取回表查询数据，最坏的情况是这1000行数据分布在1000个块中，则需要读取1000块。那么算上上面的8块总共尧都区1000+8=1008块

我们换一种写法：

 Select * from table t,   
(select rid from (select rowid rid,row_number over(order by a) rn from table where b=? And c=?)   
 where rn>=1 and rn<=20) tmp   
Where tmp.rid=t.rowid   
Select * from table t,
(select rid from (select rowid rid,row_number over(order by a) rn from table where b=? And c=?)
 where rn>=1 and rn<=20) tmp
Where tmp.rid=t.rowid

再来分析一下：

  1、最里层的sql select rid from (select rowid rid,row_number over(order by a) rn from table where b=? And c=?) where rn>=1 and rn<=20 可以全部从索引中获得数据，由于索引有序，差不多也是8块读

 2、分页之后，只有20行数据，再根据这20行的rowid回表查询数据，最坏情况是20行都在20个不同块中，那么总共20+8=28

   

  从以上分析可以看出，有效的利用索引，减少回表次数，可以大大提高SQL性能，值得大家去花功夫了解一下。

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/lovingprince/archive/2009/04/16/4084786.aspx

（二）

在看别人的LOG时看到一个新的名词，是回表。
在这里记录下：
简单来说，通过索引访问得到表的ROWID，然后根据这些ROWID再去访问表中数据行，就称为回表
如果执行计划里出现table access by rowid说明要回表。

例子：
create table test( name char(5), id int);
create index id_test on test(id);

如果这时查询语句是：
select max(id) from test;
因为索引id_test上有关于id的信息，所以只要对索引进行查询就可以了，不用再根据ROWID查询TEST.。

如果查询
select name from test where id= 200;
那根据索引查询到ID为200的rowid后，根据rowid指向的数据，回查到TEST获得相应的NAME。

http://www.itpub.net/thread-1340290-1-1.html 

“回表”一般就是指执行计划里显示的“TABLE ACCESS BY INDEX ROWID”。

例如select的字段里有索引不包含的列，
SQL> select ename from emp  where empno =1;

Execution Plan
----------------------------------------------------------
Plan hash value: 4066871323

----------------------------------------------------------------------
| Id  | Operation                                        | Name    | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |     1 |     8 |     1 |
|   1 |  TABLE ACCESS BY INDEX ROWID    | EMP       |     1 |     8 |     1 |
|*  2 |   INDEX UNIQUE SCAN                    | PK_EMP |     1 |       |     1 |
----------------------------------------------------------------------

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

   2 - access("EMPNO"=1)


再例如，虽然只查询索引里的列，但是需要回表过滤掉其他行。
SQL> select empno from emp  where empno =1 and ename='hao';

Execution Plan
----------------------------------------------------------
Plan hash value: 4066871323

----------------------------------------------------------------------
| Id  | Operation                                    | Name     | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |     1 |     8 |     1 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     1 |     8 |     1 |
|*  2 |   INDEX UNIQUE SCAN                 | PK_EMP |     1 |       |     1 |
----------------------------------------------------------------------

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

   1 - filter("ENAME"='hao')
   2 - access("EMPNO"=1)