sql性能优化

1. 书写一致性 和 绑定变量
   A.字符级的比较，如空格，大小写
   B.两个语句所指的对象必须完全相同
   C.两个SQL语句中必须使用相同的名字的绑定变量
   不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。
   select * from emp a where e.empno=:1;
   select * from emp b where b.empno=:1;
2. 多表连接注意
   使用表的别名（Alias）
   当在SQL语句中连接多个表时， 请使用表的别名并把别名前缀于每个Column上。
   这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。别名尽量要短。
   对每个列清晰定义别名，不容易产生bug
   select *
   from emp a
   where exists (select 1 from dept a where a.deptno = a.deptno);
   select *
   from emp a
   where exists (select 1 from dept b where a.deptno = b.deptno);
3. SELECT子句中避免使用 ’ * ’
   SELECT子句中避免使用 ‘ * ’
   当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用 '’ 是一个方便的方法。不幸的是，这是一个非常低效的方法。
   实际上，ORACLE在解析的过程中， 会将‘’ 依次转换成所有的列名， 这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
   Insert into (一定要写上列) values 或 select 列
4. 减少表查询的次数
   使用DECODE函数或CASE WHEN可以避免重复扫描相同记录或重复连接相同的表。
   –正确的
   select owner,
   COUNT(DECODE(OBJECT_TYPE, ‘TABLE’, 1, 0)) TABLE_CN,
   COUNT(DECODE(OBJECT_TYPE, ‘INDEX’, 1, 0)) INDEX_CN
   from t1
   group by owner;

–错误的
SELECT OWNER,
(SELECT COUNT() FROM T1 A
WHERE OBJECT_TYPE = ‘TABLE’ AND A.OWNER = B.OWNER),
(SELECT COUNT() FROM T1 A
WHERE OBJECT_TYPE = ‘INDEX’ AND A.OWNER = B.OWNER)
FROM T1 B
GROUP BY OWNER;
–正确的
select owner,
COUNT(case when object_type=‘TABLE’ then 1 END),
COUNT(case when object_type=‘INDEX’ then 1 END)
from t1
group by owner;
–错误的
SELECT OWNER,
(SELECT COUNT() FROM T1 A
WHERE OBJECT_TYPE = ‘TABLE’ AND A.OWNER = B.OWNER),
(SELECT COUNT() FROM T1 A
WHERE OBJECT_TYPE = ‘INDEX’ AND A.OWNER = B.OWNER)
FROM T1 B
GROUP BY OWNER;
5. commit请批量
过多的commit 会导致log file sync等待事件大量增加，严重影响数据库性能，特别是IO比较紧张的服务器上。
commit 会触发lgwr写，把log buffer里的日志拷贝到redo file中去，commit频繁，那么每次写的日志少，频繁写，容易引起IO问题。
建议大量插入的交易，请控制事务大小
优化中遇到大量的报表和统计类语句，选择合适的批量维度，比如分区条件等。
6. in和exists
Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。NOT EXISTS比NOT IN效率稍高。但具体在选择IN或EXISTS操作时，要根据主子表数据量大小来具体考虑。
一般情况下：用EXISTS代替IN 用NOT EXISTS代替NOT IN。
在子查询中，NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。
7. 用UNION-ALL 替换UNION （如果有可能的话）
UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并， 然后在输出最终结果前进行排序，且去掉重复记录，数据量大的情况下可能会引起磁盘排序
（当建立同Oracle会话时，会在服务器内存中划分出一个专门用来排序的区域，从而为会话提供排序空间。但是，这个排序空间毕竟有限，若记录数量超过这个排序空间的话，就需要进行磁盘排序。但是，我们都知道，磁盘排序的执行速度要比内存排序的执行速度慢1400倍。而且，磁盘排序会消耗临时表空间的资源，并且可能影响到正在进行的其他SQL排序，因为Oracle必须为临时表空间中的数据块分配缓冲池。而且，过多的磁盘排序会导致空闲缓冲等待，以及将执行其他任务的数据块从缓冲池中分页出去。对于数据库管理员来说，在内存中进行排序总是比磁盘排序更受欢迎。所以说，磁盘排序是影响Oracle数据库性能的罪魁祸首。）

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。 因此各位还是要从业务需求分析使用UNION ALL的可行性。
8. 慎用以下功能
带有DISTINCT，UNION，MINUS，group by ,INTERSECT，ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序（SORT）功能。 使用这个功能之前应确定必须使用。

10g开始group by不进行排序，要排序，必须最后加order by
内部参数为:_gby_hash_aggregation_enabled=false或optimizer_features_enabled=9.2.0;

Order By语句中的非索引列会降低性能，可以通过添加索引的方式处理。
严格控制在Order By语句中使用表达式。

9. 避免在索引列上使用IS NULL
   Null值不存储在索引中，因此在索引列上带Is null条件的查询不会使用索引，而是使用Table Access Full 操作解析查询语句。
   如果在null列上使用is not null会使用索引
   如果在索引列上改条件为Is Not Null，因为索引列的所有非空值都存储在索引中，按道理也是可以走索引的。但是，为了解析查询语句，优化程序需要从索引中读取每一个值，在映射到表中索引返回的行。
   在大多数情况下，执行全表扫描比为索引返回的所有值执行索引扫描(相关的Table Access By Index Rowid操作)效率更高。（一般超过10%的记录的话全表扫描就会更快一些）
   Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。
   全表扫描（Full Table Scans, FTS）
   为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件。Oracle顺序地读取分配给表的每个数据块，直到读到表的最高水线处(high water mark, HWM，标识表的最后一个数据块)。一个多块读操作可以使一次I/O能读取多块数据块(db_block_multiblock_read_count参数设定)，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。由于HWM标识最后一块被读入的数据，而delete操作不影响HWM值，所以一个表的所有数据被delete后，其全表扫描的时间不会有改善，一般我们需要使用truncate命令来使HWM值归为0。幸运的是oracle 10G后，可以人工收缩HWM的值。
   由FTS模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表的尾部，这样可以使其快速交换出内存，从而不使内存重要的数据被交换出内存。
   使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5%-10%，或你想使用并行查询功能时。
   通过ROWID的表存取（Table Access by ROWID或rowid lookup）
   行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。
   为了通过ROWID存取表，Oracle 首先要获取被选择行的ROWID，或者从语句的WHERE子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。
   这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们会经常在执行计划中看到该存取方法，如通过索引查询数据。
   索引扫描（Index Scan或index lookup）
   先通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找(index lookup)。一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，在此情况下该次i/o只会读取一个数据库块。
   在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。
   索引扫描可以由2步组成：(1) 扫描索引得到对应的rowid值。 (2) 通过找到的rowid从表中读出具体的数据。每步都是单独的一次I/O，但是对于索引，由于经常使用，绝大多数都已经CACHE到内存中，所以第1步的I/O经常是逻辑I/O，即数据可以从内存中得到。但是对于第2步来说，如果表比较大，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，这是一个机械操作，相对逻辑I/O来说，是极其费时间的。
   所以如果多大表进行索引扫描，取出的数据如果大于总量的5%-10%，使用索引扫描会效率下降很多。

10. where后的列不要操作
    对于有连接的列“||”，最后一个连接列索引会无效。尽量避免连接，可以分开连接或者使用不作用在列上的函数替代。
    SELECT empno,ename FROM emp
    WHERE empno||ename=‘2000naem’
    改成这样就可使用索引了
    SELECT * FROM emp
    WHERE empno=2000 AND ename=‘dd’
    Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。
    如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。
    select * from sy
    where to_char(begin_date,‘YYYYMMDD’) =‘20081110’
    改
    select * from sy
    where begin_date between to_date(‘20081110’, ‘YYYYMMDD’) and
    to_date(‘20081110235959’, ‘YYYYMMDDHH24MISS’)

11. like使用
    错误使用:
    ‘%…’ 和 like ‘%…%’
    正确使用
    ‘…%’
    SELECT * FROM C_CONS WHERE CONS_NAME like ‘张%’

12. 避免使用数据库的类型自动转换功能(会导致无法走索引)
    字符型的就加上引号
    数字型的就不要加引号
    Date型不要代入timestamp
    NVARCHAR2和VARCHAR2

SELECT COUNT(1) FROM CONS WHERE CONS_NO = :B2 AND ORG_NO = :B1 AND EFFECT_FLAG = ‘1’

CONS表的字段cons_no为varchar2类型 ，但是传入的cons_no为number类型，所以无法走cons_no的索引，请开发修改程序在传入的变量前面增加to_char(:B2);
13. 选择必须的列
只选择你所需要的列，不要选择无关紧要的列，只会增加sql语句的消耗,也就是能不做就不做，能少做就少做.
14. 慎用视图
使用视图之前应知道试图的定义，有些视图中定义了很多表的union all，但是您可能只用到其中一个表，那么就不要使用视图，
直接使用表，减少对不必要表的扫描。
15. 选择所需要的表
使用多表连接时，要把表中的列的情况了解清楚，很多时候您要的数据可能在一张表里已经有了，实际上并不需要去连接其他表，
去掉无关的表，提高sql的性能
16. 操作符
不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，可以用“<” or “>”代替。
当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。
尽可能用“>=”替代“>”。
17. IN和=
子查询结果集为1的时候，不要使用in，应当使用=
18. Rownum
rownum在其通过查询的谓词部分之后并且在完成排序或聚集之前赋给行。rownum仅当其分配后增加1
错误的：
select * from emp where rownum<5 order by sal desc;
正确的：
Select * from (select * from emp order by sal desc) where rownum<5;
用rownum标记页号
select * from (
select a.*,rownum rn from (select * from emp order by sal desc) a where rownum<5)
Where rn>2;
无效的语句
select * from test where rownum>1;
因为rownum>1 为false
19. 缩小数据结果集
增加可以增加的条件来过滤数据结果集，加快表与表之间的连接

20. 用表连接替换exists
    当没有太多表连接的时候，建议使用表连接替换exists
    用EXISTS替换DISTINCT
    当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。 一般可以考虑用EXIST替换
    EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.
21. with tmp as的使用
    with as 作用：当查询中多次用到某一部分时，可以用Oracle with语句创建一个公共临时表。因为子查询在内存临时表中，避免了重复解析，
    所以执行效率会提高不少。临时表在一次查询结束自动清除
22. 表之间一定要有关联条件，避免笛卡尔连接
23. 引起全表扫描的语句
    在索引列上使用NOT或者“<>”;
    对索引列使用函数或者计算；
    NOT IN操作；
    通配符位于查询字符串的第一个字符；
    IS NULL或者IS NOT NULL；
    多列索引，但它的第一个列并没有被Where子句引用；
24. 尽量多使用分区索引的条件(针对分区表)。
25. 树查询connect by后一定要跟上prior 关键字
    select t.org_no,t.org_name from org t
    start with t.org_no=‘35401’
    connect by t.p_org_no= prior t.org_no
26. 用Where子句替换HAVING子句
    避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。
27. Count(1),count()等价
    从执行计划来看，count(1)和count()的效果是一样的。
    但是在表做过分析之后，count(1)会比count()的用时少些（1w以内数据量），不过差不了多少。
    这个也与表的记录数多少有关！如果1w以外的数据量，做过表分析之后，反而count(1)的用时比count()多了。

另外，当数据量达到10w多的时候，使用count(1)要比使用count(*)的用时稍微少点！

如果你的数据表没有主键，那么count(1)比count()快
如果有主键的话，那主键（联合主键）作为count的条件也比count()要快
如果你的表只有一个字段的话那count()就是最快的啦
count() count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(),自动会优化指定到那一个字段。所以没必要去count(1)，用count(),sql会帮你完成优化的
因此：count(1)和count(*)基本没有差别！

28. COUNT(*)不能与ORDER BY操作 同时存在
29. 不允许使用truncate语句
    当删除表中的记录时，在通常情况下， 回滚段(rollback segments ) 用来存放可以被恢复的信息。 如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。
    而当运用TRUNCATE时， 回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。
30. UNION ALL替换OR (适用于索引列)