Oracle SQL 优化
POSS
软件平台（
www.poss.cn
）
目录
目录........................................................................................................................................ 2
第 1 章 模块功能概要描述................................................................................................... 4
第 2 章 SQL 语句编写注意问题 .......................................................................................... 5
2.1 IS NULL 与 IS NOT NULL.................................................................................... 5
2.2 联接列...................................................................................................................... 5
2.3 带通配符（%）的 like 语句 .................................................................................. 6
2.4 Order by 语句 ...................................................................................................... 6
2.5 NOT......................................................................................................................... 6
2.6 IN 和 EXISTS........................................................................................................ 7
第 3 章 SQL 语句性能优化 .................................................................................................. 9
3.1 选用合适的 ORACLE 优化器................................................................................ 9
3.2 访问 Table 的方式................................................................................................... 9
3.3 共享 SQL 语句 ...................................................................................................... 10
3.4 选择最有效率的表名顺序(只在基于规则的优化器中有效)............................. 11
3.5 WHERE 子句中的连接顺序................................................................................. 12
3.6 SELECT 子句中避免使用 ‘ *’ ............................................................................. 13
3.7 减少访问数据库的次数........................................................................................ 13
3.8 使用 DECODE 函数来减少处理时间.................................................................. 14
3.9 整合简单,无关联的数据库访问........................................................................... 15
3.10 删除重复记录...................................................................................................... 16
3.11 用 TRUNCATE 替代 DELETE .......................................................................... 16
3.12 尽量多使用 COMMIT........................................................................................ 16
3.13 计算记录条数...................................................................................................... 17
3.14 用 Where 子句替换 HAVING 子句.................................................................... 17
3.15 减少对表的查询.................................................................................................. 17
3.16 通过内部函数提高 SQL 效率. ........................................................................... 18
3.17 使用表的别名(Alias) .......................................................................................... 20
3.18 用 EXISTS 替代 IN............................................................................................. 20
3.19 用 NOT EXISTS 替代 NOT IN........................................................................... 21
3.20 用表连接替换 EXISTS....................................................................................... 21
3.21 用 EXISTS 替换 DISTINCT............................................................................... 22
3.22 识别’低效执行’的 SQL 语句 ........................................................................ 22
3.23 使用 TKPROF 工具来查询 SQL 性能状态....................................................... 23
3.24 用 EXPLAIN PLAN 分析 SQL 语句.................................................................. 23
3.25 用索引提高效率.................................................................................................. 25
3.26 索引的操作.......................................................................................................... 25
3.27 基础表的选择...................................................................................................... 27
3.28 多个平等的索引.................................................................................................. 27
评论0