索引分类:
Full Index Scan
数据的获取仅从索引中得到,结果集按序排列(每次得到叶块中的数据都是从branch遍历到leaf block得到,顺序是以引导列为基础进行排序),索引列部分或者全部存在于select中,谓词中以某个或某些非索引引导列作为判断条件,或者没有where子句。
如存在索引EMP_DEPT_NAME_SAL_IX(on employees(department_id,last_name,salary)),如下语句:
HR>select department_id,salary,last_name from employees where salary>8000;
DEPARTMENT_ID SALARY LAST_NAME
------------- ---------- -------------------------
20 13000 Hartstein
30 11000 Raphaely
。。。。。。
33 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 833936428
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 78 | 1170 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | EMP_DEPT_NAME_SAL_IX | 78 | 1170 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
另有查询语句:
HR>select department_id,salary,last_name from employees where last_name like 'A%';
DEPARTMENT_ID SALARY LAST_NAME
------------- ---------- -------------------------
50 2800 Atkinson
60 4800 Austin
。。。。。。
Execution Plan
----------------------------------------------------------
Plan hash value: 833936428
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 45 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | EMP_DEPT_NAME_SAL_IX | 3 | 45 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
对于以引导列存在于谓词中的语句而言,如:
HR>select department_id,salary,last_name from employees where department_id>100;
DEPARTMENT_ID SALARY LAST_NAME
------------- ---------- -------------------------
110 8300 Gietz
110 12008 Higgins
Execution Plan
----------------------------------------------------------
Plan hash value: 2518869551
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 30 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP_DEPT_NAME_SAL_IX | 2 | 30 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
注意:::若引导列出现在where条件中,可能会发生Index Range Scan,或者Index Unique Scan,或者Index Skip Scan。
Fast Full Index Scan
与全索引扫描类似,不需要从表中获取数据,但返回结果集是无序的(在获得第一个叶块之后,沿着双向链接表的方向读取各个叶块中的数据,不再通过branch到达leaf block的方式获取)。
From Oracle Concepts:
索引须包含多有要查询的列;
查询结果不能返回空值(所有列均为空),为保证该结果,需要:
索引列上至少有一列有非空的约束条件,
或者,谓词中指定非空的条件判断。
Index Range Scan
对索引的有序扫描,对于该类的索引有以下特性:
一个或多个索引引导列出现在where子句中;
对每个Index key对应着0,1或者多个索引值(即可能是非唯一性索引)
出索引范围扫描的情形:
1、对于组合索引,谓词中出现了对于索引引导列判断;
2、对于非唯一性索引,谓词中出现范围操作符,如,=,between...and... ;
3、对于唯一性索引,谓词中出现非相等判断的范围操作符,如,between...and... ;
例如,组合索引EMP_DEPT_NAME_SAL_IX(on employees(department_id,last_name,salary)),前面有例子说明。
唯一性索引EMP_EMAIL_UK (on employees(email)),查询如下,
HR>select email from employees where email like 'A%';
EMAIL
-------------------------
ABANDA
ABULL
ACABRIO
。。。。。。
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2492960955
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 32 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP_EMAIL_UK | 4 | 32 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
对非唯一性索引,ind_owner(on test(owner))
SYS>create table hr.test as select * from dba_objects;
Table created.
SYS>create index hr.ind_owner on hr.test(owner);
Index created.
SYS>create index hr.ind_id on hr.test(object_id);
Index created.
SYS>create index hr.ind_owner_name_type on hr.test(owner,object_name,object_type);
Index created.
HR>select owner,object_name,object_type from test where wner='CORA';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
CORA DEPARTMENTS TABLE
CORA DEPT_ID_PK INDEX
。。。。。。
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3692119038
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2276 | 91040 | 18 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_OWNER_NAME_TYPE | 2276 | 91040 | 18 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Index Unique Scan
发生该索引扫描,需要:
1.Index是唯一性索引(存在primary key或者UNIQUE约束条件时,唯一性索引自动创建,或者手工创建)
2.谓词中使用=作为条件判断
以上例,
HR>select owner from test where object_id=10;
OWNER
------------------------------
SYS
Execution Plan
----------------------------------------------------------
Plan hash value: 3701878773
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | TEST_ID_UNQ | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Index Skip Scan
当查询可以通过组合索引得到结果,而且但会结果很少,同时谓词中没有包含索引引导列的时候,可能发生索引跳跃扫描
HR>select owner,object_name,object_type from test where object_name='EMP_DEPT_CLUSTER';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
CORA EMP_DEPT_CLUSTER CLUSTER
Execution Plan
----------------------------------------------------------
Plan hash value: 51567994
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 80 | 34 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IND_OWNER_NAME_TYPE | 2 | 80 | 34 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
因为查询需要的信息通过组合所应获得,谓词中没有引导列owner,且返回的行数很少,所以CBO选择索引跳跃扫描。
HR>select owner,object_name,object_type from test where object_type='INDEX';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
APEX_030200 COL_EXCEPTIONS_PK INDEX
APEX_030200 FND_FLOW_USER_GROUP_PK INDEX
APEX_030200 PARENT_LIST_ITEM_ID_IDX INDEX
APEX_030200 PK_FLOW_PROV_PROJECT_ID INDEX
。。。。。。
3987 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1168656533
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1655 | 66200 | 139 (0)| 00:00:02 |
|* 1 | INDEX FAST FULL SCAN| IND_OWNER_NAME_TYPE | 1655 | 66200 | 139 (0)| 00:00:02 |
--------------------------------------------------------------------------------------------
因为查询需要的信息通过组合所应获得,谓词中没有引导列owner,且返回的行数很多,所以CBO选择索引快速全扫描,以避免全表扫描。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26679417/viewspace-758761/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26679417/viewspace-758761/