table access by index rowid

最新推荐文章于 2024-07-24 04:12:13 发布

老农民挖数据

最新推荐文章于 2024-07-24 04:12:13 发布

阅读量1w

点赞数

分类专栏： oracle_sql tuning

本文链接：https://blog.csdn.net/shushugood/article/details/51475825

版权

oracle_sql tuning 专栏收录该内容

37 篇文章 0 订阅

订阅专栏

table access by index rowid

回表：在数据中，当查询数据的时候，在索引中查找索引后，获得该行的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);

--end---

mengl 2016-5-22