INDEX UNIQUE SCAN 索引唯一扫描。单块读 只可能发生在unique index/primary key 等值查找
等待事件:db file sequential read 但是你几乎看不到,因为只读一条数据
HINT 无需指定,有索引会自动走INDEX UNIQUE SCAN
唯一索引等值扫描,需要几个I/O?
需要索引高度个IO
INDEX UNIQUE SCAN 索引唯一扫描。单块读 只可能发生在unique index/primary key 等值查找
等待事件:db file sequential read 但是你几乎看不到,因为只读一条数据
HINT 无需指定,有索引会自动走INDEX UNIQUE SCAN
唯一索引等值扫描,需要几个I/O?需要索引高度个IO
转载于:https://www.cnblogs.com/zhaoyangjian724/p/3797938.html
Index Unique Scan唯一索引(B-tree)
Index unique scan is one of the most efficient ways of accessing data. This access method is used for returning the data from B-tree indexes. The optimizer chooses a unique scan when all columns of a unique (B-tree) index are specified with equality conditions.Index Range Scan 范围(升序 相同值根据ROWID升)
Index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted (in ascending order) by the ROWIDs.Bitmap Indexes(节省空间;不适合修改多的事务操作;不适合范围条件的查询,适合相等条件的查询;适用类型手机号;可以有NULL值跟其他索引不一样)
The purpose of an index is to provide pointers to the rows in a table that contain a given key value. In a regular index, this is achieved by storing a list of rowids for each key corresponding to the rows with that key value. Oracle stores each key value repeatedly with each stored rowid. In a bitmap index, a bitmap for each key value is used instead of a list of rowids.
http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#sthref984
两篇还不错的文章:
http://blog.csdn.net/robinson1988/article/details/4980611
http://www.cnblogs.com/tracy/archive/2011/09/02/2163462.html
索引唯一性扫描(INDEX UNIQUE SCAN)是针对唯一性索引(UNIQUE INDEX)的扫描,它仅仅适用于where条件里是等值查询的目标SQL。因为扫描的对象是唯一性索引,所以索引唯一性扫描的结果至多只会返回一条记录。
Emp表中empno为primary key,对应一个unique index
View Code
SCOTT@PDBORCL> select * from emp where empno=7369;
执行计划
----------------------------------------------------------
Plan hash value: 2949544139--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("EMPNO"=7369)
统计信息
----------------------------------------------------------
69 recursive calls
0 db block gets
95 consistent gets
21 physical reads
0 redo size
898 bytes sent via SQL*Net to client
533 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
1 rows processedSCOTT@PDBORCL>
从上述显示内容可以看出,"select * from emp where empno=7369"的执行计划走的是索引唯一性扫描
转载于:https://my.oschina.net/wangshengzhuang/blog/785041
index unique scan ,是因为建了唯一索引, unique
如 :create unique index AA_INDEX on AA_TEST (SID, ITEMVALUE)
在执行语句中 where子句必须包括 唯一索引中的所有字段,而不用管select是否是索引中的字段.
analyze table aa_test compute statistics;
select sid,itemvalue from aa_test t where sid=1;
这个用 index range scan
select sid,itemvalue from aa_test t where itemvalue ='100';
这个用index full scan
也就是说如果在where子句中没有选择 唯一索引的前置索引字段,而是唯一索引的其他的字段,就会变成
index full scan
而对其他索引(不是唯一索引)中,只用索引中的某个字段(不是前置),只是 index range scan,不是index full scan,
从某些文档中知道 index full scan是index range scan的一个极限,当从index的叶子从左找到右时,就是index full scan.
下面是强制用 跳跃索引
select /*+ index_ss(aa_t inde_aa_t) */ aa_t.object_type
from aa_t where object_id =2
from :http://space.itpub.net/12712263/viewspace-606960