Oracle B树索引实测

1.创建测试表cust

create table cust(
  cust_id number not null primary key,
  cust_first_name varchar2(40),
  cust_last_name varchar2(40),
  cust_address varchar2(100)
  )

2.打开oracle自动追踪工具

set autotrace on

3.在没有建立索引的情况下查询数据

select cust_first_name from cust where cust_first_name = ‘chen‘

　　得到输出结果：

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

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

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

|   0 | SELECT STATEMENT  |      |     5 |   110 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| CUST |     5 |   110 |     3   (0)| 00:00:01 |

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

观察Operation和Name可知，进行了全表检索。

4.建立在column cust_first_name上的索引

create index cust_first_name_index on cust (cust_first_name)

5.再次进行第3步中的查询，得到输出结果：

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

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

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

|   0 | SELECT STATEMENT |                       |     1 |    22 |     1   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| CUST_FIRST_NAME_INDEX |     1 |    22 |     1   (0)| 00:00:01 |

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

　　可知Oracle使用索引进行了查询，并且，在对应的索引数据块中可以获取所需字段cust_first_name，所以没有进行表数据块的读取。

6.再次执行查询语句

select * from cust where cust_first_name = ‘chen’

　　得到输出结果：　　

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

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

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

|   0 | SELECT STATEMENT            |                       |     1 |    57 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| CUST                  |     1 |    57 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | CUST_FIRST_NAME_INDEX |     1 |       |     1   (0)| 00:00:01 |

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

可见，读取了索引块以及数据块。

7.创建基于column cust_first_name 和 cust_last_name的索引

create index cust_name_index on cust (cust_first_name,cust_last_name)

8.执行查询语句

select cust_last_name from cust where cust_last_name=’yibo’

　　得到输出结果：　　

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

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

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

|   0 | SELECT STATEMENT |                 |     1 |    22 |     1   (0)| 00:00:01 |

|*  1 |  INDEX FULL SCAN | CUST_NAME_INDEX |     1 |    22 |     1   (0)| 00:00:01 |

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

select cust_last_name from cust where cust_last_name = ‘yibo‘ and cust_first_name = ‘jiang‘

　　得到输出结果：

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

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

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

|   0 | SELECT STATEMENT |                 |     1 |    44 |     1   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| CUST_NAME_INDEX |     1 |    44 |     1   (0)| 00:00:01 |

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

select cust_last_name from cust where cust_last_name = ‘yibo‘ and address = ‘town‘

　　得到输出结果：

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    74 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CUST |     1 |    74 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

观察此三条记录可知，在组合索引的情况下，

　　1.若where子句中的查询条件字段A=（A1,A2,...）为组成组合索引I(I1,I2,....)的子项,即A∈I，并且未对A建立索引，则将以I为索引进行查询。

　　2.若where子句中的查询条件，为A∉I，且未对A建立索引，则将进行全表检索。

9.继续执行sql语句

select cust_first_name,cust_last_name from cust where cust_first_name = ‘chen‘

　　得到输出结果：

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

此处未使用建立在cust_first_name上的索引CUST_FIRST_NAME_INDEX，猜测是因为oracle做了优化，在返回列可用索引CUST_NAME_INDEX的索引数据返回，并且该索引比CUST_FIRST_NAME_INDEX具有更高效率时，使用了CUST_NAME_INDEX。

4.