构建案例如下:
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/,如需转载,请注明出处,否则将追究法律责任。