♣题目 部分
在Oracle中，表的访问方式有哪几种？
♣答案部分
访问表的方式也叫优化器访问路径，主要有3种访问路径：全表扫描（FULL TABLE SCAN，FTS）、索引扫描（INDEX SCAN）和ROWID访问。
（一）全表扫描（FULL TABLE SCAN，FTS）
全表扫描将读取高水位（High Warter Mark，HWM）之下的所有数据块，所有行都要经WHERE子句过滤来判断是否满足条件。当Oracle执行全表扫描时，会按顺序读取每个块且只读一次，如果能够一次读取多个块，那么可以有效地提高效率，初始化参数DB_FILE_MULTIBLOCK_READ_COUNT用来设置在一次I/O中可以读取多少个数据块。通常应该避免全表扫描，但是在检索大量数据时全表扫描优于索引扫描，这正是因为全表扫描可以在一次I/O中读取多个块，从而减少了I/O的次数。在使用全表扫描的同时也可以使用并行来提高扫描的速度。全表扫描的Hint为：FULL(T)。
CBO优化器在以下几种情况下会选择全表扫描：
① 无合适的索引。
② 检索表中绝大多数的数据。
③ 表非常小。例如，表中的块小于DB_FILE_MULTIBLOCK_READ_COUNT，只需一次I/O。如果这样的表被频繁使用，应该执行“ALTER TABLE TABLE_NAME STORAGE(BUFFER_POOL KEEP);”将表保存在内存中。
④ 高并行度。如果在表级设置了较高的并行度，例如“ALTER TABLE T_NAME PARALLEL 4;”，那么通常会选择全表扫描。通常建议在语句级用HINT来实现并行，例如/*+ FULL(T_NAME) PARALLEL(T_NAME 4)*/。
⑤ 太旧的统计数据。如果表没有进行过分析或很久没有再次分析，那么CBO可能会错误的认为表含有及少的数据块。
⑥ 在语句中嵌入了全表扫描的Hint。
⑦ WHERE子句的索引列上只存在极少数不同的值。
需要注意的是，由于全表扫描是扫描高水位以下的所有数据块，所以即使使用DELETE语句清空了目标表中的所有数据，高水位线还是会在原来的位置，这意味着对该表的全表扫描操作所耗费的时间与删除之前相比并不会有明显的改观。
（二）索引扫描（INDEX SCAN）
索引不仅包含被索引的字段值，还包含行的位置标识ROWID，如果SQL语句只检索索引字段，那么Oracle将直接从索引中读取而不需要通过ROWID去访问表；如果SQL语句通过索引检索其它字段值，那么Oracle通过索引获得ROWID再回表读就可以迅速找到需要的内容。
索引扫描类型有如下几种类型：
（1）索引唯一扫描（INDEX UNIQUE SCAN）
索引唯一扫描是针对唯一性索引（UNIQUE INDEX）的扫描，它仅仅适用于WHERE条件里是等值查询的目标SQL。因为扫描的对象是唯一性索引，所以索引唯一性扫描的结果至多只会返回一条记录。对于组合唯一索引而言，WHERE条件需要列出所有的索引列才能使用索引唯一扫描。
1LHR@orclasm > set line 9999 2LHR@orclasm > select * from scott.emp t where t.empno=10; 3 4Execution Plan 5---------------------------------------------------------- 6Plan hash value: 2949544139 7 8-------------------------------------------------------------------------------------- 9| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |10--------------------------------------------------------------------------------------11| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |12| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |13|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |14--------------------------------------------------------------------------------------1516Predicate Information (identified by operation id):17---------------------------------------------------1819 2 - access("T"."EMPNO"=10)2021LHR@orclasm > select * from scott.emp t where t.empno>=10 and t.empno<=10;2223Execution Plan24----------------------------------------------------------25Plan hash value: 29495441392627--------------------------------------------------------------------------------------28| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |29--------------------------------------------------------------------------------------30| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |31| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |32|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |33--------------------------------------------------------------------------------------3435Predicate Information (identified by operation id):36---------------------------------------------------3738 2 - access("T"."EMPNO"=10)3940LHR@orclasm > create table t_emp_lhr as select * from scott.emp;4142Table created.43LHR@orclasm > create unique index idx_dup_lhr on t_emp_lhr(empno,ename,job);4445Index created.4647LHR@orclasm > select * from t_emp_lhr t where t.empno=7369 and t.ename='lhr';4849Execution Plan50----------------------------------------------------------51Plan hash value: 24956576055253-------------------------------------------------------------------------------------------54| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |55-------------------------------------------------------------------------------------------56| 0 | SELECT STATEMENT | | 1 | 87 | 0 (0)| 00:00:01 |57| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP_LHR | 1 | 87 | 0 (0)| 00:00:01 |58|* 2 | INDEX RANGE SCAN | IDX_DUP_LHR | 1 | | 0 (0)| 00:00:01 |59-------------------------------------------------------------------------------------------6061Predicate Information (identified by operation id):62---------------------------------------------------6364 2 - access("T"."EMPNO"=7369 AND "T"."ENAME"='lhr')6566Note67-----68 - dynamic sampling used for this statement (level=2)6970LHR@orclasm > select * from t_emp_lhr t where t.empno=7369 and t.ename='lhr' and t.job='dba';7172Execution Plan73----------------------------------------------------------74Plan hash value: 8596933667576-------------------------------------------------------------------------------------------77| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |78-------------------------------------------------------------------------------------------79| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |80| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP_LHR | 1 | 87 | 1 (0)| 00:00:01 |81|* 2 | INDEX UNIQUE SCAN | IDX_DUP_LHR | 1 | | 0 (0)| 00:00:01 |82-------------------------------------------------------------------------------------------8384Predicate Information (identified by operation id):85---------------------------------------------------8687 2 - access("T"."EMPNO"=7369 AND "T"."ENAME"='lhr' AND "T"."JOB"='dba')set line 9999
2LHR@orclasm > select * from scott.emp t where t.empno=10;
3
4Execution Plan
5----------------------------------------------------------
6Plan hash value: 2949544139
7
8--------------------------------------------------------------------------------------
9| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
10--------------------------------------------------------------------------------------
11| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
12| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
13|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
14--------------------------------------------------------------------------------------
15
16Predicate Information (identified by operation id):
17---------------------------------------------------
18
19 2 - access("T"."EMPNO"=10)
20
21LHR@orclasm > select * from scott.emp t where t.empno>=10 and t.empno<=10;
22
23Execution Plan
24----------------------------------------------------------
25Plan hash value: 2949544139
26
27--------------------------------------------------------------------------------------
28| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
29--------------------------------------------------------------------------------------
30| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
31| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
32|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
33--------------------------------------------------------------------------------------
34
35Predicate Information (identified by operation id):
36---------------------------------------------------
37
38 2 - access("T"."EMPNO"=10)
39
40LHR@orclasm > create table t_emp_lhr as select * from scott.emp;
41
42Table created.
43LHR@orclasm > create unique index idx_dup_lhr on t_emp_lhr(empno,ename,job);
44
45Index created.
46
47LHR@orclasm > select * from t_emp_lhr t where t.empno=7369 and t.ename='lhr';
48
49Execution Plan
50----------------------------------------------------------
51Plan hash value: 2495657605
52
53-------------------------------------------------------------------------------------------
54| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
55-------------------------------------------------------------------------------------------
56| 0 | SELECT STATEMENT | | 1 | 87 | 0 (0)| 00:00:01 |
57| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP_LHR | 1 | 87 | 0 (0)| 00:00:01 |
58|* 2 | INDEX RANGE SCAN | IDX_DUP_LHR | 1 | | 0 (0)| 00:00:01 |
59-------------------------------------------------------------------------------------------
60
61Predicate Information (identified by operation id):
62---------------------------------------------------
63
64 2 - access("T"."EMPNO"=7369 AND "T"."ENAME"='lhr')
65
66Note
67-----
68 - dynamic sampling used for this statement (level=2)
69
70LHR@orclasm > select * from t_emp_lhr t where t.empno=7369 and t.ename='lhr' and t.job='dba';
71
72Execution Plan
73----------------------------------------------------------
74Plan hash value: 859693366
75
76-------------------------------------------------------------------------------------------
77| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
78-------------------------------------------------------------------------------------------
79| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |
80| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP_LHR | 1 | 87 | 1 (0)| 00:00:01 |
81|* 2 | INDEX UNIQUE SCAN | IDX_DUP_LHR | 1 | | 0 (0)| 00:00:01 |
82-------------------------------------------------------------------------------------------
83
84Predicate Information (identified by operation id):
85---------------------------------------------------
86
87 2 - access("T"."EMPNO"=7369 AND "T"."ENAME"='lhr' AND "T"."JOB"='dba')
（2）索引范围扫描（INDEX RANGE SCAN）
使用索引范围扫描的3种情况：①在唯一索引列上使用了范围操作符（>、<、<>、>=、<=、BETWEEN）。②在组合索引上，只使用部分列进行查询，导致查询出多行。③在非唯一索引列上进行的任何查询。
1LHR@orclasm > select * from scott.emp t where t.empno>=10 and t.empno<=20; 2 3Execution Plan 4---------------------------------------------------------- 5Plan hash value: 169057108 6 7-------------------------------------------------------------------------------------- 8| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 9--------------------------------------------------------------------------------------10| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |11| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |12|* 2 | INDEX RANGE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |13--------------------------------------------------------------------------------------1415Predicate Information (identified by operation id):16---------------------------------------------------1718 2 - access("T"."EMPNO">=10 AND "T"."EMPNO"<=20)1920LHR@orclasm > select * from t_emp_lhr t where t.empno=7369 and t.ename='lhr';2122Execution Plan23----------------------------------------------------------24Plan hash value: 24956576052526-------------------------------------------------------------------------------------------27| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |28-------------------------------------------------------------------------------------------29| 0 | SELECT STATEMENT | | 1 | 87 | 0 (0)| 00:00:01 |30| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP_LHR | 1 | 87 | 0 (0)| 00:00:01 |31|* 2 | INDEX RANGE SCAN | IDX_DUP_LHR | 1 | | 0 (0)| 00:00:01 |32-------------------------------------------------------------------------------------------3334Predicate Information (identified by operation id):35---------------------------------------------------3637 2 - access("T"."EMPNO"=7369 AND "T"."ENAME"='lhr')3839Note40-----41 - dynamic sampling used for this statement (level=2)424344LHR@orclasm > create index idx_nounique_lhr on t_emp_lhr(DEPTNO);4546Index created.4748LHR@orclasm > select * from t_emp_lhr t where t.deptno=7369;4950Execution Plan51----------------------------------------------------------52Plan hash value: 42625409015354------------------------------------------------------------------------------------------------55| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |56------------------------------------------------------------------------------------------------57| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |58| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP_LHR | 1 | 87 | 1 (0)| 00:00:01 |59|* 2 | INDEX RANGE SCAN | IDX_NOUNIQUE_LHR | 1 | | 1 (0)| 00:00:01 |60------------------------------------------------------------------------------------------------6162Predicate Information (identified by operation id):63---------------------------------------------------6465 2 - access("T"."DEPTNO"=7369)6667Note68-----69 - dynamic sampling used for this statement (level=2)7071--索引降序范围扫描（INDEX RANGE SCAN DESCENDING）72LHR@orclasm > select * from t_emp_lhr t where t.deptno between 7369 and 8000 order by deptno desc;7374Execution Plan75----------------------------------------------------------76Plan hash value: 30394887927778-------------------------------------------------------------------------------------------------79| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |80-------------------------------------------------------------------------------------------------81| 0 | SELECT STATEMENT | | 1 | 87 | 0 (0)| 00:00:01 |82| 1 | TABLE ACCESS BY INDEX ROWID | T_EMP_LHR | 1 | 87 | 0 (0)| 00:00:01 |83|* 2 | INDEX RANGE SCAN DESCENDING| IDX_NOUNIQUE_LHR | 1 | | 0 (0)| 00:00:01 |84-------------------------------------------------------------------------------------------------8586Predicate Information (identified by operation id):87---------------------------------------------------8889 2 - access("T"."DEPTNO">=7369 AND "T"."DEPTNO"<=8000)9091Note92-----93 - dynamic sampling used for this statement (level=2)select * from scott.emp t where t.empno>=10 and t.empno<=20;
2
3Execution Plan
4----------------------------------------------------------
5Plan hash value: 169057108
6
7--------------------------------------------------------------------------------------
8| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
9--------------------------------------------------------------------------------------
10| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
11| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |
12|* 2 | INDEX RANGE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
13--------------------------------------------------------------------------------------
14
15Predicate Information (identified by operation id):
16---------------------------------------------------
17
18 2 - access("T"."EMPNO">=10 AND "T"."EMPNO"<=20)
19
20LHR@orclasm > select * from t_emp_lhr t where t.empno=7369 and t.ename='lhr';
21
22Execution Plan
23----------------------------------------------------------
24Plan hash value: 2495657605
25
26-------------------------------------------------------------------------------------------
27| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
28-------------------------------------------------------------------------------------------
29| 0 | SELECT STATEMENT | | 1 | 87 | 0 (0)| 00:00:01 |
30| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP_LHR | 1 | 87 | 0 (0)| 00:00:01 |
31|* 2 | INDEX RANGE SCAN | IDX_DUP_LHR | 1 | | 0 (0)| 00:00:01 |
32-------------------------------------------------------------------------------------------
33
34Predicate Information (identified by operation id):
35---------------------------------------------------
36
37 2 - access("T"."EMPNO"=7369 AND "T"."ENAME"='lhr')
38
39Note
40-----
41 - dynamic sampling used for this statement (level=2)
42
43
44LHR@orclasm > create index idx_nounique_lhr on t_emp_lhr(DEPTNO);
45
46Index created.
47
48LHR@orclasm > select * from t_emp_lhr t where t.deptno=7369;
49
50Execution Plan
51----------------------------------------------------------
52Plan hash value: 4262540901
53
54------------------------------------------------------------------------------------------------
55| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
56------------------------------------------------------------------------------------------------
57| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |
58| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP_LHR | 1 | 87 | 1 (0)| 00:00:01 |
59|* 2 | INDEX RANGE SCAN | IDX_NOUNIQUE_LHR | 1 | | 1 (0)| 00:00:01 |
60------------------------------------------------------------------------------------------------
61
62Predicate Information (identified by operation id):
63---------------------------------------------------
64
65 2 - access("T"."DEPTNO"=7369)
66
67Note
68-----
69 - dynamic sampling used for this statement (level=2)
70
71--索引降序范围扫描（INDEX RANGE SCAN DESCENDING）
72LHR@orclasm > select * from t_emp_lhr t where t.deptno between 7369 and 8000 order by deptno desc;
73
74Execution Plan
75----------------------------------------------------------
76Plan hash value: 3039488792
77
78-------------------------------------------------------------------------------------------------
79| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
80-------------------------------------------------------------------------------------------------
81| 0 | SELECT STATEMENT | | 1 | 87 | 0 (0)| 00:00:01 |
82| 1 | TABLE ACCESS BY INDEX ROWID | T_EMP_LHR | 1 | 87 | 0 (0)| 00:00:01 |
83|* 2 | INDEX RANGE SCAN DESCENDING| IDX_NOUNIQUE_LHR | 1 | | 0 (0)| 00:00:01 |
84-------------------------------------------------------------------------------------------------
85
86Predicate Information (identified by operation id):
87---------------------------------------------------
88
89 2 - access("T"."DEPTNO">=7369 AND "T"."DEPTNO"<=8000)
90
91Note
92-----
93 - dynamic sampling used for this statement (level=2)
（3）索引全扫描（INDEX FULL SCAN）
索引全扫描需要扫描目标索引所有叶子块的所有索引行。这里需要注意的是，索引全扫描需要扫描目标索引的所有叶子块，但这并不意味着需要扫描该索引的所有分支块。在默认情况下，Oracle在做索引全扫描时只需要通过访问必要的分支块定位到位于该索引最左边的叶子块的第一行索引行，就可以利用该索引叶子块之间的双向指针链表，从左至右依次顺序扫描该索引所有叶子块的所有索引行了。索引全扫描的执行结果是有序的，并且是按照该索引的索引键值列来排序，这也意味着走索引全扫描能够既达到排序的效果，又同时避免了对该索引的索引键值列的真正排序操作默认情况下，索引全扫描的扫描结果的有序性就决定了索引全扫描是不能够并行执行的，并且通常情况下索引全扫描使用的是单块读。通常情况下，索引全扫描是不需要回表的，所以索引全扫描适用于目标SQL的查询列全部是目标索引的索引键值列的情形。默认情况下，索引全扫描的扫描结果的有序性就决定了索引全扫描是不能够并行执行的，并且通常情况下索引全扫描使用的是单块读。通常情况下，索引全扫描是不需要回表的，所以索引全扫描适用于目标SQL的查询列全部是目标索引的索引键值列的情形。Oracle中能做索引全扫描的前提条件是目标索引至少有一个索引键值列的属性是NOT NULL。
1LHR@orclasm > create index idx_full_emp_lhr on scott.emp(empno,ename); 2 3Index created. 4 5LHR@orclasm > select empno, ename from scott.emp order by empno,ename; 6 7Execution Plan 8---------------------------------------------------------- 9Plan hash value: 37928931511011-------------------------------------------------------------------------------------12| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |13-------------------------------------------------------------------------------------14| 0 | SELECT STATEMENT | | 14 | 140 | 1 (0)| 00:00:01 |15| 1 | INDEX FULL SCAN | IDX_FULL_EMP_LHR | 14 | 140 | 1 (0)| 00:00:01 |16-------------------------------------------------------------------------------------create index idx_full_emp_lhr on scott.emp(empno,ename);
2
3Index created.
4
5LHR@orclasm > select empno, ename from scott.emp order by empno,ename;
6
7Execution Plan
8----------------------------------------------------------
9Plan hash value: 3792893151
10
11-------------------------------------------------------------------------------------
12| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
13-------------------------------------------------------------------------------------
14| 0 | SELECT STATEMENT | | 14 | 140 | 1 (0)| 00:00:01 |
15| 1 | INDEX FULL SCAN | IDX_FULL_EMP_LHR | 14 | 140 | 1 (0)| 00:00:01 |
16-------------------------------------------------------------------------------------
（4）索引快速全扫描（INDEX FAST FULL SCAN）
和索引全扫描一样，索引快速全扫描也需要扫描目标索引所有叶子块的所有索引行。索引快速全扫描的Hint为INDEX_FFS。索引快速全扫描与索引全扫描相比有如下三点区别：
① 索引快速全扫描只适用于CBO，而索引全扫描既可以用于CBO也可以用于RBO。
② 索引快速全扫描可以使用多块读，也可以并行执行。
③ 索引快速全扫描的执行结果不一定是有序的。这是因为索引快速全扫描时Oracle是根据索引行在磁盘上的物理存储顺序来扫描，而不是根据索引行的逻辑顺序来扫描的，所以扫描结果才不一定有序（对于单个索引叶子块中的索引行而言，其物理存储顺序和逻辑存储顺序一致；但对于物理存储位置相邻的索引叶子块而言，块与块之间索引行的物理存储顺序则不一定在逻辑上有序）。
1LHR@orclasm > select /*+ index_ffs(t) */ empno from scott.emp t where empno>0; 2 3Execution Plan 4---------------------------------------------------------- 5Plan hash value: 36645660 6 7----------------------------------------------------------------------------------------- 8| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 9-----------------------------------------------------------------------------------------10| 0 | SELECT STATEMENT | | 14 | 56 | 2 (0)| 00:00:01 |11|* 1 | INDEX FAST FULL SCAN| IDX_FULL_EMP_LHR | 14 | 56 | 2 (0)| 00:00:01 |12-----------------------------------------------------------------------------------------1314Predicate Information (identified by operation id):15---------------------------------------------------1617 1 - filter("EMPNO">0)select /*+ index_ffs(t) */ empno from scott.emp t where empno>0;
2
3Execution Plan
4----------------------------------------------------------
5Plan hash value: 36645660
6
7-----------------------------------------------------------------------------------------
8| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
9-----------------------------------------------------------------------------------------
10| 0 | SELECT STATEMENT | | 14 | 56 | 2 (0)| 00:00:01 |
11|* 1 | INDEX FAST FULL SCAN| IDX_FULL_EMP_LHR | 14 | 56 | 2 (0)| 00:00:01 |
12-----------------------------------------------------------------------------------------
13
14Predicate Information (identified by operation id):
15---------------------------------------------------
16
17 1 - filter("EMPNO">0)
（5）索引跳跃扫描（INDEX SKIP SCAN）
索引跳跃扫描发生在多个列建立的复合索引上，如果SQL中谓词条件只包含索引中的部分列，并且这些列不包含建立索引时的第一列（前导列），就可能发生索引跳跃扫描。索引跳跃扫描仅仅适用于那些目标索引前导列的DISTINCT值数量较少、后续非前导列的可选择性又非常好的情形，因为索引跳跃扫描的执行效率一定会随着目标索引前导列的DISTINCT值数量的递增而递减。可以通过“ALTER SYSTEM SET "_OPTIMIZER_SKIP_SCAN_ENABLED" = FALSE SCOPE=SPFILE;”来禁用索引跳跃扫描。索引跳跃扫描的Hint为INDEX_SS。
1LHR@orclasm > select /*+index_ss(t)*/ * from t_emp_lhr t where t.ename='lhr'; 2 3Execution Plan 4---------------------------------------------------------- 5Plan hash value: 3374324980 6 7------------------------------------------------------------------------------------------- 8| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 9-------------------------------------------------------------------------------------------10| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |11| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP_LHR | 1 | 87 | 2 (0)| 00:00:01 |12|* 2 | INDEX SKIP SCAN | IDX_DUP_LHR | 1 | | 1 (0)| 00:00:01 |13-------------------------------------------------------------------------------------------1415Predicate Information (identified by operation id):16---------------------------------------------------1718 2 - access("T"."ENAME"='lhr')19 filter("T"."ENAME"='lhr')2021Note22-----23 - dynamic sampling used for this statement (level=2)2425LHR@orclasm > create table t_idxss_20170607_lhr as select owner,object_id,object_type,created from dba_objects;2627Table created.2829LHR@orclasm > create index idx_idxss_com on t_idxss_20170607_lhr(owner,object_id,object_type);3031Index created.32LHR@orclasm > exec dbms_stats.gather_table_stats(user,'t_idxss_20170607_lhr');3334PL/SQL procedure successfully completed.3536LHR@orclasm > select * from t_idxss_20170607_lhr where object_id=20 and object_type='TABLE';3738Execution Plan39----------------------------------------------------------40Plan hash value: 12854548044142----------------------------------------------------------------------------------------------------43| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |44----------------------------------------------------------------------------------------------------45| 0 | SELECT STATEMENT | | 1 | 28 | 41 (0)| 00:00:01 |46| 1 | TABLE ACCESS BY INDEX ROWID| T_IDXSS_20170607_LHR | 1 | 28 | 41 (0)| 00:00:01 |47|* 2 | INDEX SKIP SCAN | IDX_IDXSS_COM | 1 | | 40 (0)| 00:00:01 |48----------------------------------------------------------------------------------------------------4950Predicate Information (identified by operation id):51---------------------------------------------------5253 2 - access("OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')54 filter("OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')select /*+index_ss(t)*/ * from t_emp_lhr t where t.ename='lhr';
2
3Execution Plan
4----------------------------------------------------------
5Plan hash value: 3374324980
6
7-------------------------------------------------------------------------------------------
8| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
9-------------------------------------------------------------------------------------------
10| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
11| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP_LHR | 1 | 87 | 2 (0)| 00:00:01 |
12|* 2 | INDEX SKIP SCAN | IDX_DUP_LHR | 1 | | 1 (0)| 00:00:01 |
13-------------------------------------------------------------------------------------------
14
15Predicate Information (identified by operation id):
16---------------------------------------------------
17
18 2 - access("T"."ENAME"='lhr')
19 filter("T"."ENAME"='lhr')
20
21Note
22-----
23 - dynamic sampling used for this statement (level=2)
24
25LHR@orclasm > create table t_idxss_20170607_lhr as select owner,object_id,object_type,created from dba_objects;
26
27Table created.
28
29LHR@orclasm > create index idx_idxss_com on t_idxss_20170607_lhr(owner,object_id,object_type);
30
31Index created.
32LHR@orclasm > exec dbms_stats.gather_table_stats(user,'t_idxss_20170607_lhr');
33
34PL/SQL procedure successfully completed.
35
36LHR@orclasm > select * from t_idxss_20170607_lhr where object_id=20 and object_type='TABLE';
37
38Execution Plan
39----------------------------------------------------------
40Plan hash value: 1285454804
41
42----------------------------------------------------------------------------------------------------
43| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
44----------------------------------------------------------------------------------------------------
45| 0 | SELECT STATEMENT | | 1 | 28 | 41 (0)| 00:00:01 |
46| 1 | TABLE ACCESS BY INDEX ROWID| T_IDXSS_20170607_LHR | 1 | 28 | 41 (0)| 00:00:01 |
47|* 2 | INDEX SKIP SCAN | IDX_IDXSS_COM | 1 | | 40 (0)| 00:00:01 |
48----------------------------------------------------------------------------------------------------
49
50Predicate Information (identified by operation id):
51---------------------------------------------------
52
53 2 - access("OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')
54 filter("OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')
（三）ROWID扫描
ROWID表示行在数据块中的具体位置，ROWID是查找具体行的最快方式。可以在WHERE子句中写入ROWID，但是不推荐这么做。通常都是通过索引来获得ROWID，但如果被检索的行都包含在索引中时，那么直接访问索引就能得到所需的数据则不会使用ROWID。对Oracle中的堆表而言，可以通过Oracle内置的ROWID伪列得到对应行记录所在的ROWID的值，然后通过DBMS_ROWID包中的相关方法（DBMS_ROWID.ROWID_RELATIVE_FNO获取文件号、DBMS_ROWID.ROWID_BLOCK_NUMBER获取块号和DBMS_ROWID.ROWID_ROW_NUMBER获取行号）将上述ROWID伪列的值翻译成对应数据行的实际物理存储地址。
需要注意的是，在通过ROWID直接访问数据的情况下，执行计划中常常是“TABLE ACCESS BY USER ROWID”，而非索引回表读的操作（TABLE ACCESS BY INDEX ROWID）。如下所示：
1SYS@orclasm > select rowid from scott.emp where rownum<=1; 2 3ROWID 4------------------ 5AAAnvoAAhAAACnzAAA 6 7SYS@orclasm > select * from scott.emp where rowid='AAAnvoAAhAAACnzAAA'; 8 9 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO10---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------11 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20121314Execution Plan15----------------------------------------------------------16Plan hash value: 11165846621718-----------------------------------------------------------------------------------19| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |20-----------------------------------------------------------------------------------21| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |22| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |23-----------------------------------------------------------------------------------242526Statistics27----------------------------------------------------------28 0 recursive calls29 0 db block gets30 1 consistent gets31 0 physical reads32 0 redo size33 1021 bytes sent via SQL*Net to client34 519 bytes received via SQL*Net from client35 2 SQL*Net roundtrips to/from client36 0 sorts (memory)37 0 sorts (disk)38 1 rows processedselect rowid from scott.emp where rownum<=1;
2
3ROWID
4------------------
5AAAnvoAAhAAACnzAAA
6
7SYS@orclasm > select * from scott.emp where rowid='AAAnvoAAhAAACnzAAA';
8
9 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
10---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
11 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
12
13
14Execution Plan
15----------------------------------------------------------
16Plan hash value: 1116584662
17
18-----------------------------------------------------------------------------------
19| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
20-----------------------------------------------------------------------------------
21| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
22| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
23-----------------------------------------------------------------------------------
24
25
26Statistics
27----------------------------------------------------------
28 0 recursive calls
29 0 db block gets
30 1 consistent gets
31 0 physical reads
32 0 redo size
33 1021 bytes sent via SQL*Net to client
34 519 bytes received via SQL*Net from client
35 2 SQL*Net roundtrips to/from client
36 0 sorts (memory)
37 0 sorts (disk)
38 1 rows processed
& 说明：
有关索引扫描类型的实验操作过程可以参考作者BLOG：http://blog.itpub.net/26736162/viewspace-2139246/
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
喜欢就点击“好看”吧