ORACLE SQL性能优化总结（oracle 性能优化之sql语句优化）

SQL优化的核心思想： 尽量避免硬解析，尽量减少锁存器数量，尽量减少一条SQL会话占用锁存器时间，尽量满足需要的数据块访问量。

1、SQL重用性越高，数据库效率越高，查询速度越快。

2、尽可能的是SQL中的条件（谓语）参数化，可提高SQL重用性。

3、SQL的大小写一致，可提高SQL重用性。

4、SQL在获取能够满足查询需要的数据行时，访问尽可能少的数据块。

5、视图合并

如果一个查询块包含分析函数、聚合函数、集合运算（如：UNION、INTERSECT、MINUS、ORDER BY 、ROWNUM ）视图合并将会被禁止或者限制。

6、子查询解嵌套

相同：子查询解嵌套与视图合并的相似之处在于子查询也是通过一个单独的查询块来表示的。

不同：可合并的视图与可以解嵌套的子查询之间的主要区别在于他们的位置是不同的，子查询位于WHERE子句后面。

7、谓词推进

谓词推进用来将谓词从一个内含查询块中应用到不可合并的查询块中，目标就是允许索引的使用或者让其他对于数据集的筛选在查询中能够尽早的进行。

例如：

SELECT E1.LAST_NAME,E1.SALARY,V.AVG_SALARY FROM EMPLOYEES E1

INNER JOIN (SELECT DEPARTMENT_ID,AVG(SALARY) AVG_SALARY FROM EMPLOYEES E2 GROUP BY DEPARTMENT_ID) V

WHERE E1.DEPARTMENT_ID = V.DEPARTMENT_ID AND E1.SALARY > V.AVG_SALARY AND E1.DEPARTMENT_ID = 60;

查询的时候优化器会默认将E1.DEPARTMENT_ID = 60这个谓语推进到视图V做谓语条件以便尽早的筛选出少量的数据集。

实际目的：减少数据访问的硬解析，减少该语句占用锁存器时间。

8、使用物化视图进行查询重写

语法：CREATE MATERIALIZED VIEW 物化视图名称 ENABLE QUERY REWRITE AS 查询语句；

9、查询一次返回的行数是一个可配置的值，称为“列大小”。

设置语法：SET ARRAYSIZE N; SQL*PLUS中默认列大小为15；

例子：查询100条数据，列大小为10时，需读取10次才能全部读取出。列大小为20时只读取5次就可全部返回。

10、数据的访问方法有两种：一种是全扫描，另一种是索引扫描。

索引扫描包括：索引范围扫描、索引唯一扫描、索引全扫描、索引跳跃扫描、索引快速全扫描，五种。

11、数据的存储方式和查询数据量占全表数据量的比例决定数据的访问方式。

如：查询数据量占全表数据量的比例为1%，数据存储位置特别零散，这样查询会走全表扫描。

查询数据量占全表数据量的比例为1%，数据存储位置比较集中，这样查询会索引扫描。

12、聚簇因子 是通过每次当前行的块编号与前一行的块编号不同时对计数器加一来进行计算的。

数据存储位置特别零散，聚簇因子就特别大，不利于使用索引扫描。

数据存储位置越集中，聚簇因子就越小，越有利于使用索引扫描。

13、全表扫描是否为高效选择的另一个关键因素是舍弃访问数据块的数目，舍弃的数据量越大，全扫描成本越高。

14、当进行全扫描运算的时候到高水位线为止的所有数据库都将被读取并扫描，即使他们是空的。

这是对于频繁加载和清除的表的查询便得越来越慢的原因。

15、当谓语中包含使用UNIQUE 或 Primary Key 索引列作为条件的时候就会选用“索引唯一扫描”。

16、当谓语中包含将会返回一定范围数据的条件时就会选用“索引范围扫描”。所指定的条件可以使用诸如：<、>、LIKE、BETWEEN甚至是=运算符。

17、如果使用一个以通配符开头的条件如“%abc”的like运算时，优化器将不会选择该列上的索引范围扫描，因为条件太宽泛了。

18、当你有一个使用组合索引中非引导列的谓语时，这种情况更可能选择使用“索引跳跃扫描”；

创建索引语法：CREATE INDEX 索引名 ON 表名（字段一，字段二，字段三）；

字段一为引导列，其他字段为非引导列；

19、当查询语句中没有谓语但是可以通过其中一列的索引来获得数据时，数据可以通过一个排过序的索引来获取，目的是为了省去单独排序的步骤。

20、索引全扫描效率优于全扫描的情况分两种

（1）：只使用索引就能够返回查询结果的查询，比如聚合查询中的函数（count、max、min）,

（2）：查询结果的记录数小于表中记录数一定比例的时候，如下：

根据数据分析，查询数据量占50%的时候，使用全表扫描和索引全扫描需读取的数据块数量相等，由于全扫描为多块读取，索引全扫描为单块读取，

如果结果不需要排序，则全扫描效率高于索引全扫描，如果需要对结构排序，则索引全扫描效率高于全扫描。

21、联结方法有：嵌套循环联结、散列联结、排序合并联结、笛卡尔联结

22、优化器通过使用统计信息和 WHERE 子句中的筛选条件计算每个表分别返回多少行数据来确定哪张表为驱动表。

SQL访问的第一张表通常被称为驱动表。

23、通过索引获取数据行的步骤如下：

（1）遍历索引树并且在将SQL语句中的谓语应用到索引列后收集子叶子快的行编号。

（2）使用行编号从表数据块中获取数据行。

（3）在所获得的数据行上应用其余的谓语来得出最终结果集。

24、各种扫描类型获取数据块方式：

（1）全表扫描和索引快速全扫描进行多块读取调用。

（2）索引全扫描、索引范围扫描、索引唯一扫描进行单块读取。

25、谓语基数 = 表中数据行总数 / 该列数据唯一值个数。

khdm基数 Select Count(*)/Count(distinct khdm) from mc.mc_jerry_test;

26、索引选择度 = 1 / 该列数据唯一值个数。

具有较低谓语基数的列更适合做为索引的候选。（该列数据唯一值越多谓语基数越小，该列越适合建索引）。

27、 谓语基数 * （1/索引选择度） = 数据总行数

28、索引分为三大类 ： B-树索引、位图索引、索引组织表

29、位图索引 不适合 需要进行大量更新的列或具有较多DML操作的表。

位图索引 适合 于大多数对具有较少唯一值得列进行只读运算的数据仓库表。

如果表需要进行定期加载，就像典型的数据仓库应用中的表，很重要的一点是在加载前删除位图索引，然后加载数据，最后再重建索引。

30、oracle 大多数表的数据存储是没有顺序的。

DB2 大多数表的数据存储是按照聚簇索引来排序的。

DB2 中大多数表按照oracle的分类规则都属于索引组织表。

31、oracle 降低聚簇因子的语句

alter table tablename move; 重构表

alter index indexname rebuild; 重建索引

DB2 降低聚簇因子的语句

reorg table 模式.表名