Oracle SQL优化

1、书写顺序和执行顺序

在Oracle SQL中，查询的书写顺序和执行顺序是不同的。

1.1SQL书写顺序如下：

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

1.2 SQL执行顺序

1. FROM：数据源被确定，表连接操作也在此步骤完成。

2. WHERE：对数据行进行筛选。
3. GROUP BY：将数据划分为不同的组。
4. HAVING：筛选满足条件的分组。
5. SELECT：选择具体的列，此时可以处理聚合函数或者别名等。
6. ORDER BY：最后对结果集进行排序。

注意，虽然SELECT在书写顺序中处于第一位，但在执行顺序中却是倒数第二个，因为只有在前面的步骤都完成之后，才能知道要返回哪些列。

1.3 Oracle数据库执行SQL语句的步骤

Oracle数据库执行SQL语句的步骤主要包括以下几个阶段：

1. 解析（Parse）：在这一阶段，Oracle会检查SQL语句的语法和语义是否正确，并生成相应的解析树。同时，Oracle还会检查用户是否有执行该SQL语句的权限。

2. 绑定（Bind）：如果SQL语句中包含了绑定变量（即参数化查询中的参数），则这些变量在此阶段被赋值。

3. 优化（Optimize）：在这一阶段，优化器将选择一个或多个执行计划。执行计划定义了如何读取和处理数据的顺序和方式。优化器将基于统计信息和其他因素来决定最优的执行计划。

4. 行源生成（Row Source Generation）：生成用于从底层数据结构获取数据的可执行代码。

5. 执行（Execute）：根据优化器生成的执行计划，实际执行SQL语句。如果是查询语句，则返回结果集；如果是插入、更新或删除语句，则修改数据并返回影响的行数。

6. 获取（Fetch）：对于查询操作，它包括检索并返回结果集中的行。

其中，解析、优化和行源生成三个阶段通常被合称为“编译”，而执行和获取两个阶段通常被合称为“运行”。

注意，这个过程可以通过数据库中的一些机制（如语句缓存、绑定变量等）来进行优化，以减少编译时间并提高查询执行的效率。

2、 优化Oracle数据库查询SQL

优化Oracle数据库查询SQL主要是为了提高查询的性能和效率。以下是一些常用的优化技巧：

2.1 使用索引

如果经常需要查询某个特定的列，应该为这个列创建索引。但请注意，不应该为数据库中的每一列都创建索引，因为索引会占用额外的存储空间，并且每次数据变更时，都需要更新索引。

例如，如果你经常执行诸如:

ELECT * FROM employees WHERE employee_id = 123;

那么在employee_id上创建一个索引就很有用。但请记住，索引虽然可以加速数据查询，却会减慢数据插入、更新和删除的速度，因为每次数据变动，都要维护索引。

2.2  避免全表扫描

尽量通过WHERE子句筛选出所需要的记录，而不是读取整个表的数据。

比如使用

SELECT name, age FROM employees;

而不是

SELECT * FROM employees;

这样可以避免读取不必要的数据，提升查询速度。

2.3 减少连接操作

如果可能，试图减少连接多个表的操作。因为连接操作通常需要消耗比较大的资源。

当进行多表联接时，尝试减少JOIN操作的数量，确保ON子句中连接字段已被正确索引。此外，尽量避免在大表之间进行笛卡尔积（无约束条件的JOIN）。

2.4 使用EXPLAIN PLAN

Oracle 提供了EXPLAIN PLAN语句，它可以显示Oracle如何执行SQL查询。你可以根据EXPLAIN PLAN的结果来调整你的查询。

通过运行

EXPLAIN PLAN FOR your_sql_statement;

然后查询PLAN_TABLE表或使用DBMS_XPLAN.DISPLAY;来查看计划，找出是否存在全表扫描、使用了哪些索引等信息。

2.4.1 执行计划的常用列字段解释：

基数（Rows）：Oracle估计的当前操作的返回结果集行数

字节（Bytes）：执行该步骤后返回的字节数

耗费（COST）、CPU耗费：Oracle估计的该步骤的执行成本，用于说明SQL执行的代价， 理论上越小越好（该值可能与实际有出入）

时间（Time）：Oracle估计的当前操作所需的时间

2.4.2.执行顺序：

根据Operation缩进来判断，缩进最多的最先执行；（缩进相同时，最上面的最先执行）

2.4.3 检索方式：

1.TABLE ACCESS BY …  即描述的是该动作执行时表访问（或者说Oracle访问数据）的方式（非全部）:

    a.TABLE ACCESS FULL（全表扫描）:

•  Oracle会读取表中所有的行，并检查每一行是否满足SQL语句中的 Where 限制条件；
• 全表扫描时可以使用多块读（即一次I/O读取多块数据块）操作，提升吞吐量；
• 使用建议：数据量太大的表不建议使用全表扫描，除非本身需要取出的数据较多，占到表数据总量的 5% ~ 10% 或以上

                                                        
    b.ABLE ACCESS BY ROWID（通过ROWID的表存取）:

• ROWID是由Oracle自动加在表中每行最后的一列伪列，既然是伪列，就说明表中并不会物理存储ROWID的值；
• 你可以像使用其它列一样使用它，只是不能对该列的值进行增、删、改操作；
• 一旦一行数据插入后，则其对应的ROWID在该行的生命周期内是唯一的，即使发生行迁移，该行的ROWID值也不变。
• 让我们再回到 TABLE ACCESS BY ROWID 来：行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID可以快速定位到目标数据上，这也是Oracle中存取单行数据最快的方法；

    c.TABLE ACCESS BY INDEX SCAN（索引扫描）:

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

• 所以索引扫描其实分为两步：

  • Ⅰ：扫描索引得到对应的ROWID

  •  Ⅱ：通过ROWID定位到具体的行读取数据

                                            
    d.TABLE ACCESS BY INDEX ROWID BATCHED: 

The BATCHED access shown in Step 1 means that the database retrieves a few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block.

•         这句话的意思是说，该操作是数据库为了从索引中获取一些rowid，接着，试着按照块顺序存取块中的数据行，以便用来改善聚集效果和减少对一个数据块存取的次数。
•         官方解释的意思就是这样，但怎么理解呢？之前，当我们通过索引获取的rowid回表获取相应数据行时，都是读一个rowid回表获取一次相应数据行，然后，再读一个rowid，再回表获取一次相应数据行。
•         这样一直读取完所有所需数据。当不同rowid对应的数据行存储在一个数据块中时，就可能会发生对同一表数据块的多次读取，当一个索引的聚集因子比较低时，这也是一个必然结果，从而浪费了系统资源。Oracle 12c中该新特性，通过对rowid对应的数据块号进行排序，然后回表读取相应数据行，从而避免了对同一表数据块的多次重复读取，从而改善了SQL语句的性能，降低了资源消耗。该特性通过隐藏参数“_optimizer_batch_table_access_by_rowid”控制，默认值为true，即为开启。

                                                            
                                                        
2.4.4 索引扫描延伸

索引扫描又分五种：

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

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

• 表中某字段存在 UNIQUE、PRIMARY KEY 约束时，Oracle常实现唯一性扫描；

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

• 使用一个索引存取多行数据；
• 发生索引范围扫描的三种情况：
• 在唯一索引列(unique索引)上使用了范围操作符（如：>   <   <>   >=   <=   between）
• 在组合索引上，只使用部分列进行查询（查询时必须包含前导列，否则会走全表扫描）对非唯一索引列(非unique)上进行的任何查询 

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

• 进行全索引扫描时，查询出的数据都必须从索引中可以直接得到（注意全索引扫描只有在CBO模式下才有效）

(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' 的条目；最后合并查询到的来自两个入口的结果集。

----------------分区表扫描方式-----------------------
PARTITION RANGE ALL          扫描所有分区
PARTITION RANGE ITERATOR    扫描部分分区
PARTITION RANGE SINGLE        扫描单个分区

2.5 合理使用子查询和连接

在某些情况下，子查询可能比连接操作更高效，或者反过来。你需要根据具体情况来选择使用哪种方式。

当进行多表联接时，尝试减少JOIN操作的数量，确保ON子句中连接字段已被正确索引。此外，尽量避免在大表之间进行笛卡尔积（无约束条件的JOIN）。

2.6 使用分区

对于非常大的表，你可以考虑使用分区。分区可以将一个大表分割成多个较小的部分，从而提高查询性能。

2.7 调整数据库参数

Oracle 允许你调整很多数据库参数，根据工作负载和硬件的特性，调整Oracle的初始化参数，例如缓存大小、I/O配置等，也可以提高查询性能。

2.8 避免频繁提交

每次提交都会生成redo日志，消耗I/O资源。如果事务较小，尝试降低提交的频率。