一、SQL优化技巧
1、不要用*代替所有列名
指定仅仅需要的列名与使用*对比:
时间:359/1327=27.05%  CUP耗费: 4092121327/6413227637=63.81%
IO耗费: 29601/110117=26.88% 可见大幅降低I/O从而降低响应时间!
2、活用COMMIT
  PL/SQL块中,经常将几个相互联系的DML语句写在BEGIN …END,如果不影响事务的完整性,则建议在每个END前面写一个COMMIT,以达到 对DML的及时提交和 释放事务所占的资源的目的。
3、减少表的查询次数
1.一个逻辑单元中,将能读出的列一次性读出,且尽量存放在本地变量中,应该杜绝不要用一个读一个
2.在包含子查询的SQL中,要特别注意减少对表的查询次数,在代码清晰时对于能减少查询次数的应坚决减少
4、以EXISTS代替DISTINCT
多表信息的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXISTS替换, EXISTS 使查询更为迅速,因为此时RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。
5、使用默认值
可见在不含默认值,是null的列上没有使用索引,是全表扫描!而使用了默认值的列上使用了索引范围扫描!
不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列的情况下,只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的
如果每列确实可能存在空值的情况,可以使用默认值的方式替代以便充分利用索引提高性能
6、使用DECODE函数减少处理步骤
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.
7、通配符的使用技巧
当通配符出现在LIKE后面字符串的首位时,索引将不会被使用,因此在已知某字符的情况下,LIKE查询中应尽量不要把通配符写在首位
%代表不定长的字符,_代表定长的字符,如果在确定要通配的字符长度时,应该尽量使用_,而不是%
8、表的连接方式 FROM表顺序选择
Oralce优化器的原理是采用自下而上的顺序解析WHERE子句,因此表之间的连接必须写在其他WHERE条件之前, 可过滤掉最大数量记录的条件必须写在WHERE子句的末尾 。
因此,我们建议任何时候编写SQL语句时要 使用表的别名、 对表的连接永远 写在WHERE后面的第一个位置,并对过滤条件进行估算, 按照降序的大小将这些条件从WHERE子句最后部分往前排列。
二、索引的限制
1、索引对不等号和NOT的限制
结论:如果索引列是数字,则对于不等号的处理可以变更为NOT的方式或者(大于 OR 小于)的方式;对于确实无法不使用不等号的方式,可以使用默认值;如果可以建立位图索引则使用位图索引 ;否则可以考虑使用分区等方法进行优化 ,具体视情况而定。
2、索引对IS NULL的限制
   一般来说,如果WHERE子句基于的列是可空的列,且其建立了索引,如果使用了IS NULL,由于NULL的列本身不包含在索引中,因此无法利用索引。
所以一般对要建立索引的列不要设置为可空,如果确实含有空值,建议使用默认值代替空值,具体参见前面章节“SQL优化技巧”部分的“使用默认值”。
3、索引对函数的限制
这是因为该索引是常规b-tree索引,对该列在WHERE子句中使用了函数,则不能使用索引。因此,对在WHERE子句中经常要使用函数时,应该建立基于函数的索引,且 只有当查询语句包含该函数或者表达式时,基于函数的索引才会被调用。
4、索引对不匹配数据类型的限制
 因为ACCOUNT_TRADE表的字段YKKH是CHAR,因此在对其指定的值是数字时,Oracle虽然能隐式的执行数字和字符的转换,但不会调用其索引。而当对其指定是字符时,则不存在此问题,索引可以调用。
注意:因为数据类型的不匹配和Oracle对数据类型的隐式转换,此种类型的低效代码在任何项目中均可能因为大意而存在,因此建议开发人员和管理人员要定期抽查相应的代码,以杜绝此类低效代码!
索引类型总结
类型   描述
b-tree索引
最常最多使用的索引,其树结构与二叉树比较类似,根据ROWID快速定位所访问的行
bitmap索引
使用位图来管理与数据行的对应关系,适用于基数比较少的列,一般比较少访问的数据列使用
降序索引
降序索引在叶子节点中的存储从左到右是按照从大到小排序的;一般是针对逆向排序较多的查询时才使用该类型索引
函数索引
针对要频繁对列使用函数的索引,只有当查询语句包含该函数或者表达式时,基于函数的索引才会被调用
反转索引
反转了b-tree索引码中的字节,使索引条目分配更均匀,多用于并行服务器环境下,用于减少索引的竞争
分区索引
分区表的索引,又包括本地分区索引(本地前缀分区索引和本地非前缀分区索引)和全局索引,一般建议使用本地分区索引,因其与基表具有良好的数据均衡性和可维护性
三、访问路径
1、ROWID扫描
  ROWID扫描(ROWID SCANS)是通过ROWID中数据文件和块位置访问数据行。一般作为访问索引后的第二步,如果访问的列全部包含在索引中,则不会执行ROWID扫描。
2. 索引扫描
类型
方式
发生条件
1.FULL INDEX SCANS 全索引扫描
逐一读取索引中的所有块,由于索引中数据已按索引键排序,因此会忽略掉排序
1.ORDER BY中的列全部在该索引中时 
2.ORDER BY中列的顺序满足索引中前导列的顺序时 
3.使用GROUP BY且该子句中的列在索引中时
2.FAST FULL INDEX SCANS 快速全索引扫描
只扫描索引中的数据,不会扫描表中的数据;由于索引中数据未按索引键排序,因此不能忽略掉排序
当同时满足下列条件是,Oracle用FFIS替代FIS: 
1.查询的所有列均包含在索引中 
2.索引中的列至少一个具有not null约束
3.INDEX RANGE SCANS  索引范围扫描
访问选择性数据最常用的扫描方式;按顺序的对某个索引进行扫描,返回数据是升序排列的,可以使用唯一索引和非唯一索引;如果对索引列使用ORDER BY/GROUP BY则可省略排序
1.在唯一索引上使用范围操作符(>、<、>=、<=、<>、BETWEEN) 
2.在组合索引上使用部分列进行查询,导致查出多行
4.INDEX UNIQUE SCANS 索引唯一扫描 
扫描唯一索引或主键,要么返回一行数据要么返回0行数据
1.当使用唯一索引时 
2.当使用主键时
5.INDEX SKIP SCANS 索引跳跃式扫描 
其实质是将索引分解成多个小的子索引来提高效率,系从9i开始引入
复合索引中前导列的取值是枚举的从而可以分拆为多个子索引,并且查询条件中不含前导列时
6.位图索引扫描(bitmap index scan)
四、使用复合索引
  多个列联合起来组成的索引称为复合索引、或联合索引或者组合索引,往往联合索引比单个索引具有更好的性能。创建联合索引一般遵循的原则包括:
经常在WHERE子句中使用的列且这些列之间使用AND连接
查询条件可能包括n个列的AND关系,而大多数情况下使用m个列是(n>m),应该考虑复合索引,且n个列为前导列
某几个列联合起来能够组成唯一索引,应坚决建立联合唯一索引
复合索引中,建议至少一个不能为null,且如果可能尽量将只是存在null的列对其null值采用其它默认值代替
例子:
   CREATE INDEX name

     ON employee (emp_lname, emp_fname)