时间：2021-07-01 10:21:17 帮助过：4人阅读
PS： 要唯一索引中才有可能触发唯一索引扫描。主键或唯一约束（如果索引非唯一索引）也是无法触发的。
扫描按顺序搜索索引以查找指定的键。索引唯一扫描一旦找到第一条记录就停止处理，因为不可能有第二条记录。数据库从索引条目中获取行标识，然后检索该行标识所指定的行。
例子:
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
00:46:24 SQL> select * from scott.dept where DEPTNO=1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2852011669
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
-----非唯一索引，无法使用唯一扫
14:09:55 SQL> CREATE TABLE "SCOTT"."DEPT1"
14:15:18 2 ( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
14:15:18 3 14:15:18 4 "LOC" VARCHAR2(13));
Table created.
14:15:19 SQL> create index scott.ind_DEPTNO on "SCOTT"."DEPT1"(DEPTNO);
Index created.
14:16:02 SQL> alter table "SCOTT"."DEPT1" add CONSTRAINT "PK_DEPT1" PRIMARY KEY ("DEPTNO");
14:18:17 SQL> select * from scott.dept1 where DEPTNO=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2017361551
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT1 | 1 | 30 | 0 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_DEPTNO | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
2.Index Range Scans 索引范围扫描
对于索引范围扫描，索引键必须有多个值。
具体来说，优化器在以下情况下考虑索引范围扫描：
在条件中指定索引的一个或多个前导列。
甲条件指定一个或多个表达式和逻辑（布尔）运算符的组合，并返回的值TRUE，FALSE或UNKNOWN。条件的示例包括：
id = :id
id < :id
id > :id
AND 索引中前导列的前述条件的组合，例如id > :low AND id < :hi
范围扫描索引，数据库将在叶块中向后或向前移动。例如，对ID在20到40之间的扫描将找到第一个叶子块，该叶子块的最低键值为20或更大。扫描通过叶节点的链接列表进行水平扫描，直到找到大于40的值，然后停止。
例子:
create table "SCOTT"."DEPT2" as select * from scott.dept;
create index scott.ind_dept2 on table scott.dept2(deptno);
02:36:30 SQL> select * from scott.dept2 where DEPTNO>1;
Execution Plan
----------------------------------------------------------
Plan hash value: 472371293
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 120 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT2 | 4 | 120 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_DEPT2 | 4 | | 1 (0)| 00:00:01 |
---Index Range Scans 如果语句中符合该条件进行范围扫，后面有排序的，将会进行索引范围降序扫描。
INDEX RANGE SCAN DESCENDING
例子:
03:04:16 SQL> select * from scott.dept2 where DEPTNO>1 order by 1 desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 2624219629
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 120 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT2 | 4 | 120 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| IND_DEPT2 | 4 | | 1 (0)| 00:00:01 |
3.Index Full Scans 索引全扫描
索引全扫描可以消除单独的排序操作，因为索引中的数据是按索引键排序的。单块IO扫描
优化器会在各种情况下考虑对索引进行全面扫描。
这些情况包括：
谓词引用索引中的列。该列不必是前导列。
未指定谓词，但满足以下所有条件：
表和查询中的所有列都在索引中。
至少一个索引列不为null。
查询包括一个ORDER BY在索引上的不可为空的列。
03:29:19 SQL> select DEPTNO,DNAME from scott.dept order by DEPTNO; --主键列，不可为空
Execution Plan
----------------------------------------------------------
Plan hash value: 3103054919
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
4.Index Fast Full Scans 索引快速全扫
索引快速全扫描读取未排序的顺序与索引块，该扫描不使用索引来探测表，而是读取索引而不是表，本质上是将索引本身用作表。
当查询仅访问索引中的属性时，优化器将考虑此扫描。数据库使用多块I / O读取根块以及所有叶块和分支块。数据库将忽略分支块和根块，并读取叶块上的索引条目。
04:53:59 SQL> select /*+ index_ffs(dept4 IND_DEP4) */ count(DEPTNO) from scott.dept4 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1367395807
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IND_DEP4 | 1 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
5.Index Skip Scans 索引跳跃扫描
索引跳跃扫描时发生复合索引的初始列是“跳过”或在查询未指定。
通常，跳过扫描索引块比扫描表块快，并且比执行全索引扫描快。
当满足以下条件时，优化器将考虑跳过扫描：
在查询谓词中未指定复合索引的前导列。
例如，查询谓词未引用该DEPTNO列，并且复合索引键为(DEPTNO,DNAME)。
复合索引的前导列中很少有不同的值，但是索引的非前导键中却存在许多不同的值。
例如，如果组合索引键为(DEPTNO,DNAME)，则该DEPTNO列只有两个不同的值，但DNAME有数千个。
索引跳过扫描在逻辑上将组合索引拆分为较小的子索引。索引的前几列中不同值的数量确定逻辑子索引的数量。数字越小，优化器必须创建的逻辑子索引越少，扫描变得越有效。扫描将分别读取每个逻辑索引，并在不超前的列上“跳过”不满足过滤条件的索引块。
DBMS_METADATA.GET_DDL(UPPER(‘TABLE‘),UPPER(‘EMPLOYEE‘),UPPER(‘SYS‘))
------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE "SYS"."EMPLOYEE"
( "GENDER" VARCHAR2(1),
"EMPLOYEE_ID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
INDEX_NAME INDEX_COL INDEX_TYPE PAR
--------------------------------------------- ------------------------------ ---------------------- ---
SYS.IDX_EMPLOYEE GENDER,EMPLOYEE_ID NORMAL-NONUNIQUE NO
05:57:11 SQL> set autotrace traceonly
05:57:19 SQL>
05:57:23 SQL> select * from employee where employee_id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 461756150
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_EMPLOYEE | 1 | 4 | 3 (0)| 00:00:01 |
------------------------------------------------------------------
6.Index Join Scans 索引关联扫描
索引联接扫描是多个索引的哈希联接，它们一起返回查询请求的所有列。数据库不需要访问表，因为所有数据都是从索引中检索的。
在以下情况下，优化器将考虑使用索引联接：
多个索引的哈希联接检索查询所请求的所有数据，而无需访问表。
从表中检索行的成本比不从表中检索行而读取索引要高。索引联接通常很昂贵。例如，在扫描两个索引并将它们结合在一起时，选择最有选择性的索引然后探查表的成本通常较低。
也可以使用提示指定索引连接。 INDEX_JOIN(table_name)
在索引联接扫描中，始终避免表访问。例如，在单个表上联接两个索引的过程如下：
扫描第一个索引以检索行标识。
扫描第二个索引以检索行ID。
通过rowid执行哈希联接以获取行。
------------恢复内容结束------------
关于ORACLE索引的几种扫描方式
标签：了解 int 返回 val cos 选择 lte rom create