1、不要用“*”代替所有列名

2、用TRUNCATE代替DELETE

3、在确保完整性的情况下多用COMMIT语句

在PL/SQL块中，经常将几个相互联系的DML语句写在一个Begin…End块中，建议在每个块的end前面使用COMMIT语句，这样可以实现对象DML语句的及时提交，同时也释放事务所占用的资源
COMMIT所释放的资源如下：

  • 回滚段上用于恢复数据的信息，撤销表空间也只做短暂的保留
  • 被程序语句获得的锁；
  • Redo log buffer中的空间；
  • Oracle为管理上述资源的内部花费。

    4、尽量减少表的查询次数

    image.png

5、用NOT EXISTS替代NOT IN

在子查询中，NOT IN子句将执行一个内部的排序和合并，无论在哪种情况下，NOT IN都是最低效的，因为它对子查询中的表执行了一个全表遍历。为了避免使用NOT IN，我们可以把它改写成外连接（OUTER JOINS）或者使用NOT EXISTS子句。
image.png

  • 当NOT IN后跟子查询，并且查询的结构集较多时，不宜使用NOT IN
  • 如果NOTIN后面的括号内是列表（可枚举的几个）或子查询所满足结果集很少时，也是可以使用的

6、用EXISTS替代IN

在许多基于驱动表的查询中，为了满足一个条件，往往需要对另一个表进行连接。在这种情况下，使用EXISTS（或NOT EXISTS）将提高查询的效率。
image.png

7、 有效利用共享游标

SQL语句的执行顺序如图：
image.png
在Parse阶段，数据库系统总是先到Share Pool中查找需要被执行的SQL语句是否已经被缓存，因此在编写SQL语句时，相同查询范围和条件的SQL语句尽可能统一SQL语句的写法，包括SQL语句的大小写和空格及注释。

  • 两个语句，大小写不同、空格数量不同，都会被认为是两个不同的sql
  • 完全相同的SQL语句被不同的数据库用户调用也将被Oracle视为不同的SQL语句而被分别缓存

SQL语句尽可能采用动态绑定参数的写法,这样在Share Pool里也会只被缓存一次
如：select * from emp where empno = ?;

8、以合理的方式使用函数

当函数在WHERE子句中被使用时，查询结果得到的每一行记录均会调用该函数一次
image.png

9、表连接方式

10、选择from表的顺序

在基于规则的优化器下，Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（驱动表，Drive Table）将被最先处理

  • 在FROM子句包含多个表的情况下，建议选择记录条数最少的表作为驱动表。
  • 当Oracle处理多个表时，会运用排序或合并的方式连接它们。首先，系统扫描FROM子句中最后的表，并对该表的数据行进行排序；然后，扫描第二个表（FROM子句中倒数第二个表）；最后，将所有从第二个表中检索出来的记录与第一个表中的合适记录进行合并。

image.png

如果有三个以上的表连接，那就需要选择交叉表（Intersection Table）作为驱动表，交叉表是指被其他表所引用的表。

例如：EMP表是LOCATION表和CATEGORY表的交集，EMP表的数据首先被读到内存，所以EMP表是一个关键的表（这里EMP表是交叉表，将EMP表写在FROM子句中最后，使其先被读到，SQL语句的执行效率高）。
image.png
相反，如果将EMP表写在FROM子句中的前面，SQL的执行效率会低得多

11、驱动表的选择

驱动表（Driving Table）是指被最先访问的表（通常以全表扫描的方式被访问）。根据优化器的不同，SQL语句中驱动表的选择是不一样的：如果使用的是CBO（Cost-Based Optimizer），优化器会检查SQL语句中每个表的物理大小、索引状态，然后选用花费最低的执行路径；如果使用RBO（Rule-Based Optimizer），并且所有的连接条件都有索引对应，在这种情况下，驱动表就是FROM子句中最后的那个表。
image.png

LOGGING表的LODGING列上有一个索引，但WORKER表的LODGING列没有索引，WORKER表将被作为查询中的驱动表，而不是在FROM子句中最后的就一定是驱动表。由此可见，只有两个表都建立有索引，优化器才按照FROM子句中最后的就是驱动表的规则来对待。

12、WHERE子句的连接顺序

Oracle采用自下而上的顺序解析WHERE子句.根据这个原理，表之间的连接必须写在其他WHERE条件之前。那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾，也就是在表进行连接操作以前，过滤掉的记录数越多越好。

13、索引列和表达式的选择

  • WHERE从句频繁使用的关键字；
  • SQL语句中频繁用于进行表连接的关键字；
  • 可选择性高的（重复性少的）关键字；
  • 对于取值较少的关键字或表达式，不要采用标准的B+树索引，可以考虑建立位图索引；
  • 不要将那些频繁修改的列作为索引列；
  • 不要使用包含操作符或者函数的WHERE从句中的关键字作为索引列，如果需要的话，可以考虑建立函数索引；
  • 如果大量并发的INSERT、UPDATE、DELETE语句访问了父表或者子表，则考虑使用完整性约束的外部键作为索引；
  • 在选择索引列时，还要考虑该索引所引起的INSERT、UPDATE、DELETE操作是否值得。

14、选择复合索引主列

复合索引有时比单列索引有更好的性能.如果在建立索引时采用了几个列作为索引，则在使用时也要按照建立时的顺序来描述，也就是说，主列是最先被选择的列
image.png

这里的索引comp_ind是一个复合索引，它包括x, y, z三个列。由于本索引被建立成复合型，所以在查询语句中要带有WHERE … AND从句才能使用该复合索引。
例如：下面的语句是合理的:
image.png

在选择复合索引的关键字时，要遵循下列原则:

  • 应该选择在WHERE从句条件中频繁使用的关键字，并且这些关键字由AND操作符连接；
  • 如果多个查询都选择相同的关键字集合，则考虑创建组合索引；
  • 创建索引以后使得WHERE从句所使用的关键字能够组成前导部分；
  • 如果某些关键字在WHERE从句中的使用频率较高，则考虑创建索引；
  • 如果某些关键字在WHERE从句中的使用频率相当，则创建索引时考虑按照从高到低的顺序来说明关键字。

15、避免对大表的全表扫描

除了一些必要的情况，如月报数据的统计，打印所有清单等可以允许使用全表扫描外，一般都应尽量避免对大表进行全表扫描。
全表扫描就是指不加任何条件或没有使用索引的查询语句。

在以下情况下，Oracle就可以使用全表扫描：

  • 所查询的表没有索引；
  • 需要返回所有的行；
  • 带LIKE并使用“％”这样的语句就是全表扫描
  • 对索引主列有条件限制，但使用了函数，则Oracle使用全表扫描。比如:where upper(area)=’Peking’，这样的语句不会使用索引，所以只能进行全表扫描。
  • 带有IS NULL和IS NOT NULL及!=等子句也导致全表扫描。比如：

image.png

16、监视索引是否被使用

主键是完整性约束而自动变为索引，我们创建的普通索引，目的就是为了提高查询速度。
如果我们创建了索引而没有被使用，那么这些不被使用的索引将起到阻碍性能的作用。为了辨别索引是否被使用，从Oracle 9i版本开始，用户可以对索引进行监视，命令为ALTER INDEX后加子句MONITORINGUSAGE，
语法如下：
image.png
检查索引使用情况：select * from V$OBJECT_USAGE;
删除不使用的索引：drop index indexname;

17、影响索引有效使用的因素

索引是一把双刃剑，有效的索引能大幅度提高查询效率，但是索引数量越多，降低DML操作性能的表现越明显，因此创建合适的索引非常关键。
对于创建好的索引，能否正确利用这些索引将是创建索引的成果直接表现，错误的索引利用方式包括但不仅限于下列情况。
（1）对索引字段进行函数操作或者运算操作。
（2）错误地使用索引字段的类型。例如：
image.png
当project_num字段为varchar2类型时，将不能利用project_num字段的索引。因为上述语句将被执行为下列语句：
image.png