ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle sql调优系列4(回表查询与不回表的性能比较)

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
--------------------------------------------------------------------------------
--------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti
me     |
--------------------------------------------------------------------------------
--------
|   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
-----------------------------------------------------------------------------
| 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
----------------------------------------------------------
          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/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2011-05-02

  • 博文量
    34
  • 访问量
    33539