oracle-组合索引字段位置与查询效率之间的关系

Oracle索引组合字段的位置不同,当查询条件不能覆盖索引时,影响查询效率。查询条件是不是索引字段的第一列影响执行计划,实验验证

实验1:查询条件为组合索引的第一列
--
创建测试表 create table test_C as select * from user_objects where 1=2; --创建测试表 create table test_a as select * from user_objects where 1=1; --创建组合索引 create index test_a_1 on test_a(object_id,object_type); set linesize 1000; set autotrace trace; select * from test_a where object_id=161728; **************************************************** 执行计划 ---------------------------------------------------------- Plan hash value: 3036018411 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 116 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_A | 1 | 116 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST_A_1 | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=161728) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1933 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

查询条件object_id为索引的前导列。执行计划:INDEX RANGE SCAN,cost是3,逻辑读是3.

drop index test_a_1;
drop index test_a_2;
--创建索引2,查询条件在索引中的位置是第二列
create index test_a_2 on test_a(object_name,object_ID);
--再次查询
select * from test_a where object_id=161728;
****************************************************
执行计划
----------------------------------------------------------
Plan hash value: 219338936

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     1 |   116 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_A   |     1 |   116 |     7   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | TEST_A_2 |     1 |       |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=161728)
       filter("OBJECT_ID"=161728)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1933  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

查询条件object_id为索引的第二列。执行计划:INDEX SKIP SCAN,cost是7,逻辑读是8.

很明显组合索引,字段的位置影响查询效率,所以在建索引的时候需注意。

oracle-组合索引字段位置与查询效率之间的关系

上一篇:数据库MySQL经典面试题之SQL语句


下一篇:MySQL分区方法及注意点