优化高手都需要深入的访问路径（ACCESS PATH）

访问路径：通过哪种方式对数据进行访问. 全表扫描、索引扫描或ROWID获取数据。

常见访问路径

1.TABLE ACCESS FULL(全表扫描)

多块读
HINT:FULL(表名/别名)
等待事件为db file scattered read.
若为并行全表扫描，等待事件为direct path read.

2.TABLE ACCESS BY USER ROWID

直接用ROWID获取数据，单块读。
该访问路径在Oracle所有的访问路径中性能是最好的。

3.TABLE ACCESS BY ROWID RANGE

表示ROWID 范围扫描，多块读。
因为同一个块里面的ROWID是连续的，同一个EXTENT里面的ROWID也是连续的，所以可以多块读。

4.TABLE ACCESS BY INDEX ROWID

回表，单块读

5.INDEX UNIQUE SCAN

索引唯一扫描，单块读。
对唯一索引或者主键列进行等值查询，就会走INDEX UNIQUE SCAN
其性能仅次于TABLE ACCESS BY USER ROWID


scott@orclpdb1:orclcdb> set autot trace
scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> select * from emp where empno=7369;
 
1 row selected.
 
 
Execution Plan
----------------------------------------------------------
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)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        961  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
scott@orclpdb1:orclcdb>

6.INDEX RANGE SCAN

索引范围扫描，单块读，返回的数据是有序的，
HINT:INDEX(表名/别名索引名)
对唯一索引或者主键索引进行范围查找（INDEX RANGE SCAN，等待事件db file sequential read）。对非唯一索引进行等值查找.


scott@orclpdb1:orclcdb> select * from test where object_id=100;
 
1 row selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3297604684
 
----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |     1 |   132 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST   |     1 |   132 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OBJECT_ID"=100)
 
 
Statistics
----------------------------------------------------------
        136  recursive calls
          0  db block gets
        408  consistent gets
         33  physical reads
          0  redo size
       2686  bytes sent via SQL*Net to client
        621  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         74  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
scott@orclpdb1:orclcdb>

索引范围扫描默认是从索引中最左边的叶子块开始，然后往右边的叶子块扫描（从小到大），当检查到不匹配数据的时候，就停止扫描。


scott@orclpdb1:orclcdb> select * from test where object_id<100 order by object_id desc;
 
98 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1069979465
 
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    94 | 12408 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST   |    94 | 12408 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ID |    94 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OBJECT_ID"<100)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         18  consistent gets
          1  physical reads
          0  redo size
      14379  bytes sent via SQL*Net to client
        711  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         98  rows processed
 
scott@orclpdb1:orclcdb>

INDEX RANGE SCAN DESCENDING 表示索引降序范围扫描，从右往左扫描，返回的数据是降序显示的。

7.INDEX SKIP SCAN

索引跳跃扫描，单块读。
返回的数据是有序的
HINT:INDEX_SS(表名/别名索引名)
当组合索引的引导列（第一个列）没有在where条件中，并且组合索引的引导列/前几个列的基数很低，where过滤条件对组合索引中非引导列进行过滤的时候就会发生索引跳跃扫描。
等待事件为 db file sequential read.


scott@orclpdb1:orclcdb> create index idx_ownerid on test(owner,object_id);
 
Index created.
 
scott@orclpdb1:orclcdb> drop index idx_id;
 
Index dropped.
 
scott@orclpdb1:orclcdb> select * from test where object_id<100;
 
98 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 607061290
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |    94 | 12408 |    42   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST        |    94 | 12408 |    42   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | IDX_OWNERID |    94 |       |    39   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OBJECT_ID"<100)
       filter("OBJECT_ID"<100)
 
 
Statistics
----------------------------------------------------------
         59  recursive calls
          0  db block gets
         92  consistent gets
         17  physical reads
          0  redo size
      14379  bytes sent via SQL*Net to client
        466  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         98  rows processed
 
scott@orclpdb1:orclcdb>

8.INDEX FULL SCAN

INDEX FULL SCAN 表示索引全扫描，单块读，返回的数据是有序的。
HINT:INDEX(表名/别名索引名)
索引全扫描会扫描索引中所有的叶子块（从左往右扫描）
若索引很大，会产生严重的性能问题（因为是单块读）
等待事件： db file sequential read


scott@orclpdb1:orclcdb> select * from test order by object_id,owner;
 
73516 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2007178810
 
-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 73516 |  9476K|       |  2570   (1)| 00:00:01 |
|   1 |  SORT ORDER BY     |      | 73516 |  9476K|    13M|  2570   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST | 73516 |  9476K|       |   399   (1)| 00:00:01 |
-----------------------------------------------------------------------------------
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1430  consistent gets
          0  physical reads
          0  redo size
    4868592  bytes sent via SQL*Net to client
      54537  bytes received via SQL*Net from client
       4903  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      73516  rows processed
 
scott@orclpdb1:orclcdb> create index idx_idowner on test(object_id,owner,0);
 
Index created.
 
scott@orclpdb1:orclcdb> select * from test order by object_id,owner;
 
73516 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3870803568
 
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             | 73516 |  9476K|  1865   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        | 73516 |  9476K|  1865   (1)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | IDX_IDOWNER | 73516 |       |   234   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      11531  consistent gets
        232  physical reads
          0  redo size
    4868592  bytes sent via SQL*Net to client
      54316  bytes received via SQL*Net from client
       4903  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73516  rows processed
 
scott@orclpdb1:orclcdb>

9.INDEX FAST FULL SCAN

索引快速全扫描，多块读。
HINT:INDEX_FFS（表名/别名索引名）
等待事件 db file scattered read


scott@orclpdb1:orclcdb> drop index idx_ownername;
 
Index dropped.
 
scott@orclpdb1:orclcdb> create index idx_ownername on test(owner,object_name,0);
 
Index created.
 
scott@orclpdb1:orclcdb> select owner,object_name from test;
 
73516 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3888663772
 
--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               | 73516 |  2871K|   146   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| IDX_OWNERNAME | 73516 |  2871K|   146   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5405  consistent gets
        537  physical reads
          0  redo size
    3773365  bytes sent via SQL*Net to client
      54307  bytes received via SQL*Net from client
       4903  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73516  rows processed
 
scott@orclpdb1:orclcdb> create index idx_id on test(object_id);
 
Index created.
 
scott@orclpdb1:orclcdb> select object_name from test where object_id<100;
 
98 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3297604684
 
----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |    94 |  3760 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST   |    94 |  3760 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_ID |    94 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OBJECT_ID"<100)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         18  consistent gets
          1  physical reads
          0  redo size
       2851  bytes sent via SQL*Net to client
        476  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         98  rows processed
 
scott@orclpdb1:orclcdb> create index idx_idname on test(object_id,object_name);
 
Index created.
 
scott@orclpdb1:orclcdb> select object_name from test where object_id<100;
 
98 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3678957952
 
-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |    94 |  3760 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_IDNAME |    94 |  3760 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("OBJECT_ID"<100)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          9  consistent gets
          1  physical reads
          0  redo size
       2851  bytes sent via SQL*Net to client
        476  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         98  rows processed
 
scott@orclpdb1:orclcdb> select object_name from test where object_id>100;
 
73415 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 252646278
 
-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            | 73420 |  2867K|   142   (1)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_IDNAME | 73420 |  2867K|   142   (1)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("OBJECT_ID">100)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5386  consistent gets
        513  physical reads
          0  redo size
    3530801  bytes sent via SQL*Net to client
      54686  bytes received via SQL*Net from client
       4896  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73415  rows processed
 
scott@orclpdb1:orclcdb>

10.INDEX FULL SCAN(MIN/MAX)

索引最小、最大值扫描、单块读
INDEX FULL SCAN(MIN/MAX) 只会访问“索引高度”个索引块，其性能与INDEX UNIQUE SCAN一样，其性能仅次于TABLE ACCESS BY USER ROWID

11.MAT_VIEW REWRITE ACCESS FULL

MAT_VIEW REWRITE ACCESS FULL 表示物化视图全表扫描、多块读。
因为物化视图本质上也是一个表，所以其扫描方式与全表扫描一样。


scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> create materialized view test_mv build immediate enable query rewrite 
  2  as select object_id,object_name from test;
 
Materialized view created.
 
scott@orclpdb1:orclcdb> select object_id,object_name from test;
 
73516 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1627509066
 
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         | 73516 |  2799K|   129   (1)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| TEST_MV | 73516 |  2799K|   129   (1)| 00:00:01 |
----------------------------------------------------------------------------------------
 
 
Statistics
----------------------------------------------------------
         36  recursive calls
         12  db block gets
       5370  consistent gets
        453  physical reads
       1980  redo size
    4184006  bytes sent via SQL*Net to client
      54311  bytes received via SQL*Net from client
       4903  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      73516  rows processed
 
scott@orclpdb1:orclcdb>

因为物化视图TEST_MV 已经包含查询需要的字段，所以该SQL会直接访问物化视图TEST_MV.

2单块单读与多块读

单块读:从磁盘1次读取1个块到buffer cache.
多块读:从磁盘1次读取多个块到buffer cache.
如果数据块都已经缓存在buffer cache中，那就不需要物理I/O了，没有物理I/O也就不存在单块读与多块读。
绝大数的平台，一次I/O最多只能读取或者写入1MB数据。
Oracle的块大小默认是8K,那么一次I/O最多只能写入128个块到磁盘，最多只能读取128个块到buffer cache.
在判断哪个访问路径性能好的时候，通常是估算每次访问的I/O次数，谁的I/O次数少，谁的性能就好。在估算I/O次数的时候，我们只需要算个大概即可。

相关阅读:
Python | R | MATLAB群体消息和遗传病筛选多元统计模型
 前端面试题记录——vue
网心云容器魔方在iStoreOS下的多开
 嵌入Circle映射和逐维小孔成像反向学习的鲸鱼优化算法-附代码
 将docker打包成镜像并保存到本地
 感染了后缀为.360勒索病毒如何应对？数据能够恢复吗？
Zabbix监控部署项目
 Web安全—Web漏扫工具NetSparker安装与使用
 MongoDB基本操作增删改查
 ethers-rs rust 创建钱包，签名消息，验证签名，恢复签名地址,签名RSV对象
原文地址：https://blog.csdn.net/u011868279/article/details/127414126