在OLTP中，索引访问的几种常规操作

在OLTP中，索引访问的几种常规操作
1、index(unique scan):唯一索引扫描，即对唯一索引进行单一匹配访问。在唯一索引中，每一个非空健值只会存在一条。主键本身是一个没有null值的唯一索引。
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    90 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OB       |     1 |    90 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IDX01_T_OB |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=TO_NUMBER(:A))

2、index(range scan):索引范围扫描，即对(唯一或非唯一)索引进行范围匹配(>、<、>=、<=、like)访问，或者对非唯一索引进行单一匹配访问。
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    90 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OB       |     1 |    90 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX02_T_OB |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME"=:A)


------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |   660 | 59400 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OB       |   660 | 59400 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX01_T_OB |   119 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">=TO_NUMBER(:A))
   
3、index(range scan(min/max)对索引进行范围扫描，以获取在where条件中对索引字段的最大值或者最小
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |    19 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |            |     1 |    19 |            |          |
|   2 |   FIRST ROW                  |            |     1 |    19 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IDX02_T_OB |     1 |    19 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_NAME"=:A)

4、index(full scan(min/max))对索引进行完全扫描访问，以获取索引字段的最大值、最小值
-----------------------------------------------------------------------------------------
| 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)| IDX01_T_OB |     1 |     5 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
9 rows selected.

5、index(range scan descending):按照与索引逻辑顺序的相反顺序对索引进行范围扫描，一般索引的逻辑顺序是asc即为升序
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   660 | 59400 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_OB       |   660 | 59400 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX01_T_OB |   119 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">TO_NUMBER(:1))

6、index(full scan descending)以索引逻辑顺序相反的顺序对索引进行完全扫描访问
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            | 13206 |  1160K|   228   (1)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OB       | 13206 |  1160K|   228   (1)| 00:00:03 |
|   2 |   INDEX FULL SCAN DESCENDING| IDX01_T_OB | 13206 |       |    28   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
9 rows selected.
 
7、index(full scan):索引全扫描，即对索引进行完全扫描访问。这种方式的读取是按照索引数据的逻辑顺序读取，每次只能读取一个而不是多个数据块
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            | 13206 |  1160K|   228   (1)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OB       | 13206 |  1160K|   228   (1)| 00:00:03 |
|   2 |   INDEX FULL SCAN           | IDX01_T_OB | 13206 |       |    28   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
9 rows selected.


8、index(fast full scan):快速完全索引扫描，对索引进行快速完全扫描访问。这种访问方式中，不会按照索引的逻辑顺序访问，而是
按照物理顺序读去所有的索引块，并可每次读取多个数据块。
-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            | 13206 | 66030 |     9   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| IDX01_T_OB | 13206 | 66030 |     9   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
8 rows selected.

9、index(sample fast full scan):索引快速完全采样扫描。与采样访问表类似，即以多数据块读取的方式扫描索引的部分数据块
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |   660 | 11220 |     9   (0)| 00:00:01 |
|   1 |  INDEX SAMPLE FAST FULL SCAN| IDX01_T_OB |   660 | 11220 |     9   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
8 rows selected.

10、index(skip scan):跳跃索引扫描，即对多个字段复合索引扫描时，跳过索引中前导的一个或多个字段，而对后续字段进行匹配。
多字段的复合索引建立，可能前驱字段的重复值比较多，那么构建索引的逻辑结构时，可能由后驱字段的顺序来决定。如果一个查询可能利用
后驱字段来做filter条件，从而可能会导致skip index的动作
SQL> explain plan for select /*+ index(t_ob,idx03_t_ob) */ count(1) from t_Ob where object_id='1';
Explained.
SQL> select * from table(dbms_xplan.display());
Plan hash value: 343431327
-------------------------------------------------------------------------------
| 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 SKIP SCAN| IDX03_T_OB |     1 |     5 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=1)
       filter("OBJECT_ID"=1)
15 rows selected.