Oracle SQL建立有效索引减少回表

p`#Ug[Hq0　　回表：在数据中，当查询数据的时候，在索引中查找索引后，获得该行的rowid，根据rowid再查询表中数据，就是回表。

zp8A9p7L0　　在数据库中，数据的存储都是以块为单位的，称为数据块，表中每一行数据都有唯一的地址标志ROWID。每次使用SQL进行查询的时候，都要扫描数据块，找到行所在的ROWID，再扫描该表的数据块。回表将会导致扫描更多的数据块。51Testing软件测试网'B;m M
PI7t6Q,Z

　　例如：SELECT a,b,cFROM TEST_DB WHERE b=1

p+N$p p G8Za ]n,k0　　在该查询语句执行的时候，可分为两种情况：51Testing软件测试网
o+N~?:NF1[b

!yKt"XB0　　A、在b上没有建立索引51Testing软件测试网1w1P a k)]C

k-Co[ X0　　如果在b上没有建立索引，那么该条SQL语句执行时，要进行全表扫描，扫描所有该表中的数据块。从该数据块中找到记录，并进行过滤。在没有索引时，查找数据会导致扫描表中所有数据块，性能较低。

/kVz\/Br0　　B、在b上建立索引51Testing软件测试网Z d _*K(I.RUBwd

;X}6K7Y(wepA0　 　如果在b上建立索引，那么在执行该条SQL语句时，先进行索引扫描，在索引中找到b=1所在的位置（一般只需要扫描3个块数据即可），获得改行的 ROWID，根据其ROWID再查询数据（回表），如果所查找的数据量较少，则回表次数就少。如上面的例子，要查询的数据只有b在索引中，a并不在索引 中，那么就要回表一次查询a；如果a也在索引中，那么就不需要回表。51Testing软件测试网 HM,YM\r P|l8n

　　在数据库查询中，需要用到回表的地方很多，如分页查询。一般要竟量在索引上分页，然后返回ROWID，在通过ROWID进行回表查询。

1a9eM v}z0B{g3Y0　 　如分页语句： SELECT *  FROM  ( SELECT ROW_NUMBER OVER (ORDER BY A ) RN,T.* FROM  TABLE  T  WHERE B=?  AND C=? ) WHERE  RN>=1 AND  RN <=2051Testing软件测试网|t:T_K%x[6e2}C

6Q%S
H(s9\r]0　　在该分页查询语句中，我们建立B,C,A的索引，那么查询时，步骤如下：51Testing软件测试网!MY.N8Kd6g
\w

　　1、先查询内层语句 SELECT *  FROM  TABLE T  WHERE  B=?  AND  C=?，假设返回1000行数据。51Testing软件测试网7Z+lc'v&N+p

%?'UL-ln|0　 　2、通过索引找到这1000行数据的ROWID，由于索引时连续的，所以假设这1000行数据的索引分布在3个数据块中，一般需要读取6个数据块。再根 据ROWID取回表查询数据，最差的情况是这1000行数据分布在1000个数据块中，则需要读取1000块。那么总共需要读取的数据块区为1006块。

　　如果我们换另外一种写法：51Testing软件测试网k["{!kN"}0ES$d

　 　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 RID  FROM (SELECT  ROWID RID, ROW_NUMBER  OVER(ORDER  BY A)  RN  FROM TABLE  WHERE  B=? AND  C=?) WHERE  RN >1 AND  RN<=20，可以全部在索引中获取到数据，和上面一样，也差不多为6数据块。分页之后，只有20行数据，在更具这20行的ROWID回表查询数 据，最坏的情况是20行都在20个不同块中，那么总共也只扫描26块数据块。51Testing软件测试网2? z0V(TW8rR5z7A

　　因此，有效的利用索引，可以减少回表的次数，大大提升SQL性能。