PLSQL 程序优化和性能测试方法
1. 前言......................................................................................................................................................5
1.1 目的.................................................................................................................................................5
1.2 文档说明.........................................................................................................................................5
1.3 词汇表.............................................................................................................................................5
1.4 参考资料.........................................................................................................................................5
2. PLSQL 程序优化原则...........................................................................................................................6
2.1 导致性能问题的内在原因................................................................................................................6
2.2 PLSQL 优化的核心思想..................................................................................................................6
2.3 ORACLE 优化器.............................................................................................................................6
2.4 PLSQL 优化....................................................................................................................................7
2.4.1 选择最有效率的表名顺序..........................................................................................................7
2.4.2 WHERE 子句中的连接顺序......................................................................................................8
2.4.3 SELECT 子句中避免使用 ‘ * ‘...................................................................................................8
2.4.4 用 EXISTS 替代 IN....................................................................................................................8
2.4.5 用 NOT EXISTS 替代 NOT IN...................................................................................................9
2.4.6 用表连接替换 EXISTS..............................................................................................................9
2.4.7 用 EXISTS 替换 DISTINCT.....................................................................................................10
2.4.8 减少对表的查询......................................................................................................................10
2.4.9 避免循环(游标)里面嵌查询.................................................................................................11
2.4.10 尽量用 union all 替换 union...................................................................................................13
2.4.11 使用 DECODE 函数来减少处理时间.....................................................................................13
2.4.12 group by 优化.......................................................................................................................13
2.4.13 尽量避免用 order by.............................................................................................................14
2.4.14 用 Where 子句替换 HAVING 子句.........................................................................................14
2.4.15 使用表的别名(Alias).............................................................................................................14
2.4.16 删除重复记录........................................................................................................................15
2.4.17 COMMIT 使用.......................................................................................................................15
2.4.18 减少多表关联........................................................................................................................15
2.4.19 批量数据插入........................................................................................................................16
2.5 索引使用优化................................................................................................................................16
2.5.1 避免在索引列上使用函数或运算.............................................................................................17
2.5.2 避免改变索引列的类型...........................................................................................................17
2.5.3 避免在索引列上使用 NOT......................................................................................................17
2.5.4 用>=替代>..............................................................................................................................18
2.5.5 避免在索引列上使用 IS NULL 和 IS NOT NULL.....................................................................18
2.5.6 带通配符(%)的 like 语句.....................................................................................................19
2.5.7 总是使用索引的第一个列.......................................................................................................19
2.5.8 多个平等的索引......................................................................................................................19
2.5.9 不明确的索引等级..................................................................................................................19
2.5.10 自动选择索引........................................................................................................................20
2.5.11 使用提示(Hints)....................................................................................................................20
2.5.12 表上存在过旧的分析.............................................................................................................21
2.5.13 表上存在并行........................................................................................................................21
第 3 页 / 共 31 页
评论11