ITPub博客

首页 > 数据库 > Oracle > TABLE ACCESS BY …ROWID回表优化分析

TABLE ACCESS BY …ROWID回表优化分析

原创 Oracle 作者:pingdanorcale 时间:2020-08-05 13:21:42 0 删除 编辑

 最近在优化sql语句时,经常看到都是由于 TABLE ACCESS BY INDX...ROWID消耗的资源比较高,导致执行计划太差。什么是 TABLE ACCESS BY INDEX ROWID(回表)那?简单说,通过索引访问得到表的ROWID,然后根据这些ROWID再去访问表中数据行,就称为回表,

如果执行计划里出现table access ….by rowid说明产生了回表。

回表就是扫面了2次数据表。

在现实中怎么去优化那?

测试表如下:

select count(*) from  TT_INFO;


  COUNT(*)

----------

    226803 

无索引的执行计划

SQL> explain plan for  SELECT  a.batch_no FROM TT_INFO a where  a.TEL_NO=':1';


Explained.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 


PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2467334803


-----------------------------------------------------------------------------------

| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |               |     3 |    87 |  1725   (1)| 00:00:21 |

|*  1 |  TABLE ACCESS FULL| TT__INFO |     3 |    87 |  1725   (1)| 00:00:21 |

-----------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------


   1 - filter("A"."TEL_NO"=':1')


13 rows selected.


创建where条件中tel_no 的索引

SQL> create index telno_inx  on TT__INFO (TEL_NO);


Index created.


SQL> 

SQL> exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TT_INFO',estimate_percent => 10,method_opt=> 'for all indexed columns') ;


PL/SQL procedure successfully completed.


SQL> explain plan for  SELECT  a.batch_no FROM TT_INFO a where  a.TEL_NO=':1';


Explained.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 


PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2616420379


---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     3 |    87 |     7   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TT_INFO |     3 |    87 |     7   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TELNO_INX     |     3 |       |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):


PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------


   2 - access("A"."TEL_NO"=':1')

14 rows selected.


此时看到执行计划中有TABLE ACCESS BY INDEX ROWID,cost 资源为7-3=4



SQL> drop index telno_inx;


Index dropped.

把select字段中batch_no和tel_no建一个复合索引如下:

SQL> create index telno_inx  on TT_INFO (TEL_NO,batch_no);


Index created.


SQL> exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TT_INFO',estimate_percent => 10,method_opt=> 'for all indexed columns') ;


PL/SQL procedure successfully completed.


SQL> explain plan for  SELECT  a.batch_no FROM TT_INFO a where  a.TEL_NO=':1';


Explained.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 


PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2615321997


------------------------------------------------------------------------------

| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |           |     3 |    87 |     3   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| TELNO_INX |     3 |    87 |     3   (0)| 00:00:01 |

------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------


   1 - access("A"."TEL_NO"=':1')


13 rows selected.


此时看到执行计划中有已经无TABLE ACCESS BY INDEX ROWID,cost为3.

总结:

Oracle 在建单列索引时,索引中保存的是where字段的值和该值对应的rowid,

查询根据索引进行查找,索引范围扫描后,就会返回该block的rowid,

然后根据rowid直接去block上batch_n字段的数据,因此就出现了:TABLE ACCESS BY INDEX ROWID

因为还要根据rowid回表的数据块上查询数据,所以cost值比较高,速度也就慢了,为了解决这个问题,可以对where条件和字段建复合索引,这样oracle就不会再重新根据rowid查一次数据。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10201716/viewspace-2709369/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-01-02

  • 博文量
    58
  • 访问量
    112626