几种索引扫描方式学习

╰半夏微凉° 2022-08-11 00:44 301阅读 0赞

1：索引唯一扫描(index unique scan)  
通过唯一索引查找一个数值经常返回单个 ROWID。

create table t1 as select \* from dba\_objects;

exec dbms\_stats.gather\_table\_stats(YALI,'T1')

create unique index t1\_id on t1(ibject\_id);      -----唯一索引  
select \* from t1 where object\_id=5;  
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 472361049  
\-------------------------------------------------------------------------------------  
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
\-------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |       |     1 |   207 |     2   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   207 |     2   (0)| 00:00:01 |  
|\*  2 |   INDEX UNIQUE SCAN         | T1\_ID |     1 |       |     1   (0)| 00:00:01 |   
\-------------------------------------------------------------------------------------  
  
2：索引范围扫描(index range scan)  
(a)  在唯一索引列上使用了 range 操作符(> < <> >= <= between)。  
(b)  在唯一组合索引上，只使用部分列进行查询（引导列需要存在），如组合索引列全存在index unique scan。  
(c)  对非唯一索引列上进行的任何查询。  
  
(a) select \* from t1 where object\_id <5; ----在唯一索引列上使用了 range 操作符(> < <> >= <= between)。  
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 2288890262  
\-------------------------------------------------------------------------------------  
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
\-------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |       |     3 |   621 |     3   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     3 |   621 |     3   (0)| 00:00:01 |  
|\*  2 |   INDEX RANGE SCAN          | T1\_ID |     3 |       |     2   (0)| 00:00:01 |  
\-------------------------------------------------------------------------------------  
  
(b) 对于唯一的组合索引  
drop index t1\_id;  
create unique index idx\_t1 on t1(owner,object\_type,object\_id);  
  
select \* from t1 where OBJECT\_TYPE='INDEX' AND OWNER='SCOTT';   
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 50753647  
\--------------------------------------------------------------------------------------  
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
\--------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |        |     2 |   414 |     3   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     2 |   414 |     3   (0)| 00:00:01 |  
|\*  2 |   INDEX RANGE SCAN          | IDX\_T1 |     2 |       |     2   (0)| 00:00:01 |  
\--------------------------------------------------------------------------------------  
引导列存在，但是查询索引部分列，INDEX RANGE SCAN。  
  
select \* from t1 where OBJECT\_ID=5 AND OBJECT\_TYPE='INDEX' AND OWNER='SCOTT';  
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 3983389365  
\--------------------------------------------------------------------------------------  
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
\--------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |        |     1 |   207 |     2   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |   207 |     2   (0)| 00:00:01 |  
|\*  2 |   INDEX UNIQUE SCAN         | IDX\_T1 |     1 |       |     1   (0)| 00:00:01 |  
\--------------------------------------------------------------------------------------  
谓词中查询所有组合索引的列， INDEX UNIQUE SCAN。  
  
3：索引全扫描(index full scan)  
与全表扫描对应，也有相应的全 Oracle 索引扫描。在某些情况下，可能进行全 Oracle 索引扫描而不是范围扫描，需要注意的是全 Oracle 索引扫描只在 CBO模式下才有效。   CBO 根据统 计数值得知进行全 Oracle 索引扫描比进行全表扫描更有效时，才进行全 Oracle 索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。      
   
create index idx\_id on t1(object\_id);   
SELECT MAX(OBJECT\_id) from t1;  
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 1123511277  
\-------------------------------------------------------------------------------------  
| Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
\-------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT           |        |     1 |     5 |     2   (0)| 00:00:01 |  
|   1 |  SORT AGGREGATE            |        |     1 |     5 |            |          |  
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX\_ID |     1 |     5 |     2   (0)| 00:00:01 |  
\-------------------------------------------------------------------------------------  
索引全扫描INDEX FULL SCAN  
     
4：索引快速扫描(index fast full scan)

扫描索引中的所有的数据块，与  index  full  scan 很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。 在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间，其他索引都采取单块读。

可以参考[http://blog.csdn.net/robinson1988/article/details/7312683][http_blog.csdn.net_robinson1988_article_details_7312683]

组合索引，返回列都在索引中，返回相对结果多。    
  
select count(object\_id) from t1;  
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 2929617283  
\--------------------------------------------------------------------------------  
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
\--------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT      |        |     1 |     5 |    41   (0)| 00:00:01 |  
|   1 |  SORT AGGREGATE       |        |     1 |     5 |            |          |  
|   2 |   INDEX FAST FULL SCAN| IDX\_ID | 65441 |   319K|    41   (0)| 00:00:01 |  
\--------------------------------------------------------------------------------  
索引快速全扫描INDEX FAST FULL SCAN  
  
create index idx\_ooo on t1(owner,object\_name,object\_type);  
select owner,object\_name,object\_type from t1 where object\_type='INDEX';  
2594 rows selected.  
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 4139004340  
\--------------------------------------------------------------------------------  
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
\--------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT     |         |  2618 |   102K|   127   (0)| 00:00:02 |  
|\*  1 |  INDEX FAST FULL SCAN| IDX\_OOO |  2618 |   102K|   127   (0)| 00:00:02 |  
\--------------------------------------------------------------------------------  
索引快速全扫描INDEX FAST FULL SCAN （组合索引，查询列都在索引中，返回结果比较多）   
  
select \* from t1 where object\_type='INDEX';  
2594 rows selected.  
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 3617692013  
\--------------------------------------------------------------------------  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
\--------------------------------------------------------------------------  
|   0 | SELECT STATEMENT  |      |  2618 |   250K|   262   (1)| 00:00:04 |  
|\*  1 |  TABLE ACCESS FULL| T1   |  2618 |   250K|   262   (1)| 00:00:04 |  
\--------------------------------------------------------------------------  
组合索引，查询列不都在索引中

5：索引跳跃扫描（INDEX SKIP SCAN）

可以参考[http://www.itpub.net/thread-1372696-1-1.html][http_www.itpub.net_thread-1372696-1-1.html] 很受启发

复合索引上，如果 SQL 中谓词条件只包含索引中的部分列，并且这些列不是建立索引时的第一列时，并且返回条数很少，就可能发生INDEX SKIP SCAN。这里 SKIP 的意思是因为查询条件没 有第一列或前面几列，被忽略了。         
 如果有引导列会走  索引范围扫描(index range scan)  
  
select \* from t1 where object\_name='EMP';  
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 2431949587  
\---------------------------------------------------------------------------------------  
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
\---------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |         |     2 |   196 |    23   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     2 |   196 |    23   (0)| 00:00:01 |  
|\*  2 |   INDEX SKIP SCAN           | IDX\_OOO |     2 |       |    21   (0)| 00:00:01 |  
\---------------------------------------------------------------------------------------  
索引跳跃扫描  INDEX SKIP SCAN   --（组合索引，谓词中没有引导列，返回条数很少）   
  
select \* from t1 where owner='YALI';  
7 rows selected.  
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 1128734305  
\---------------------------------------------------------------------------------------  
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
\---------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |         |     6 |   588 |     8   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     6 |   588 |     8   (0)| 00:00:01 |  
|\*  2 |   INDEX RANGE SCAN          | IDX\_OOO |     6 |       |     3   (0)| 00:00:01 |  
\---------------------------------------------------------------------------------------  
索引范围扫描 INDEX RANGE SCAN   （组合索引，谓词中引导列，返回条数很少）

[http_blog.csdn.net_robinson1988_article_details_7312683]: http://blog.csdn.net/robinson1988/article/details/7312683
[http_www.itpub.net_thread-1372696-1-1.html]: http://www.itpub.net/thread-1372696-1-1.html