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)

