在看别人的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。
减少回表次数提高SQL查询性能
SELECT OBJECT_ID, OBJECT_NAME
FROM
(
SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM
(
SELECT /*+ index_rs(t T_I) */OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME
)
WHERE ROWNUM <= 10
)
WHERE RN >= 1;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 920 | 70538 (1)| 00:14:07 |
|* 1 | VIEW | | 10 | 920 | 70538 (1)| 00:14:07 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 81332 | 6274K| 70538 (1)| 00:14:07 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 81332 | 1906K| 70538 (1)| 00:14:07 |
| 5 | INDEX FULL SCAN | T_I | 81332 | | 367 (1)| 00:00:05 |
---------------------------------------------------------------------------------------
FROM
(
SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM
(
SELECT /*+ index_rs(t T_I) */OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME
)
WHERE ROWNUM <= 10
)
WHERE RN >= 1;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 920 | 70538 (1)| 00:14:07 |
|* 1 | VIEW | | 10 | 920 | 70538 (1)| 00:14:07 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 81332 | 6274K| 70538 (1)| 00:14:07 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 81332 | 1906K| 70538 (1)| 00:14:07 |
| 5 | INDEX FULL SCAN | T_I | 81332 | | 367 (1)| 00:00:05 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
2 - filter(ROWNUM<=10)
SELECT /*+ first_rows */
t.*
FROM (SELECT rid, rn
from (SELECT rowid as rid, rownum as rn
FROM t
ORDER BY object_name)
WHERE rownum <= :2) a,
t
WHERE a.rn >= :3
AND a.rid = t.rowid ;
t.*
FROM (SELECT rid, rn
from (SELECT rowid as rid, rownum as rn
FROM t
ORDER BY object_name)
WHERE rownum <= :2) a,
t
WHERE a.rn >= :3
AND a.rid = t.rowid ;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 81332 | 9372K| 81733 (1)| 00:16:21 |
| 1 | NESTED LOOPS | | 81332 | 9372K| 81733 (1)| 00:16:21 |
|* 2 | VIEW | | 81332 | 1985K| 367 (1)| 00:00:05 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 81332 | 1985K| 367 (1)| 00:00:05 |
| 5 | COUNT | | | | | |
| 6 | INDEX FULL SCAN | T_I | 81332 | 2462K| 367 (1)| 00:00:05 |
| 7 | TABLE ACCESS BY USER ROWID| T | 1 | 93 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 81332 | 9372K| 81733 (1)| 00:16:21 |
| 1 | NESTED LOOPS | | 81332 | 9372K| 81733 (1)| 00:16:21 |
|* 2 | VIEW | | 81332 | 1985K| 367 (1)| 00:00:05 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 81332 | 1985K| 367 (1)| 00:00:05 |
| 5 | COUNT | | | | | |
| 6 | INDEX FULL SCAN | T_I | 81332 | 2462K| 367 (1)| 00:00:05 |
| 7 | TABLE ACCESS BY USER ROWID| T | 1 | 93 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
第二种比第一种好,减少了回表的成本。直接在索引层就运用到了第二个谓词RN>1.无论如何索引层的成本没减少。
我们把第二种查询,再次改写如下方式
SELECT /*+ first_rows */
t.*
FROM (SELECT rid, rn
from (SELECT rowid as rid, rownum as rn,object_type
FROM t
ORDER BY object_name)
WHERE rownum <= :2) a,
t
WHERE a.rn >= :3
AND a.rid = t.rowid
t.*
FROM (SELECT rid, rn
from (SELECT rowid as rid, rownum as rn,object_type
FROM t
ORDER BY object_name)
WHERE rownum <= :2) a,
t
WHERE a.rn >= :3
AND a.rid = t.rowid
最内层的查询增加了字段object_type字段,由于这个字段不在索引里,因此在9I,ORACLE选择回表获取这个字段。导致上面所说的优化失效。但是在10G,11G,这种优化却依然有效,ORACLE知道字段对查询是没有用的。因此就没有回表。
9I下:
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11517 | 2294K| 12343 |
| 1 | NESTED LOOPS | | 11517 | 2294K| 12343 |
|* 2 | VIEW | | 11517 | 224K| 826 |
|* 3 | COUNT STOPKEY | | | | |
| 4 | VIEW | | 11517 | 224K| 826 |
| 5 | COUNT | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| T | 11517 | 944K| 826 |
| 7 | INDEX FULL SCAN | T_I | 11517 | | 26 |
| 8 | TABLE ACCESS BY USER ROWID | T | 1 | 184 | 1 |
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11517 | 2294K| 12343 |
| 1 | NESTED LOOPS | | 11517 | 2294K| 12343 |
|* 2 | VIEW | | 11517 | 224K| 826 |
|* 3 | COUNT STOPKEY | | | | |
| 4 | VIEW | | 11517 | 224K| 826 |
| 5 | COUNT | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| T | 11517 | 944K| 826 |
| 7 | INDEX FULL SCAN | T_I | 11517 | | 26 |
| 8 | TABLE ACCESS BY USER ROWID | T | 1 | 184 | 1 |
--------------------------------------------------------------------------------