本文主要是介绍回表 table access by index rowid,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
111
回表:在数据中,当查询数据的时候,在索引中查找索引后,获得该行的rowid,根据rowid再查询表中数据,就是回表。
--创建一个表, 索引只建立在object_id上
SQL> create table ml_1 as
2 select * from dba_objects
3 ;
Table created
SQL> create index idx_ml_1 on ml_1 (object_id);
Index created
--当select语句只有object_id时, 就不存在回表,因为索引包含此列。
unicomidmp@UNICOM>select object_id from ml_1 where object_id =46;
Execution Plan
----------------------------------------------------------
Plan hash value: 328998971
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_ML_1 | 1 | 13 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=46)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
69 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--当select不仅仅有object_id列,还有其他列, 这时就需要回表。
unicomidmp@UNICOM>select status,object_id from ml_1 where object_id =46;
Execution Plan
----------------------------------------------------------
Plan hash value: 1099796311
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ML_1 | 1 | 18 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ML_1 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=46)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
72 consistent gets
0 physical reads
0 redo size
601 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--所以并不是每次查询,都要走索引才是最优。
结论:
如果在obiect_id上建立索引,那么在执行该条SQL语句时,先进行索引扫描,在索引中找到 obiect_id=46 所在的位置(一般只需要扫描3个块数据即可)
,获得该行的ROWID,根据其ROWID再查询数据(回表),如果所查找的数据量较少,则回表次数就少。如上面的例子,
要查询的数据只有 obiect_id在索引中,status并不在索引中,那么就要回表一次查询status,如果status也在索引中,那么就不需要回表。
如果索引查询的数据量大, 那么回表的次数就多, 索引扫描的io块+回表的io > 全表扫描io ,此时就不能用索引。 即 no_index(x);
---------------------
作者:老农民挖数据
来源:CSDN
原文:https://blog.csdn.net/shushugood/article/details/51475825?utm_source=copy
版权声明:本文为博主原创文章,转载请附上博文链接!
这篇关于回表 table access by index rowid的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!