1、创建测试表

 CREATE TABLE studneds(
  id NUMBER PRIMARY KEY, 
  name varchar2(64),
  sex VARCHAR(8),
  age int,
  createTime TIMESTAMP 
) 

2、插入测试数据

INSERT INTO studneds (ID,NAME,SEX,AGE,CREATETIME) VALUES 
(1,'张三','男',22,to_timestamp(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'))
INSERT INTO studneds (ID,NAME,SEX,AGE,CREATETIME) VALUES 
(2,'王五','男',22,null)
INSERT INTO studneds (ID,NAME,SEX,AGE,CREATETIME) VALUES 
(3,'李四','男',22,to_timestamp(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'))

3、创建索引 createTime 创建B树索引（默认得索引）、sex创建位图所以

create index studneds_createTime_idx on studneds(createTime) tablespace USERS;
create bitmap index studneds_sex_bitind on studneds(sex) tablespace USERS;

4、执行查询计划（第一中情况全表扫描）

explain plan FOR 
SELECT * FROM studneds;
select * from table(dbms_xplan.display);

Plan hash value: 4261487360
 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     3 |   237 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| STUDNEDS |     3 |   237 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

注意：TABLE ACCESS FULL我们可以看出这次查询是全表扫描得

5、执行查询计划（B树索引查询）

explain plan FOR 
SELECT * FROM studneds
WHERE sex = '男';
select * from table(dbms_xplan.display);

Plan hash value: 833741144
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     3 |   237 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | STUDNEDS            |     3 |   237 |     1   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                     |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | STUDNEDS_SEX_BITIND |       |       |            |          |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("SEX"='男')
 
Note
-----
   - dynamic sampling used for this statement (level=2)

注意：BITMAP INDEX SINGLE VALUE | STUDNEDS_SEX_BITIND，可以看出，使用得是BITMAP类型得索引 STUDNEDS_SEX_BITIND

6、执行查询计划（范围索引查询）

explain plan FOR 
SELECT * FROM studneds  
WHERE CREATETIME < to_date('2020-06-15 23:59:59','yyyy--mm-dd hh24:mi:ss');
select * from table(dbms_xplan.display);

Plan hash value: 3335771644
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                         |     1 |    79 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| STUDNEDS                |     1 |    79 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | STUDNEDS_CREATETIME_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CREATETIME"<TO_DATE('2020-06-15 23:59:59','yyyy--mm-dd hh24:mi:ss'))
 
Note
-----
   - dynamic sampling used for this statement (level=2)