项目经历2019xinge_notification

Oracle explain

什么是Oracle执行计划？

执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述

table建索引，联合索引

1
2
3
4
5
6
7
8
9
@Entity
@Table(name="notification", 
    indexes={@Index(name="notif_index_custid_date", columnList="custId, notificationDate", unique=false)})
public class Notification{
    private String id;
    private String custId;
    private java.util.Date notificationDate;
    ...
}

SQL

1
2
explain plan for
  select * from table where ...

Display the result

1
select plan_table from table(DBMS_XPLAN.DISPLAY());

image-20210405171832366

read the result

表访问的几种方式：（非全部）

  • TABLE ACCESS FULL（全表扫描）
  • TABLE ACCESS BY ROWID（通过ROWID的表存取）
  • TABLE ACCESS BY INDEX SCAN（索引扫描）

TABLE ACCESS FULL（全表扫描）：

Oracle会读取表中所有的行，并检查每一行是否满足SQL语句中的 Where 限制条件；

全表扫描时可以使用多块读（即一次I/O读取多块数据块）操作，提升吞吐量；

使用建议：数据量太大的表不建议使用全表扫描，除非本身需要取出的数据较多，占到表数据总量的 5% ~ 10% 或以上

TABLE ACCESS BY ROWID（通过ROWID的表存取） :

先说一下什么是ROWID？

ROWID是由Oracle自动加在表中每行最后的一列伪列，既然是伪列，就说明表中并不会物理存储ROWID的值；

你可以像使用其它列一样使用它，只是不能对该列的值进行增、删、改操作；

一旦一行数据插入后，则其对应的ROWID在该行的生命周期内是唯一的，即使发生行迁移，该行的ROWID值也不变。

让我们再回到 TABLE ACCESS BY ROWID 来：

行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID可以快速定位到目标数据上，这也是Oracle中存取单行数据最快的方法；

TABLE ACCESS BY INDEX SCAN（索引扫描）：

在索引块中，既存储每个索引的键值，也存储具有该键值的行的ROWID。

索引扫描又分五种：

  • INDEX UNIQUE SCAN（索引唯一扫描）
  • INDEX RANGE SCAN（索引范围扫描）
  • INDEX FULL SCAN（索引全扫描）
  • INDEX FAST FULL SCAN（索引快速扫描）
  • INDEX SKIP SCAN（索引跳跃扫描）

a) INDEX UNIQUE SCAN（索引唯一扫描）：

针对唯一性索引（UNIQUE INDEX）的扫描，每次至多只返回一条记录；

b) INDEX RANGE SCAN（索引范围扫描）：

使用一个索引存取多行数据；

发生索引范围扫描的三种情况：

  • 在唯一索引列上使用了范围操作符（如：> < <> >= <= between）
  • 在组合索引上，只使用部分列进行查询（查询时必须包含前导列，否则会走全表扫描）
  • 对非唯一索引列上进行的任何查询

c) INDEX FULL SCAN（索引全扫描）：

进行全索引扫描时，查询出的数据都必须从索引中可以直接得到

d) INDEX FAST FULL SCAN（索引快速扫描）:

扫描索引中的所有的数据块，与 INDEX FULL SCAN 类似，但是一个显著的区别是它不对查询出的数据进行排序

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

Oracle 9i后提供，有时候复合索引的前导列（索引包含的第一列）没有在查询语句中出现，oralce也会使用该复合索引，这时候就使用的INDEX SKIP SCAN;

什么时候会触发 INDEX SKIP SCAN 呢？

前提条件：表有一个复合索引，且在查询时有除了前导列（索引中第一列）外的其他列作为条件，并且优化器模式为CBO时

当Oracle发现前导列的唯一值个数很少时，会将每个唯一值都作为常规扫描的入口，在此基础上做一次查找，最后合并这些查询；

例如：

假设表emp有ename（雇员名称）、job（职位名）、sex（性别）三个字段，并且建立了如 create index idx_emp on emp (sex, ename, job) 的复合索引；

因为性别只有 ‘男’ 和 ‘女’ 两个值，所以为了提高索引的利用率，Oracle可将这个复合索引拆成 (‘男’, ename, job)，(‘女’, ename, job) 这两个复合索引；

当查询 select * from emp where job = ‘Programmer’ 时，该查询发出后：

Oracle先进入sex为’男’的入口，这时候使用到了 (‘男’, ename, job) 这条复合索引，查找 job = ‘Programmer’ 的条目；

再进入sex为’女’的入口，这时候使用到了 (‘女’, ename, job) 这条复合索引，查找 job = ‘Programmer’ 的条目；

最后合并查询到的来自两个入口的结果集。所以按道理是不应该走索引的，因为没有where sex。但是优化成合并多个索引结果。

我的发现

如果索引覆盖，不回表，那就不用“TABLE ACCESS BY ROWID”