关于ORACLE索引的几种扫描方式

Question

如题所述

Answer 1

具体来说，仅当查询谓词使用等号运算符引用唯一索引键中的所有列时，数据库才执行唯一扫描.

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返回valcos选择lteromcreate