oracle sql调优系列4(回表查询与不回表的性能比较)

原创 Linux操作系统作者：alsrt 时间：2012-06-07 09:33:16 0 删除编辑

构建案例如下:

SQL> drop table test;

Table dropped.

SQL> create table test as select * from dba_objects;

Table created.

SQL>create index ind_test on test(object_id)

SQL> exec dbms_stats.gather_table_stats('scott','TEST',CASCADE=>TRUE)

SQL> select index_name from dba_indexes where table_name='TEST' and table_owner='SCOTT';

INDEX_NAME
------------------------------
IDX_TEST

需要明确一点,索引是建立在object_id列上的,当我们查询其他列的时候,会回表查询,

我们执行如下语句:

select object_id,object_name from test where object_id > 100 and object_id <200

看执行计划产生了18个逻辑读

Execution Plan
----------------------------------------------------------
Plan hash value: 3736352305

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

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

| 0 | SELECT STATEMENT | | 99 | 2673 | 14 (0)| 00
:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 99 | 2673 | 14 (0)| 00
:00:01 |

|* 2 | INDEX RANGE SCAN | IND_TEST | 99 | | 2 (0)| 00
:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID">100 AND "OBJECT_ID"<200)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         18 consistent gets
          0 physical reads
          0 redo size
       3384 bytes sent via SQL*Net to client
        558 bytes received via SQL*Net from client
          8 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         98 rows processed
接下来继续测试,我们改写该sql语句,去掉object_name列,然后看执行计划

产生了9个逻辑度

select object_id from test where object_id > 100 and object_id <200

Execution Plan
----------------------------------------------------------
Plan hash value: 2176510370

-----------------------------------------------------------------------------
| Id | Operation        | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |    99 |   594 |     2   (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_TEST |    99 |   594 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID">100 AND "OBJECT_ID"<200)

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          9 consistent gets
          0 physical reads
          0 redo size
       2225 bytes sent via SQL*Net to client
        558 bytes received via SQL*Net from client
          8 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         98 rows processed

这两个模拟案例说明了一个道理,对于一般表来说,查询的话回表与不回表性能差别巨大

那我们如何做到既要查询object_id,object_name,并且让他不产生回表呢,我们接下来继续测试:

SQL> drop index ind_test;
2

Index dropped.

SQL> create index ind_test on test(object_id,object_name);

Index created.

SQL> exec dbms_stats.gather_table_stats('scott','TEST',CASCADE=>TRUE)

SQL> select index_name from dba_indexes where table_name='TEST' and table_owner='SCOTT';

INDEX_NAME
------------------------------
IND_TEST

执行如下语句:

select object_id,object_name from test where object_id > 100 and object_id <200

查看执行计划,逻辑读也是9个.为什么呢?

Execution Plan
----------------------------------------------------------
Plan hash value: 2176510370

-----------------------------------------------------------------------------
| Id | Operation        | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |    99 | 2673 |     2   (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_TEST |    99 | 2673 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID">100 AND "OBJECT_ID"<200)

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          9 consistent gets
          0 physical reads
          0 redo size
       3384 bytes sent via SQL*Net to client
        558 bytes received via SQL*Net from client
          8 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         98 rows processed

继续测试:执行下面语句

select object_id from test where object_id > 100 and object_id <200

逻辑度也是9个

Execution Plan
----------------------------------------------------------
Plan hash value: 2176510370

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID">100 AND "OBJECT_ID"<200)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          9 consistent gets
          0 physical reads
          0 redo size
       2225 bytes sent via SQL*Net to client
        558 bytes received via SQL*Net from client
          8 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         98 rows processed

其实如果你dump索引你就会发现

1:create index ind_test on test(object_id)

2: create index ind_test on test(object_id,object_name)

这两个建立索引的语句,其实有很大的差别,索引1leaf仅仅包括了object_id的rowid信息

如何你仅仅查询的是object_id,那没问题你可以在索引里边获得全部的数据,但是如果你还要查询object_name的信息,那么就需要回表查询了,索引2 就不一样了同时包含了

object_id和object_name的信息,全部的数据可以在索引里边获得,不需要回表,性能有很大的提升!要想深刻了解该测试案例,需要dump出索引的信息,我安排在其他文章里边播出.

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

上一篇：绑定变量、BIND PEEKING、histogram(柱状图)的使用(转载)

下一篇： Oracle临时表优化查询速度(转载)

请登录后发表评论登录

全部评论

alsrt

注册时间：2011-05-02

博文量
34
访问量

35577

ITPub博客

oracle sql调优系列4(回表查询与不回表的性能比较)

最新文章