oracle表访问方式
0.参考文献：
Index Full Scan && Index Range Scan
oracle-index unique scan 与index range scan等的区别
index range scan,index fast full scan,index skip scan发生的条件
1.oracle访问表的方式
oracle 访问表中记录有三种方式：
ORACLE 采用三种访问表中记录的方式: 全表扫描、通过ROWID访问表、索引扫描
2.全表扫描（Full Table Scans, FTS）
为实现全表扫描，Oracle顺序地访问表中每条记录，并检查每一条记录是否满足WHERE语句的限制条件。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描。需要注意的是只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。
使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% -- 10%，或你想使用并行查询功能时。
全表扫描实例（TABLE ACCESS FULL）
创建表并插入数据，并进行查询。
View Code
--创建数据库 CREATE TABLE "USERINFO" ( "NO" INT , "NAME" VARCHAR2(50), "BIRTHDAY" DATE ) --创建序列 CREATE SEQUENCE SEQ_USERINFO_NO INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 --插入100000条数据 begin for i in 1..100000 loop INSERT INTO USERINFO VALUES(SEQ_USERINFO_NO.nextval,'XUWEI',SYSDATE); end loop; end; / --查询NO=5000的结果 set autotrace traceonly select * from userinfo where no = 5000; 执行计划 ---------------------------------------------------------- Plan hash value: 3576123897 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 49 | 102 (0)| 00:00:02 | |* 1 | TABLE ACCESS FULL| USERINFO | 1 | 49 | 102 (0)| 00:00:02 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NO"=5000) Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 5 recursive calls 0 db block gets 119023 consistent gets 0 physical reads 25048 redo size 391 bytes sent via SQL*Net to client 405 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
从查询计划我们可以看到所采用的查询方式是“TABLE ACCESS FULL”，这就是全表扫描。也正是因为采用全表扫描，所以consistent gets会很大。
3.通过ROWID访问表（table access by ROWID）
ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。为了通过ROWID存取表，Oracle 首先要获取被选择行的ROWID，或者从语句的WHERE子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。下面给出使用rowid访问表的实例。
3.1.单个rowid的情形
View Code
--查看表上rowid select rowid,empno,ename from emp where deptno=20; --得到的查询结果 ROWID EMPNO ENAME ------------------ ---------- ---------- AAAX7bAAEAAAo1VAAA 7369 SMITH AAAX7bAAEAAAo1VAAD 7566 JONES AAAX7bAAEAAAo1VAAH 7788 SCOTT AAAX7bAAEAAAo1VAAK 7876 ADAMS AAAX7bAAEAAAo1VAAM 7902 FORD --根据rowid查询记录 set autotrace on select empno,ename from emp where rowid='AAAX7bAAEAAAo1VAAM'; --查询结果 EMPNO ENAME ---------- ---------- 7902 FORD 执行计划 ---------------------------------------------------------- Plan hash value: 1116584662 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 32 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 477 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
查询计划中说明该查询是的表访问方式是”TABLE ACCESS BY USER ROWID“，也就是直接通过USER ROWID来访问，这也是为什么只需要1次consistent gets的原因。
3.2.多个rowid的倾向
使用如下查询进行范围查询
View Code
--使用多个rowid的情形 select empno,ename from emp where rowid in ('AAAX7bAAEAAAo1VAAA','AAAX7bAAEAAAo1VAAD'); --查询结果 EMPNO ENAME ---------- ---------- 7369 SMITH 7566 JONES 执行计划 ---------------------------------------------------------- Plan hash value: 1106538681 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | | 2 | TABLE ACCESS BY USER ROWID| EMP | 1 | 32 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 524 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed
查询计划分析：
- 上面的执行计划中出现了INLIST ITERATOR，即INLIST迭代，该操作说明其子操作多次重复时，会出现该操作。
- 由于我们使用了in运算，且传递了2个rowid，故出现INLIST迭代操作
- 迭代操作意味着条件中的对象列表一个接一个的迭代传递给子操作
- 此时统计信息中的consistent gets为2，并不是因为传入的rowid有2个，假如传入的rowid有4个，consistent gets也等于4。
注意：使用ROWID进行查询的前提是我们明确知道了一个正确的ROWID，然后通过这个ROWID进行查询。所以这里所提到的所有ROWID必须是真实存在的，否则会报错。
4.索引扫描（Index scan）
我们先通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找(index lookup)。一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，在此情况下该次i/o只会读取一个数据库块。
在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。索引扫描可以由2步组成：(1) 扫描索引得到对应的rowid值。 (2) 通过找到的rowid从表中读出具体的数据。
根据索引的类型与where限制条件的不同，有4种类型的索引扫描。
4.1.索引范围扫描(INDEX RANGE SCAN)
使用一个索引存取多行数据，在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(如>、<、<>、>=、<=、between)。在非唯一索引上，谓词"="也可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。
使用index rang scan的3种情况：
- 在唯一索引列上使用了range操作符(> < <> >= <= between)
- 在组合索引上，只使用部分列进行查询，导致查询出多行
- 对非唯一索引列上进行的任何查询。
4.1.1实例
跟全表扫描一样，使用userinfo表作为实例介绍
View Code
--为userinfo表中的no字段创建索引 IX_USERINFO_NO create index IX_USERINFO_NO on USERINFO(NO); --查询no在1000到1005之间的记录 select * from userinfo where no between 1000 and 1005; --查询结果 NO NAME BIRTHDAY ---------- -------------------------------------------------- -------------- 1000 XUWEI 12-6月 -12 1001 XUWEI 12-6月 -12 1002 XUWEI 12-6月 -12 1003 XUWEI 12-6月 -12 1004 XUWEI 12-6月 -12 1005 XUWEI 12-6月 -12 执行计划 ---------------------------------------------------------- Plan hash value: 1066629497 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 126 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| USERINFO | 7 | 126 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IX_USERINFO_NO | 7 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NO">=1000 AND "NO"<=1005) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 692 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6 rows processed
4.2.索引唯一扫描(INDEX UNIQUE SCAN)
通过唯一索引查找一个数值经常返回单个ROWID。如果存在UNIQUE 或PRIMARY KEY 约束(它保证了语句只存取单行)的话，Oracle经常实现唯一性扫描。
下面依然以userinfo表为例进行举例说明。首先删除原先在no字段上创建的索引，然后将no字段设为主键，再进行“=”的查询，比如查询no=5000的字段，代码实例如下：
View Code
--删除索引 drop index IX_USERINFO_NO; --添加主键 alter table USERINFO add constraint PK_USERINFO_NO primary key(NO); --查询no为5000的记录 set autotrace traceonly select * from userinfo where no = 5000; 执行计划 ---------------------------------------------------------- Plan hash value: 4161181038 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 49 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| USERINFO | 1 | 49 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_USERINFO_NO | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NO"=5000) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 447 bytes sent via SQL*Net to client 405 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
4.3.索引全扫描(index full scan)
有顺序的输出，不能并行访问索引。
4.4.索引快速扫描(index fast full scan)
Fast Full Index Scans（来自官方文档）
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query（组合索引中的列包含了需要查询的所有列）, and at least one column in the index key has the NOT NULL constraint（至少有一个有非空约束）. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.
A fast full scan is faster than a normal full index scan in that it can use multiblock I/O（一次可以读多个块，跟全表扫描一样） and can be parallelized just like a table scan.
实例
View Code
--创建测试表 create table test as select * from all_objects; --创建唯一索引 create unique index IX_OBJECT_ID on test(object_id); --创建组合索引 create index IX2 on test(owner,object_name,object_type); --INDEX RANGE SCAN select owner,object_name,object_type from test where owner='SCOTT'; 执行计划 ---------------------------------------------------------- Plan hash value: 3671601508 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 270 | 3 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IX2 | 6 | 270 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------- --INDEX FAST FULL SCAN select owner, object_name,object_type from test where object_name='EMP' ; 执行计划 ---------------------------------------------------------- Plan hash value: 4039984554 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 495 | 136 (1)| 00:00:02 | |* 1 | INDEX FAST FULL SCAN| IX2 | 11 | 495 | 136 (1)| 00:00:02 | ----------------------------------------------------------------------------- --INDEX FAST FULL SCAN select owner, object_name,object_type from test where object_type='INDEX'; 执行计划 ---------------------------------------------------------- Plan hash value: 4039984554 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1288 | 57960 | 136 (1)| 00:00:02 | |* 1 | INDEX FAST FULL SCAN| IX2 | 1288 | 57960 | 136 (1)| 00:00:02 | -----------------------------------------------------------------------------
4.5.index skip scan(索引跳跃式扫描)
Index Skip Scans
Index skip scans improve index scans by no nprefix columns. Often, scanning index blocks is faster than sc anning table data blocks.
Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.
The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageo us if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.
当查询可以通过组合索引得到结果，而且返回结果很少，并且where条件中没有包含索引引导列的时候，可能执行index skip scan。
索引跳跃式扫描发生的条件:
- 必须是组合索引。
- 引导列没有出现在where条件中。
