oralce数据库设计及开发规范_v2.1


文 档 状 态 [ ] 初稿 [ ] 评审通过 [ ] 修改 [√] 发布 [ ] 作废 Oracle 数据库设计及开发 初稿 评审通过 修改 发布 作废 文档名称 Oracle 数据库设计及开发 规范 文档标识 CPIC-DBA-001 当前版本 V2.1 作 者 开发 DBA 团队 部门 /厂商 信息技术中心应用开发三部 完成日期 2014.07. CPIC 软件工程文档 【内部资料 不得外泄】 开发规范 规范 文档版本历史表 作者 V1.0 李辉 V1.1 李辉 V1.2 李辉 V1.3 李辉 V2.0 张拥军、聂东杰 施晓晨、李辉 V2.1 张拥军、施晓晨 李辉等 文档审核记录表 审核人 内容范围 本文档是关于中国太平洋保险 的信息系统的数据库设计 及开发 适用的对象 本文档适用于中国太平洋保险 的有关决策者、评估者、设计者 第 1 页 操作 日期 说明 创建 2014-03-19 创建初稿 修改 2014-04-08 修改 修改 2014-05-09 修改 修改 2014-06-06 修改 聂东杰 、 李辉 等 完善 2014-06-23 统一布局 每一知识点 施晓晨 、 完善 2014-07-03 对规范要求进行更新 增加 审核人签字 审核日期 本文档是关于中国太平洋保险 (集团)股份有限公司应用开发三部基于 Oracle 及开发 规范。 本文档适用于中国太平洋保险 (集团)股份有限公司应用开发三部、外包商及各实施项目组 设计者 、开发者阅读。 说明 创建初稿 修改 数据库设计规范 修改 数据库编程规范 修改 PLSQL 优化 统一布局 ,检查、完善 每一知识点 对规范要求进行更新 , 增加 和完善部分内容 说明 Oracle 数据库实施 外包商及各实施项目组 1 前言 ................................ ................................ 1.1 目的 ................................ 1.2 预期的读者和阅读建议 2 模型设计规范 ................................ 2.1 数据库建模原则 性规范 2.2 实体型之间关系认定规范 2.3 范式化 1NF 的规范 2.4 范式化 2NF 的规范 2.5 范式化 3NF 的规范 2.6 反范式化冗余字段使用规范 2.7 数据库对象命名基本规范 2.7.1 遵循行业规范 2.7.2 简单命名原则 2.7.3 字符范围原则 2.7.4 字母全部大写或小写原则 2.7.5 勿用保留词原则 2.7.6 同义性原则 ................................ 2.7.7 富有含义原则 2.7.8 扩展性原则 3 表的设计规范 ................................ 3.1 命名规范 ................................ 3.1.1 表的命名规范 3.1.2 字段的命名规范 3.2 表的设计规范 ................................ 3.2.1 指定表空间规范 3.2.2 主键设计规范 3.2.3 外键设计规范 3.2.4 字段能否为空的规范 3.2.5 关于冗余列的规范 3.2.6 使用注释的规范 3.2.7 一个表所含数据量的规范 3.2.8 增量同步表的设计规范 3.2.9 字段类型 、 3.2.10 一个表所含字段访问频繁度的规范 3.2.11 大对象字段 3.3 字段类型规范 ................................ 3.3.1 禁止使用会发生隐式转换的类型 3.3.2 禁止使用过时老类型 3.3.3 禁止使用大对象 3.3.4 禁止使用高精度 3.3.5 禁止使用 CHAR 第 2 页 目目目目 录录录录 ................................................................................................ ................................................................................................................................ 预期的读者和阅读建议 ................................................................................................ ................................................................................................................................ 性规范 ................................................................................................ 实体型之间关系认定规范 ............................................................................................... 的规范 ................................................................................................ 的规范 ................................................................................................ 的规范 ................................................................................................ 反范式化冗余字段使用规范 ........................................................................................... 数据库对象命名基本规范 ............................................................................................. 遵循行业规范 ................................................................................................ 简单命名原则 ................................................................................................ 字符范围原则 ................................................................................................ 字母全部大写或小写原则 ................................ ................................ 勿用保留词原则 ................................................................................................ ................................................................................................ 富有含义原则 ................................................................................................ 扩展性原则 ................................................................................................ .............................................................................................................................. ......................................................................................................................... 表的命名规范 ................................................................................................ 字段的命名规范 ................................................................................................ ................................................................................................ 指定表空间规范 ,禁止采用默认表空间的形式 ................................ 主键设计规范 ,禁止使用含有实际语义的列作为主键 ................................ 外键设计规范 ,禁止仅依靠主外键关系来保证数据一致性 ......................... 字段能否为空的规范 ,禁止“主键外的其他字段都允许为空 关于冗余列的规范 ,除非必要,否则尽量不加冗余列 ................................ 使用注释的规范 ,禁止不对字段添加注释 ................................ 一个表所含数据量的规范 ,合理设计分区表 ................................ 增量同步表的设计规范 ,标识和时间戳不可或缺 ................................ 、宽度及一个表所含字段总长度的规范 ................................ 一个表所含字段访问频繁度的规范 ................................ ............................... 大对象字段 (BLOB,CLOB)使用规范 ................................ ........................... ................................................................................................ 禁止使用会发生隐式转换的类型 :INTEGER,FLOAT ................................ 禁止使用过时老类型 :RAW,LONG,LONG RAW ................................ 禁止使用大对象 :BLOB,CLOB,NCLOB ................................ ........................... 禁止使用高精度 :TIMESTAMP ................................ ................................ CHAR 类型 ........................................................................................... ............................................... 6 .................................... 6 .................................... 6 ................................ 6 ................................... 6 ............................... 7 ........................................... 7 ........................................... 8 ........................................... 9 ........................... 9 ............................. 10 ...................................... 10 ...................................... 10 ..................................... 11 .................................................. 11 .................................. 11 .......................................... 11 ...................................... 11 ......................................... 11 .............................. 12 ......................... 12 ..................................... 12 ................................. 12 ................................................. 13 ............................................. 13 ................................. 13 ......................... 13 主键外的其他字段都允许为空 ”的设计 ..... 14 ................................. 14 ..................................................... 15 ................................................. 15 ......................................... 15 ......................................... 16 ............................... 16 ........................... 16 ................................................. 17 ..................................... 17 ............................................... 17 ........................... 17 ........................................... 17 ........................... 18 4 分区表设计规范 ................................ 4.1 表空间及分区表的概念 4.1.1 表空间 ................................ 4.1.2 分区表 ................................ 4.2 表分区的具体作用 4.3 表分区的优缺点 ................................ 4.4 分区表设计规范 ................................ 4.2.1 不使用全局索引 4.2.2 RANGE 分区的规范 4.2.3 LIST 分区的规范 4.2.4 HASH 分区的规范 4.2.5 RANGE-LIST 4.2.6 RANGE-HASH 5 索引设计规范 ................................ 5.1 索引分类 ................................ 5.1.1 单列索引与复合索引 5.1.2 唯一索引与非唯一索引 5.1.3 B 树索引、位图索引与函数索引 5.2 命名规范 ................................ 5.3 索引设计规范 ................................ 5.3.1 指定表空间规范 5.3.2 主键索引的规范 5.3.3 唯一约束索引的规范 5.3.4 外键 列索引的规范 5.3.5 复合索引的规范 5.3.6 审慎使用函数索引 5.3.7 分区索引的规范 5.3.8 索引重建的规范 6 其他设计规范 ................................ 6.1 其他数据库对 象命名规范 6.2 视图设计规范 ................................ 6.2.1 尽量使用简单的视图 6.2.2 按照必要性原 6.3 存储过程、函数 、 6.3.1 禁止使用触发器 6.3.2 禁止不建立 出错处 理 6.3.3 禁止使用 GOTO 6.3.4 需 要循 环更新 时 6.3.5 禁止 退出 时不关 闭 所有 打 开的 6.3.6 如 对数据 进 行 更新 6.3.7 确 保 事务 的原 子 性及 隔离 性 6.3.8 在没 有 事务处 理时 7 S Q L 访问规范 ................................ 7.1 不要写复 杂 的 S QL ................................ 第 3 页 .......................................................................................................................... 表空间及分区表的概念 ................................................................................................ ................................................................................................ ................................................................................................ 表分区的具体作用 ................................................................................................ ................................................................................................ ................................................................................................ 不使用全局索引 ,及禁止 将需 U PD ATE 字段设为分区 条件 ........................... 分区的规范 .............................................................................................. 分区的规范 ................................................................................................ 分区的规范 ................................................................................................ LIST 分区的规范 ................................ ................................ HASH 分区的规范 ................................ ................................ .............................................................................................................................. ......................................................................................................................... 单列索引与复合索引 .......................................................................................... 唯一索引与非唯一索引 ................................ ................................ 位图索引与函数索引 ................................ ................................ ......................................................................................................................... ................................................................................................ 指定表空间规范 ,禁止采用默认表空间 ................................ ......................... 主键索引的规范 ,先 建唯一索引,再 建主键约束 ................................ 唯一约束索引的规范 ,先 建 普通 索引,再 建唯一性约束 ............................. 列索引的规范 ,外键 均需 建立索引 ................................ ......................... 复合索引的规范 ,列数禁止 超 过 5 个 ................................ ............................. 审慎使用函数索引 、位图索引、反 向 索引 ................................ 分区索引的规范 ................................................................................................ 索引重建的规范 ................................................................................................ .............................................................................................................................. 象命名规范 ............................................................................................. ................................................................................................ 尽量使用简单的视图 ,避免 使用复 杂 的视图 ................................ 按照必要性原 则建立视图 ................................ ................................ 、触发器设计规范 ................................ ................................ 禁止使用触发器 ................................................................................................ 禁止不建立 出错处 理 机制 ................................ ................................ GOTO 进 行 跳 转 ................................ ................................ 需 要循 环更新 时 ,必 须进 行 批 量 提交 ,且做事务控制 ................................ 禁止 退出 时不关 闭 所有 打 开的 db l ink 、游 标 ................................ 如 对数据 进 行 更新 ,则必 须在异常捕获代码 中 做回退操 作 ......................... 确 保 事务 的原 子 性及 隔离 性 ,成功 及时 c ommit ,失败 及时 在没 有 事务处 理时 ,禁止 随意 使用 COMMIT 和 ROLLBAC K .............................. .............................................................................................................................. ................................................................................................ .......................... 18 ................................. 18 ................................................. 18 ................................................. 18 ......................................... 18 ............................................. 19 ............................................. 19 ........................... 19 .............................. 19 ................................ 20 ................................ 21 .................................................... 21 .................................................... 22 .............................. 23 ......................... 23 .......................... 23 ..................................................... 23 ...................................... 23 ......................... 23 ................................................. 24 ......................... 25 ......................................... 25 ............................. 26 ......................... 26 ............................. 26 ..................................................... 26 ................................. 27 ................................. 27 .............................. 27 ............................. 27 ................................................. 28 ................................................. 28 ................................................. 28 ............................................. 28 ................................. 29 ................................................. 29 ................................................... 30 ................................. 30 ................................................ 30 ......................... 30 失败 及时 r o ll b ac k ....... 30 .............................. 30 .............................. 32 .......................................... 32 7.2 避免 使用 SELECT * 7.3 INSERT 时 需 写全列名 7.4 进 行 DML 操 作时使用 7.5 大数据量 DML 操 作分 多次执 行 7.6 使用 绑 定 变 量,降低 高 硬解析 7.7 选择最 有 效率 的表名 顺序 7.8 关注 WHERE 子句 中的 连接顺序 7.9 避免 使用 IN 操 作 , 7.10 用表 连接替 换 EXISTS 7.11 用 E XISTS 替 换 DISTINCT 7.12 用 union all 替 换 7.13 使用 DECOD E 函数来 减少处 理时间 7.14 避免 使用 o r d er by 7.15 避免 使用 HAVING 7.16 减少多 表关 联,OLTP 7.17 避免 重复访问 ................................ 7.17.1 使用 g r oup by 7.17.2 用表 更新 表 7.17.3 竖向显示变横向显示 7.18 禁止字段 参 与表 达 式 运算 7.19 禁止 利 用 S QL 语 句做 业 务 7.20 索引的使用 ................................ 7.20.1 避免在 索引列 上 使用函数或 运算 7.20.2 避免改变 索引列 7.20.3 避免在 索引列 上 使用 7.20.4 用>=替代> ................................ 7.20.5 避免在 索引列 上 使用 7.20.6 进 行模 糊查询 时 7.20.7 总是使用索引的 第 一个列 8 编码 及注释规范 ................................ 8.1 编码 规范 ................................ 8.1.1 严 禁 潜在 的数据类型转换 8.1.2 不要 将 空的 变 量 值直接 与 比较运算 符 8.1.3 跨 行语 句 , 8.1.4 I ns er t … v al u e s 8.1.5 I ns er t … s elec t 8.1.6 避免 使用 嵌套 的 8.1.7 减少控制 语 句 的 检查次 数 8.1.8 语 句涉 及 多 个表时 8.1.9 其他 编码 规范 8.2 注释规范 ................................ 8.2.1 注释语 法 ................................ 8.2.2 每 个 块 和过程开发 放置 注释 8.2.3 代码 注释应 放在 其 上方 或 右方 第 4 页 SELECT * ................................................................................................ 时 需 写全列名 ................................................................................................ 操 作时使用 CTAS 进 行数据 备 份 ................................ ................................ 操 作分 多次执 行 ................................ ................................ 降低 高 硬解析 ................................ ................................ 选择最 有 效率 的表名 顺序 ............................................................................................. 子句 中的 连接顺序 ................................ ................................ ,严 禁使用 NOT IN 操 作 ................................ ................................ EXISTS ................................................................................................ DISTINCT ............................................................................................. 替 换 union ............................................................................................ 数来 减少处 理时间 ................................ ................................ o r d er by ................................................................................................ HAVING 子句 ................................................................................................ OLTP 系统表关 联 不 超 过 3 个 ................................ .......................... ................................................................................................ g r oup by ................................................................................................ 表 更新 表 ................................................................................................ 竖向显示变横向显示 ................................................................ 禁止字段 参 与表 达 式 运算 ........................................................................................... 语 句做 业 务 逻辑 的 判断 或 操 作。 ................................ ................................................................................................ 避免在 索引列 上 使用函数或 运算 ................................ ................................ 避免改变 索引列 的类型 ................................ ................................ 避免在 索引列 上 使用 NOT ................................ ................................ ................................................................................................ 避免在 索引列 上 使用 IS N U LL 和 IS NOT N ULL ................................ 进 行模 糊查询 时 ,禁止 条件 中字符 串直接以 “%”开 头 ............................. 总是使用索引的 第 一个列 ................................ ................................ ......................................................................................................................... ......................................................................................................................... 严 禁 潜在 的数据类型转换 ................................ ................................ 不要 将 空的 变 量 值直接 与 比较运算 符 (符 号)比较 ................................ ,第 一关键字应 当左 对 齐 ................................ ................................ 8.1.4 I ns er t … v al u e s 和 upd a t e 语 句书 写规范 ................................ 8.1.5 I ns er t … s elec t 语 句书 写规范 ................................ ................................ 避免 使用 嵌套 的 IF 语 句 ................................ ................................ 减少控制 语 句 的 检查次 数 ................................ ................................ 语 句涉 及 多 个表时 ,使用 别 名来限定字段名 ................................ 其他 编码 规范 ................................................................................................ ......................................................................................................................... ................................................................................................ 每 个 块 和过程开发 放置 注释 ................................ ................................ 代码 注释应 放在 其 上方 或 右方 ................................ ................................ ........................................ 32 .................................... 33 ................................... 33 ..................................................... 33 ..................................................... 34 ............................. 34 ..................................................... 34 ................................. 35 .................................. 36 ............................. 37 ............................ 38 ............................................. 38 ...................................... 39 ................................. 39 .......................... 39 ............................................... 40 .................................. 40 ....................................... 40 ........................ 41 ........................... 42 ....................................................... 42 ................................................... 43 .................................... 43 .................................................... 43 ................................................. 44 .......................................... 44 ............................................ 45 ............................. 45 ............................................... 45 ......................... 46 ......................... 46 ................................................. 46 ......................................... 46 ................................. 47 ................................................ 47 ................................... 48 ................................................... 48 ................................................. 49 ................................................. 50 ..................................... 50 ......................... 53 ............................................. 53 ............................................. 54 ......................................... 54 8.2.4 其他注释规范 9 PLS Q L 优化 ................................ 9.1 性能问 题 分 析 ................................ 9.2 PLS QL 优化的 核心思想 9.3 PLS Q L 优化 示例 ................................ 9.3.1 减少 对表的 查询 9.3.2 避免 循 环 ( 9.3.3 g r oup by 优化 9.3.4 删 除重复 记录 9.3.5 COMMIT 使用 9.3.6 批 量数据 插入 9.3.7 索引使用优化 9.3.8 使用 提示 (H ints) 9.3.9 表 上 存 在 过 旧 的分 析 9.3.10 表 上 存 在并 行 9.3.11 关于索引建立 9.3.12 E xp a in Pla n 第 5 页 其他注释规范 ................................................................................................ ................................................................................................................................ ................................................................................................ 优化的 核心思想 ................................................................................................ ................................................................................................ 减少 对表的 查询 ................................................................................................ (游 标)里面嵌查询 ................................ ................................ 优化 ................................................................................................ 删 除重复 记录 ................................................................................................ 使用 ................................................................................................ 批 量数据 插入 ................................................................................................ 索引使用优化 ................................................................................................ (H ints) ............................................................................................... 表 上 存 在 过 旧 的分 析 ......................................................................................... 表 上 存 在并 行 ................................................................................................ 关于索引建立 ................................................................................................ 9.3.12 E xp a in Pla n 分 析 索引使用 ................................ ................................ ..................................... 54 .................................. 55 .................................................. 55 .................................. 56 .............................................. 57 ................................. 57 ......................................... 59 .................................... 60 ..................................... 61 ........................................ 61 ..................................... 62 ..................................... 63 ............................... 64 ......................... 65 ................................... 65 ................................... 65 ............................................ 66 1111 前言前言前言前言 1.11.11.11.1 目的目的目的目的 本文档 详细 定义 了 Oracle 计规范、索引的设计规范、其他数据库对象的设计规范 并介绍了 PLSQL 调 优的关注点和 常 用 方法 术参考资料 ,用 以 指 导 Oracle 我们希望通 过 该 规范的 推 行 数据库设计和开发,使数据库 结构 和 编码风格 标 准 化 提 前 避免由 于数据库设计不 当而产 生的 麻烦 习惯 ,好 的 习惯 是 软件质 量的 很好 的保证 1.1.1.1.2222 预期的读者和阅读建议预期的读者和阅读建议预期的读者和阅读建议预期的读者和阅读建议 本文档预期的读者为 中国太平洋保险 建设 相 关的 自 主 研 发团 队 及外包团 队 统设计 师 、开发 师 、测试师等相 关 岗 位的 人员 读者可 以通篇 阅读 该 文档 重点关注 跟自身相 关的 内容章节 注释规范 等 。 2222 模型设计规范模型设计规范模型设计规范模型设计规范 2.12.12.12.1 数据库建模原则性规范数据库建模原则性规范数据库建模原则性规范数据库建模原则性规范 对于 涉 及数据库的项目 在 数据库 层面沟通交互 的依据 20 个 以上 的模型,需 要 DBA 对于 OLTP 系统,采用范式化 思想进 行模型设计 第 6 页 Oracle 数据库的数据库模型设计规范、表的设计规范 其他数据库对象的设计规范 、SQ L 的访问规范、编码 和注释规范 调 优的关注点和 常 用 方法 ,可作为数据库规 划 、设计、开发及 维护人员 的 技 Oracle 数据库的设计和开发。 我们希望通 过 该 规范的 推 行 ,能 够 实 现以 系统、体系的 工 程化 思维 模式 去 规范 使数据库 结构 和 编码风格 标 准 化 ,提 高模型的前 瞻 性 、 提 前 避免由 于数据库设计不 当而产 生的 麻烦 ,同时 好 的规范,在执 行的时 候 可 以培养出好 的 好 的 习惯 是 软件质 量的 很好 的保证 。 预期的读者和阅读建议预期的读者和阅读建议预期的读者和阅读建议预期的读者和阅读建议 中国太平洋保险 (集团)股份有限公司信息 技术 中 心跟 信息系统 建设 相 关的 自 主 研 发团 队 及外包团 队 内 部项目 经 理、开发 经 理、DBA、数据 结构管 理 师 测试师等相 关 岗 位的 人员 。 读者可 以通篇 阅读 该 文档 ,以整 体 熟悉 和 掌握 Oracle 数据库设计 和开发 重点关注 跟自身相 关的 内容章节 ,如 数据库模型设计、表的设计,或 S QL 访问规范 模型设计规范模型设计规范模型设计规范模型设计规范 数据库建模原则性规范数据库建模原则性规范数据库建模原则性规范数据库建模原则性规范 对于 涉 及数据库的项目 ,需 要 构 建数据库 逻辑 模型图,逻辑 模型图是项目组 成员 之间 在 数据库 层面沟通交互 的依据 ,必 须 规范 画 图(表,主键,外键,关系 )。 DBA 参 与设计,并 作 最终 审 核 。 采用范式化 思想进 行模型设计 ,对于 OLAP 系统,采用 面向 问 题 及 多 表的设计规范 、分区表的设 编码 和注释规范 , 开发及 维护人员 的 技 体系的 工 程化 思维 模式 去 规范 Oracle 、高 效 性,以尽 早 在执 行的时 候 可 以培养出好 的 股份有限公司信息 技术 中 心跟 信息系统 数据 结构管 理 师 、系 和开发 规范,也 可 以 访问规范 、编码 和 逻辑 模型图是项目组 成员 之间 )。 对于表的个数 在 采用 面向 问 题 及 多 级颗粒 度的 思想进 行模型设计 需 采用主 流 的模型设计 软件工 具 2.2 2.2 2.2 2.2 实体型之间关系认定规范实体型之间关系认定规范实体型之间关系认定规范实体型之间关系认定规范 所有实体 间的业 务逻辑 关系 成 关系数据库的三 种 关系(1 : 1 需 要 DBA 参 与设计。 比如 ,实体型 A 和实体型 一个 A 可 以 对应 几 个 B?一个 (1)一个 A 对应一个 B (2)一个 A 对应 多 个 B (3)一个 A 对应 多 个 B 具体实施的时 候 ,掌握如 下 原则 ¢ 1 : 1 关系 选 取任何 一个表的主键 到另 一个表中 ¢ 1:N 关系 将 1 表的主键 在 ¢ N: M 关系采用“关系表 合主键 ;各实体表 主键不 但 组 成了该 关系表的主键 中存 在 。 ¢ 对于三个 以上 表之间的 保证 查询 速 度。 2.3 2.3 2.3 2.3 范式化范式化范式化范式化 1NF1NF1NF1NF 的规范的规范的规范的规范 OLTP 系统的模型,需 要符合 第 三范式 范式化要 求 (1NF):列是访问的 最 小单位 在 具体实施的时 候 ,需 要依据 情况 对 相 应 属 性 进 行 拆 分或者合 并 ¢ 同一个 属 性 值 的不同 细 度 把 握 比如,常见的“姓名 ” 列,“名”是一个列,两个列的值组合起来才表达一个 同的系统中都有应用,这主要是依据需求的细度来确定 第 7 页 级颗粒 度的 思想进 行模型设计 。 需 采用主 流 的模型设计 软件工 具 P o w er D e sign er,ERW in 。 实体型之间关系认定规范实体型之间关系认定规范实体型之间关系认定规范实体型之间关系认定规范 间的业 务逻辑 关系 ,除 了 语义 上 保留其原有的业 务 关系外 , 1 : 1 )(1 :N)(N: M),对于 3 个及 以上 实体型之间的 和实体型 B 之间的关系,可 以通 过问 两 个问 题 来 确 定他 们 之间的关系 一个 B 可 以 对应 几 个 A ? B,相 反一个 B 对应一个 A,那么 A 对 B 就 是 1 : 1 B,相 反一个 B 对应一个 A,那么 A 对 B 就 是 1 :N B,相 反一个 B 对应对个 A,那么 A 对 B 就 是 N:M 掌握如 下 原则 : 关系 选 取任何 一个表的主键 到另 一个表中 ,作为外键来体 现 。 表的主键 在 N 表中,以 外键形式来体 现 。 关系表 ”来体 现 ,该 关系表的主键是 由相 关实体表的主键组 成 的复 主键不 但 组 成了该 关系表的主键 ,同时 也 被看 作外键 在 对于三个 以上 表之间的 “多 元 关系”常需 要和反范式化冗余字段 结 合 起 来设计 的规范的规范的规范的规范 需 要符合 第 三范式 ,对于表 在 20 个 以上 的模型,需 要 列是访问的 最 小单位 ,具有原 子 性,不可 再 被 分 割 需 要依据 情况 对 相 应 属 性 进 行 拆 分或者合 并 : 同一个 属 性 值 的不同 细 度 把 握 ”这个属性,设计一:“姓名”是一个列,设计二 两个列的值组合起来才表达一个 “姓名”语义。两种设计方法 这主要是依据需求的细度来确定 ,灵活把握。 ,本 质上 都要转化 个及 以上 实体型之间的 “多 元 关系”, 可 以通 过问 两 个问 题 来 确 定他 们 之间的关系 : 1 : 1 关系 ; 1 :N 关系 ; N:M 关系 ; 。 该 关系表的主键是 由相 关实体表的主键组 成 的复 同时 也 被看 作外键 在 该 关系表 常需 要和反范式化冗余字段 结 合 起 来设计 ,以 需 要 DBA 参 与设计。 不可 再 被 分 割 。 设计二 :“姓”是一个 两种设计方法 ,在不 ¢ 把 多 个 属 性 值错 误 的作为一个 属 性 值 存储 比如:常见的 OA 系统要存储员工的各种属性 JAVA,.NET,C#,Perl,UNIX 等等 能属性字段,然后某员工所掌握的多种技能用逗号 个员工表的技能属性字段中 这里的错误在于将多个属性值作为一个属性值存储在一个字段中 工对某个技能掌握情况,而且 等等),则再增加字段,里面的对应关系将很容易错乱 正确的设计应该是:两个实体表 握多个技能,也就是(1:N ) 双向都是(1:N)关系,那么综合起来员工和技能之间就是 规范,应该设计一张“关系表 键),该关系有一个属性“技能掌握程度 2.42.42.42.4 范式化范式化范式化范式化 2NF2NF2NF2NF 的规范的规范的规范的规范 OLTP 系统的模型,需 要符合 第 三范式 范式化要 求 (2NF):满足 实体表中一 般 不会 出现 违 反 以上 列的“复合”主键,故 而 关系表 容 易 出现 违 反 属 性,本 该 属 于 相 关的 某 个实体表的 表的复合主键唯一 确 定,DML 系表中 拆 分,也 许会单 独 形 成 一个表 中。 违反 2NF 的例子: 学生考试情况中,有两个实体表 N:M 的关系,就要创建一张关系表存储该多对多的考试关系 号,属性为考试分数;那么 “ 任课老师,必须先进行考试 , 科编号,只是依赖于学科编号 第 8 页 把 多 个 属 性 值错 误 的作为一个 属 性 值 存储 系统要存储员工的各种属性 ,包括技能信息,技能范围 等等 ,一种常见的错误设计是:设计一张员工表 然后某员工所掌握的多种技能用逗号 (,)间隔,然后将这个字符串存储到这 个员工表的技能属性字段中 。 这里的错误在于将多个属性值作为一个属性值存储在一个字段中 ,不能满足直接遍历员 而且 如果再要求说明员工对个技能的掌握程度(精通 里面的对应关系将很容易错乱 ,这是严重违反 1NF 两个实体表 :一张是员工表,一张是技能字典表 , )关系,相反一个技能可以被多个员工掌握,也是 那么综合起来员工和技能之间就是 “多对多关系(N:M 关系表 ”来存储“多对多关系”,主键为复合主键(员工主键 技能掌握程度 ”。 的规范的规范的规范的规范 需 要符合 第 三范式 。对于表 在 20 个 以上 的模型,需 要 满足 1NF,不存 在 非主键 属 性对主键 属 性的部分依 赖 实体表中一 般 不会 出现 违 反 2NF 的 情况 ,因 为都是“一个”主键列 , 故 而 关系表 容 易 出现 违 反 2NF 的 情况 。主要是 该 关系表非主键外的 本 该 属 于 相 关的 某 个实体表的 ,却 放 到 了该 关系表中。这 使 得 该 属 性不能 通 过 该 关系 DML 操 作会发生 错 误 。如 果违 反 了 2NF,那么 应 该 把这 个 属 性 从 关 也 许会单 独 形 成 一个表 ,绝 大部分 情况下 是 将该 属 性 归 并 到某 个 相 关的实体表 有两个实体表 :学生表和学科表,学生与学科之间的考试关系就是 就要创建一张关系表存储该多对多的考试关系 ,表的主键为学生编号和学科编 “任课老师”该放在那里呢?如果放到考试关系表中 必须先进行考试 ,这显然不符合实际,也就是任课老师不该依赖于学生编号和学 只是依赖于学科编号 ,也就是说任课教师信息应该放在学科表中 。 技能范围 :Oracle, 设计一张员工表 ,其中有一个技 然后将这个字符串存储到这 不能满足直接遍历员 精通 ,熟悉,一般 1NF 的情况。 ,一个员工可以掌 也是 (1:N)关系, N:M)”,依据前述 员工主键 +技能主 需 要 DBA 参 与设计。 不存 在 非主键 属 性对主键 属 性的部分依 赖 。 ,而 关系表是 两 个 主要是 该 关系表非主键外的 这 使 得 该 属 性不能 通 过 该 关系 那么 应 该 把这 个 属 性 从 关 绝 大部分 情况下 是 将该 属 性 归 并 到某 个 相 关的实体表 学生与学科之间的考试关系就是 表的主键为学生编号和学科编 如果放到考试关系表中 ,那么安排 也就是任课老师不该依赖于学生编号和学 。 2.5 2.5 2.5 2.5 范式化范式化范式化范式化 3NF3NF3NF3NF 的规范的规范的规范的规范 OLTP 系统的模型,需 要符合 第 三范式 范式化要 求 (3NF):满足 违 反 3NF 的 情况 ,绝 大 多 数是 在 含有外键的表中 主键,那么 依 赖 于 B k e y 的 属 性应 当 属 于 对 A 表的主键 A k e y 的依 赖 , 赖,传递 依 赖 于 A (A k e y) ;三 种 关系 的 情况 。违 反 3NF 的 后果 , 违反 3NF 的例子: 教师和学科之间,存在着上课关系 上,那么该关系为 1:1 关系 , 该 1:1 关系,那么教师的 “联系电话 科表中,就将联系电话放入学科表中 教师编号对学科的依赖,达到了学科编号的依赖 依赖,违反了 3NF。应该将其从学科表中拆出来放入教师表中 比如,假设一个教师已经存在但是还没有为其分配科目 2.2.2.2.6666 反范式化冗余字段使用规范反范式化冗余字段使用规范反范式化冗余字段使用规范反范式化冗余字段使用规范 OLTP 系统中 在 完 成 范式化 工 作之 后 高数据访问性能 ;在 OLAP 中采用的是 面向 问 题 的设计 思想 息。 当 SQL 关 连查询涉 及 到 常 用 在 两 个 地 方 :(1) 达 到 关 连查询 时 减少 表的关 联 数量的目的 中,将 父 表的 属 性存储 在 “ 反范式化冗余字段实例 (1)关系表中的冗余 : 加到考试关系表中,这样,每个学生得了多少学分 第 9 页 的规范的规范的规范的规范 需 要符合 第 三范式 。对于表 在 20 个 以上 的模型,需 要 满足 2NF,不存 在 非主键 属 性对主键 属 性的 传递 依 赖 绝 大 多 数是 在 含有外键的表中 。比如 A 表中的外键字段 的 属 性应 当 属 于 B 表的 属 性,而 不是 A 表,如 果 放入 ,首 先 是依 赖 于 A(B K e y ), 而 后 通 过 A (BK e y) 对 三 种 关系 (1 : 1 , 1 :N,N: M)都含有外键,都 很 可能发生 违 反 ,会 导 致 那些 问 题 属 性 插入异常 ,或者 被误 删 。 存在着上课关系 ,假设一个教师上一门课而且一门课只有一个教师 ,将教师表的主键教师编号在学科表中以外键形式存在就表达了 “联系电话 ”属性该放哪里呢?如果看到“教师编号 就将联系电话放入学科表中 ,那么联系电话首先是对表中的教师编号依赖 达到了学科编号的依赖 ,那么联系电话对学科编号的依赖就是传递 该将其从学科表中拆出来放入教师表中 ,不然的话,会发生 操 作 异 常 假设一个教师已经存在但是还没有为其分配科目 ,那么 他 的电话就 无 法存入 库 中 反范式化冗余字段使用规范反范式化冗余字段使用规范反范式化冗余字段使用规范反范式化冗余字段使用规范 系统中 在 完 成 范式化 工 作之 后 ,对 某些 表,可 以 适 当 反范式化增加冗余字段 以提 中采用的是 面向 问 题 的设计 思想 ,应 该 大量使用反范式化冗余信 关 连查询涉 及 到 4 张 表时可 考 虑 采用冗余字段。 )关系表中的冗余:在 关系表中增加 相 关实体表的 相 关 属 性 达 到 关 连查询 时 减少 表的关 联 数量的目的 (2)层次 关系中的冗余:在多层次 的 子 父 表关系 “子 表”或者“孙 子 表”或者“重 孙 表”中。 反范式化冗余字段实例 : :比如在考试关系中,原本 在学科表中的学分信息 每个学生得了多少学分 ,就可以直接从考试表得到 需 要 DBA 参 与设计。 不存 在 非主键 属 性对主键 属 性的 传递 依 赖 ; 表中的外键字段 B k e y 是 B 的 如 果 放入 A 表,则 这些 对 A(AK e y )的依 都 很 可能发生 违 反 3NF 假设一个教师上一门课而且一门课只有一个教师 将教师表的主键教师编号在学科表中以外键形式存在就表达了 教师编号 ”出 现 在了学 那么联系电话首先是对表中的教师编号依赖 ,再依据 那么联系电话对学科编号的依赖就是传递 会发生 操 作 异 常 , 那么 他 的电话就 无 法存入 库 中 。 可 以 适 当 反范式化增加冗余字段 以提 应 该 大量使用反范式化冗余信 在 关系表中增加 相 关实体表的 相 关 属 性 ,以 在多层次 的 子 父 表关系 原本 在学科表中的学分信息 ,可以冗余 添 就可以直接从考试表得到 ,而 无 需关联 学科表来得到。 (2)多 层 关系中的冗余 社区 表,它们 之间的 层次 关系是通 过 上一 级 的主键在 下 一 级 中以外键形式存在来体 现 的 是,如果需要 问 :某个设计属于哪个 国家 这 时 可以将 国家 编号以外键形式放入到 社区 表中 做 冗余 得到 答案 。一般的,每间隔一 级 增加一个冗余外键 编号放入 社区 表中。 如 何 保证冗余字段数据的 正 确 性 添加注释,说明 冗余 了 什么 ¢ 如 果 在 程 序 开发前设计的冗余字段 ¢ 如 果 是程 序 完 成 之 后 增加的冗余字段 ¢ 对于 OLAP 中大量存 在 冗余字段 2.2.2.2.7777 数据库对象命名基本规范数据库对象命名基本规范数据库对象命名基本规范数据库对象命名基本规范 2.2.2.2.7777.1.1.1.1 遵循行业规范遵循行业规范遵循行业规范遵循行业规范 当 有 相 关国 家/行业 强 制 性数据 结构 标 准 规范存 在 时 表名命名 上 原则 上 应 该 遵 从 标 准 规定 规范 上 有规定的应遵循规定 也 应 当 遵循或保证能 直接 兼 容 保存和访问 2.2.2.2.7777.2.2.2.2 简单命名原则简单命名原则简单命名原则简单命名原则 命名尽可能简单,避免 太长的命名 的含义。凡 是 需 要命名的对象其标识符 均 不能 超 过 段名,函数名,过程名,触发器名 过数据库命名长度限 制 (Oracle 第 10 页 多 层 关系中的冗余 :假设为之范 畴 从 大 到 小 有 国家 表,省份 表,城市 表 它们 之间的 层次 关系是通 过 上一 级 的主键在 下 一 级 中以外键形式存在来体 现 的 某个设计属于哪个 国家 ?这样就要关 连查询 所有的 5 张表 这 时 可以将 国家 编号以外键形式放入到 社区 表中 做 冗余 ,这样直接关联 国家 表和 社区 表 即 可 每间隔一 级 增加一个冗余外键 ,比如将 国家 编号放入 城市 表中 如 何 保证冗余字段数据的 正 确 性 (一致性)是反范式化的关键,需 要对冗余字段 详细 说明 冗余 了 什么 ,以 及 该 字段的 维护方法 ,常 用 维护方法如 下 : 如 果 在 程 序 开发前设计的冗余字段 ,可 以在 正 常 的业 务逻辑 程 序 中一 并处 理 如 果 是程 序 完 成 之 后 增加的冗余字段 ,可 以 使用触发器 维护 ; 中大量存 在 冗余字段 ,可能 需 要 使用单 独 的 处 理 任 务进 行 维护 数据库对象命名基本规范数据库对象命名基本规范数据库对象命名基本规范数据库对象命名基本规范 行业 强 制 性数据 结构 标 准 规范存 在 时 ,用于存储 某 业 务 数据的业 务 表 在 表名命名 上 原则 上 应 该 遵 从 标 准 规定 ,其表中 相 关字段的中文名 称 (即 数据项名 称 规范 上 有规定的应遵循规定 。此 外,若 标 准 规范 上 对数据项的类型、长度有规定的 也 应 当 遵循或保证能 直接 兼 容 保存和访问 。 避免 太长的命名 ,尽量使用 缩 写形式,但 是 缩 写 也 要能 够 表 达 命名 凡 是 需 要命名的对象其标识符 均 不能 超 过 30 个字符,也 即 :Oracle 触发器名 ,序 列名,视图名的长度 均 不能 超 过 30 Oracle 有 30 的限 制 )。 建议 每 个单词分段长度不要 超 过 城市 表 ,城区 表, 它们 之间的 层次 关系是通 过 上一 级 的主键在 下 一 级 中以外键形式存在来体 现 的 ,但 张表 ,性能会很 差 。 这样直接关联 国家 表和 社区 表 即 可 比如将 国家 编号放入 城市 表中 ,将 城市 需 要对冗余字段 详细 : 可 以在 正 常 的业 务逻辑 程 序 中一 并处 理 ; 使用单 独 的 处 理 任 务进 行 维护 。 用于存储 某 业 务 数据的业 务 表 在 即 数据项名 称 )若 标 准 长度有规定的 ,原则 上 但 是 缩 写 也 要能 够 表 达 命名 Oracle 中的表名、字 30 个字符,以免超 建议 每 个单词分段长度不要 超 过 6 位。 2.2.2.2.7777.3 .3 .3 .3 字符范围原则字符范围原则字符范围原则字符范围原则 数据库各 种 名 称 必 须以 字母开 头 下 划 线 “_”三类字符,“_” 数据库对象的 映射 。如 XXX_XXX_XXX 2.2.2.2.7777....4444 字母全部大写或小写原则字母全部大写或小写原则字母全部大写或小写原则字母全部大写或小写原则 所有数据库对象命名字母全部大写或 对大小写 敏感 ,统一大小写有 助 于 在多 个数据库间 移植 2.2.2.2.7777.5.5.5.5 勿用保留词原则勿用保留词原则勿用保留词原则勿用保留词原则 数据库对象命名不能 直接 使用数据库保留关键字 于表名、列名 等 ,但 是 USER_NAME 2.2.2.2.7777....6666 同义性原则同义性原则同义性原则同义性原则 对于同一含义尽量使用 相 同的单词命名 误 解 。如 TELEPNHOE 的 A 表中表 示 固 定 电话 号码 尽量 避免 同一单词表 示多 种 含义的 情况 2.2.2.2.7777....7777 富有含义原则富有含义原则富有含义原则富有含义原则 命名尽量采用富有 意 义的 英 文词 汇 2.72.72.72.7....8888 扩展性原则扩展性原则扩展性原则扩展性原则 各系统或者项目 在 遵循本规范的基 础 上 可 以 根 据 需 要 制 定 更 明 确 的规范 细 则 项目 管 理 需 要。如 对模 块进 行统一命名 建立数据字 典 ,管 理命名中使用的 英 文单词 管 理。 第 11 页 字符范围原则字符范围原则字符范围原则字符范围原则 数据库各 种 名 称 必 须以 字母开 头 ,但 严 禁使用 S Y S 开 头 ;名 称只 能含有字母 ”用于间 隔 名 称 中的各语义字段,以 便 阅读同时 方 便某些 工 具对 XXX_XXX_XXX,但 不限于三段式。 字母全部大写或小写原则字母全部大写或小写原则字母全部大写或小写原则字母全部大写或小写原则 所有数据库对象命名字母全部大写或 小写。Oracle 对大小写不 敏感 , 统一大小写有 助 于 在多 个数据库间 移植 。 勿用保留词原则勿用保留词原则勿用保留词原则勿用保留词原则 数据库对象命名不能 直接 使用数据库保留关键字 ,但 分段中可 以 使用 。 USER_NAME 可 以 用于列名,USER_INFO 也 可 以 用于表名 对于同一含义尽量使用 相 同的单词命名 ,不 管 使用 英 文单词 还 是 英 文 缩 写 表中表 示 固 定 电话 号码 ,在 B 表中 就 不应 该 用于表 示 移动电话 号码 尽量 避免 同一单词表 示多 种 含义的 情况 。 命名尽量采用富有 意 义的 英 文词 汇 ,不 准 采用 汉 语 拼音 。 各系统或者项目 在 遵循本规范的基 础 上 可 以 根 据 需 要 制 定 更 明 确 的规范 细 则 如 对模 块进 行统一命名 ,然后 用于表名的前 缀 。建议 每 个系统 在 启动 开发时 管 理命名中使用的 英 文单词 、英 文单词 缩 写 等 ,对用于命名的单词 进 行统一 名 称只 能含有字母 ,数字和 以 便 阅读同时 方 便某些 工 具对 ,但 是有 些 数据库 。如 U SER 不能用 也 可 以 用于表名 。 不 管 使用 英 文单词 还 是 英 文 缩 写 ,以免 引 起 表中 就 不应 该 用于表 示 移动电话 号码 。 各系统或者项目 在 遵循本规范的基 础 上 可 以 根 据 需 要 制 定 更 明 确 的规范 细 则 ,以 满足 建议 每 个系统 在 启动 开发时 对用于命名的单词 进 行统一 3333 表的设计规范表的设计规范表的设计规范表的设计规范 3.3.3.3.1111 命名规范命名规范命名规范命名规范 3.13.13.13.1.1 .1 .1 .1 表的命名规范表的命名规范表的命名规范表的命名规范 命名规则命名规则命名规则命名规则::::3 位类 别码 _ 类别码类别码类别码类别码 :一 般 表 TBL、临 时表 接 口 表 INT,一 般 表的 3 位类 别码 可 以 省略 模块名模块名模块名模块名 :模 块 名 代 表 子 系统 SLS;基 础 数据:T Y P。 表名表名表名表名 :表名应 该 简 洁明 了 个字符,则 从 最 后 一个单词开 始 采用 该 单词前 4 个字母来表 示 如:使用 FACTOR Y 而 非 FACTORI 附加码附加码附加码附加码 :为可 选 项,各系统 根 据实际 情况 自 行 编码 数据存 放 日 期 YYMMDD。 3.13.13.13.1.2 .2 .2 .2 字段的命名规范字段的命名规范字段的命名规范字段的命名规范 命名规则命名规则命名规则命名规则::::英 文单词之间用 下 划 线 连结 写字符。 ¢ 字段用来存储 s e q u e n ce ¢ 字段用来存储 号码 ¢ 字段用来存储 日 期 ¢ 字段用来存储数量 ¢ 字段用来存储 金额 ¢ 字段用来存储名 称 第 12 页 表的设计规范表的设计规范表的设计规范表的设计规范 表的命名规范表的命名规范表的命名规范表的命名规范 _模 块 名 _表名 _附 加 码 ,采用大写字符。 临 时表 TMP、中间表 C VT、删 除表 DEL、历史 表 HIS 位类 别码 可 以 省略 ,其他类型表的类 别码 必 填 。 模 块 名 代 表 子 系统 (或者 子 模 块 )的名 称 ,如:保单 相 关表 表名应 该 简 洁明 了 ,尽量使用 完 整 的单词,如 果 导 致 拼 上 表名 后 则 从 最 后 一个单词开 始 ,依 次向 前采用 该 单词的 缩 写。( 如 果 没 有约定的 缩 写 个字母来表 示 )。 另 外,表名中的名词单词都应使用单数形式 FACTORI ES。 各系统 根 据实际 情况 自 行 编码 ,如:可 以 用 以 标 记 临 时表的生 成 及 字段的命名规范字段的命名规范字段的命名规范字段的命名规范 英 文单词之间用 下 划 线 连结 ,且每 个单词 皆 为单数.例:us er _ n a m e s e q u e n ce 序 列,命名 以 id 结 尾 。例:b ar _ c od e _ id 字段用来存储 号码 ,命名 以 no 结 尾 。例:po l i c y _ no 。 字段用来存储 日 期 ,命名 以 d a t e 结 尾 。例:crea t e _ d a t e。 字段用来存储数量 ,命名 以 num 结 尾 。例:insu re d _ num 。 段用来存储 金额 ,命名 以 a mt 结 尾 。例:p re m _ a mt 。 字段用来存储名 称 ,命名 以 n a m e 结 尾 。例:cl i e nt _ n a m e。 HIS、配 置 表 CFG, 保单 相 关表 PLC ;订 单 相 关 如 果 导 致 拼 上 表名 后 ,长度 超 过 30 如 果 没 有约定的 缩 写 ,则 表名中的名词单词都应使用单数形式 ,以免 混淆 , 可 以 用 以 标 记 临 时表的生 成 及 us er _ n a m e ,采用小 b ar _ c od e _ id 。 ¢ 字段用来存储 描述 信息 ¢ 字段用来存储基 础 表的 ¢ 字段用来存储标 志 信息 ¢ 字段用来存储 英 文名 称 和 英 文 描述 3.23.23.23.2 表的设计规范表的设计规范表的设计规范表的设计规范 3.23.23.23.2.1 .1 .1 .1 指定表空间规范指定表空间规范指定表空间规范指定表空间规范 每 个表 在 创 建时 候 ,必 须 指定所 在 的表空间 立 在 syst e m 空间 上 ,导 致性能问 题 对于 事务比较 繁 忙 的数据表 3.23.23.23.2....2222 主键设计规范主键设计规范主键设计规范主键设计规范 表的主键设计,应 该 遵循 如 下 三项 ¢ 有 无 原则 除 临 时表和外部表,以 及 流 水 表 的唯一标识,保证主键不可 随意更新 修 改 会为 你 的程 序以 及 将 来 查 找 数据中的 错 误 等等 ¢ 构成 原则 主键禁止使用 含有实际语义的列 取 值 来 自序 列 s e q u e n ce,禁止使用 ¢ 创 建原则 对于 500 万 以上 的表 , 对于实体表,主键 就 是一列 键 就 是 相 关实体表主键形 成 的复合主键 3.2.3.2.3.2.3.2.3333 外键外键外键外键设计设计设计设计 规范规范规范规范 一个表 的 某 列与 另 一表有关 联 关系的时 候 第 13 页 字段用来存储 描述 信息 ,命名 以 d e s c 结 尾 。例:b a nk _ d e s c。 字段用来存储基 础 表的 c od e 信息,命名 以 c od e 结 尾 。例:re gion _ c od e 字段用来存储标 志 信息 ,命名 以 f la g 结 尾 。例:und er w r it _f la g 字段用来存储 英 文名 称 和 英 文 描述 ,命名 以 e n 结 尾 。例:a dd re ss _ e n 表的设计规范表的设计规范表的设计规范表的设计规范 指定表空间规范指定表空间规范指定表空间规范指定表空间规范 ,,,,禁止采用默认表空间禁止采用默认表空间禁止采用默认表空间禁止采用默认表空间的形式的形式的形式的形式 必 须 指定所 在 的表空间 ,禁止采用默认表空间 的形式 导 致性能问 题 。 对于 事务比较 繁 忙 的数据表 ,必 须 存 放在该 表 专 用空间中。 主键设计规范主键设计规范主键设计规范主键设计规范 ,,,,禁止使用含有实际语义的列禁止使用含有实际语义的列禁止使用含有实际语义的列禁止使用含有实际语义的列 作为主键作为主键作为主键作为主键 应 该 遵循 如 下 三项 原则: 以 及 流 水 表 ,日志 表外,其他表都要建立主键 。 保证主键不可 随意更新 修 改 ,在 不 知道 是否 需 要主键的时 候 , 会为 你 的程 序以 及 将 来 查 找 数据中的 错 误 等等 ,提 供 一定的 帮助 。 含有实际语义的列 ,应 该 增加一个 xx _ id 字段 做 主键,类型为 禁止使用 GUID 或键表的 方 式。 ,采用 先 建唯一索引 再 添加主键约束的 方 式来 创 建主键 主键 就 是一列 ,就 是 没 有 任何 语义的 自 增的 N U MBER 列; 键 就 是 相 关实体表主键形 成 的复合主键 ,是 多 列。 规范规范规范规范 ,,,,禁止禁止禁止禁止仅仅仅仅依靠依靠依靠依靠主外键主外键主外键主外键关系关系关系关系 来保证数据一致性来保证数据一致性来保证数据一致性来保证数据一致性 的 某 列与 另 一表有关 联 关系的时 候 ,如 果 加 得 上 的 话 ,请 加 上 外键约束 re gion _ c od e 。 und er w r it _f la g 。 a dd re ss _ e n 。 的形式 ,以 防 止表建 作为主键作为主键作为主键作为主键 。主键是 每 行数据 ,请 加 上 主键,它 类型为 numb er, 采用 先 建唯一索引 再 添加主键约束的 方 式来 创 建主键 。 ;对于关系表,主 来保证数据一致性来保证数据一致性来保证数据一致性来保证数据一致性 请 加 上 外键约束 ,但 要 禁止仅 依靠设计数据库表之间的主外 外键是 很 重要的,所 以 要 特 别 强 调 ¢ 适量建立外键 为 了 保证外键的一致性 无 法 满足 用 户 需 求 的证据 , ¢ 不要 以 数据 操 作不 方 便 为理 由而 不建外键 是的,加 上 外键 以 后 ,一 些 数据 操 作 变 得 有 些 麻烦 正 是 因 为 这种 保 护很 有 效 , ¢ 以 缺 省 的 方 式建立外键 以 缺 省 的 方 式建立外键 ( 外键 在 保 护 数据一致 方面 非 常 有 效 知晓 。当 数据量 很 大的时 候 , 往 没 有 考 虑 或者 也 无 法 照 顾到垃圾 数据 且 表 现出 来的 现 象会 很 奇怪 3.2.3.2.3.2.3.2.4444 字段字段字段字段能否能否能否能否为为为为空空空空 的设计的设计的设计的设计 对于字段能否为 nu ll , 键外,表中其他 属 性都允许为空的设计 由 于 nu ll 值在参 加 任何 计 算 时 能为 nu ll 值 的字段或 变 量转换非 3.2.3.2.3.2.3.2.5555 关于冗余列的规范关于冗余列的规范关于冗余列的规范关于冗余列的规范 除非必要,否则尽量不加冗余列 所 谓 冗余列,是指能 通 过其他列计 算出 来的列 者是 从 其他表复 制 过来的列 等等 候,冗余列 也需 要 随 之 修 改 , 况。 第 14 页 依靠设计数据库表之间的主外 键关系来保证数据一致性。 所 以 要 特 别 强 调 。表的外键设计,应 该 遵循 如 下 三项 为 了 保证外键的一致性 ,数据库会增加一 些 开 销 ,如 果 有 确 凿 的 并且 是对性能 影响到 ,可 以考 虑 不建外键。否则,还 是应 该 建外键。 不要 以 数据 操 作不 方 便 为理 由而 不建外键 一 些 数据 操 作 变 得 有 些 麻烦 ,但 是 这正 是对数据一致性的保 护 ,所 以最好 不要 拒绝它 。 以 缺 省 的 方 式建立外键 以 缺 省 的 方 式建立外键 (即 用 d ele t e re st r i c t 方 式), 以达 到 保 护 数据一 外键 在 保 护 数据一致 方面 非 常 有 效 。如 果 不建外键,数据库中 容 易 出现 垃圾 数据 ,查 找这些垃圾 数据 也 是 相当 困难 的。而 应用程 序在 设计时 往 没 有 考 虑 或者 也 无 法 照 顾到垃圾 数据 。因此垃圾 数据 很 可能 造 成 应用程 序工 作不 正 常 且 表 现出 来的 现 象会 很 奇怪 ,让 人 摸 不 着 头 脑 。 空空空空的的的的规范规范规范规范,,,,禁止禁止禁止禁止““““主键外主键外主键外主键外的的的的其他字段其他字段其他字段其他字段 都允许为空都允许为空都允许为空都允许为空 ,应 该在 s q l 建表 脚 本中 明 确 指定,不应 该 使用缺 省 表中其他 属 性都允许为空的设计 。 任何 计 算 时 ,结 果 均 为 nu ll,所 以在 程 序 中必 须 用 值 的字段或 变 量转换非 nu ll 的默认 值 。 关于冗余列的规范关于冗余列的规范关于冗余列的规范关于冗余列的规范 ,,,,除非必要除非必要除非必要除非必要,,,,否则尽量不加冗余列否则尽量不加冗余列否则尽量不加冗余列否则尽量不加冗余列 否则尽量不加冗余列 。 是指能 通 过其他列计 算出 来的列 ,或者是与 某 列表 达 同一含义的列 者是 从 其他表复 制 过来的列 等等 。冗余列 需 要应用程 序 来 维护 一致性,相 关列的 值改变 的时 ,而 这 一规则 未 必所有 人 都 知道 ,就 有可能 因此 发生不一致的 情 应 该 遵循 如 下 三项 原则: 如 果 有 确 凿 的 并且 是对性能 影响到 但 是 这正 是对数据一致性的保 护 。 以达 到 保 护 数据一 致性的目的 ; 数据库中 容 易 出现 垃圾 数据 ,并且 无 人 而 应用程 序在 设计时 ,往 因此垃圾 数据 很 可能 造 成 应用程 序工 作不 正 常 ,并 都允许为空都允许为空都允许为空都允许为空 ”””” 不应 该 使用缺 省 。禁止除主 所 以在 程 序 中必 须 用 nv l () 函数 把 可 否则尽量不加冗余列否则尽量不加冗余列否则尽量不加冗余列否则尽量不加冗余列 或者是与 某 列表 达 同一含义的列 ,或 相 关列的 值改变 的时 就 有可能 因此 发生不一致的 情 如 果 是应用的 特 殊 需 要 列。 3.23.23.23.2....6666 使用注释的规范使用注释的规范使用注释的规范使用注释的规范 每 个表,每 个字段都要有注释 外键字段 说明 参 照与 那 个表 查询 字 典 表 us er _ t a b _ c omm e nts 对表 添 加 注释 : SQL> c omm e nt on t a b le is 'xx'; 对字段 添 加 注释 : SQL> c omm e nt on c o l umn . < c o l _n a m 3.23.23.23.2....7777 一个表所含数据量的规范一个表所含数据量的规范一个表所含数据量的规范一个表所含数据量的规范 一个非分区表中的数据量不要 超 过 需 设计 成 分区表 ;如 果 该 表数据量 超 过 在 系统 上 线 前,通 过对业 务 分 析 的 日志 ,T op 性能 S QL,c ount( 1 ) 将这 些 表进行分 区 ,具 体方法 请参 看分 区 表的设计规范 记录 数 超 过 两亿 条 的表一定要 考 虑 信息生命 周 期 用设计中 完 成 对 历史 数据 的 相 应 处 理 功 能 3.23.23.23.2....8888 增量同步表的设计规范增量同步表的设计规范增量同步表的设计规范增量同步表的设计规范 字 典 信息表和 需 要使用增量同步的表必 须 增加 如 下属 性 属性名属性名属性名属性名 类型类型类型类型 S t a tus C h ar ( 1 ) Crea t e _ tim e D a t e Upd a t e _ tim e D a t e 第 15 页 殊 需 要 ,或者是为 了 优化 某些 逻辑很 复 杂 的 查询等 操 作 使用注释的规范使用注释的规范使用注释的规范使用注释的规范 ,,,,禁止不对字段添加注释禁止不对字段添加注释禁止不对字段添加注释禁止不对字段添加注释 每 个字段都要有注释 ,说明 其含义,对于冗余字段 还 要 特 别 说明 其 维护方法 外键字段 说明 参 照与 那 个表 。原则 上 谁 设计 谁 注释。 us er _ t a b _ c omm e nts 和 us er _ c o l _ c omm e nts 可 知道 表和字段的注释信息 SQL> c omm e nt on t a b le is 'xx'; SQL> c omm e nt on c o l umn . < c o l _n a m e > is 'xx'; 一个表所含数据量的规范一个表所含数据量的规范一个表所含数据量的规范一个表所含数据量的规范 ,,,,合理设计分区表合理设计分区表合理设计分区表合理设计分区表 一个非分区表中的数据量不要 超 过 500 万。当 一个非分区表中的数据量 超 过 如 果 该 表数据量 超 过 5000 万,请 DBA 参 与设计。 通 过对业 务 分 析 ,判断 一个表的数据量 ;在 系统 上 线后 c ount( 1 ) 来发 现 数据量大的表。 具 体方法 请参 看分 区 表的设计规范 。 记录 数 超 过 两亿 条 的表一定要 考 虑 信息生命 周 期 ,必 须考 虑历史 数据的 剥 离 的 相 应 处 理 功 能 (历史 数据的 剥 离 规则 须经 业 务 使用部 门 的 确 认 增量同步表的设计规范增量同步表的设计规范增量同步表的设计规范增量同步表的设计规范 ,,,,标识标识标识标识和时间戳不可或缺和时间戳不可或缺和时间戳不可或缺和时间戳不可或缺 字 典 信息表和 需 要使用增量同步的表必 须 增加 如 下属 性 。 取值取值取值取值 说明说明说明说明 Y/N:Y 为 激活 N 为 作 废 ,默认为 Y 标识 该 行是否使用。用于 软删 除 需将 主键和唯一约束列添加 随机 数 后缀 默认为 sysd a t e 创 建时间 默认为 sysd a t e 最 后修 改 时间 操 作 ,可 以 加冗余 对于冗余字段 还 要 特 别 说明 其 维护方法 , 可 知道 表和字段的注释信息 。 当 一个非分区表中的数据量 超 过 500 万 时, 在 系统 上 线后 ,可 以通 过 e xp 必 须考 虑历史 数据的 剥 离 ,并在 应 历史 数据的 剥 离 规则 须经 业 务 使用部 门 的 确 认 )。 用于 软删 除 ,软删 除 需将 主键和唯一约束列添加 随机 数 后缀 。 3.2.3.2.3.2.3.2.9999 字段字段字段字段类型类型类型类型、、、、宽度宽度宽度宽度 字段的宽度要 在 一定时间 内 足 够 用 确 定的列,采用可 变 长度的数据类型 如 字段的类型及宽度 在 设计 以 及 后 面进 行开发时 以 得到双 方 认可的类型及宽度 一个表中的所有字段 , 长度 < db _ b l o c k(减去 p c t f ree ) 请 D BA 团 队参 与设计。 查询 字 典 表 USER_TAB_COLU 依据 db _ b l o c k 和表的 p c t f ree 中。 对表添 如 果 所有字段的总长度 超出了 一个数据 块 多 个)表,拆 分的依据是字段的频繁使用程度 使用的字段 放在 另 一个表中 表所含字段访问频繁度的规范 3.2.3.2.3.2.3.2.10101010 一个表所含字段访问频繁度的规范一个表所含字段访问频繁度的规范一个表所含字段访问频繁度的规范一个表所含字段访问频繁度的规范 一个表中的各字段的访问频繁度应 DBA 参 与审 核 。 如 果 一个表的字段数过 多超 过 问,另 一 些 字段则 很少 被 访问 取 很少 被 访问的信息,可 以提 高 将 访问频繁度 相 差 太 远 的字段 拆 分 到两 个表中 存 很少 被 访问的字段。 3.2.3.2.3.2.3.2.11111111 大对象字段大对象字段大对象字段大对象字段 (((( 存储图 片 ,视频,音 频 , 不能和其他字段存储 在 一个表中 第 16 页 宽度宽度宽度宽度 及及及及一个表所含字段总长度的规范一个表所含字段总长度的规范一个表所含字段总长度的规范一个表所含字段总长度的规范 字段的宽度要 在 一定时间 内 足 够 用 ,但 也 不要过宽,占 用过 多 的存储空间 采用可 变 长度的数据类型 如 v arc h ar2 类型 ; 字段的类型及宽度 在 设计 以 及 后 面进 行开发时 ,往往 要与应用的设计 、 以 得到双 方 认可的类型及宽度 。 ,应 当 能存储 在 一个数据 块 中(BLOC K), 也 即 : p c t f ree )。对不含有大对象数据类型字段的表,字段数大于 USER_TAB_COLU MNS 中的字段 DATA_LENGTH 得到 表中所有字段的总长度 p c t f ree 参 数可 以判断 是否一个数据行可 以 存储 在 一个数据 块 对表添 如 果 所有字段的总长度 超出了 一个数据 块 ,那么 需 要 将该 表 拆 分 成 两 个 拆 分的依据是字段的频繁使用程度 ,也 就 是频繁使用的字段 在 一个表中 使用的字段 放在 另 一个表中 ,他 们 之间使用 相 同的主键 值 ,用主外键关 联 。 表所含字段访问频繁度的规范 ”。 一个表所含字段访问频繁度的规范一个表所含字段访问频繁度的规范一个表所含字段访问频繁度的规范一个表所含字段访问频繁度的规范 一个表中的各字段的访问频繁度应 该 基本一致,如 果 一个表的字段数 超 过 如 果 一个表的字段数过 多超 过 50 个,并且 依据业 务逻辑确 定 该 表中一 些 字段频繁 被 访 另 一 些 字段则 很少 被 访问 ,则 该 表 需 要 做 拆 分 处 理,这样 可 以避免 读 取 频繁信息时 多 读 可 以提 高 IO 性能,减少内 存 耗费 ,这 在 OLAP 系统中 比较常 见 将 访问频繁度 相 差 太 远 的字段 拆 分 到两 个表中 ,一个表存频繁访问的字段 ((((BLOBBLOBBLOBBLOB,,,,CLOBCLOBCLOBCLOB))))使用规范使用规范使用规范使用规范 ,文 件 ,500 字 节以上 文本 等 占 用太 多 空间的字段 ( 不能和其他字段存储 在 一个表中 。含有大对象(BLOB,CLOB)字段的表设计和存储 请 占 用过 多 的存储空间 ,对于长度不 、开发 人员 商 讨 , :表的单行字段总 字段数大于 50 个的, 得到 表中所有字段的总长度 ,再 参 数可 以判断 是否一个数据行可 以 存储 在 一个数据 块 (BLOCK) 那么 需 要 将该 表 拆 分 成 两 个 (甚至 也 就 是频繁使用的字段 在 一个表中 ,很少 被 。这 点 就 是“一个 如 果 一个表的字段数 超 过 50 个, 请 并且 依据业 务逻辑确 定 该 表中一 些 字段频繁 被 访 这样 可 以避免 读 取 频繁信息时 多 读 系统中 比较常 见 。 一个表存频繁访问的字段 ,另 一个表 (大对象字段), 字段的表设计和存储 请 DBA 参 与设计。一 般 有 两种 方法 ¢ 数据库存储 可 以 重 新 建一个表 专门 存储 该 大对象字段 ID 为主键,一个为大对象 内容 本 身 单 独 的表空间中。 ¢ 操 作系统存储 将 这些 文 件 存储 在操 作系统空间中 如 果 该 大对象字段 常 被修 改 本不 变 ,那么 可 以 采用 方法 二 易丢 失 。 3.33.33.33.3 字段类型规范字段类型规范字段类型规范字段类型规范 3.33.33.33.3.1 .1 .1 .1 禁止禁止禁止禁止 使用会发生隐式转换使用会发生隐式转换使用会发生隐式转换使用会发生隐式转换 INTEGER 改 为 NUMBER(n) FLOAT 改 为 NUMBER(p , s) 3.33.33.33.3.2 .2 .2 .2 禁止使用禁止使用禁止使用禁止使用 过时老类型过时老类型过时老类型过时老类型  非标 准 :V ARCHAR2 (n CHAR ) V ARCHAR2 (n CHAR ) 改 为 CHAR(n CHAR )改 为 CHAR(n) 3.33.33.33.3....3333 禁止禁止禁止禁止 使用大对象使用大对象使用大对象使用大对象  不能使用大对象:BLOB CLOB 和 NCLOB 改 为 V ARCHAR2 3.33.33.33.3....4444 禁止禁止禁止禁止 使用高精度使用高精度使用高精度使用高精度  不能使用高精度:TIMESTAMP 第 17 页 一 般 有 两种 方法 : 可 以 重 新 建一个表 专门 存储 该 大对象字段 ,该 表基本为 两 个字段,一个为大对象 编号 一个为大对象 内容 本 身 ,并将该 主键 在 原表中作外键关 联 ,该 大对象表存储 在 将 这些 文 件 存储 在操 作系统空间中 ,大对象字段存储 该 文 件 的全 路径 名 如 果 该 大对象字段 常 被修 改 ,那么 采用 方法 一 ;如 果 该 大对象信息为 静态 那么 可 以 采用 方法 二 ,它 有一 个致命缺点 就 是信息存储 在 数据库外部 字段类型规范字段类型规范字段类型规范字段类型规范 使用会发生隐式转换使用会发生隐式转换使用会发生隐式转换使用会发生隐式转换 的类型的类型的类型的类型::::INTEGERINTEGERINTEGERINTEGER,,,,FLOATFLOATFLOATFLOAT NUMBER(n) NUMBER(p , s) 过时老类型过时老类型过时老类型过时老类型 ::::RAWRAWRAWRAW,,,,LONGLONGLONGLONG,,,,LONG RAWLONG RAWLONG RAWLONG RAW V ARCHAR2 (n CHAR )、CHAR(n CHAR ) 改 为 V ARCHAR2 (n) CHAR(n) 使用大对象使用大对象使用大对象使用大对象 ::::BLOBBLOBBLOBBLOB,,,,CLOCLOCLOCLOBBBB,,,,NCLOBNCLOBNCLOBNCLOB BLOB,CLOB,NCLOB V ARCHAR2 。 使用高精度使用高精度使用高精度使用高精度 ::::TIMESTAMPTIMESTAMPTIMESTAMPTIMESTAMP TIMESTAMP 一个为大对象 编号 该 大对象表存储 在 大对象字段存储 该 文 件 的全 路径 名 。 如 果 该 大对象信息为 静态 ,加 载后 基 个致命缺点 就 是信息存储 在 数据库外部 ,不 安 全,容 FLOATFLOATFLOATFLOAT TIMESTAMP 改 为 DATE。 3.33.33.33.3....5555 禁止使用禁止使用禁止使用禁止使用 CHARCHARCHARCHAR 一 般情况下 ,禁止使用 充 空 格 ,后续 处 理不 便 ,应 该 使用 4444 分区表分区表分区表分区表设计规范设计规范设计规范设计规范 4.14.14.14.1 表空间及分区表的概念表空间及分区表的概念表空间及分区表的概念表空间及分区表的概念 4.1.1 4.1.1 4.1.1 4.1.1 表空间表空间表空间表空间 是一个或 多 个数据文 件 的集合 的是表,所 以 称 作表空间 。 4.1.2 4.1.2 4.1.2 4.1.2 分区表分区表分区表分区表 当 表中的数据量不 断 增大 就 应 该考 虑 对表 进 行分区。表 进 行分区 后 据 在 物 理 上 存 放 到 多 个“表空间 表 而 只 是 从 当 前的分区 查 到 所要的数据 4444.2.2.2.2 表分区的具体作用表分区的具体作用表分区的具体作用表分区的具体作用 Oracle 的表分区 功 能 通 过 改 善 可 管 理性 极 大的 好处 。通常 ,分区可 以 使 某些 查询以 及 维护操 作的性能大大 提 高 极 大简化 常 见 的 管 理 任 务 ,分区是 构 建 千兆 字 节 数据系统或 超 高可用性系统的关键 工 具 区 功 能能 够将 表、索引或索引组 织 表 进 一步 细 分为段 分区有 自 己 的名 称 ,还 可 以选择自 己 的存储 特 性 的对象具有 多 个段,这些 段 既 可 进 行集体 管 理 第 18 页 。 CHARCHARCHARCHAR 类型类型类型类型 禁止使用 CHAR 类型,CHAR 字段类型长度小于 100,会 自 动 在 字符 后 面 补 应 该 使用 V ARCHAR2 字段类型来存储。 设计规范设计规范设计规范设计规范 表空间及分区表的概念表空间及分区表的概念表空间及分区表的概念表空间及分区表的概念 是一个或 多 个数据文 件 的集合 ,所有的数据对象都存 放在 指定的表空间中 。 当 表中的数据量不 断 增大 ,查询 数据的 速 度 就 会 变 慢 ,应用程 序 的性能 就 会 下 降 表 进 行分区 后 ,逻辑上 表 仍然 是一 张完 整 的表 , 表空间 ”(物 理文 件上),这样 查询 数据时,不 至 于 每次 都 扫描 整 张 表 而 只 是 从 当 前的分区 查 到 所要的数据 提 高 了 数据 查询 的 速 度。 表分区的具体作用表分区的具体作用表分区的具体作用表分区的具体作用 的表分区 功 能 通 过 改 善 可 管 理性 、性能和可用性,从 而 为各式应用程 序 带 来 了 分区可 以 使 某些 查询以 及 维护操 作的性能大大 提 高 。此 外 分区是 构 建 千兆 字 节 数据系统或 超 高可用性系统的关键 工 具 索引或索引组 织 表 进 一步 细 分为段 ,这些 数据库对象的段 叫 做 分区 还 可 以选择自 己 的存储 特 性 。从 数据库 管 理 员 的 角 度来 看 这些 段 既 可 进 行集体 管 理 ,也 可单 独 管 理,这就 使数据库 管 理 员在管 理 会 自 动 在 字符 后 面 补 所有的数据对象都存 放在 指定的表空间中 ,但 主要存 放 应用程 序 的性能 就 会 下 降 ,这 时 ,只 是 将 表中的数 不 至 于 每次 都 扫描 整 张 从 而 为各式应用程 序 带 来 了 此 外 ,分区 还 可 以 分区是 构 建 千兆 字 节 数据系统或 超 高可用性系统的关键 工 具 。 分 这些 数据库对象的段 叫 做 分区 。每 个 从 数据库 管 理 员 的 角 度来 看 ,一个分区 后 这就 使数据库 管 理 员在管 理 分区 后 的对象时有 相当 大的 灵活 性 完 全 相 同,使用 S Q L D ML 命 令 访问分区 后 什么 时 候 使用分区表 : 1、表的大小 超 过 2GB , 2、表中包含 历史 数据 , 4.34.34.34.3 表分区的优缺点表分区的优缺点表分区的优缺点表分区的优缺点 表分区有 以 下 优点: ¢ 改 善 查询 性能:对分区对象的 查询 可 以 仅 搜 索 自 己 关 心 的分区 ¢ 增 强 可用性:如 果 表的 某 个分区 出现 故障 ¢ 维护方 便 :如 果 表的 某 个分区 出现 故障 ¢ 均 衡 I/O:可 以 把 不同的分区 映射到磁盘 以 平 衡 缺点: ¢ 已 经 存 在 的表 没 有 方法 可 以 表的 功 能。 4.4 4.4 4.4 4.4 分区表设计规范分区表设计规范分区表设计规范分区表设计规范 4.2.14.2.14.2.14.2.1 不不不不使用使用使用使用 全局索引全局索引全局索引全局索引 在 分区表中不要 使用全局索引 维护 。另 外,需 要 UPDATE 的字段 4.2.2 4.2.2 4.2.2 4.2.2 RANGERANGERANGERANGE 分区的规范分区的规范分区的规范分区的规范 大数据量的表 需进 行分区化 据量 超 过 5000 万,请 DBA 参 与设计 SQL 常 依据 某 列的范围访问表 进 行 查询 ,则使用时间字段作为分区关键字 进 行 将 对表的 多 种 访问 结 合 考 虑 来 确 定分区的 细 度 第 19 页 分区 后 的对象时有 相当 大的 灵活 性 。但 是,从 应用程 序 的 角 度来 看 ,分区 后 的表与非分区表 命 令 访问分区 后 的表时,无 需 任何修 改 。 : ,数据量 超 过 500 万; ,新 的数据 被 增加 到 新 的分区中。 表分区的优缺点表分区的优缺点表分区的优缺点表分区的优缺点 对分区对象的 查询 可 以 仅 搜 索 自 己 关 心 的分区 ,提 高 检 索 速 度 如 果 表的 某 个分区 出现 故障 ,表 在 其他分区的数据 仍然 可用 如 果 表的 某 个分区 出现 故障 ,需 要 修 复数据,只修 复 该 分区 即 可 可 以 把 不同的分区 映射到磁盘 以 平 衡 I/O,改 善 整 个系统性能 已 经 存 在 的表 没 有 方法 可 以 直接 转化为分区表。不过, Oracle 提 供 了在 线 重定义 分区表设计规范分区表设计规范分区表设计规范分区表设计规范 全局索引全局索引全局索引全局索引 ,,,,及及及及禁止将需禁止将需禁止将需禁止将需 UPDATEUPDATEUPDATEUPDATE 字段设为分区条件字段设为分区条件字段设为分区条件字段设为分区条件 使用全局索引 ,因 为 t r un ca t e 分区时会 导 致全局索引 失效 的字段 ,不 得 设计为分区 条件 字段。 分区的规范分区的规范分区的规范分区的规范 大数据量的表 需进 行分区化 ,当 表的数据量 超 过 500 万,需 设计 成 分区表 参 与设计 。 常 依据 某 列的范围访问表 ,则对表使用 RNAGE 分区。常 见情况 是 SQL 则使用时间字段作为分区关键字 进 行 RANGE 分区 ; 将 对表的 多 种 访问 结 合 考 虑 来 确 定分区的 细 度 : 分区 后 的表与非分区表 提 高 检 索 速 度 。 表 在 其他分区的数据 仍然 可用 ; 只修 复 该 分区 即 可 ; 改 善 整 个系统性能 。 提 供 了在 线 重定义 字段设为分区条件字段设为分区条件字段设为分区条件字段设为分区条件 分区时会 导 致全局索引 失效 ,造 成 难 以 需 设计 成 分区表 ,当 表的数 SQL 根 据时间范围 ¢ 大 多 数 S QL 操 作的分区关键字 值 的范围 ¢ 数据 维护 的 需 要 , ¢ 数据访问的性能 , 还 需 要 进 行 细 分 ; ¢ 一个分区的数据量要小于 分区 10 万 数据量的 情况 比每 个分区 掌握 。 1. 当 各个分 区 中的数据能 均 等 划 分 时 性能 最好 或者 将 大 数据 量 的分 区 再进行 2. 各分 区采 用各 自 的表 空 间存储 区 的表 空 间 位置 ; 3. 分 区 表的 索引采 用 本地索引 (比如 删除 1 年 前的数据 效,而 本地索引 不会 失效 4.2.4.2.4.2.4.2.3333 LISTLISTLISTLIST 分区的规范分区的规范分区的规范分区的规范 大数据量的表 需进 行分区化 据量 超 过 5000 万,请 DBA 参 SQL 常 居 于 某 列的 散 列 值 访问表 键字 ;常 见情况针 对 某 个 地 区或者 某 个业 务进 行数据访问 号 作为分区关键字。 将 对表的 多 种 访问 结 合 考 虑 来 确 定分区的 细 度 ¢ 一 般 使用一个分区关键字的 值 来 划 定一个分区 ¢ 可 以 把 分区关键字的 值相 对应数据 比较少 的 几 个分区合 并 作一个分区 ¢ 如 果 一个分区关键字 值 所对应的数据量过大 RANGE 分区,对 该值 的分区 再 采用 区的表,肯 定 可 以 转化 成 ¢ 一个分区的数据量要小于 分区 10 万 数据量分区 方法比每 个分区 第 20 页 操 作的分区关键字 值 的范围 ; ,比如以 月 为单位 删 除 历史 数据 ; ,以操 作范围 确 定的分区数据量 还 是过大,比如 大于 ; 一个分区的数据量要小于 500 万,这 是一个 硬 性的 尺 度,但从 技术上 来 看 万 数据量的 情况 比每 个分区 20 万 数据量的 情况 要 快 很多 当 各个分 区 中的数据能 均 等 划 分 时 性能 最好 ,如果相 差太大 ,则考 虑采 用其 它 分 区 或者 将 大 数据 量 的分 区 再进行 HASH 子分 区 ; 各分 区采 用各 自 的表 空 间存储 ,使 用 us er _t a b_p ar titions 字典来 查 看确定每个分 分 区 表的 索引采 用 本地索引 ,因 为常会 根 据分 区 关键字(比如 时 间 年 前的数据 ,也就是 删除 1 年 前的分 区 ), 分 区维护时全局索引 会 失 而 本地索引 不会 失效 ,这能 保证访问 表 时索引 正常可用。 分区的规范分区的规范分区的规范分区的规范 大数据量的表 需进 行分区化 ,当 表的数据量 超 过 500 万,需 设计 成 分区表 参与设计。 常 居 于 某 列的 散 列 值 访问表 ,则对表使用 LIST 分区,LIST 分区不 支持 多 列分区关 常 见情况针 对 某 个 地 区或者 某 个业 务进 行数据访问 ,那么就 使用 地 区 编号 或者业 务编 将 对表的 多 种 访问 结 合 考 虑 来 确 定分区的 细 度 : 一 般 使用一个分区关键字的 值 来 划 定一个分区 ; 可 以 把 分区关键字的 值相 对应数据 比较少 的 几 个分区合 并 作一个分区 如 果 一个分区关键字 值 所对应的数据量过大 ,比如 大于 500 万, 对 该值 的分区 再 采用 HASH 子 分区 ;也 就 是 说 ,一个可 以 采用 可 以 转化 成 RANGE 分区(可 带 子 分区),反之不 然 ; 一个分区的数据量要小于 500 万,这 是一个 硬 性的 尺 度,但从 技术上 来 看 万 数据量分区 方法比每 个分区 20 万 数据量的分区 方法 要 快 很多 比如 大于 500 万,则 但从 技术上 来 看 ,每 个 万 数据量的 情况 要 快 很多 ,所 以需 要 灵活 则考 虑采 用其 它 分 区 , 字典来 查 看确定每个分 比如 时 间 )进行分 区维护 分 区维护时全局索引 会 失 需 设计 成 分区表 ,当 表的数 分区不 支持 多 列分区关 那么就 使用 地 区 编号 或者业 务编 可 以 把 分区关键字的 值相 对应数据 比较少 的 几 个分区合 并 作一个分区 ; ,则应 该 对表采用 一个可 以 采用 LIST 分 ; 但从 技术上 来 看 ,每 个 万 数据量的分区 方法 要 快 很多 ,所 以需 要 灵活 掌握 。 1. 各分 区采 用各 自 的表 空 间存储 表 空 间; 2. 分 区 表的 索引采 用 本地索引 4.2.4.2.4.2.4.2.4444 HASHHASHHASHHASH 分区的规范分区的规范分区的规范分区的规范 大数据量的表 需进 行分区化 据量 超 过 5000 万,请 DBA 参 与设计 SQL 访问表不按照 某 列的范 据 某 列的 值 范围或者 离 散 值进 行定期 维护 择 何种 分区时的 选择 ;HASH 分区 清 理数据的 情况 。 对 确 定分区 细 度的 考 虑 ¢ 依据分区的数据量规 划 和表的 最 大数据量来 确 定分区数 ¢ 一个分区的数据量要小于 分区 10 万 数据量分区 方法比每 个分区 要 灵活 掌握 。 1. 各分 区采 用各 自 的表 空 间存储 表 空 间; 2. 对于 HASH 分 区 表 , 可以 采 用 全局索引 , 该表很少会发生分 区维护 的工作 4.2.54.2.54.2.54.2.5 RANGERANGERANGERANGE----LISTLISTLISTLIST 分区的规范分区的规范分区的规范分区的规范 大数据量的表 需进 行分区化 据量 超 过 5000 万,请 DBA 参 与设计 SQ L 访问表时,既 依据 某 列 值 的范围 用 RANGE-LIST 复合分区,常 用于表中的数据 需 要依据一个时间字段 做 周 期性 删 除 等维护 并且 正 常 业 务 SQL 访问 既 依据时间字段 第 21 页 各分 区采 用各 自 的表 空 间存储 ,使 用 us er _t a b_p ar titions 字典来确定每个分 区 的 分 区 表的 索引采 用 本地索引 。 分区的规范分区的规范分区的规范分区的规范 大数据量的表 需进 行分区化 ,当 表的数据量 超 过 500 万,需 设计 成 分区表 参 与设计 。 访问表不按照 某 列的范 围 进 行,也 不按 某 列 离 散 值进 行,而且 对 该 表的数据不会依 据 某 列的 值 范围或者 离 散 值进 行定期 维护 ,那么 使用 HASH 分区 ;HASH 分区是不 知道 应 该选 HASH 分区的各分区都可能存有各 种情况 的数据,故 而 不能用于依据 对 确 定分区 细 度的 考 虑 : 依据分区的数据量规 划 和表的 最 大数据量来 确 定分区数 ; 一个分区的数据量要小于 500 万,这 是一个 硬 性的 尺 度,但从 技术上 来 看 万 数据量分区 方法比每 个分区 20 万 数据量的分区 方法 要 快 很多 各分 区采 用各 自 的表 空 间存储 ,使 用 us er _t a b_p ar titions 字典来确定每个分 区 的 ,大 多数情况 下 依然要求 采 用 本地索引 ,但是如果分 区过 细 ,因 为 根 据 HASH 分 区 表的 特征 (各分 区无业务区 分 该表很少会发生分 区维护 的工作 。 分区的规范分区的规范分区的规范分区的规范 大数据量的表 需进 行分区化 ,当 表的数据量 超 过 500 万,需 设计 成 分区表 参 与设计 。 既 依据 某 列 值 的范围 ,又 依据其他列的 离 散 值 或者范围 常 用于表中的数据 需 要依据一个时间字段 做 周 期性 删 除 等维护 访问 既 依据时间字段 ,又 依据其他字段的 散 列 值进 行访问的 情况 字典来确定每个分 区 的 需 设计 成 分区表 ,当 表的数 而且 对 该 表的数据不会依 分区是不 知道 应 该选 故 而 不能用于依据 但从 技术上 来 看 ,每 个 万 数据量的分区 方法 要 快 很多 ,所 以需 字典来确定每个分 区 的 但是如果分 区过 细 ,也 各分 区无业务区 分 ,都有数据), 需 设计 成 分区表 ,当 表的数 又 依据其他列的 离 散 值 或者范围 ,这种情况下 采 常 用于表中的数据 需 要依据一个时间字段 做 周 期性 删 除 等维护 , 又 依据其他字段的 散 列 值进 行访问的 情况 。 比如 :电 信增 值 业 务 计 费 表 围和业 务 属 性,所 以 可 以以 时间列为分区关键字建立 立 LIST 子 分区 ;分区 划 分的 方法 ¢ 就 按照大 多 数范围访问的范围 值 来 划 定 关键字的 值 来 划 分 子 分区 ¢ 如 果 LIST 子 分区中数据量 较 小 而且 又 常 被 一 起 访问的 子 分区可 以 合 并成 一个 子 分 区; ¢ 如 果 LIST 子 分区中一个 子 分区关键字 值 对应的 子 分区数据量 还 是 很 大 万,影响 性能,那么 可 以通 过 细 分 的,这 点和 LIST 分区 在该 情况下 的 处 理 方法 1. 各子分 区 应该 尽量 分 散 到不同的表 空 间中存储 典来确定每个子分 区 的表 空 间 2. RANGE-LIST 大 多数情况 采 用 本地索引 维护 。 4.2.4.2.4.2.4.2.6666 RANGERANGERANGERANGE----HASHHASHHASHHASH 分区的规范分区的规范分区的规范分区的规范 大数据量的表 需进 行分区化 据量 超 过 5000 万,请 DBA 参 与设计 SQ L 访问表时,主要依据 某 个列的范围 进 行访问 或者数据 维护 特征 符合 RANGE 区数据量 又 很 大,对性能有 影响 子 分区采用 HASH 方法 ,整 个表 就 是 划 定分区的 方法 :先 按照大 多 数范围访问的范围 值 来 划 定 性能 情况 来 确 定 HASH 子 分区的数据量 1. 各子分 区 应该 尽量 分 散 到不同的表 空 间中存储 典来确定每个子分 区 的表 空 间 2. RANGE-HASH 大 多数情况 采 用 本地索引 维护 。 第 22 页 电 信增 值 业 务 计 费 表 ,既 有时间 又 有业 务 属 性列,统计的时 候 所 以 可 以以 时间列为分区关键字建立 RANGE 分区,以 业 务 属 性列为关键字建 分区 划 分的 方法 : 就 按照大 多 数范围访问的范围 值 来 划 定 RANGE 分区的范围,依据单个 关键字的 值 来 划 分 子 分区 ; 子 分区中数据量 较 小 而且 又 常 被 一 起 访问的 子 分区可 以 合 并成 一个 子 分 子 分区中一个 子 分区关键字 值 对应的 子 分区数据量 还 是 很 大 那么 可 以通 过 细 分 RANGE 分区来 达 到 减少 LIST 子 分区数据量的目 分区 在该 情况下 的 处 理 方法 (转化 成 RANGE-HASH 各子分 区 应该 尽量 分 散 到不同的表 空 间中存储 ,使 用 us er _t a b_subp ar titions 典来确定每个子分 区 的表 空 间 ; 大 多数情况 采 用 本地索引 ,因 为常 根 据 RANGE 分 区 关键字的来进行分 区 分区的规范分区的规范分区的规范分区的规范 需进 行分区化 ,当 表的数据量 超 过 500 万,需 设计 成 分区表 参 与设计 。 主要依据 某 个列的范围 进 行访问 ,即 访问 特征 符合 RANGE RANGE 分区的要 求 ,但 是 以 SQL 或者 维护 的数据范围来 划 定分区 对性能有 影响 ,需再进 行 子 分区,由 于分区中的数据都会 被 访问 到 整 个表 就 是 RANGE-HASH 分区 ; 先 按照大 多 数范围访问的范围 值 来 划 定 RANGE 分区的范围 子 分区的数据量 。 各子分 区 应该 尽量 分 散 到不同的表 空 间中存储 ,使 用 us er _t a b_subp ar titions 典来确定每个子分 区 的表 空 间 ; 大 多数情况 采 用 本地索引 ,因 为常 根 据 RANGE 分 区 关键字的来进行分 区 统计的时 候 ,会 选择 时间范 以 业 务 属 性列为关键字建 依据单个 LIST 子 分区 子 分区中数据量 较 小 而且 又 常 被 一 起 访问的 子 分区可 以 合 并成 一个 子 分 子 分区中一个 子 分区关键字 值 对应的 子 分区数据量 还 是 很 大 ,超 过 500 子 分区数据量的目 HASH)不同。 us er _t a b_subp ar titions 字 分 区 关键字的来进行分 区 需 设计 成 分区表 ,当 表的数 RANGE 分区的要 求 , 或者 维护 的数据范围来 划 定分区 ,分 由 于分区中的数据都会 被 访问 到 ,所 以 分区的范围 ,再 依据 us er _t a b_subp ar titions 字 分 区 关键字的来进行分 区 5555 索引索引索引索引设计规范设计规范设计规范设计规范 5.15.15.15.1 索引分类索引分类索引分类索引分类 Oracle 中可 以 创 建 多 种 类型的索引 以 按列的 多少 、索引列是否唯一 5.1.15.1.15.1.15.1.1 单列索引与复合索引单列索引与复合索引单列索引与复合索引单列索引与复合索引 一个索引可 以由 一个或 多 个列组 成 单列索引是基于单列所 创 建的索引 5.1.25.1.25.1.25.1.2 唯一索引与非唯一索引唯一索引与非唯一索引唯一索引与非唯一索引唯一索引与非唯一索引 唯一索引是索引列 值 不能重复的索引 无论 是唯一索引 还 是非唯一索引 的索引是不唯一索引。 5.1.35.1.35.1.35.1.3 BBBB 树索引树索引树索引树索引、、、、位图索引与函数索引位图索引与函数索引位图索引与函数索引位图索引与函数索引 B 树索引是按 B 树 算法 组 织 并 存 放 索引数据的 引数据的 算法 来实 现 快速 检 索 功 能 位图索引 在多 列 查询 时 效 果 。 Oracle 中不仅能 够直接 对表中的列 创 建索引 引,这种 索引 称 为“函数索引 5.25.25.25.2 命名规范命名规范命名规范命名规范 命名规则命名规则命名规则命名规则::::类 别码 _表名 类别码类别码类别码类别码 :一 般 索引 IDX 根 据索引的性 质 填 写。 第 23 页 设计规范设计规范设计规范设计规范 中可 以 创 建 多 种 类型的索引 ,以 适应各 种 表的 特 点和各 种 查询条件 的 特 点 索引列是否唯一 、索引数据的组 织 形式对索引 进 行分类。 单列索引与复合索引单列索引与复合索引单列索引与复合索引单列索引与复合索引 一个索引可 以由 一个或 多 个列组 成 ,用来 创 建索引的列 被称 为“索引列 单列索引是基于单列所 创 建的索引 ,复合索引是基于 两 列或者 多 列所 创 建的索引 唯一索引与非唯一索引唯一索引与非唯一索引唯一索引与非唯一索引唯一索引与非唯一索引 唯一索引是索引列 值 不能重复的索引 ,非唯一索引是索引列可 以 重复的索引 无论 是唯一索引 还 是非唯一索引 ,索引列都允许 取 NULL 值。默认 情况下 位图索引与函数索引位图索引与函数索引位图索引与函数索引位图索引与函数索引 树 算法 组 织 并 存 放 索引数据的 ,所 以 B 树索引主要依 赖 其组 织 并 存 放 索 引数据的 算法 来实 现 快速 检 索 功 能 。 位图索引 在多 列 查询 时 ,可 以 对 两 个列 上 的位图 进 行 AN D 和 OR 操 作 , 中不仅能 够直接 对表中的列 创 建索引 ,还 可 以 对包含列的函数或表 达 式 创 建索 函数索引 ”。 表名 _附 加 码 ,采用大写字符。 IDX、位图索引 BI DX 、唯一索引 UK、主键 P K、外键 以 适应各 种 表的 特 点和各 种 查询条件 的 特 点 。可 索引列 ”。 复合索引是基于 两 列或者 多 列所 创 建的索引 。 非唯一索引是索引列可 以 重复的索引 。 默认 情况下 ,Oracle 创 建 树索引主要依 赖 其组 织 并 存 放 索 ,达 到 更好 的 查询 还 可 以 对包含列的函数或表 达 式 创 建索 外键 F K,类 别码 表名表名表名表名 :表名应 该 简 洁明 了 个字符,则 从 最 后 一个单词开 始 采用 该 单词前 4 个字母来表 示 如:使用 FACTOR Y 而 非 FACTORIES 附加码附加码附加码附加码::::可 以 是 序号 , 5.35.35.35.3 索引设计规范索引设计规范索引设计规范索引设计规范 索引是 从 数据库中 获 取 数据的 最 高 效方 式之一 技术 得到 解 决。但 大量的 DML 于 插入相当 频繁的表要慎重建索引 用 需 求 在 空间和时间 上达 到 最好 的平 衡 点 ¢ 适 当利 用索引 提 高 查询 速 度 依据 这些 查询需 求 建 相 应的索引 量,在 此 数据量 下 , 对于 DML 频繁列的索引要定期 维护 如在插入 数据的时 候 性能的 影响 ,最终 还 是 以试 验 为 准 ¢ 不要建实际用不 上 的索引 查询 速 度,则要 把它 删 除 可 以利 用一 下 。 ¢ 索引列的 选择 :如 果 检 索 条件 有可能包含 多 列 最常 用于 检 索 条件 的列 放在最 前 端 型表,假 如 这些 小表有频繁的 扫描 表的 代 价; ¢ 主键索引 在 建立的时 候 一定要 明 确 的指定名 称 能有 些 数据库 无 法 指定主键名 ¢ 当 有 联 合主键或者 联 合索引时 过 2 个,即 一个 单字段 最多 可 在上面 建立一个单字段索引和一个组合索引包含 这 个字段 ; 第 24 页 表名应 该 简 洁明 了 ,尽量使用 完 整 的单词,如 果 导 致 拼 上 表名 后 则 从 最 后 一个单词开 始 ,依 次向 前采用 该 单词的 缩 写。( 如 果 没 有约定的 缩 写 个字母来表 示 )。 另 外,表名中的名 词单词都应使用单数形式 FACTORIES。 ,也 可 以 是字段名,根 据实际的使用 情况 进 行 填 写 索引设计规范索引设计规范索引设计规范索引设计规范 索引是 从 数据库中 获 取 数据的 最 高 效方 式之一 。95 % 的数据库性能问 题 都可 以 采用索引 DML 操 作会增加系统对索引的 维护成 本,对性能会有一定 影响 于 插入相当 频繁的表要慎重建索引 ,索引 也 会 占 相当 的存储空间,所 以 要 根 据 硬件环 境 和应 用 需 求 在 空间和时间 上达 到 最好 的平 衡 点 。主要原则: 适 当利 用索引 提 高 查询 速 度 :当 数据量 比较 大,了解 应用程 序 的会有 哪些 查询 依据 这些 查询需 求 建 相 应的索引 ;最好 亲 自试 验 一 下 ,模 拟 一 下 生 产环 境 的数据 ,比较 一 下 建索引前 后 的 查询 速 度 ;索引对性能会有一定 影响 频繁列的索引要定期 维护 (重建)。 但 是,索引的 结构 对于索引的 更新 如在插入 数据的时 候 )是有一定优化的,所 以 不要 在没 有 试 验 以 前过分 夸 大 它 对 最终 还 是 以试 验 为 准 。 不要建实际用不 上 的索引 ,与 上条相 关,如 果 建的索引 并 不 提 高 任何 一应用中的 则要 把它 删 除 ;有 些 数据库有 相 关 工 具可 以 发 现 实际 未被 使用的索引 如 果 检 索 条件 有可能包含 多 列 ,创 建 联 合主键或者 联 合索引 最常 用于 检 索 条件 的列 放在最 前 端 ,其他的列 排 在 后 面 ;不要索引使用频繁的小 假 如 这些 小表有频繁的 DML 就 更 不要建立索引,维护 索引的 代 价远远 高于 主键索引 在 建立的时 候 一定要 明 确 的指定名 称 ,不能 让 系统默认建立主键索引 能有 些 数据库 无 法 指定主键名 ,则 例 外) ; 当 有 联 合主键或者 联 合索引时 ,注 意 不要建重复的索引。单字段 上 的索引不 得 超 单字段 最多 可 在上面 建立一个单字段索引和一个组合索引包含 这 如 果 导 致 拼 上 表名 后 ,长度 超 过 30 如 果 没 有约定的 缩 写 ,则 词单词都应使用单数形式 ,以免 混淆 , 根 据实际的使用 情况 进 行 填 写 。 的数据库性能问 题 都可 以 采用索引 对性能会有一定 影响 ,对 所 以 要 根 据 硬件环 境 和应 序 的会有 哪些 查询 , 模 拟 一 下 生 产环 境 的数据 索引对性能会有一定 影响 , 索引的 结构 对于索引的 更新 (比 所 以 不要 在没 有 试 验 以 前过分 夸 大 它 对 如 果 建的索引 并 不 提 高 任何 一应用中的 有 些 数据库有 相 关 工 具可 以 发 现 实际 未被 使用的索引 , 创 建 联 合主键或者 联 合索引 ,把 不要索引使用频繁的小 维护 索引的 代 价远远 高于 不能 让 系统默认建立主键索引 (可 单字段 上 的索引不 得 超 单字段 最多 可 在上面 建立一个单字段索引和一个组合索引包含 这 ¢ 无特 别 说明 ,每 个表的索引 举 例说明: 表 EMP LOYEES,它 的主键是建 立 在列 主键的语 句 中 DEPARTID 在前 DEPARTID 建一个 索引 了,联合 索引 的情况也一样 更 复 杂 的情况,比如表 EMPLOYEES 三 列上,在创建语 句 中也依据上述 顺序 建 立 以 CO RPID 在前,DEPARTID EMPLOYEEID 建 立 一个 索引 是不与上面的 索引 重复的 ¢ 控制 一个表的索引数量 5.3.15.3.15.3.15.3.1 指定表空间规范指定表空间规范指定表空间规范指定表空间规范 每 个索引 在 创 建时,必 须 指定表空间 空间和非索引 专 用空间,以减少 5.3.2 5.3.2 5.3.2 5.3.2 主键索引的规范主键索引的规范主键索引的规范主键索引的规范 对数据量表应 该先在 主键列建唯一索引 法设计。原则 上 所有的数据表都要有主键 主键 上 隐含索引,d r op 键约束和 相 应索引有 更多 的 控制 1. 在准备 建主键的列 上 建立唯一索引 I nd e x _ Na m e ON Ta b le _ Na m e (C o l umn _ Na m e ) TABLESPACE TBS _INDEX; 2. 再 加 上 主键约束: ALTER TABLE Ta b le _ Na m e A DD (PRIMARY TBS_INDEX ); Oracle 会 在 指定的列 上 加 上 主键约束 分 区 表的主键 默认索引 是 全局索引 引,再建主键 约束 。 第 25 页 每 个表的索引 ,不 得 超 过 5 个。 它 的主键是建 立 在列 DEPARTID 和 EMP LOYEEID 上的联合主键 在前 ,EMPLOYEEID 在后。在这样一个表里,通常就没有必要再为 联合 索引 的情况也一样 。 EMPLOYEES,有一个 索引 建 立 在列 CORPID, DEPARTID, EMP LOYEEID 在创建语 句 中也依据上述 顺序 ,就没有必要再为 CO RPID 建 立索引 DEPARTID 在后的联合 索引 ;如果 EMP LOYEEID 需要 索引 建 立 一个 索引 是不与上面的 索引 重复的 ;DEPARTID 列也 类似 控制 一个表的索引数量 ,尽量使 得 一个表的索引数量小于 五 个。 指定表空间规范指定表空间规范指定表空间规范指定表空间规范 ,,,,禁止采用默认表空间禁止采用默认表空间禁止采用默认表空间禁止采用默认表空间 必 须 指定表空间 ,禁止采用默认表空间,以 防 止索引建立 在 以减少 IO 冲突 ,提 高性能。 主键索引的规范主键索引的规范主键索引的规范主键索引的规范 ,,,,先建唯一索引先建唯一索引先建唯一索引先建唯一索引,,,,再建主键约束再建主键约束再建主键约束再建主键约束 对数据量表应 该先在 主键列建唯一索引 ,再 建主键约束。分区表的主键必 须 采用 该方 则 上 所有的数据表都要有主键 。 d r op 或 dis a b le 主键时,索引会 丢 失 ,为保证性能不 变 键约束和 相 应索引有 更多 的 控制 ,对大表 (分区表 )的索引采用 如 下 方 式建立 在准备 建主键的列 上 建立唯一索引 (UNIQU E IN DEX): CREATE UNIQU E IN DEX I nd e x _ Na m e ON Ta b le _ Na m e (C o l umn _ Na m e ) TABLESPACE TBS _INDEX; ALTER TABLE Ta b le _ Na m e A DD (PRIMARY KEY(C o l umn _ Na m e ) U SING IN DEX TABLESPACE 会 在 指定的列 上 加 上 主键约束 ,并且 使用 该 索引。 分 区 表的主键 默认索引 是 全局索引 ,所以主键 索引 的分 区 方法:先建 立 分 区 化的 唯 一 索 上的联合主键 ,并 且创建 通常就没有必要再为 CORPID, DEPARTID, EMP LOYEEID 建 立索引 ;也没有必要再 需要 索引 ,那么为 以 防 止索引建立 在 syst e m 分区表的主键必 须 采用 该方 为保证性能不 变 ,为 了 对主 的索引采用 如 下 方 式建立 : : CREATE UNIQU E IN DEX KEY(C o l umn _ Na m e ) U SING IN DEX TABLESPACE 先建 立 分 区 化的 唯 一 索 5.3.5.3.5.3.5.3.3333 唯一约束索引的规范唯一约束索引的规范唯一约束索引的规范唯一约束索引的规范 针 对大数据量表应 该先在 唯一约束列 上 建立 普通 索引 唯一约束必 须 采用 该方法 。 删 除或禁用唯一性约束 通常 同时使 相 关 联 的唯一索引 失效 要 避免 这样 问 题 ,可 以 采 取下 面 的步 骤 索引) ;( b)添加唯一性约束 5.3.5.3.5.3.5.3.4444 外键列索引的规范外键列索引的规范外键列索引的规范外键列索引的规范 对于关 联 两 个表字段 , 据 完 整 性的要 求 决定。为 了提 高性能 关 联查询 的性能 考 虑 ,二 是为 了避免 父 子 表 修 改而 发生 死锁 键,必 须 指定 on d ele t e ca s ca d e 普通 表的外键列建立 普通 索引 即 可 或者全局索引。 5.3.5.3.5.3.5.3.5555 复合索引的规范复合索引的规范复合索引的规范复合索引的规范 复合索引 只 有 在该 种 复合 常 被 和 该 表 相 关的大 多 数 数不能 超 过 5 个,否则 该 索引 很少 会 被 使用 ¢ 复合索引的 第 一列 中复合使用列为 ABC 引可 以 按照 CAB 的 顺序 建立 ¢ 对于不能 把 握好 的复合索引 ¢ 切 忌 不能 将 表 相 关的所有 5.3.5.3.5.3.5.3.6666 审慎使用函数索引审慎使用函数索引审慎使用函数索引审慎使用函数索引 ¢ 函数索引 由 于使用形式 需 和 创 建形式一致 第 26 页 唯一约束索引的规范唯一约束索引的规范唯一约束索引的规范唯一约束索引的规范 ,,,,先建普通索引先建普通索引先建普通索引先建普通索引,,,,再建唯一性约束再建唯一性约束再建唯一性约束再建唯一性约束 针 对大数据量表应 该先在 唯一约束列 上 建立 普通 索引 ,再 添加唯一性约束 。 删 除或禁用唯一性约束 通常 同时使 相 关 联 的唯一索引 失效 ,因 而降低了 数据库性能 可 以 采 取下 面 的步 骤 :(a)在 唯一性约束的列 上 创 建非唯一性索引 添加唯一性约束 。 外键列索引的规范外键列索引的规范外键列索引的规范外键列索引的规范 ,,,,外键均需建立索引外键均需建立索引外键均需建立索引外键均需建立索引 ,一 般 应 该 分 别 建立主键、外键。实际是否建立外键 为 了提 高性能 ,无论 表的大小,外键都要建立索引 , 二 是为 了避免 父 子 表 修 改而 发生 死锁 。对于有要 求 级联删 除 属 性的外 on d ele t e ca s ca d e 。 普通 表的外键列建立 普通 索引 即 可 ,如 果 表是分区表,则依据表的 情况 建立本 地 索引 复合索引的规范复合索引的规范复合索引的规范复合索引的规范 ,,,,列数禁止超过列数禁止超过列数禁止超过列数禁止超过 5555 个个个个 复合索引 只 有 在该 种 复合 常 被 和 该 表 相 关的大 多 数 SQL 使用时 才 建立 否则 该 索引 很少 会 被 使用 。 复合索引的 第 一列 ,可 以通 过不使用 该 种 复合的 S Q L 来 确 定。假 设一 些 ABC,而 其他一 些 SQ L 的 WHERE 中 常 使用的是 C 列 的 顺序 建立 ,这样 上 述两种 SQ L 都能使用 该 索引 ; 对于不能 把 握好 的复合索引 ,请 在选 择 性大的列 上 分 别 建立单列索引 切 忌 不能 将 表 相 关的所有 SQ L 中 WHERE 涉 及 到 的列复合 起 来建立复合索引 审慎使用函数索引审慎使用函数索引审慎使用函数索引审慎使用函数索引 、、、、位图索引位图索引位图索引位图索引、、、、反向索引反向索引反向索引反向索引 由 于使用形式 需 和 创 建形式一致 ,尽量 避免 使用函数索引。如 果 想 要使用函数索引 再建唯一性约束再建唯一性约束再建唯一性约束再建唯一性约束 再 添加唯一性约束 。分区表的 因 而降低了 数据库性能 。 在 唯一性约束的列 上 创 建非唯一性索引 (普通 实际是否建立外键 ,根 据对数 ,一是为 了子 父 表 对于有要 求 级联删 除 属 性的外 则依据表的 情况 建立本 地 索引 使用时 才 建立 。复合索引的列 假 设一 些 SQ L 的 WHERE 列,那么 该 复合索 ; 择 性大的列 上 分 别 建立单列索引 ; 涉 及 到 的列复合 起 来建立复合索引 。 如 果 想 要使用函数索引 , 请 尽量 进 行转化。 由 于函数索引 在 使用时 引,尽量采用 如 下 方法 转化 原本 在 WHERE 中列 上 添加函数的 这样只 需 要 在 列 上 建立 普通 索引 即 可 TO_CHAR(CREATE_TIME)= CREATE_TIME = TO_DATE(‘2010 ¢ 位图索引 静态 表中的 低 基数列可 以 使用位图索引 (bitm a p )索引,在 报 表型数据库 ¢ 反 向 索引 列 值顺序 增加的列,其 上 的 检 索时,可 以 采用反 向 函数 5.3.5.3.5.3.5.3.7777 分区索引的规范分区索引的规范分区索引的规范分区索引的规范 对分区表的索引,需 要 做 分区 维护 的 可 以 采用全局索引,其他分区 分区表不 常进 行分区 维护 。 5.3.5.3.5.3.5.3.8888 索引重建的规范索引重建的规范索引重建的规范索引重建的规范 重建索引使用 ALTER IN DEX REB UILD 式。分区表 等 大数据量表的索引必 须 采用 方法方法方法方法 :ALTER IN DEX I DX_ NAME REB UILD [ TABLESPACE TBSP _NAME] 6666 其他其他其他其他设计设计设计设计规范规范规范规范 6.16.16.16.1 其他数据库对象其他数据库对象其他数据库对象其他数据库对象 ¢ 视图:VW_ 相 关表名 第 27 页 由 于函数索引 在 使用时 ,使用形式必 须 和 创 建形式一致,故 应 该 尽量 避免 使用函数索 尽量采用 如 下 方法 转化 SQL 以避免 函数索引的使用: 中列 上 添加函数的 ,取 函数的反 意 义函数添加 到 “=”另 一 侧 的 常 数项 上 这样只 需 要 在 列 上 建立 普通 索引 即 可 ,比如常 见 的 日 期转化函数: TO_CHAR(CREATE_TIME)= ’ 2010-07-07 ’ 采 用 TO_DATE() 2010 -07-07 ’,’yyyy -mm -dd ’)。 静态 表中的 低 基数列可 以 使用位图索引 。在事务 型数据库(OLTP)中禁止使用位图 在 报 表型数据库 (OLAP)中的 静态 表,可 以 适 当 使用。 其 上 的 WHERE 运算 是 < > 或者 =而 不是范围(b e t w ee n a nd 可 以 采用反 向 函数 。一般创 建反 向 索引的列为 N U MBER 类型,值由 分区索引的规范分区索引的规范分区索引的规范分区索引的规范 需 要 做 分区 维护 的 ,必 须 使用局部索引。一 般情况下 其他分区 ,包 括 RANGE-HASH 也 应 该 采用本 地 索引,主要是 由 于 。 索引重建的规范索引重建的规范索引重建的规范索引重建的规范 ALTER IN DEX REB UILD 方 式,禁止采用 D ROP IN DEX & CREATE IN DEX 分区表 等 大数据量表的索引必 须 采用 ALTER IN DEX REB UIL D 方 式重建。 ALTER IN DEX I DX_ NAME REB UILD [ TABLESPACE TBSP _NAME]。 规范规范规范规范 其他数据库对象其他数据库对象其他数据库对象其他数据库对象 命名规范命名规范命名规范命名规范 相 关表名 ,或者 根 据 需 要 另取 名字 ; 故 应 该 尽量 避免 使用函数索 另 一 侧 的 常 数项 上 , TO_DATE() 转 化 为 中禁止使用位图 b e t w ee n a nd 或者 < a nd > ) 值由 SEQU ENCE 生 成 。 一 般情况下 ,HASH 分区表 主要是 由 于 HASH D ROP IN DEX & CREATE IN DEX 方 。 ¢ 存储过程:SP _ 存储过程名 ¢ 函数:F UN_ 函数名 称 ¢ 触发器:TR _ 触发器名 称 ¢ 包及包体:P KG_ 包或包体名 称 ¢ 序 列:SE Q_ 序 列名 称 ¢ 游 标:C UR_ 游 标名 称 ¢ 自 定义 记录 类型 : ¢ 自 定义 记录 类型 变 量 型 变 量含义 ; ¢ 自 定义 嵌套 类型 : ¢ 自 定义 嵌套 类型 变 量 型 变 量含义 ; ¢ 输 入参 数:I _ 输 入参 数名 称 ¢ 输 出参 数:O _ 输 出参 数名 称 6.6.6.6.2222 视图设计规范视图设计规范视图设计规范视图设计规范 6.2.16.2.16.2.16.2.1 尽量使用简单的视图尽量使用简单的视图尽量使用简单的视图尽量使用简单的视图 简单视图:数据来 自 单个表 复 杂 视图:数据来 自多 个表 6.2.2 6.2.2 6.2.2 6.2.2 按照必要性原则建立视图按照必要性原则建立视图按照必要性原则建立视图按照必要性原则建立视图 在 不太 清 楚 视图用 法 的 情况下 要建视图,只 要是 打算 长期使用的 6.3 6.3 6.3 6.3 存储过程存储过程存储过程存储过程、、、、函数函数函数函数 请把 程 序 包、存储过程 因 为 此 四 者包含 了代码 ,应用程 序 对他 们 的依 赖 程度 比 对表 第 28 页 存储过程名 ,用 英 文表 达 存储过程 意 义 ; 函数名 称 ,用 英 文表 达 函数作用 ; 触发器名 称 ,用 英 文表 达 触发器作用 ; 包或包体名 称 ,用 英 文表 达 包及包体的作用 ; 序 列名 称 ,用 英 文表 达序 列的 意 义 ; 游 标名 称 ; :REC_ 自 定义 记录 类型名 称 ,用 英 文表 达自 定义 记录 类型含义 自 定义 记录 类型 变 量 :V_REC_ 自 定义 记录 类型 变 量名 称 ,用 英 文表 达自 定义 记录 类 :TBL_ 自 定义 嵌套 类型名 称 ,用 英 文表 达自 定义 嵌套 类型含义 自 定义 嵌套 类型 变 量 :V_TBL_ 自 定义 嵌套 类型 变 量名 称 ,用 英 文表 达自 定义 嵌套 类 输 入参 数名 称 ,用 英 文表 达 输 入参 数类型或含义 ; 输 出参 数名 称 ,用 英 文表 达 输 出参 数类型或含义。 视图设计规范视图设计规范视图设计规范视图设计规范 尽量使用简单的视图尽量使用简单的视图尽量使用简单的视图尽量使用简单的视图 ,,,,避免使用复杂的视图避免使用复杂的视图避免使用复杂的视图避免使用复杂的视图 数据来 自 单个表 ,且 无 分组 (distin c t / g r oup by) 、无 函数 数据来 自多 个表 ,或有分组、有函数。 按照必要性原则建立视图按照必要性原则建立视图按照必要性原则建立视图按照必要性原则建立视图 在 不太 清 楚 视图用 法 的 情况下 ,尽量不建。因 为一 旦 建 了 ,就 有 被 滥 用的 危 险 只 要是 打算 长期使用的 ,请 写 入 数据库设计中,明 确 它 的用 途 、 函数函数函数函数 、、、、触发器触发器触发器触发器设计规范设计规范设计规范设计规范 存储过程 、函数、触发器,与应用程 序 一同加 入 SV N 中, 应用程 序 对他 们 的依 赖 程度 比 对表 、视图的依 赖 程度 更 高 用 英 文表 达自 定义 记录 类型含义 ; 用 英 文表 达自 定义 记录 类 用 英 文表 达自 定义 嵌套 类型含义 ; 用 英 文表 达自 定义 嵌套 类 无 函数 。 就 有 被 滥 用的 危 险 ;如 果 需 、目的。 ,进 行 版 本 控制 。 视图的依 赖 程度 更 高 。 适量 但 尽量 少 使用存储过程 ¢ 可 以减少 数据库与 客 户端 的 交互 ¢ 有的数据库 还 对他 们进 行 了 某种 程度的 编 译 等 语 句进 行 解析 , ¢ 如 果 有 多 个应用,使用 了 不同的开发语言 则可 以考 虑 使用存储过程或者函数 ¢ 增 强 了 应用对数据库的依 赖 的 困难 越 大 ;数据库中的业 务逻辑 越 多 护 难 度 也 会增大 ; ¢ 通常 存储过程 等没 有 面向 对象的 特 性 比较 复 杂 时,或者 它 们相互 间的 调 用关系 比较 复 杂 时 6.3.1 6.3.1 6.3.1 6.3.1 禁止使用触发器禁止使用触发器禁止使用触发器禁止使用触发器 触发器是一 种特殊 的存储过程 据的 完 整 性和一致性不 被 破坏 而 创 建 在 系统中禁止 使用触发器 6.3.2 6.3.2 6.3.2 6.3.2 禁止不建立禁止不建立禁止不建立禁止不建立 出错处理机制出错处理机制出错处理机制出错处理机制 出错处 理 机制 一 般 都是 自 己 定义的 以考 虑下 面 三 种 : ¢ 错 误 发生 即 处 理 也 就 是 错 误 出现在 哪 里 ¢ 集中 错 误 处 理 错 误 发生 后 统一转发 到某 一个模 块进 行统一 处 理 ¢ 错 误 分包 处 理 每 个包 处 理 自 己 的 错 误 信息 理的 方 式。 在 设计和开发存储过程 机制 ,凡 是 涉 及 到 表 操 作 (ins er t 第 29 页 适量 但 尽量 少 使用存储过程 、函数、触发器。使用存储过程、函数、触发器的 影响 可 以减少 数据库与 客 户端 的 交互 ,提 高性能 ; 有的数据库 还 对他 们进 行 了 某种 程度的 编 译 ,在执 行的时 候 ,不用 再 对其中的 ,从 而提 高 速 度 ; 使用 了 不同的开发语言 ,当 有 某些 关键的或者复 杂逻辑希望 共享 则可 以考 虑 使用存储过程或者函数 。因 为存储过程 等在 数据库一 级 是 共享 的 增 强 了 应用对数据库的依 赖 ,如 果 打算将 来 移植 数据库的 话 ,使用 得 越 多 数据库中的业 务逻辑 越 多 (存储过程 等 ), 应用 以 及存储过程 等 的 维 ; 通常 存储过程 等没 有 面向 对象的 特 性 ,不 容 易 设计 出 易 于扩展的 结构 或者 它 们相互 间的 调 用关系 比较 复 杂 时 ,可能 难 于 维护 禁止使用触发器禁止使用触发器禁止使用触发器禁止使用触发器 触发器是一 种特殊 的存储过程 ,通 过数据表的 D ML 操 作 而 触发 执 行 , 据的 完 整 性和一致性不 被 破坏 而 创 建 ,实 现 数据的 完 整 性约束。 使用触发器 。 出错处理机制出错处理机制出错处理机制出错处理机制 一 般 都是 自 己 定义的 ,在 一个团 队内 部 自 行设定统一的 错 误 处 理模式 也 就 是 错 误 出现在 哪 里 ,就 在 那 里 及时 处 理,而 不 进 行转发或者其他 操 作 错 误 发生 后 统一转发 到某 一个模 块进 行统一 处 理 。 每 个包 处 理 自 己 的 错 误 信息 ,不 进 行统一转发,但 是 在 包 内 统一 处 理 , 在 设计和开发存储过程 、函数时,必 须 建立统一的 出错处 理 机制 。禁止 无 出错捕获处 理 (ins er t 、upd a t e、s elec t 、d ele t e ) 的 s q l 语 句 , 触发器的 影响 : 不用 再 对其中的 SQL 当 有 某些 关键的或者复 杂逻辑希望 共享 , 因 为存储过程 等在 数据库一 级 是 共享 的 ; 使用 得 越 多 ,则 移植 应用 以 及存储过程 等 的 维 不 容 易 设计 出 易 于扩展的 结构 。当 存储过程 可能 难 于 维护 。 ,其作用为 确 保数 在 一个团 队内 部 自 行设定统一的 错 误 处 理模式 。可 而 不 进 行转发或者其他 操 作 。 ,而 不是发生 级处 禁止 无 出错捕获处 理 ,都必 须进 行 错 误 捕 捉 。 6.3.3 6.3.3 6.3.3 6.3.3 禁止使用禁止使用禁止使用禁止使用 GOTOGOTOGOTOGOTO 不加限 制 地 使用 GOTO , 维护 的“面条代码 ”。经常 带 来 错 误 或隐 患 始 化、重要的计 算等 语 句 。 在 设计和开发存储过程 6.3.4 6.3.4 6.3.4 6.3.4 需要循环更新时需要循环更新时需要循环更新时需要循环更新时 有循 环更新 的存储过程 6.3.5 6.3.5 6.3.5 6.3.5 禁止禁止禁止禁止退出退出退出退出 时不时不时不时不 如 果 在 存储过程或函数中 打 开的 db l ink 、游 标。 6.3.6 6.3.6 6.3.6 6.3.6 如如如如对数据进行对数据进行对数据进行对数据进行 如 果 存储过程或函数中 6.3.76.3.76.3.76.3.7 确保事务的原子性及隔离性确保事务的原子性及隔离性确保事务的原子性及隔离性确保事务的原子性及隔离性 rollbackrollbackrollbackrollback 确 保 事务 的原 子 性及 隔离 性 对于一个 完 成了 的 事务 别 是对 DML 操 作频繁的表 。 6.3.6.3.6.3.6.3.8 8 8 8 在没有事务处理时在没有事务处理时在没有事务处理时在没有事务处理时 过 多多 余的 ROLLBAC K 和 影响 ,下 面 语 句 中的 COMMIT 第 30 页 GOTOGOTOGOTOGOTO 进行跳转进行跳转进行跳转进行跳转 ,会 破坏 清 晰 的程 序结构 ,使程 序 的可读性 变 差 经常 带 来 错 误 或隐 患 ,比如 它 可能 跳 过 了 某些 对象的 构 造 。 在 设计和开发存储过程 、函数时,禁止使用 GOTO 进 行 跳 转。 需要循环更新时需要循环更新时需要循环更新时需要循环更新时 ,,,,必须进行批量提交必须进行批量提交必须进行批量提交必须进行批量提交,,,,且做事务控制且做事务控制且做事务控制且做事务控制 有循 环更新 的存储过程 或函数,必 须进 行 批 量 提交 ,且 必 须进 行 事 物 控制 时不时不时不时不 关闭所有打开的关闭所有打开的关闭所有打开的关闭所有打开的 dblinkdblinkdblinkdblink、、、、游标游标游标游标 在 存储过程或函数中 打 开 了 db l ink 、游 标,则 在 正 常 或者 异常退出 必 须 关 闭 所有 对数据进行对数据进行对数据进行对数据进行 更新更新更新更新,,,,则则则则必须在异常捕获代码必须在异常捕获代码必须在异常捕获代码必须在异常捕获代码 中做回退操作中做回退操作中做回退操作中做回退操作 如 果 存储过程或函数中 对数据 进 行 了更新操 作,则必 须在异常捕获代码 中 做回退操 作 确保事务的原子性及隔离性确保事务的原子性及隔离性确保事务的原子性及隔离性确保事务的原子性及隔离性 ,,,,成功成功成功成功及时及时及时及时 cocococo mmmmmmmm iiii tttt 确 保 事务 的原 子 性及 隔离 性 ,要 么 全部 成功 ,提交 ;要 么 失败 ,回 滚 。 对于一个 完 成了 的 事务 ,请 用 c ommit 显 式 提交 ,这 是 避免 锁 争 用的 锁 等 待 的 需 要 。 在没有事务处理时在没有事务处理时在没有事务处理时在没有事务处理时 ,,,,禁止禁止禁止禁止 随意随意随意随意 使用使用使用使用 COCOCOCOMMMMMMMMITITITIT 和和和和 ROLLBACROLLBACROLLBACROLLBACKKKK 和 COMMIT 容 易 引 起 数据库的同步 日志 等 待 事件 , COMMIT 和 ROLLBAC K 就 是 多 余的。 使程 序 的可读性 变 差 ,甚至 成 为不可 比如 它 可能 跳 过 了 某些 对象的 构 造 、变 量的 初 且做事务控制且做事务控制且做事务控制且做事务控制 且 必 须进 行 事 物 控制 。 则 在 正 常 或者 异常退出 必 须 关 闭 所有 中做回退操作中做回退操作中做回退操作中做回退操作 必 须在异常捕获代码 中 做回退操 作 。 cocococo mmmmmmmm iiii tttt ,,,,失败失败失败失败 及时及时及时及时 。 这 是 避免 锁 争 用的 锁 等 待 的 需 要 ,特 ROLLBACROLLBACROLLBACROLLBACKKKK ,对系统的性能有 示 例: 错误的用法: CR EATE O R REPL ACE P ROCEDUR E P _MS_QUERYBYSERIALNO ( i_S er i alN o VA RCHAR 2, rC u r so r OUT PAC K_SERVICE. t_R e tD a t a S e t ) AS BEG IN OPEN rC u r so r FOR SEL ECT b .S TAFFNO DECODE(b .COMMITRESUL T, 0 , ' FR OM T _MS_SENDLOGHIS a, T _MS_INTERFACECALLED b WHER E b .SERIAL NO = i_S er i alN o; COMMIT ; EXCEPTION WH EN OT HERS T H EN ROLLBACK; END; 正确的用法: CR EATE O R REPL ACE P ROCEDUR E P _MS_QUERYBYSERIALNO ( i_S er i alN o VA RCHAR 2, rC u r so r OUT PAC K_SERVICE. t_R e tD a t a S e t ) IS BEG IN OPEN rC u r so r FOR SEL ECT b .S TAFFNO DECODE(b .COMMITRESUL T, 0 , ' 第 31 页 CR EATE O R REPL ACE P ROCEDUR E P _MS_QUERYBYSERIALNO i_S er i alN o VA RCHAR 2, -- 工 单流水 号 rC u r so r OUT PAC K_SERVICE. t_R e tD a t a S e t -- 返回结 果 集 FOR SEL ECT b .S TAFFNO DECODE(b .COMMITRESUL T, 0 , ' 成功' , '失败 ') A S COMMIT RESULT FR OM T _MS_SENDLOGHIS a, T _MS_INTERFACECALLED b WHER E b .SERIAL NO = i_S er i alN o; CR EATE O R REPL ACE P ROCEDUR E P _MS_QUERYBYSERIALNO i_S er i alN o VA RCHAR 2, -- 工 单流水 号 rC u r so r OUT PAC K_SERVICE. t_R e tD a t a S e t -- 返回结 果 集 FOR SEL ECT b .S TAFFNO DECODE(b .COMMITRESUL T, 0 , ' 成功' , '失败 ') A S COMMIT RESULT AS S TAFFNO, ') A S COMMIT RESULT AS S TAFFNO, ') A S COMMIT RESULT FR OM T _MS_INTERFACECALLED b WHER E b .SERIAL NO = i_S er i alN o; EXCEPTION WH EN OT HERS T H EN OPEN rC u r so r FO R SEL ECT ' 成功 '成功 FR OM DUAL WHER E 1 = 2 ; END; 7 SQL7 SQL7 SQL7 SQL 访问规范访问规范访问规范访问规范 7.17.17.17.1 不要写复杂的不要写复杂的不要写复杂的不要写复杂的 过于复 杂 的 S QL 可 以 用存储过程或函数来 代替 颈 的 话 ,把 条 SQL 拆 成多条也 是可 以 的 懂 也 就 意 味 着 容 易 维护 ,对 较 为复 杂 的 单 独 成 行、放在 语 句 前 面 。 7.2 7.2 7.2 7.2 避免使用避免使用避免使用避免使用 SELECT *SELECT *SELECT *SELECT * 程 序 中不能 出现 SELECT * 下 原 因 : 第 一,使用 *相 对 比较 慢 列名,这 个 工 作是 通 过 遍 历 数据字 典完 成 第 二 ,为 避免以 后 相 关表增加字段 造 成 程 序错 误 INTO 语 句 会 报 错 。 以 下 不符合规范: s elec t * f r om sm_duty 第 32 页 FR OM T _MS_INTERFACECALLED b WHER E b .SERIAL NO = i_S er i alN o; 成功 ' A S S TAFFNO, 成功 ' A S COMMIT RESUL T FR OM DUAL WHER E 1 = 2 ; 访问规范访问规范访问规范访问规范 不要写复杂的不要写复杂的不要写复杂的不要写复杂的 SQLSQLSQLSQL 可 以 用存储过程或函数来 代替 ,效率更 高 ;甚至 如 果 能保证不 造 成 瓶 拆 成多条也 是可 以 的 。这 与一 般 的 编码 规范 很相 似 的,首 先 是要 易 懂 对 较 为复 杂 的 s q l 语 句 加 上 注释,说明 算法 、功 能注释 风格 。 SELECT *SELECT *SELECT *SELECT * SELECT *,即 使是 选择 全部 选择 项,也需 要全部指 明 相 对 比较 慢 ,因 为 Oracle 在解析 的过程中,会 将 “*”依 次 转换 成 所有的 这 个 工 作是 通 过 遍 历 数据字 典完 成 ,这 意 味 着 将 耗费 更多 的时间 ; 为 避免以 后 相 关表增加字段 造 成 程 序错 误 ,比如 INSERT INTO SELECT s elec t * f r om sm_duty ; 甚至 如 果 能保证不 造 成 瓶 首 先 是要 易 懂 。易 功 能注释 风格 :注释 也需 要全部指 明 ,这 主要 出 于 如 依 次 转换 成 所有的 INSERT INTO SELECT 和 SELECT 应如 下书写 : s elec t duty_id , duty_n a m e,crea tion_d 7.7.7.7.3333 INSERTINSERTINSERTINSERT 时需写全列名时需写全列名时需写全列名时需写全列名 代码 中 INSERT 语 句 必 须 写 出 全部列名 以 下 不符合规范: ins er t into inv_p ara m e t er s v al u e s( : fi el d 1,: fi el d 2,: fi el d 3 ); 应如 下书写 : ins er t into inv_p ara m e t er s(fi el d 1, fi el d 2, fi el d 3 ) v al u e s (: fi el d 1,: fi el d 2,: fi el d 3 ) 7.7.7.7.4444 进行进行进行进行 DMLDMLDMLDML 操作时操作时操作时操作时 在进 行 DML 操 作(INSERT 语 句 。 方法一:表数据 全部备份 CREATETABL E TA B_NAME_BAK A S SEL ECT * F R OM TA B_NAME 方法二:部 分 备份 :对 大 表 仅备份 将要 修改 的数据 CR EATE TA BL E TA B_NAME_BAK AS SEL ECT * F R OM TA B_ NAME WHER E [ 7.7.7.7.5555 大数据量大数据量大数据量大数据量 DMLDMLDMLDML DML 操 作 涉 及 到 大数据 量时 对于 UPD ATE 和 DELETE 对于 INSERT INTO SELECT 的数据量。 第 33 页 s elec t duty_id , duty_n a m e,crea tion_d a t e,crea t e d_by f r om sm_duty 时需写全列名时需写全列名时需写全列名时需写全列名 语 句 必 须 写 出 全部列名 ,以 保证表增加字段 后 语 句执 行不 受 影响 ins er t into inv_p ara m e t er s v al u e s( : fi el d 1,: fi el d 2,: fi el d 3 ); ins er t into inv_p ara m e t er s(fi el d 1, fi el d 2, fi el d 3 ) v al u e s (: fi el d 1,: fi el d 2,: fi el d 3 ); 操作时操作时操作时操作时 使用使用使用使用 CTASCTASCTASCTAS 进行数据备份进行数据备份进行数据备份进行数据备份 INSERT,UPDATE,D ELETE)之前,必 须 对数据 进 行 备 份 表数据 全部备份 : CREATETABL E TA B_NAME_BAK A S SEL ECT * F R OM TA B_NAME; 对 大 表 仅备份 将要 修改 的数据 : CR EATE TA BL E TA B_NAME_BAK AS SEL ECT * F R OM TA B_ NAME WHER E [ 选择 出被 操 作数据的 条件]; DMLDMLDMLDML 操作分多次执行操作分多次执行操作分多次执行操作分多次执行 量时 ,请 分 解 为 多次执 行: DELETE 每次涉 及数据量 在 1 万 条左右 ,并且每次执 行 完就 提交 INSERT INTO SELECT 如 果 采用 提示( /* + a pp e nd p arallel */) 可 以处 理 百 万 级别 f r om sm_duty ; 以 保证表增加字段 后 语 句执 行不 受 影响 。 必 须 对数据 进 行 备 份 ,使用 如 下 并且每次执 行 完就 提交 ; 可 以处 理 百 万 级别 7.7.7.7.6666 使用绑定变量使用绑定变量使用绑定变量使用绑定变量 使用“变 量 绑 定”来 处 理一 条 化 S Q L 的 执 行 效率 ,还 可 以 优化 在 J a v a 中,结 合使用 s e tXXX 而 大大优化 了 SQ L 语 句 的 性能 JAVA 情况 下 的 动态绑 定 示 例如 下 St r ing v_id = 'xxxxx'; St r ing v_sq l = 's elec t n a m e f r om tb_ a wh ere id = ? '; stmt = c on . p re p are St a t e m e nt( v_sq l ); stmt . s e tSt r ing( 1, v_id ); // stmt .e x ec ut e Qu er y(); 7.7.7.7.7777 选择最有效率的表名顺序选择最有效率的表名顺序选择最有效率的表名顺序选择最有效率的表名顺序 ORACLE 的 解析 器按照 从 右 到 左 的 顺序处 理 最 后 的表 (基 础 表 d r iving t a b le ) 必 须选择记录条 数 最少 的表作为基 础 表 式 连接 它 们 :首 先 ,扫描 第 一个表 描 第 二 个表 (FROM 子句 中 最 后 第 二 个表 表中合适 记录进 行合 并 。 示 例:表 po l i c y 有 1 8 , 888 选择 po l i c y 作为 基础 表 s elec t c o unt(*) f r om cla im , po l i c y 选择 cla im 作为 基础 表 s elec t c ount(*) f r om po l i c y , cla im 7.7.7.7.8888 关注关注关注关注 WHEREWHEREWHEREWHERE 子句中的连接顺序子句中的连接顺序子句中的连接顺序子句中的连接顺序 ORACLE 采用 自 下 而上 的 顺序解析 其他 WHERE 条件 之前。 第 34 页 使用绑定变量使用绑定变量使用绑定变量使用绑定变量 ,,,,降低高硬解析降低高硬解析降低高硬解析降低高硬解析 来 处 理一 条 SQL 带 不同 常 量 多次执 行的 情况 ,动态 绑 定可 以 大大优 还 可 以 优化 Oracle 的 内 存使用。 s e tXXX 系列 方法 ,可 以 为不同数据类型的 绑 定 变 量 进 行 赋 值 性能 。 情况 下 的 动态绑 定 示 例如 下 : St r ing v_id = 'xxxxx'; St r ing v_sq l = 's elec t n a m e f r om tb_ a wh ere id = ? '; stmt = c on . p re p are St a t e m e nt( v_sq l ); stmt . s e tSt r ing( 1, v_id ); // 为 绑 定 变量赋 值 stmt .e x ec ut e Qu er y(); 选择最有效率的表名顺序选择最有效率的表名顺序选择最有效率的表名顺序选择最有效率的表名顺序 的 解析 器按照 从 右 到 左 的 顺序处 理 FROM 子句 中的表名,因此 ,FROM d r iving t a b le ) 将 被 最先处 理。在 FROM 子句 中包含 多 个表的 情况下 必 须选择记录条 数 最少 的表作为基 础 表 。当 ORACLE 处 理 多 个表时,会 运 用 排 序 及合 并 的 方 扫描 第 一个表 (FROM 子句 中 最 后 的 那 个表 )并 对 记录进 行 排 序 子句 中 最 后 第 二 个表 ),最 后 将 所有 从 第 二 个表中 检 索 出 的 记录 与 第 一个 1 8 , 888 条记录 ;表 cla im 有 1 条记录 作为 基础 表 (不 好 的方法 ) unt(*) f r om cla im , po l i c y 执 行 时 间 2 6 . 09 秒 作为 基础 表 (好 的方法 ) s elec t c ount(*) f r om po l i c y , cla im 执 行 时 间 0 . 96 秒; 子句中的连接顺序子句中的连接顺序子句中的连接顺序子句中的连接顺序 采用 自 下 而上 的 顺序解析 WHERE 子句 ,根 据 这 个原理,表之间的 连接 必 须 写 在 动态 绑 定可 以 大大优 可 以 为不同数据类型的 绑 定 变 量 进 行 赋 值 ,从 FROM 子句 中写 在 子句 中包含 多 个表的 情况下 ,你 会 运 用 排 序 及合 并 的 方 并 对 记录进 行 排 序 ,然后扫 最 后 将 所有 从 第 二 个表中 检 索 出 的 记录 与 第 一个 表之间的 连接 必 须 写 在 示 例: (低效) SEL ECT po l i c y .aa b00 1,cla im .aa b05 1 FR OM po l i c y ,cla im WHER E cla im .aae1 40=’ 31 ’ AND po l i c y .aa b00 1 = cla im .aa b00 1 ; (高效) SEL ECT po l i c y .aa b00 1,cla im .aa b05 1 F R OM po l i c y ,cla im WHER E po l i c y .aa b00 1 = cla im .aa b00 1 AND cla im .aae1 40=’ 31 ’; 7.7.7.7.9999 避免使用避免使用避免使用避免使用 ININININ 操作操作操作操作 实际 情况看 ,使用 e xists 在 许 多 基于基 础 表的 查询 中 种情况下 ,使用 E XISTS( 或 示 例: (低效) SEL ECT * F R OM po l i c y Wh ere aac 00 1 in (s elec t aac 00 1 f r om cla im wh ere aa b00 1 =st r _ aa b00 1 a nd aae1 40=’ 31 ’); 或 SEL ECT * F R OM po l i c y Wh ere aac 00 1 in (s elec t distin c t aac 00 1 f r om cla im wh ere aa b00 1 =st aae1 40=’ 31 ’); (高效) SEL ECT * 第 35 页 SEL ECT po l i c y .aa b00 1,cla im .aa b05 1 FR OM po l i c y ,cla im .aae1 40=’ 31 ’ AND po l i c y .aa b00 1 = cla im .aa b00 1 ; SEL ECT po l i c y .aa b00 1,cla im .aa b05 1 WHER E po l i c y .aa b00 1 = cla im .aa b00 1 AND cla im .aae1 40=’ 31 ’; 操作操作操作操作 ,,,,严禁使用严禁使用严禁使用严禁使用 NOT INNOT INNOT INNOT IN 操作操作操作操作 e xists 替 换 in 效 果 不是 很 明 显 ,基本一 样 。 在 许 多 基于基 础 表的 查询 中 ,为 了 满足 一个 条件 ,往往 需 要对 另 一个表 进 行 联接 或 NOT E XISTS)通常将提 高 查询 的 效率 。 Wh ere aac 00 1 in (s elec t aac 00 1 f r om cla im wh ere aa b00 1 =st r _ aa b00 1 a nd Wh ere aac 00 1 in (s elec t distin c t aac 00 1 f r om cla im wh ere aa b00 1 =st 一个表 进 行 联接 。在 这 Wh ere aac 00 1 in (s elec t aac 00 1 f r om cla im wh ere aa b00 1 =st r _ aa b00 1 a nd Wh ere aac 00 1 in (s elec t distin c t aac 00 1 f r om cla im wh ere aa b00 1 =st r _ aa b00 1 a nd F R OM po l i c y Wh ere e xists (s elec t 1 f r om cla im wh ere aac 00 1 =po l i c y .aac 00 1 a nd aa b00 1 =st r _ aa b00 1 a nd aae1 40=’ 31 ’); in 的常 量 列表是 优 化的 列表相 当 于 o r。 Oracle 在 10 g 之前 版 本 改进 ,但仍然还 是存 在 一 些 问 题 在子查询 中,NOT IN 子句将执 行一个 内 部的 排 序 和合 并 都是 最低效 的 (因 为 它 对 子查询 中的表 执 行 了 一个全表 遍 历 们 可 以 把它 改 写 成 NOT E XISTS 示 例: (低效) SEL ECT * F R OM po l i c y WHER E aa b00 1 NOT IN (SEL ECT aa b00 1 f r om po l i c y wh ere acla i (高效) SEL ECT * F R OM po l i c y WHER E not e xists (SEL ECT 1 f r om po l i c y wh ere aa b00 1 =po l i c y .aa b00 1 a nd acla im0=’ 1 00’); 7.7.7.7.10101010 用表连接替换用表连接替换用表连接替换用表连接替换 在子查询 的表和主表 查询 是 多 对一的 情况 示 例: (低效) SEL ECT po l i c y .* F R OM po l i c y Wh ere e xists (s elec t 1 f r o 第 36 页 Wh ere e xists (s elec t 1 f r om cla im wh ere aac 00 1 =po l i c y .aac 00 1 a nd aa b00 1 =st r _ aa b00 1 a nd aae1 40=’ 31 ’); 的常 量 列表是 优 化的 (例如:aae11 0 in (‘2 0’,’3 0’)) ,不用 e xists 之前 版 本 not in 都是 最低效 的语 句 ,虽 然 在 10 g 上 no t in 但仍然还 是存 在 一 些 问 题 ,因此 我们 一定要使用 not e xists 来 替代 子句将执 行一个 内 部的 排 序 和合 并 。无论 在 哪种情况下 因 为 它 对 子查询 中的表 执 行 了 一个全表 遍 历 )。为 了避免 使用 NOT E XISTS。 WHER E aa b00 1 NOT IN (SEL ECT aa b00 1 f r om po l i c y wh ere acla i m0=’ 1 00’); WHER E not e xists (SEL ECT 1 f r om po l i c y wh ere aa b00 1 =po l i c y .aa b00 1 a nd 用表连接替换用表连接替换用表连接替换用表连接替换 EXISTSEXISTSEXISTSEXISTS 在子查询 的表和主表 查询 是 多 对一的 情况 ,一 般 采用表 连接 的 方 式 比 EXISTS Wh ere e xists (s elec t 1 f r o m cla im wh ere aac 00 1 =po l i c y .aac 00 1 Wh ere e xists (s elec t 1 f r om cla im wh ere aac 00 1 =po l i c y .aac 00 1 a nd e xists 替换 ;in t in 做 到 了 一 些 来 替代 not in 的写 法 。 无论 在 哪种情况下 ,NOT IN 为 了避免 使用 NOT IN ,我 m0=’ 1 00’); WHER E not e xists (SEL ECT 1 f r om po l i c y wh ere aa b00 1 =po l i c y .aa b00 1 a nd EXISTS 更 有 效率 。 (高效) SEL ECT po l i c y .* F R OM po l i c y , cla im Wh ere po l i c y .aac 0 0 1 = cla im .aac 00 1 a nd po l i c y .aa b00 1 = cla im .aa b00 1 a nd cla im .aae1 40=' 31 ' a nd cla im .aae 04 1 =' 2 0080 1 '; 到 底 e xists 和表关 联 哪种 效率 高 如 果差 别 不大实际 上 速 度基本 差 不 多 7.17.17.17.11111 用用用用 EXISTSEXISTSEXISTSEXISTS 替换替换替换替换 当提交 一个包含一对 多 表信息 在 SELECT 子句 中使用 DISTINCT 示 例: (低效) s elec t distin c t po l i c y .aac 00 1 f r om cla im , po l i c y wh ere cla im .aac 00 1 = po l i c y .aac 00 1 a nd cla im .aae1 40=' 31 ' a nd po l i c y .aa b00 1 =' 1 00 1 00'; (高效) s elec t po l i c y .aac 00 1 f r om po l i c y wh ere e xists (s elec t 1 f r om cla im wh ere aac 00 1 = po l i c y .aac 00 1 a nd aae1 40=' 31 ') a nd po l i c y .aa b00 1 =' 1 00 1 00'; 第 37 页 a nd aa b00 1 =po l i c y .aa b00 1 a nd aae1 40=' 31 ' a nd aae 04 1 =' 2 0080 1 '); 0 1 = cla im .aac 00 1 a nd po l i c y .aa b00 1 = cla im .aa b00 1 a nd cla im .aae1 40=' 31 ' a nd cla im .aae 04 1 =' 2 0080 1 '; 和表关 联 哪种 效率 高 ,其实是 根 据 两 个表之间的数据量 差 别 大小是有关的 如 果差 别 不大实际 上 速 度基本 差 不 多 。 替换替换替换替换 DISTINCTDISTINCTDISTINCTDISTINCT 当提交 一个包含一对 多 表信息 (比如 个 人 基本信息表和个 人参 保信息表 DISTINCT。一 般 可 以考 虑 用 E XISTS 替 换。 s elec t distin c t po l i c y .aac 00 1 wh ere cla im .aac 00 1 = po l i c y .aac 00 1 a nd cla im .aae1 40=' 31 ' a nd po l i c y .aa b00 1 =' 1 00 1 00'; s elec t po l i c y .aac 00 1 wh ere e xists (s elec t 1 f r om cla im wh ere aac 00 1 = po l i c y .aac 00 1 a nd po l i c y .aa b00 1 =' 1 00 1 00'; 其实是 根 据 两 个表之间的数据量 差 别 大小是有关的 , 比如 个 人 基本信息表和个 人参 保信息表 )的 查询 时,避免 wh ere e xists (s elec t 1 f r om cla im wh ere aac 00 1 = po l i c y .aac 00 1 EX ISTS 使 查询更 为 迅 速 回结 果 。因此 如 果 不是 特 别研 究 和 追 求速 度的 话 要关 联 其他表的 这种情况 查询 7.17.17.17.12222 用用用用 union allunion allunion allunion all Union 会 去 掉 重复的 记录 下 或可 以 允许有重复 记录 的 话 7.7.7.7.11113333 使用使用使用使用 DECODEDECODEDECODEDECODE 使用 DECODE 函数可 以避免 重复 扫描 相 同 记录 或重复 连接相 同的表 示 例: (低效) s elec t c ount( 1 ) f r om po l i c y wh ere aa b00 1 =’ 1 0000 1 ’ a nd aac 008=’ 1 ’; s elec t c ount( 1 ) f r om po l i c y wh ere aa b00 1 =’ 1 0000 1 ’ a nd aac 008=’ 2 ’; (低效) S elec t c ount( 1 ),aac 008 Fr om po l i c y Wh ere aa b00 1 =’ 1 0 000 1 ’ a nd aac 008 in (’ 1 ’,’ 2 ’) g r oup by aac 008; (高效) s elec t c ount(d ec od e ( aac 008 ,’ 1 ’,’ 1 ’, nu ll )) zz , c ount(d ec od e ( aac 008 ,’ 2 ’,’ 1 ’, nu ll )) tx f r om po l i c y wh ere aa b00 1 =’ 1 0000 1 ’; g r oup by 和 o r d er by 都会 影响 性能 过其他的有 效 的 编 程 办 法去替 换 第 38 页 使 查询更 为 迅 速 ,因 为 R DBMS 核心 模 块将在子查询 的 条件 一 旦 满足后 因此 如 果 不是 特 别研 究 和 追 求速 度的 话 (例如 :数据转换), 查询 一个表的数据 需 要关 联 其他表的 这种情况 查询 ,建议采用 E X ISTS 的 方 式。 union allunion allunion allunion all 替换替换替换替换 unionunionunionunion 会 去 掉 重复的 记录 ,会有 排 序 的 动 作,会 浪 费 时间。因此 在没 有重复 记录 的 情况 下 或可 以 允许有重复 记录 的 话 ,要尽量采用 union all 来关 联 。 DECODEDECODEDECODEDECODE 函数来减少处理时间函数来减少处理时间函数来减少处理时间函数来减少处理时间 函数可 以避免 重复 扫描 相 同 记录 或重复 连接相 同的表 。 s elec t c ount( 1 ) f r om po l i c y wh ere aa b00 1 =’ 1 0000 1 ’ a nd aac 008=’ 1 ’; s elec t c ount( 1 ) f r om po l i c y wh ere aa b00 1 =’ 1 0000 1 ’ a nd aac 008=’ 2 ’; S elec t c ount( 1 ),aac 008 000 1 ’ a nd aac 008 in (’ 1 ’,’ 2 ’) s elec t c ount(d ec od e ( aac 008 ,’ 1 ’,’ 1 ’, nu ll )) zz , c ount(d ec od e ( aac 008 ,’ 2 ’,’ 1 ’, nu ll )) tx wh ere aa b00 1 =’ 1 0000 1 ’; 都会 影响 性能 ,编 程时尽量 避免没 有必要的分组和 排 序 过其他的有 效 的 编 程 办 法去替 换 ,比如上面 的 处 理 办 法 。 核心 模 块将在子查询 的 条件 一 旦 满足后 ,立 刻返 查询 一个表的数据 需 因此 在没 有重复 记录 的 情况 s elec t c ount( 1 ) f r om po l i c y wh ere aa b00 1 =’ 1 0000 1 ’ a nd aac 008=’ 1 ’; s elec t c ount( 1 ) f r om po l i c y wh ere aa b00 1 =’ 1 0000 1 ’ a nd aac 008=’ 2 ’; 编 程时尽量 避免没 有必要的分组和 排 序 ,或者 通 7.17.17.17.14444 避免避免避免避免使用使用使用使用 order byorder byorder byorder by Or d er by 需 要 查询 后排 序 但 我们也 不能 避免 不使用 , 必 须 符合索引,这样 在 速 度 上 会 得到 很 大的 提 升 7.17.17.17.15555 避免使用避免使用避免使用避免使用 HAVINGHAVINGHAVINGHAVING 避免 使用 HA VING 子句 , 处 理 需 要 排 序 ,总计 等操 作 开 销 。 示 例: (低效) SEL ECT aac 008 ,c ount( 1 FR OM po l i c y GR OUP BY aac 008 HAVING aac 008 in (‘ 1 ’,’ 2 ’); (高效) SEL ECT aac 008 ,c ount( 1 ) F R OM po l i c y Wh ere aac 008 in (‘ 1 ’,’ 2 ’) GR OUP BY aac 008 ; HAV ING 中的 条件 一 般 用于对一 些 集合函数的 比较 的 条件 应 该 写 在 WHERE 子句 中 7.7.7.7.16161616 减少多表关联减少多表关联减少多表关联减少多表关联 表关 联 的 越 多 ,查询 速 度 就 越 慢 表 连接 的 操 作,尽量 避免 建立 多 表的关系 禁止 在多 表关 联 的时 候 ,在 非索引字段 上 的关 联 数据转换 上 会存 在 大数据量表的关 联 第 39 页 order byorder byorder byorder by 需 要 查询 后排 序 ,速 度 慢影响 性能,如 果 查询 数据量大,排 序 的时间 就 很 长 ,这样 大 家 一定注 意 一点的是 如 果 使用 o r d er by 这样 在 速 度 上 会 得到 很 大的 提 升 。 HAVINGHAVINGHAVINGHAVING 子句子句子句子句 ,HAV ING 只 会 在检 索 出 所有 记录 之 后 才 对 结 果 集 进 行过 滤 总计 等操 作 。如 果 能 通 过 WHERE 子句 限 制记录 的数目 ,那就 能 减少 这 方面 的 SEL ECT aac 008 ,c ount( 1 ) HAVING aac 008 in (‘ 1 ’,’ 2 ’); SEL ECT aac 008 ,c ount( 1 ) Wh ere aac 008 in (‘ 1 ’,’ 2 ’) 中的 条件 一 般 用于对一 些 集合函数的 比较 ,如 COUNT() 等等 。 子句 中 。 减少多表关联减少多表关联减少多表关联减少多表关联 ,,,,OLTPOLTPOLTPOLTP 系统表关联不超过系统表关联不超过系统表关联不超过系统表关联不超过 3333 个个个个 查询 速 度 就 越 慢 ,在联机事务处 理(OLTP)的应用中 , 尽量 避免 建立 多 表的关系 ,建议表关 联 不要 超 过 3 个(子查询也 属 于表关 联 在 非索引字段 上 的关 联 。 数据转换 上 会存 在 大数据量表的关 联 ,关 联多了 会 影响 索引的 效率 ,可 以 采用建立 临 时 排 序 的时间 就 很 长 。 o r d er by 那么排 序 的列表 只 会 在检 索 出 所有 记录 之 后 才 对 结 果 集 进 行过 滤 。这 个 那就 能 减少 这 方面 的 。除 此 而 外,一 般 个个个个 ,应 该 尽量 避免多 子查询也 属 于表关 联 )。 可 以 采用建立 临 时 表的 办 法 ,有时 更 能 提 高 速 度 7.7.7.7.11117777 避免重复访问避免重复访问避免重复访问避免重复访问 7.7.7.7.11117777.1.1.1.1 使用使用使用使用 gggg rorororo upupupup b b b b yyyy 同 源 单组单 查询 如 下 不符合规范: SEL ECT C LASS, sum( COL) F R OM TA B_TES T WHER E C LA SS=’ A’ UNION A LL SELEC T C LASS, sum( COL) F R OM TA B_TES T WHER E C LA SS=’B’ UNION A LL SEL ECT C LASS, sum( COL) F R OM TA B_TES T WHER E C LA SS=’ C’ 应如 下书写 : SEL ECT C LASS, sum( COL) F R OM TA B_TES T GR OUP BY C LASS 7.7.7.7.11117777.2.2.2.2 用表更新表用表更新表用表更新表用表更新表 一个表同时 更新 另 一个表的 多 个字段 如 下 不符合规范:使 用 UPD ATE T B_TARG ET A S ET A.COL 1 = (s elec t B.COL 1 f r om T B_SOUR CE B wh ere B. id = A. id) , A.COL 2 = (s elec t B.COL 2 f r om T B_SOUR CE B wh ere B. id = A. id) , A.COL 3 = (s elec t B.COL 3 f r om T B_SOUR CE B wh ere B. id = A. id) , A.CO L4 = (s elec t B.CO L4 f r om T B_SOUR CE B wh ere B. id = A. i WHER E A. id IN ( s elec t B. id f r om T B_SOUR CE B) 应如 下书写 : UPD ATE T B_TARG ET A S ET (COL 1, A.CO L 2, A.CO L 3, A.CO L4 )=(S EL ECT B.COL 1, B.COL 2, B.COL 3, B.CO L4 FR OM T B_SOUR CE B WHER E B. id = A. id) WHER E EXI STS (s elec t 1 f r om T B_SOUR CE B wh ere B. id = A. id) 第 40 页 有时 更 能 提 高 速 度 。 避免重复访问避免重复访问避免重复访问避免重复访问 gggg rorororo upupupup b b b b yyyy SEL ECT C LASS, sum( COL) F R OM TA B_TES T WHER E C LA SS=’ A’ UNION A LL T C LASS, sum( COL) F R OM TA B_TES T WHER E C LA SS=’B’ UNION A LL SEL ECT C LASS, sum( COL) F R OM TA B_TES T WHER E C LA SS=’ C’; SEL ECT C LASS, sum( COL) F R OM TA B_TES T GR OUP BY C LASS; 一个表同时 更新 另 一个表的 多 个字段 使 用 TB_SOUR CE 表 更新 表 T B_TARG ET 的多个字段 ET A S ET A.COL 1 = (s elec t B.COL 1 f r om T B_SOUR CE B wh ere B. id = A. id) , A.COL 2 = (s elec t B.COL 2 f r om T B_SOUR CE B wh ere B. id = A. id) , A.COL 3 = (s elec t B.COL 3 f r om T B_SOUR CE B wh ere B. id = A. id) , A.CO L4 = (s elec t B.CO L4 f r om T B_SOUR CE B wh ere B. id = A. i d) WHER E A. id IN ( s elec t B. id f r om T B_SOUR CE B); UPD ATE T B_TARG ET A S ET (COL 1, A.CO L 2, A.CO L 3, A.CO L4 )=(S EL ECT B.COL 1, B.COL 2, B.COL 3, B.CO L4 FR OM T B_SOUR CE B WHER E B. id = A. id) WHER E EXI STS (s elec t 1 f r om T B_SOUR CE B wh ere B. id = A. id) ; SEL ECT C LASS, sum( COL) F R OM TA B_TES T WHER E C LA SS=’ A’ UNION A LL T C LASS, sum( COL) F R OM TA B_TES T WHER E C LA SS=’B’ UNION A LL A.COL 1 = (s elec t B.COL 1 f r om T B_SOUR CE B wh ere B. id = A. id) , A.COL 2 = (s elec t B.COL 2 f r om T B_SOUR CE B wh ere B. id = A. id) , A.COL 3 = (s elec t B.COL 3 f r om T B_SOUR CE B wh ere B. id = A. id) , S ET (COL 1, A.CO L 2, A.CO L 3, A.CO L4 )=(S EL ECT B.COL 1, B.COL 2, B.COL 3, B.CO L4 7.7.7.7.11117777....3333 竖竖竖竖 向向向向 显示变横显示变横显示变横显示变横 向向向向 显示显示显示显示 竖向显示变横向显示 如 下 不符合规范: SEL ECTA.C1 AC1,A.C2AC2,A.C3AC3, B.C1 B C1, B.C2 B C2, B.C3 B C3, C.C1CC1,C.C2CC2,C.C3CC3 FR OM (SELECT' 123 ' X, 'SYNONYM' C1, sum( 2 ) C2,c ount( 1 ) C3 F R OM TAB WHER E TA BTY PE = 'SYNONYM')A, (SELECT' 123 ' X, 'TABLE' C1, sum F R OM TAB WHER E TA BTY PE = 'TABLE')B, (SELECT' 123 ' X, 'VIEW' C1, sum( 2 ) C2,c ount( 1 ) C3 F R OM TAB WHER E TA BTY PE = 'VIEW')C 应如 下书写 : SELECTMAX(DECODE(TABTYPE,'SYNONYM','SYNONYM',NULL)) AC1, MAX(DECODE(TABTYPE,'SYNONYM', sum( 2 ), 0)) MAX(DECODE(TABTYPE,'SYNONYM',c ount( 1 ), 0)) AC3, MAX(DECODE(TABTYPE,'TABLE','TABLE',NULL)) B C1, MAX(DECODE(TABTYPE,'TABLE', sum( 2 ), 0))B C2, MAX(DECODE(TABTYPE,'TABLE',c ount( 1 ), 0))B C3, MAX(DECODE(TABTYPE,'VIEW','VIEW',NULL)) CC MAX(DECODE(TABTYPE,'VIEW', sum( 2 ), 0)) CC2, MAX(DECODE(TABTYPE,'VIEW',c ount( 1 ), 0)) CC3 FR OM TA B WHER E TABTY PE IN ('TABLE','SYNONYM','VIEW') GROUPBY TA BTYPE; 第 41 页 竖竖竖竖 向向向向 显示变横显示变横显示变横显示变横 向向向向 显示显示显示显示 SEL ECTA.C1 AC1,A.C2AC2,A.C3AC3, B.C1 B C1, B.C2 B C2, B.C3 B C3, C.C1CC1,C.C2CC2,C.C3CC3 (SELECT' 123 ' X, 'SYNONYM' C1, sum( 2 ) C2,c ount( 1 ) C3 TAB WHER E TA BTY PE = 'SYNONYM')A, (SELECT' 123 ' X, 'TABLE' C1, sum ( 2 ) C2,c ount( 1 ) C3 TAB WHER E TA BTY PE = 'TABLE')B, (SELECT' 123 ' X, 'VIEW' C1, sum( 2 ) C2,c ount( 1 ) C3 TAB WHER E TA BTY PE = 'VIEW')C SELECTMAX(DECODE(TABTYPE,'SYNONYM','SYNONYM',NULL)) AC1, MAX(DECODE(TABTYPE,'SYNONYM', sum( 2 ), 0)) AC2, MAX(DECODE(TABTYPE,'SYNONYM',c ount( 1 ), 0)) AC3, MAX(DECODE(TABTYPE,'TABLE','TABLE',NULL)) B C1, MAX(DECODE(TABTYPE,'TABLE', sum( 2 ), 0))B C2, MAX(DECODE(TABTYPE,'TABLE',c ount( 1 ), 0))B C3, MAX(DECODE(TABTYPE,'VIEW','VIEW',NULL)) CC 1, MAX(DECODE(TABTYPE,'VIEW', sum( 2 ), 0)) CC2, MAX(DECODE(TABTYPE,'VIEW',c ount( 1 ), 0)) CC3 TABTY PE IN ('TABLE','SYNONYM','VIEW') 7.7.7.7.18 18 18 18 禁止禁止禁止禁止 字段参与表达式运算字段参与表达式运算字段参与表达式运算字段参与表达式运算 SQ L 语 句 的 WHERE 子句 中应尽可能 将 字段 放在等 式 左 边 除非是要 屏蔽 该 字段的的索引 说明:任 何 对字段的 操 作都将 造成此 字段上的 索引 被 屏蔽 的 操 作包括数据 库函 数 、计 算 表达式等等 示 例: 错误的用法: SEL ECT SOME_FIELD F R OM TA BL ENAME WHER E NUM _FIELD / 3 0 < 1 000 正确的用法: SEL ECT SOME_FIELD F R OM TA BL ENAME WHER E NUM _FIELD < 1 000 * 3 0 错误的用法: SEL ECT SOME_FIELD F R OM TA BL ENAME WHER E TO _CHAR(LOGD ATE, 'YYYYMMDD') = ' 1 999 12 0 1 ' 正确的用法: SEL ECT SOME_FIELD F R OM TA BL ENAME WHER E LOGD ATE >= TO _DATE(' 1 999 12 0 1 ', 'YYYYMMDD') AND LOGD ATE < TO _DATE(' 1 999 12 0 2 ', 'YYYYMMDD') 7.7.7.7.19191919 禁止利用禁止利用禁止利用禁止利用 SQLSQLSQLSQL 示 例: 错误的用法: 第 42 页 字段参与表达式运算字段参与表达式运算字段参与表达式运算字段参与表达式运算 子句 中应尽可能 将 字段 放在等 式 左 边 ,将 计 算操 作 放在等 式的 右 边 除非是要 屏蔽 该 字段的的索引 ,否则禁止字段 参 与表 达 式 运算 。 任 何 对字段的 操 作都将 造成此 字段上的 索引 被 屏蔽 ,导致全 表 扫描 计 算 表达式等等 。 WHER E NUM _FIELD / 3 0 < 1 000 ; WHER E NUM _FIELD < 1 000 * 3 0 ; WHER E TO _CHAR(LOGD ATE, 'YYYYMMDD') = ' 1 999 12 0 1 '; WHER E LOGD ATE >= TO _DATE(' 1 999 12 0 1 ', 'YYYYMMDD') AND LOGD ATE < TO _DATE(' 1 999 12 0 2 ', 'YYYYMMDD'); SQLSQLSQLSQL 语句做语句做语句做语句做 业务逻辑的判断或操作业务逻辑的判断或操作业务逻辑的判断或操作业务逻辑的判断或操作 将 计 算操 作 放在等 式的 右 边 , 导致全 表 扫描 ,这里所 谓 业务逻辑的判断或操作业务逻辑的判断或操作业务逻辑的判断或操作业务逻辑的判断或操作 。。。。 SEL ECT S TAFFNO, STAF F R OM T _PUB_STAFF WHER E (i_St a ff N o I S NU LL O R S TAFFNO = i_St a ff N o) AND (i_St a ff Na m e I S NU LL O R S TAFFNAME LIK E '%' || i_St a ff Na m e || '%') 错误分 析 :上面的 SQL 中,对该表进行 全 表 扫描 。 正确的用法:通 过代码 中对 变量 的值进行 判断 然后 决 定 执 行对应的 7.7.7.7.22220000 索引的使用索引的使用索引的使用索引的使用 在 实际的应用 系统中索引问 题导 致性能问 题 可能能 占到 需 要 我们 特 别 关注的。 7.7.7.7.22220000.1.1.1.1 避免在索引列避免在索引列避免在索引列避免在索引列 上上上上 使用函数或使用函数或使用函数或使用函数或 运算运算运算运算 这 个问 题 是 在我们 实际 编 程中 出现 过的 算,查询条件 都不会使用索引 低效 ,索引失效 : S elec t * f r om k a 0 2 wh ere a k a 060=’ 1 000 1 000’ a nd to_ c h ar ( aae 0 3 0 ,’yyyymm’)=’ 2 0080 1 ’; 高效 ,索引 有 效 : S elec t * f r om k a 0 2 wh ere a k a 060=’ 1 000 1 0 aae 0 3 0=to_d a t e (’ 2 0080 1 ’,’yyyymm’); 低效 ,索引失效 : S elec t * f r om k a 0 2 wh ere a k a 060=’ 1 000 1 000’ a nd aae 0 31+1 =sysd a t e ; 高效 ,索引 有 效 : S elec t * f r om ac 04 wh ere aac 00 1 =’ 1 000 1 000’ a nd aae 0 31 =sysd a t e 7.7.7.7.22220000.2.2.2.2 避免避免避免避免 改变改变改变改变 索引列的类型索引列的类型索引列的类型索引列的类型 索引列的 条件如 果 类型不 匹 配 低效 ,索引失效 : 第 43 页 SEL ECT S TAFFNO, STAF FNAME WHER E (i_St a ff N o I S NU LL O R S TAFFNO = i_St a ff N o) AND (i_St a ff Na m e I S NU LL O R S TAFFNAME LIK E '%' || i_St a ff Na m e || '%') SQL 语 句 中,利 用 SQL 引擎 对 变量 的值进行 判断 , 。 通 过代码 中对 变量 的值进行 判断 然后 决 定 执 行对应的 SQL 系统中索引问 题导 致性能问 题 可能能 占到 80 %,在 程 序 优化 上 索引问 题 是 避免在索引列避免在索引列避免在索引列避免在索引列 上上上上 使用函数或使用函数或使用函数或使用函数或 运算运算运算运算 这 个问 题 是 在我们 实际 编 程中 出现 过的 ,请 大 家 一定注 意 。在 索引列 上 使用函数或 运 查询条件 都不会使用索引 。 S elec t * f r om k a 0 2 wh ere a k a 060=’ 1 000 1 000’ a nd to_ c h ar ( aae 0 3 0 ,’yyyymm’)=’ 2 0080 1 ’; S elec t * f r om k a 0 2 wh ere a k a 060=’ 1 000 1 0 aae 0 3 0=to_d a t e (’ 2 0080 1 ’,’yyyymm’); S elec t * f r om k a 0 2 wh ere a k a 060=’ 1 000 1 000’ a nd aae 0 31+1 =sysd a t e ; S elec t * f r om ac 04 wh ere aac 00 1 =’ 1 000 1 000’ a nd aae 0 31 =sysd a t e 避免避免避免避免 改变改变改变改变 索引列的类型索引列的类型索引列的类型索引列的类型 索引列的 条件如 果 类型不 匹 配 ,则不能使用索引。 AND (i_St a ff Na m e I S NU LL O R S TAFFNAME LIK E '%' || i_St a ff Na m e || '%') ,导致 在 使 用 过 程 SQL 语 句 。 在 程 序 优化 上 索引问 题 是 在 索引列 上 使用函数或 运 S elec t * f r om k a 0 2 wh ere a k a 060=’ 1 000 1 000’ a nd S elec t * f r om k a 0 2 wh ere a k a 060=’ 1 000 1 0 00’ a nd S elec t * f r om k a 0 2 wh ere a k a 060=’ 1 000 1 000’ a nd aae 0 31+1 =sysd a t e ; S elec t * f r om ac 04 wh ere aac 00 1 =’ 1 000 1 000’ a nd aae 0 31 =sysd a t e -1 ; S elec t * f r om p o l i c y wh ere aac 00 1 = 1 000 1 000; 高效 ,索引 有 效 : S elec t * f r om po l i c y wh ere aac 00 1 =’ 1 000 1 000’; 7.7.7.7.22220000.3.3.3.3 避免在索引列避免在索引列避免在索引列避免在索引列 上上上上 使用使用使用使用 避免在 索引列 上 使用 NOT, NOT 使用索引的,索引 只 能 告诉 你什么 存 在 于表中 低效 ,索引失效 : s elec t * Fr om cla im Wh ere not a po l i c y9=’ 1 0’; 高效 ,索引 有 效 : s elec t * Fr om cla im Wh ere a po l i c y9 in (’ 2 0’ ,’ 3 0’); 7.7.7.7.22220000.4.4.4.4 用用用用>=>=>=>=替替替替 代代代代 >>>> 虽 然 效 果 不是 特 别 明 显 低效 : SEL ECT * FR OM po l i c y WHER E a po l i c y9 > ‘ 1 0’ 高效 : SEL ECT * FR OM po l i c y WHER E a po l i c y9 >=’ 1 两 者 的 区别 在于, 前 者 a po l i c y9 大 于 1 0 的 记录 ,而后 者 第 44 页 o l i c y wh ere aac 00 1 = 1 000 1 000; S elec t * f r om po l i c y wh ere aac 00 1 =’ 1 000 1 000’; 避免在索引列避免在索引列避免在索引列避免在索引列 上上上上 使用使用使用使用 NOTNOTNOTNOT NOT, NOT 不会使 查询条件 使用索引。对于 ! = 这样 的 判断也 是不能 索引 只 能 告诉 你什么 存 在 于表中 , 而 不能 告诉 你什么 不存 在 于表中 Wh ere not a po l i c y9=’ 1 0’; Wh ere a po l i c y9 in (’ 2 0’ ,’ 3 0’); 虽 然 效 果 不是 特 别 明 显 ,但 建议采用 这种 方 式。 WHER E a po l i c y9 > ‘ 1 0’ ; 10’ 前 者 DBMS 首先定 位 到 a po l i c y9= 1 0 的 记录并 且向前 扫描 到 第 一个 而后 者 DBMS 将直接 跳 到 第 一个 a po l i c y9 等于 1 0 这样 的 判断也 是不能 而 不能 告诉 你什么 不存 在 于表中 。 的 记录并 且向前 扫描 到 第 一个 1 0 的 记录 。 7.7.7.7.22220000.5 .5 .5 .5 避免在索引列避免在索引列避免在索引列避免在索引列 上上上上 使用使用使用使用 对于索引列使用 is nu ll 中,所 以 WHERE 子句 中对索引列 进 行空 值比较将 使 低效 ,索引失效 : s elec t * f r om po l i c y wh ere a po l i c y9 is not nu ll ; 高效 ,索引 有 效 : s elec t * f r om po l i c y wh ere a po l i c y9 in(‘ 1 0’ ,’ 2 0’ ,’ 3 0’) 在实际 开 发中,对于这 类 的 问题 很 难避免 可以 忽略 。 7.7.7.7.22220000.6 .6 .6 .6 进行进行进行进行 模糊查询模糊查询模糊查询模糊查询 时时时时 带 通 配 符(%)的 l ik e 低效 ,索引失效 : S elec t * f r om po l i c y wh ere aac 00 2 l ik e ‘% 21 0 1 04’; S elec t * f r om po l i c y wh ere aac 00 2 l ik e ‘% 21 0 1 04%’; 高效 ,索引 有 效 : S elec t * f r om po l i c y wh ere aac 00 2 l 7.7.7.7.22220000.7 .7 .7 .7 总总总总 是是是是 使用索引的使用索引的使用索引的使用索引的 第第第第 一个列一个列一个列一个列 如 果 索引是建立 在多 个列 上 择 使用 该 索引。如 Cla im 的复合索引 低效 ,索引失效 : S elec t * f r om cla im wh ere aae1 40=’ 31 ’ a nd aae 04 1 =’ 2 0080 1 ’; 高效 ,索引 有 效 : S elec t * f r om cla im wh ere aac 00 1 =’ 1 000 1 000’ 如果不 使 用 索引第 一列 基本 上不会 使 用 索 使 用复合 索引 的列 越 多,查询 的 速 度就 越快 第 45 页 避免在索引列避免在索引列避免在索引列避免在索引列 上上上上 使用使用使用使用 IS NULLIS NULLIS NULLIS NULL 和和和和 IS NOT NULLIS NOT NULLIS NOT NULLIS NOT NULL is nu ll 或 is not nu ll 不会使用 上 索引。因 为空 值 不存 在 于索引列 子句 中对索引列 进 行空 值比较将 使 ORACLE 停 用 该 索引。 s elec t * f r om po l i c y wh ere a po l i c y9 is not nu ll ; s elec t * f r om po l i c y wh ere a po l i c y9 in(‘ 1 0’ ,’ 2 0’ ,’ 3 0’) ; 对于这 类 的 问题 很 难避免 ,如果不是 特别影响速 度 或者 要求 速 度的 进行进行进行进行 模糊查询模糊查询模糊查询模糊查询 时时时时 ,,,,禁止条件中字符禁止条件中字符禁止条件中字符禁止条件中字符 串直接以串直接以串直接以串直接以 ““““%%%%”””” l ik e 语 句 ,%在常 量前 面 索引 就 不会使用。 S elec t * f r om po l i c y wh ere aac 00 2 l ik e ‘% 21 0 1 04’; S elec t * f r om po l i c y wh ere aac 00 2 l ik e ‘% 21 0 1 04%’; S elec t * f r om po l i c y wh ere aac 00 2 l ik e ‘ 21 0 1 04%’; 总总总总 是是是是 使用索引的使用索引的使用索引的使用索引的 第第第第 一个列一个列一个列一个列 如 果 索引是建立 在多 个列 上 , 只 有 在 它 的 第 一个列 被 wh ere 子句 引用时 的复合索引 :aac001、aae140、aae041 S elec t * f r om cla im wh ere aae1 40=’ 31 ’ a nd aae 04 1 =’ 2 0080 1 ’; S elec t * f r om cla im wh ere aac 00 1 =’ 1 000 1 000’ ; 如果不 使 用 索引第 一列 基本 上不会 使 用 索 引,使 用 索引 要 按照索引 的 顺序使 用 查询 的 速 度就 越快 。 因 为空 值 不存 在 于索引列 ; 如果不是 特别影响速 度 或者 要求 速 度的 , ””””开开开开 头头头头 子句 引用时 ,优化器 才 会 选 S elec t * f r om cla im wh ere aae1 40=’ 31 ’ a nd aae 04 1 =’ 2 0080 1 ’; 使 用 索引 要 按照索引 的 顺序使 用 ,另 外 8 8 8 8 编码及注释规范编码及注释规范编码及注释规范编码及注释规范 8.8.8.8.1 1 1 1 编码规范编码规范编码规范编码规范 8.1.1 8.1.1 8.1.1 8.1.1 严严严严 禁禁禁禁 潜潜潜潜 在的数据类型转换在的数据类型转换在的数据类型转换在的数据类型转换 SQ L 语 句 的 WHERE 子句 中 每 个 条件 的 操 作符 两 边 类型应 相 同 说明:潜 在的字段数据 类型转换 将 造成索引 被 屏蔽 据与数值 型 数据比 较 ,ORACLE 示 例: 错误的用法: 表 TA BL ENAME 中的列 STRING_FIELD SEL ECT SOMEFIELD F R OM TA BL ENAME WHER E STRING_FIELD > 1 0 正确的用法: SEL ECT SOMEFIELD F R OM TA BL ENAME WHER E STRING_FIELD > ' 1 0' 8.1.2 8.1.2 8.1.2 8.1.2 不要将空的不要将空的不要将空的不要将空的 变变变变 量量量量 值直接值直接值直接值直接 与与与与 比较运算比较运算比较运算比较运算 符符符符 不要 将 空的 变 量 值直接 与 比较运算 符 或 is not nu ll 或 nv l 函数 进 行 比较 以 下代码 不符合规范 if v_us er _ n a m e = nu ll th e n dbms_output . put_ l in e (‘us er n a m e is nu ll ’); e nd if; 应该如 下书写 : if v_us er _n a m e is nu ll th e n 第 46 页 编码及注释规范编码及注释规范编码及注释规范编码及注释规范 严严严严 禁禁禁禁 潜潜潜潜 在的数据类型转换在的数据类型转换在的数据类型转换在的数据类型转换 子句 中 每 个 条件 的 操 作符 两 边 类型应 相 同 ,禁止 潜在 的数据类型转换 潜 在的字段数据 类型转换 将 造成索引 被 屏蔽 ,导致全 表 扫描 。 ORACLE 会 自动 将字符 类型 字段用 TO _NUMBER 函 数进行 转换 STRING_FIELD 是字符 型 (VARCHAR),则以 下 语 句 存在 类型转换 WHER E STRING_FIELD > 1 0 ; WHER E STRING_FIELD > ' 1 0' ; 不要将空的不要将空的不要将空的不要将空的 变变变变 量量量量 值直接值直接值直接值直接 与与与与 比较运算比较运算比较运算比较运算 符符符符 ((((符符符符 号号号号))))比较比较比较比较 不要 将 空的 变 量 值直接 与 比较运算 符 (符 号)比较 。如 果 变 量可能为空 , 函数 进 行 比较 。 n a m e = nu ll th e n dbms_output . put_ l in e (‘us er n a m e is nu ll ’); if v_us er _n a m e is nu ll th e n 禁止 潜在 的数据类型转换 。 。例如将字符 型 数 函 数进行 转换 。 句 存在 类型转换 : ,应使用 is nu ll dbms_output . put_ l in e (‘us er n a m e is nu ll ’); e nd if; 8.1.3 8.1.3 8.1.3 8.1.3 跨跨跨跨 行语行语行语行语 句句句句 ,,,,第第第第 一关键字一关键字一关键字一关键字 应当左应当左应当左应当左 对对对对 齐齐齐齐 同一 条 语 句 占 用 多 于一行时 示 例 s elec t fi el d 1, fi el d 2, … f r om t_t a b le n a m e wh ere fi el d 1 > 1 a nd fi le d 2 t r un c (sys 应如 下书写 : s elec t w e. wip_ e ntity_n a m e, wdj . wip_ e ntity_id , wdj . d a t e _ relea s e d f r om wip . wip_ e ntiti e s w e, wip . wip_dis cre t e _jobs wdj wh ere w e. wip_ e ntity_id = wdj . wip_ e ntity_id a nd wdj . st a tus_typ e = 3 a nd wdj . d a t e _ relea s e d > t r un c (sysd a t e ) 8.1.9 8.1.9 8.1.9 8.1.9 其他其他其他其他 编编编编 码规范码规范码规范码规范 ¢ 程 序块 采用 缩 进风格书 写 缩 进格 数统一为 2 ¢ 必 须 使用空 格 ,不允许使用 ¢ 不允许 把 多 个语 句 写 在 一行中 示 例: v_ c ount : = 1 ; 第 50 页 语语语语 句涉句涉句涉句涉 及及及及 多多多多 个表时个表时个表时个表时 ,,,,使用使用使用使用 别别别别 名来名来名来名来 限限限限 定字段名定字段名定字段名定字段名 SQ L 语 句 中 涉 及 到 多 个表时,始 终 使用 别 名来限定字段名 避免了 含义模 糊 的引用 ,其中能 够通 过 别 名中 清 晰 地 判断出 表名 尽量 避 逸 使用 无 意 义的 代号 a、b 、c… , 而 应 该 有 意 义 yst e m _ it e ms _ b 对应 别 名为 msi ,po _h ea d er s _ all 别 名对应为 : s elec t wip_ e ntity_n a m e,a. wip_ e ntity_id ,a. d a t e _ relea s e d f r om wip . wip_ e ntiti e s b , wip . wip_dis cre t e _jobs a wh ere b . wip_ e ntity_id = a. wip_ e ntity_id a nd a. st a tus_typ e = 3 a nd a. d a t e _ relea s e d > t r un c (sys d a t e ) s elec t w e. wip_ e ntity_n a m e, wdj . wip_ e ntity_id , wdj . d a t e _ relea s e d f r om wip . wip_ e ntiti e s w e, wip . wip_dis cre t e _jobs wdj wh ere w e. wip_ e ntity_id = wdj . wip_ e ntity_id a nd wdj . st a tus_typ e = 3 a nd wdj . d a t e _ relea s e d > t r un c (sysd a t e ); 其他其他其他其他 编编编编 码规范码规范码规范码规范 进风格书 写 ,保证 代码准确 高 效 、清 晰 易 读、易 于 维护 2 个。 不允许使用 TAB 键。 不允许 把 多 个语 句 写 在 一行中 ,即 一行 只 写一 条 语 句 。 始 终 使用 别 名来限定字段名 ,这 使其 其中能 够通 过 别 名中 清 晰 地 判断出 表名 ,别 而 应 该 有 意 义 。 别 名对应为 p h a。 s elec t w e. wip_ e ntity_n a m e, wdj . wip_ e ntity_id , wdj . d a t e _ relea s e d 易 于 维护 ,风格 一致, v_ crea tion_d a t e : = sysd a t e ; ¢ 避免将 SQ L 语 句 写 到 同一行 示 例: s elec t duty_id , duty_n a m e f r om sm_duty wh ere duty_id = : duty_id; ¢ 相 对 独 立的程 序块 之间必 须 加空行 示 例: v_duty_id : = 1 ; if t r un c (nv l (dis a b le d_d a t e, sysd a t e + 1 )) > t r un c (sysd a t e ) th e n s elec t duty_n a m e f r om sm_duty wh ere duty_id = : duty_id; … e nd if; ¢ 超 过 110 列的语 句 要分行 书 写 键字 放在新 行之 首 示例 : 以 下 不符合规范 ( a *b* c *d) + ( e *f) + … 应 写成 : ( a *b* c *d) + ( e *f) + … 说明: A. 加法的 优 先 级低 于 乘 法 B. 两组 乘 法 虽 然在 逻辑 上会先于加法 ¢ b e gin 、e nd 独 立 成 行 示 例: 第 51 页 v_ crea tion_d a t e : = sysd a t e ; 语 句 写 到 同一行 ,再 短 的语 句也 要 在 关键字和 谓 词 处 换行 wh ere duty_id = : duty_id; 相 对 独 立的程 序块 之间必 须 加空行 。 if t r un c (nv l (dis a b le d_d a t e, sysd a t e + 1 )) > t r un c (sysd a t e ) th e n wh ere duty_id = : duty_id; 列的语 句 要分行 书 写 ,长表 达 式应 在低先级操 作符 处 换行 键字 放在新 行之 首 。划 分 出新 行应 当 适 当 地缩 进 ,使 排 版 整齐 ,语 句 可读 ( a *b* c *d) + ( e *f) + … 加法的 优 先 级低 于 乘 法 ,因此 应在加号 处折 行。 两组 乘 法 虽 然在 逻辑 上会先于加法 ,但显 示 加上括号 使 用可 读 性 更强 独 立 成 行 。 再 短 的语 句也 要 在 关键字和 谓 词 处 换行 。 if t r un c (nv l (dis a b le d_d a t e, sysd a t e + 1 )) > t r un c (sysd a t e ) th e n 长表 达 式应 在低先级操 作符 处 换行 ,操作符或关 语 句 可读 。 但显 示 加上括号 使 用可 读 性 更强 。 示 例:以 下 不符合规范 b e gin nu ll ; e x ce ption wh e n oth er s th e n nu ll ; e nd; 应 写成 : b e gin nu ll ; e x ce ption wh e n oth er s th e n nu ll ; e nd; ¢ i f 后 的 条件 要用 括 号 括 起 来 示 例: if (v_ c ount = 1 o r v_ c ount = 2 o r v_ c ount = 5 o r v_ c ount = 6 ) th e n s elec t sysd a t e into v_d a t e f r om du al ; e nd if; ¢ 不同类型的 操 作符 混 合使用时 示 例: if (‘ a b c ’||’d e f’) = ‘ a b c d e f’ th e n nu ll ; e nd if; ¢ 确 保 变 量和 参 数 在 类型和长度与表数据列类型和长度 相 匹 配 示 例: 说明:如果与表数据列 宽 度不 匹 配 如 fnd_us er s 表 us er _n a m e d eclare v_us er _n a m e v arc h ar2 ( 1 0); b e gin 第 52 页 以 下 不符合规范 b e gin nu ll ; e x ce ption wh e n oth er s th e n nu ll ; e nd; 后 的 条件 要用 括 号 括 起 来 ,括 号内每 行 最多 两 个 条件 。 f (v_ c ount = 1 o r v_ c ount = 2 o r v_ c ount = 5 o r v_ c ount = 6 不同类型的 操 作符 混 合使用时 ,建议使用 括 号进 行 隔离 ,以 使 代码 清 晰 if (‘ a b c ’||’d e f’) = ‘ a b c d e f’ th e n 确 保 变 量和 参 数 在 类型和长度与表数据列类型和长度 相 匹 配 。 如果与表数据列 宽 度不 匹 配 ,则 当较宽或较大 的 数据传进来 时 会 产 生 运 行 异 常 us er _n a m e 字符 宽 为 50 ,当 用 户 名 大 于 1 0 时 会 报 错 v_us er _n a m e v arc h ar2 ( 1 0); 以 使 代码 清 晰 。 数据传进来 时 会 产 生 运 行 异 常 。 时 会 报 错 。 s elec t fu . us er _n a m e into v_us er _n a m e f r om fnd_us er fu wh ere fu . us er _id = p_us er _id; e nd; ¢ 对于非 常 复 杂 的 s q l ( 计不 当 引 起 的,对于复 杂 的一 些 只 做 一 件事 情 。 ¢ 尽可能 地 使用 相 关表字段的类型定义 ¢ 存储过程中 变 量的 声 明 应集中 在 定义 变 量。定义 变 量时 形量应空行 隔 开 , ¢ o r d er by 后 面 字段不唯一时分 页 会 出现 问 题 不唯一,一定要 让 追 加 r o w id ,o r d er by f i r st _ r o w s 。 ¢ 当 存储过程有 多 个分 支 返 回 时 ¢ in 、out 参 数应按类 别 分开 书 写 rec o r d ,尽量都 带 上 ¢ 禁止对 VARCHAR( 2000 ) 之类的 操 作。此 类 操 作 将 消 耗 大量的 ¢ 禁止使用 COUNT(*),COUNT(*) 8.2 8.2 8.2 8.2 注释规范注释规范注释规范注释规范 8.2.1 8.2.1 8.2.1 8.2.1 注释语注释语注释语注释语 法法法法 注释语 法 包含 两种情况 ¢ 单行注释:注释前有 两 个 连 字符 ¢ 多 行注释:符 号 /* 第 53 页 s elec t fu . us er _n a m e wh ere fu . us er _id = p_us er _id; s q l ( 特 别 是 多层嵌套 ,带 子句 或 相 关的 查询),应 该先考 虑 是否设 对于复 杂 的一 些 s q l 可 以考 虑 使用程 序 实 现 ,原 尽可能 地 使用 相 关表字段的类型定义 ,形 如%typ e、% r o w typ e。 存储过程中 变 量的 声 明 应集中 在 a s 和 b e gin 关键字之间,不允许 在代码 中 随意 定义 变 量时 ,完 成相 同 功 能模 块 的 变 量应 放在 一 起 , ,增加 代码 的可读性。 后 面 字段不唯一时分 页 会 出现 问 题 ,分 页 时 如 果 o r d er by 一定要 让 o r d er by 唯一,最 佳 方 案 是增加一 pk ,如 实 在没 办 法 则可 以 o r d er by 后 尽量 避免 使用 r o w id 。分 页 可 以考 虑 优化器设 置 为 当 存储过程有 多 个分 支 返 回 时 ,若 有 事务 ,需确 保各个分 支 都 结 束 了事务 参 数应按类 别 分开 书 写 ,不要 交 叉 ,对于 out 参 数,特 别 是 尽量都 带 上 no c opy ,提 高程 序 的 运 行 效率 。 VARCHAR( 2000 ) 之类的大字段 值进 行 OR D ER B Y、DISTINCT、 此 类 操 作 将 消 耗 大量的 CPU 和 内 存 资 源 。 COUNT(*),COUNT(*) 会对全字段 做 聚 集,建议 通 过索引列 检 索计 注释语 法 包含 两种情况 :单行注释、多 行注释。 注释前有 两 个 连 字符 (--)。 /* 和*/ 之间的 内容 为注释 内容 。 应 该先考 虑 是否设 原则 上 遵循一 句 话 不允许 在代码 中 随意 ,与不同模 块 的 变 o r d er by 后 面 的字段 如 实 在没 办 法 则可 以 分 页 可 以考 虑 优化器设 置 为 需确 保各个分 支 都 结 束 了事务 。 特 别 是 n e stt a b le、 、GROU P B Y、UNION 建议 通 过索引列 检 索计 算。 8.2.2 8.2.2 8.2.2 8.2.2 每每每每 个个个个 块块块块 和过和过和过和过 程程程程 开发开发开发开发 放置放置放置放置 注释注释注释注释 在每 一个 块 和过程(存储过程 /***************************************************************** *n a m e : -- 程 序 名 *fun c tion : -- 程 序功 能 *input : --输 入 参 数 *output : -- 输 出 参 数 * a utho r : -- 作 者 * Crea t e D a t e : -- 创建 时 间 *U pd a t e D a t e : -- 程 序更改 信息 ******************************************************************/ 8.2.3 8.2.3 8.2.3 8.2.3 代码注释代码注释代码注释代码注释 应放应放应放应放 在其在其在其在其 上方上方上方上方 或或或或 右方右方右方右方 注释应与其 描述 的 代码相 似 位 置 ,不可 放在 下 面 。 示 例: -- 注释 放在上方 或右 方 s elec t po l i c y_no ,-- 保单 号 f r om po l i c y wh ere po l i c y_no =‘00000000000000000 7 ’; 8.2.4 8.2.4 8.2.4 8.2.4 其他注释规范其他注释规范其他注释规范其他注释规范 ¢ 创 建 每 一数据库对象时都要加 上 建表时,对数据列 也 要加 上 ¢ 一 般情况下 ,源 程 序 有 效 注释量 须在 ¢ 注释 内容 要 清 晰 、 ¢ 禁止 在 注释中使用 缩 写 ¢ 对存储过程的 任何修 改 第 54 页 每每每每 个个个个 块块块块 和过和过和过和过 程程程程 开发开发开发开发 放置放置放置放置 注释注释注释注释 存储过程 、函数、包、触发器、视图 等 )的开 头放置 注释 /***************************************************************** 程 序功 能 输 出 参 数 创建 时 间 程 序更改 信息 (包括作 者、时 间 、更改 内 容等) ******************************************************************/ 代码注释代码注释代码注释代码注释 应放应放应放应放 在其在其在其在其 上方上方上方上方 或或或或 右方右方右方右方 注释应与其 描述 的 代码相 似 ,对 代码 注释应 放在 其 上方 或 右方(对单 条 语 句 的注释 注释 放在上方 或右 方 保单 号 wh ere po l i c y_no =‘00000000000000000 7 ’; 其他注释规范其他注释规范其他注释规范其他注释规范 创 建 每 一数据库对象时都要加 上 COMMENT ON 注释,以 说明 该 对象的 功 能和用 途 对数据列 也 要加 上 COMMENT ON 注释,以 说明 该 列 取 值 的含义 源 程 序 有 效 注释量 须在 30 %以上 。 、明 了 、含义 准确 ,防 止注释 二 义性。 禁止 在 注释中使用 缩 写 ,特 别 是非 常 用的 缩 写。 对存储过程的 任何修 改 ,都 需 要 在 注释 最 后 添加 修 改人 、修 改 日 期及 修 改 原 因 等 的开 头放置 注释 /***************************************************************** ******************************************************************/ 对单 条 语 句 的注释 )相 应 以 说明 该 对象的 功 能和用 途 ; 以 说明 该 列 取 值 的含义 。 修 改 日 期及 修 改 原 因 等 信息。 ¢ 对程 序 分 支 必 须书 写注释 ¢ 在代码 的 功 能、意 图 层次上进 行注释 ¢ 注释与所 描述 的 内容进 行同 样 的 缩排 ¢ 注释 上面 的 代码 应空行 隔 开 9999 PLSQLPLSQLPLSQLPLSQL 优化优化优化优化 9999.1.1.1.1 性能问题分析性能问题分析性能问题分析性能问题分析 出现 性能问 题 ,需 要 从 整 体 进 行分 析 ¢ 整 个系统 运 行 速 度都 慢  -- 在 业务 不 忙 的 时 候  -- 只有在 业务 繁忙 的 时 候  -- 时快时 慢 。 ¢ 个 别业务 模块 运 行 速 度 慢  -- 在 业务 不 忙 的 时 候  -- 只有在 业务 繁忙 的 时 候  -- 时快时 慢 。 一 般 导 致系统性能 慢 的 因 素 ¢ 硬 件 :客 户 端 、服 务 器 ¢ 网络 :网 速低、丢 包 ¢ 操 作系统 参 数设 置 ¢ 受 到其 他 软 件 干扰 ¢ W e b l ogi c 设 置 :参 数设 置 不合 理 ¢ Oracle 设 置 :内 存 ¢ PLSQL 程 序 :p l sq l 在 不同 现 象 下 ,可能 导 致性能问 题 的 因 素 ¢ 一般来说,如果在不 办理 业务 的情况 下 程 序 优 化是没有关系的 第 55 页 对程 序 分 支 必 须书 写注释 。 意 图 层次上进 行注释 ,提 供 有用、额 外的信息。 注释与所 描述 的 内容进 行同 样 的 缩排 。 注释 上面 的 代码 应空行 隔 开 。 优化优化优化优化 需 要 从 整 体 进 行分 析 ,一 般 总体 上 会有 几种 现 象: 整 个系统 运 行 速 度都 慢 在 业务 不 忙 的 时 候 ,所有 模块 都 慢 ; 只有在 业务 繁忙 的 时 候 ,所有 模块 都 慢 ; 个 别业务 模块 运 行 速 度 慢 在 业务 不 忙 的 时 候 ,该 模块 就 慢 ; 只有在 业务 繁忙 的 时 候 ,该模块 才 慢 ; 一 般 导 致系统性能 慢 的 因 素 : 服 务 器 CPU、内 存和存储设 备 配 置 不符合应用系统要求 丢 包 ; 操 作系统 参 数设 置 :参 数设 置 不合 理 ; 受 到其 他 软 件 干扰 :例如:防火墙 、病毒 ; 参 数设 置 不合 理 ; 内 存 、表 空 间 、re do l og、系统 参 数设 置 不合 理 等 ; p l sq l 不 优 化 、未 使 用 索引、锁 表。 可能 导 致性能问 题 的 因 素 : 如果在不 办理 业务 的情况 下 ,整 个系统性能就 慢 的话 优 化是没有关系的 。可能的 因 素 为 硬 件、网络 、操 作系统 、其 他 软 件 干扰 内 存和存储设 备 配 置 不符合应用系统要求 ; ; 整 个系统性能就 慢 的话 ,应该和 P LSQL 其 他 软 件 干扰 、ORACLE 设 置 。 ¢ 只有在 业务 繁忙 的 时 候 WEBLOG IC 设 置、ORACLE 就和 w e b l ogi c 没有关系 ¢ 一般来说,如果在不 办理 业务 的情况 下 就是 P LSQL 程 序 不 优 化 或 未 使 用 索引造成 的 ¢ 只有在 业务 繁忙 的 时 候 WEBLOG IC 设 置、ORACLE 这 里我们 重点是 说明 PLSQL 统和 o racle 设 置等 因 素 我们 不 进 行 说明 9999....2 PLSQL2 PLSQL2 PLSQL2 PLSQL 优化的核心思想优化的核心思想优化的核心思想优化的核心思想 出现 PLSQL 优化实际 上 就 是 避免出现 以 及性能问 题跟 踪 应 该 本 着这 个 核心思想去考 虑 和 解 决问 题 ¢ PLSQL 程 序 占 用 CPU  -- 系统 解 析 SQL 语 句执 行  -- 运算 (计 算 )会 消耗 ¢ PLSQL 程 序 占 用 内 存的情况  -- 读写 数据都需要 访问 内 存  -- 内 存不足 时 ,也会 使 用 磁盘 ¢ PLSQL 程 序 增 大 IO  -- 读写 数据都需要 访问 磁盘  -- 读 取 的数据 越 多 大 家 都 知道 CPU 现在 都 很 高 问 相 对前 两 个 相比 速 度 就差 的非 常 大 了 换 句 话说就 是尽量 减少 IO 的访问 性能的优 先级 CPU->内 存 化的 核心思想 为: ¢ 避免过 多复 杂 的 SQL 第 56 页 只有在 业务 繁忙 的 时 候 ,整 体系统性能都 慢 ,有可能的 因 素 有 硬 件 、 ORACLE 设 置、PLSQL 程 序 。如果在 sq l p l us 下做查询 都 慢 没有关系 。 如果在不 办理 业务 的情况 下 ,个 别业务 模块 速 度就 慢 的话 程 序 不 优 化 或 未 使 用 索引造成 的 。 只有在 业务 繁忙 的 时 候 ,个 别 模块慢 ,有可能的 因 素 有 硬 件、操 作系统设 置 ORACLE 设 置、PLSQL 程 序、锁 表。 PLSQL 优化、索引优化的问 题 ,其他 例如 :硬件 设 置等 因 素 我们 不 进 行 说明 。 优化的核心思想优化的核心思想优化的核心思想优化的核心思想 优化实际 上 就 是 避免出现 “导 致性能问 题 的 内在 原 因 ”,实际 上编 写程 序 以 及性能问 题跟 踪 应 该 本 着这 个 核心思想去考 虑 和 解 决问 题 。 CPU 的情况 语 句执 行 ,会 消耗 CPU 的 使 用; 会 消耗 CPU 的 使 用。 程 序 占 用 内 存的情况 读写 数据都需要 访问 内 存 ; 也会 使 用 磁盘 。 IO 的情况 读写 数据都需要 访问 磁盘 IO; 读 取 的数据 越 多 ,IO 就 越大 。 现在 都 很 高 ,计 算 速 度非 常 快;访问 内 存的 速 度 也很 快 问 相 对前 两 个 相比 速 度 就差 的非 常 大 了 ,因此 PLSQL 性能优化的重点 也 就 是 减少 的访问 。 内 存 -> IO,影响 性能的 因 素 依 次 递 增。根 据 上面 的分 析 SQL 脚 本 ,减 少系统的 解 析过 程; 、操 作系统设 置、 下做查询 都 慢 ,那么 个 别业务 模块 速 度就 慢 的话 ,那么 基本 上 操 作系统设 置 、 硬件 、网络 、操 作系 实际 上编 写程 序 , 访问 内 存的 速 度 也很 快 ;但磁盘 的访 性能优化的重点 也 就 是 减少 IO 的 瓶颈 , 根 据 上面 的分 析 ,PLSQ L 优 ¢ 避免过 多的 无 用的计 算 ¢ 避免 浪费内 存 空 间没有必要的 ¢ 内 存中计 算 和 访问速 度很 快 ¢ 尽 可能的 减 少 磁盘 的 访问 的数据 量 ¢ 尽 可能的 减 少 磁盘 的 访问 的 次 数 PLSQ L 优化、索引不使用的问 题产 生的 影响 ¢ 对于某段不 优 化的程 序或 语 句 频繁 或者全 表 扫描 一个表 时 占 用的 吞吐 量 是很 高 的 能 下 降 。 ¢ 对于某段不 优 化的程 序或 语 句 频繁 或者全 表 扫描 一个表 时 问同一个表 时 ,速 度将 大大下 降 ¢ 如果是 更新 表 操 作 时 间 长 出 现 压力 问题 ,导致 这个系统性能 下 降 我们 一 般根 据 这些 现 象 围。不过对于 这 一点,还 是 比较 困难 的 联。 9999....3 PLSQL3 PLSQL3 PLSQL3 PLSQL 优化示例优化示例优化示例优化示例 9999.3.1 .3.1 .3.1 .3.1 减少减少减少减少 对表的对表的对表的对表的 查询查询查询查询 该 问 题 是 我们编 程中 出现 过的问 题 大性能的 提 升 。 示 例: 低效 c u r so r c u r _k c2 4_mz is S elec t a k c2 60 f r om k c2 4 wh ere a kb0 2 0 =st r _ a kb0 2 0 a nd a k a13 0=’ 11 ’; 第 57 页 用的计 算 ,例如:死循环 ; 避免 浪费内 存 空 间没有必要的 SQL 脚 本 ,导致 内 存不足; 内 存中计 算 和 访问速 度很 快 ; 尽 可能的 减 少 磁盘 的 访问 的数据 量 ,该 原 则是 P LSQL 优 化中重要 思想 尽 可能的 减 少 磁盘 的 访问 的 次 数 ,该 原 则是 P LSQL 优 化中重要 思想 索引不使用的问 题产 生的 影响 : 对于某段不 优 化的程 序或 语 句 频繁 或者全 表 扫描 一个表 时 ,它访问 磁盘 的 时 间和 占 用的 吞吐 量 是很 高 的 ,这就 导致 系统 IO 长 时 间 处 于 忙 的 状 态 , 对于某段不 优 化的程 序或 语 句 频繁 或者全 表 扫描 一个表 时 ,其 他 的 业务 程 序 也 访 速 度将 大大下 降 。 如果是 更新 表 操 作 时 间 长 ,还可能会 导致 锁 等 待 ,导致 会话 堵塞 , 导致 这个系统性能 下 降 。 我们 一 般根 据 这些 现 象 、以 及一 些 方法判断 ,来 初 步分 析产 生性能问 题 的大致原 因 的范 还 是 比较 困难 的 ,因 为 产 生问 题 的原 因 是 多 种 的,并且 还 有一定的关 优化示例优化示例优化示例优化示例 减少减少减少减少 对表的对表的对表的对表的 查询查询查询查询 该 问 题 是 我们编 程中 出现 过的问 题 ,请 大 家 一定注 意 ,并且该 类问 题 优化可 以 带 来 较 c u r so r c u r _k c2 4_mz is wh ere a kb0 2 0 =st r _ a kb0 2 0 a nd a k a13 0=’ 11 ’; 优 化中重要 思想 ; 优 化中重要 思想 。 它访问 磁盘 的 时 间和 ,导致 整 个系统性 其 他 的 业务 程 序 也 访 ,w e b l ogi c 端 也 来 初 步分 析产 生性能问 题 的大致原 因 的范 并且 还 有一定的关 并且该 类问 题 优化可 以 带 来 较 c u r so r c u r _k c2 4_zy is S elec t a k c2 60 f r om k c2 4 wh ere a kb0 2 0 =st r _ a kb0 2 0 a nd a k a13 0=’ 21 ’; fo r rec _mz in c u r _k c2 4_mz l oop ….. e nd l oop; fo r rec _mz in c u r _k c2 4_zy l oop ….. e nd l oop; 高效 c u r so r c u r _k c2 4 is S elec t a k c2 60 ,a k a13 0 f r om k c2 4 wh ere a kb0 2 0 =st r _ a kb0 2 0 a nd a k a13 0 in (’ 11 ’,’ 21 ’); fo r rec _k c2 4 in c u r _k c2 4 l oop if rec _k c2 4 .a k a13 0=’ 11 ’ th e n ….. e nd if; if rec _k c2 4 .a k a13 0=’ 21 ’ th e n ….. e nd if; e nd l oop; 高 效 的 做法 使用同 样 的 条件 次 磁盘 ,这样速 度 差 别将 近 第 58 页 c u r so r c u r _k c2 4_zy is wh ere a kb0 2 0 =st r _ a kb0 2 0 fo r rec _mz in c u r _k c2 4_mz l oop fo r rec _mz in c u r _k c2 4_zy l oop S elec t a k c2 60 ,a k a13 0 wh ere a kb0 2 0 =st r _ a kb0 2 0 a nd a k a13 0 in (’ 11 ’,’ 21 ’); fo r rec _k c2 4 in c u r _k c2 4 l oop if rec _k c2 4 .a k a13 0=’ 11 ’ th e n if rec _k c2 4 .a k a13 0=’ 21 ’ th e n 使用同 样 的 条件 (或者 说 是索引)只 访问一 次 磁盘 ,低效 的 做法 访问 了 这样速 度 差 别将 近 2 倍。 低效 的 做法 访问 了 2 9999....3.3.3.3.2 2 2 2 避免避免避免避免循环循环循环循环((((游标游标游标游标 游 标 里面 不能 嵌入查询 有 ins er t 语 句 。但 在 实际的 编 程 情况下 是不可能 完 全 避免 的 类问 题也 是 我们 程 序 中 出现 过的问 题 示 例: 低效 C u r so r c u r _ ac 04 is S elec t aac 00 1,a k c 0 1 0 Fr om ac 04 Wh ere aa b00 1 = p r m_ aa b00 1 ; …… F o r rec _ ac 04 in c u r _ ac 04 l oop S elec t aac 008 I nto st r _ aac 008 f r om po l i c y wh ere aac 00 1 = rec _ ac 04 .aac 00 1 ; if st r _ aac 008=’ 1 ’ th e n n_jfj e : = rec _ ac 04 .a k c 0 1 0*0 . 08; e nd if; if st r _ aac 008=’ 2 ’ th e n n_jfj e : = rec _ ac 04 .a k c 0 1 0*0 .1 ; e nd if; E nd l oop; 高效 C u r so r c u r _ ac 04 is S elec t po l i c y .aac 00 1,ac 04 .a k c 0 1 0 , po l i c y .aac 008 Fr om ac 04 , po l i c y Wh ere ac 04 .aac 00 1 =po l i c y .aac 00 1 a nd aa b00 1 = p r m_ aa b00 1 ; 第 59 页 游标游标游标游标 ))))里面嵌查询里面嵌查询里面嵌查询里面嵌查询 游 标 里面 不能 嵌入查询 (或者 再嵌游 标 ),其实 也 不能有 upd a t e d ele t e 但 在 实际的 编 程 情况下 是不可能 完 全 避免 的 ,但 我们 一定要尽量 避免 类问 题也 是 我们 程 序 中 出现 过的问 题 ,该 类问 题也 可 以 大大 提 升 程 序效率 ,请 大 家 一定注 意 S elec t aac 00 1,a k c 0 1 0 Wh ere aa b00 1 = p r m_ aa b00 1 ; F o r rec _ ac 04 in c u r _ ac 04 l oop I nto st r _ aac 008 wh ere aac 00 1 = rec _ ac 04 .aac 00 1 ; if st r _ aac 008=’ 1 ’ th e n n_jfj e : = rec _ ac 04 .a k c 0 1 0*0 . 08; if st r _ aac 008=’ 2 ’ th e n n_jfj e : = rec _ ac 04 .a k c 0 1 0*0 .1 ; S elec t po l i c y .aac 00 1,ac 04 .a k c 0 1 0 , po l i c y .aac 008 Wh ere ac 04 .aac 00 1 =po l i c y .aac 00 1 a nd aa b00 1 = p r m_ aa b00 1 ; upd a t e d ele t e 等 语 句 ,只 能 但 我们 一定要尽量 避免 。该 请 大 家 一定注 意 。 …… F o r rec _ ac 04 in c u r _ ac 04 l oop if rec.aac 008=’ 1 n_jfj e : = rec _ ac 04 .a k c 0 1 0*0 . 08; e nd if; if rec.aac 008=’ 2 ’ th e n n_jfj e : = rec _ ac 04 .a k c 0 1 0*0 .1 ; e nd if; e nd l oop; 优化的 方法 是尽量 把 游 标循 环 中的 查询 语 句放 到 游 标 查询 中一 起 查询出 来 于 只 访问 了 1 次 磁盘 读 到 内 存 需 要 100 万 次 磁盘 ,可 以想 象 浪 费 了多少 如 果 在 程 序编 写 上没 有 办 么 也 要保证 游 标中的 查询 使用的索引 标中的 查询 语 句执 行 需 要 0.02 秒= 2 万 秒 = 5 小时 33 分 钟 ,如 果 写一个不 够 优化的语 句需 要 9999....3.3.3.3.3 3 3 3 gggg rorororo upupupup b b b b yyyy 优化优化优化优化 Gr oup by 需 要 查询 后排 序 样 的 查询 语 句在 性能 上 是有问 题 的 采用 g r oup by 的 也 一定要 进 行优化 示 例: 低效 s elec t ac 04 .aac 00 1, po l i c y .aac 00 2, po l i c y .aac 00 3, sum( aac 040) , po l i c y .aa b00 1 f r om ac 04 , po l i c y wh ere ac 04 .aac 00 1 =po l i c y .aac 00 1 a nd po l i c y .aa b00 1 =' 1 000000 3 7 0' g r oup by ac 04 .aac 00 1, po l i c y .aac 00 2, po l i c y .aac 00 3, po l i c y .aa b00 1 ; 高效 s el ec t ac 04 .aac 00 1, po l i c y .aac 00 2, po l i c y .aac 00 3, gzz e, po l i c y .aa b00 1 第 60 页 F o r rec _ ac 04 in c u r _ ac 04 l oop if rec.aac 008=’ 1 ’ th e n n_jfj e : = rec _ ac 04 .a k c 0 1 0*0 . 08; if rec.aac 008=’ 2 ’ th e n n_jfj e : = rec _ ac 04 .a k c 0 1 0*0 .1 ; 优化的 方法 是尽量 把 游 标循 环 中的 查询 语 句放 到 游 标 查询 中一 起 查询出 来 次 磁盘 读 到 内 存 ;如 果 放 到 游 标中的 话 ,假 如游 标有 100 万 数据量 可 以想 象 浪 费 了多少 IO 的访问。 如 果 在 程 序编 写 上没 有 办 法避免游 标中有 查询 语 句 的 话 (一 般情况 是可 以避免 的 么 也 要保证 游 标中的 查询 使用的索引 (即 查询 速 度非 常 快 ), 例如 :游 标 100 0.02 秒,从这 个 速 度 上 来 说 是 很 快 的,但 总体 上 看 如 果 写一个不 够 优化的语 句需 要 1 秒,那么 需 要 几 天 能 执 行 完 呢 优化优化优化优化 需 要 查询 后排 序 ,速 度 慢影响 性能,如 果 查询 数据量大,并且 分组复 杂 样 的 查询 语 句在 性能 上 是有问 题 的 。尽量 避免 使用分组或者采用 上面 的一 节 的 办 法去代替 的 也 一定要 进 行优化 。 s elec t ac 04 .aac 00 1, po l i c y .aac 00 2, po l i c y .aac 00 3, sum( aac 040) , po l i c y .aa b00 1 wh ere ac 04 .aac 00 1 =po l i c y .aac 00 1 a nd po l i c y .aa b00 1 =' 1 000000 3 7 0' g r oup by ac 04 .aac 00 1, po l i c y .aac 00 2, po l i c y .aac 00 3, po l i c y .aa b00 1 ; ec t ac 04 .aac 00 1, po l i c y .aac 00 2, po l i c y .aac 00 3, gzz e, po l i c y .aa b00 1 优化的 方法 是尽量 把 游 标循 环 中的 查询 语 句放 到 游 标 查询 中一 起 查询出 来 ,这样 相当 万 数据量 ,那么 程 序 一 般情况 是可 以避免 的 ), 那 100 万 数据量,游 但 总体 上 看 100 万* 0.02 那么 需 要 几 天 能 执 行 完 呢 ? 并且 分组复 杂 ,这 尽量 避免 使用分组或者采用 上面 的一 节 的 办 法去代替 。 s elec t ac 04 .aac 00 1, po l i c y .aac 00 2, po l i c y .aac 00 3, sum( aac 040) , po l i c y .aa b00 1 wh ere ac 04 .aac 00 1 =po l i c y .aac 00 1 a nd po l i c y .aa b00 1 =' 1 000000 3 7 0' g r oup by ac 04 .aac 00 1, po l i c y .aac 00 2, po l i c y .aac 00 3, po l i c y .aa b00 1 ; ec t ac 04 .aac 00 1, po l i c y .aac 00 2, po l i c y .aac 00 3, gzz e, po l i c y .aa b00 1 f r om (s elec t aac 00 1, sum( aac 040) gzz e f r om ac 04 g r oup by aac 00 1 ) ac 04 , po l i c y wh ere ac 04 .aac 00 1 =po l i c y .aac 00 1 a nd aa b00 1 =' 1 000000 3 7 0'; 9999....3.3.3.3.4 4 4 4 删删删删 除重复除重复除重复除重复 记录记录记录记录 一 般 数据转换的程 序经常 会使用 到 该方法 ROWID)。 示 例: DEL ETE F R OM po l i c y a WHER E a.r owid > (SEL ECT MIN (b .r owid) FR OM po l i c y b WHER E a.aac 00 2 =b .aac 00 2 a nd a.aac 00 3 =b .aac 00 3 ); 9999....3.3.3.3.5 5 5 5 COCOCOCOMMMMMMMMITITITIT 使用使用使用使用 数据转换的程 序需 要关注 这 一点 1. C ommit 执 行 也 是有时间的 间。 2. c ommit 可 以 释 放资 源 ¢ 回 滚 段 上 用于 恢 复数据的信息 ¢ 被程 序 语 句获 得 的 锁 ¢ re do l og bu ff er ¢ ORACLE 为 管 理 上 述 示 例: C u r _ ac2 0 有 5000 万 数据 n_ c ount : =0; F o r arec in c u r _ ac2 0 l oop I ns er t into ac2 0 …… n_ c ount : = n_ c ount + 1 ; 第 61 页 f r om (s elec t aac 00 1, sum( aac 040) gzz e f r om ac 04 g r oup by aac 00 1 ) ac 04 , po l i c y wh ere ac 04 .aac 00 1 =po l i c y .aac 00 1 a nd aa b00 1 =' 1 000000 3 7 0'; 删删删删 除重复除重复除重复除重复 记录记录记录记录 一 般 数据转换的程 序经常 会使用 到 该方法 。最 高 效 的 删 除重复 记录方法 DEL ETE F R OM po l i c y a WHER E a.r owid > (SEL ECT MIN (b .r owid) WHER E a.aac 00 2 =b .aac 00 2 a nd a.aac 00 3 =b .aac 00 3 ); 数据转换的程 序需 要关注 这 一点 。 执 行 也 是有时间的 ,不过时间 特 别 短 ,但 提交 频 率 特 别 大 , 可 以 释 放资 源 ,在 大量数据 更新 时,必 须 及时 提交 。 回 滚 段 上 用于 恢 复数据的信息 ; 程 序 语 句获 得 的 锁 ; re do l og bu ff er 中的空间 ; 为 管 理 上 述 3 种 资 源 中的 内 部 花 费 。 万 数据 F o r arec in c u r _ ac2 0 l oop I ns er t into ac2 0 …… n_ c ount : = n_ c ount + 1 ; f r om (s elec t aac 00 1, sum( aac 040) gzz e f r om ac 04 g r oup by aac 00 1 ) ac 04 , po l i c y 最 高 效 的 删 除重复 记录方法 ( 因 为使用 了 ,必 然 也 会 浪 费 时 I f n_ c ount = = 1 00000 th e n c ommit; n_ c ount : = 0; E nd if; E nd l oop; C ommit; 如 果 1 条 一 提交 ,需 要 提交 能必 须 下 降 。在 实际 编 程时 9999....3.3.3.3.6 6 6 6 批量数据批量数据批量数据批量数据 插入插入插入插入 数据转换时或者大业 务 数据 插入 时 和 s q ll o a d er)。 ¢ I ns er t into …s elec t 将查询 的 结 果 一 次插入 到 目标表中 例如 : I ns er t into po l i c y _ b a k s elec t * f r om po l i c y ; 由 于是一 次查询 一 次插入 式 速 度要 快 。但 查询插入 的数据量过大必 然 会 占 用 更多 的 内 存和 完 成 后 提交 ,这样 资 源 不能释 放 需 要全部 回 滚 。因此 建议小数据量 ¢ I ns er t /* + a pp e nd */ into 该 种 方 式同 上 种 方 式 , 文 件 ,速 度非 常 快 。注 意 系统开发 编 程不能使用 该 种 方 式 ¢ C u r so r 方 式 定义 游 标,然后 逐 行 进 行 插入 示 例: C uso r c u r _ ac2 0 is S elec t * f r om ac2 0; …. 第 62 页 I f n_ c ount = = 1 00000 th e n -- 1 0 万 一 提交 需 要 提交 5000 万 必 然 浪 费 时间 ;如 果 整 体 提交 ,资 源 不能释 放 在 实际 编 程时 ,应注 意提交 的 次 数和 提交 的数据量的平 衡 关系 批量数据批量数据批量数据批量数据 插入插入插入插入 数据转换时或者大业 务 数据 插入 时 ,有 以 下几种 办 法进 行数据 插入 (不包 括 s elec t 方 式 将查询 的 结 果 一 次插入 到 目标表中 。 I ns er t into po l i c y _ b a k s elec t * f r om po l i c y ; 由 于是一 次查询 一 次插入 ,并且最 后 一 次提交 ,他的 速 度要 比 下 面 描述 的 但 查询插入 的数据量过大必 然 会 占 用 更多 的 内 存和 undo 表空间 这样 资 源 不能释 放 ,会 导 致 回 滚 表空间不 足 和 快 照过 旧 的问 题 因此 建议小数据量 (例如 :300 万 以 下 )的 导入 采用 该 种 方 式 I ns er t /* + a pp e nd */ into … s elec t 方 式 ,不过 由 于有 a pp e nd 的 提示 ,这种 语 句 不 走 回 滚 段 直接插入 数据 注 意 系统开发 编 程不能使用 该 种 方 式 ,数据转换可 以 灵活 使用 然后 逐 行 进 行 插入 ,然后 定量 提交 。 S elec t * f r om ac2 0; 资 源 不能释 放 ,性 应注 意提交 的 次 数和 提交 的数据量的平 衡 关系 。 不包 括 imp 、impdp 他的 速 度要 比 下 面 描述 的 c u r os r 的 方 表空间 ,只 能 在插入 会 导 致 回 滚 表空间不 足 和 快 照过 旧 的问 题 ,另 外一 旦 失败 的 导入 采用 该 种 方 式 。 这种 语 句 不 走 回 滚 段 直接插入 数据 数据转换可 以 灵活 使用 。 n_ c ount : =0; F o r rec _ ac2 0 in c u r _ ac2 0 l oop I ns er t into ac2 0_b a k ( aac 00 1, …….) Val u e s ( rec _ ac2 0 .aac 00 1, ….); I f n_ c ount : == 1 00000 th e n C ommit; n_ c ount : =0; E nd if; E nd l oop; ¢ 批绑 定的 方 式 通 过 游 标 查询将 数据 逐 行写 到 数组 里 f o rall … in… ins er t into c u r so r 的 方 式 减少了 对 io 的访问 次 数 9999....3.3.3.3.7 7 7 7 索引使用索引使用索引使用索引使用 优化优化优化优化 在 实际的应用系统中索引问 题导 致性能问 题 可能能 占到 需 要 我们 特 别 关注的。本 节 主要 描述什么情况 索引会不 ¢ 多 个平 等 的索引 当 SQL 语 句 的 执 行 路径 可 以 使用分 布 在多 个表 上 的 多 个索引时 个索引 并在运 行时对 它 们 的 记录进 行合 并 在 ORACLE 选择执 行 路径 时 当 WHERE 子句 中索引列和 常 量 比较 才 有 效 在 优化器中的 等级 是非 常低 的 如 果 不同表中 两 个 相 同 等级 的索引 将 被 引用 先 使用。FROM 子句 中 最 后 的表的索引 将 有 最 高的优 先级 第 63 页 F o r rec _ ac2 0 in c u r _ ac2 0 l oop I ns er t into ac2 0_b a k ( rec _ ac2 0 .aac 00 1, I f n_ c ount : == 1 00000 th e n 通 过 游 标 查询将 数据 逐 行写 到 数组 里 (实际 上 就 是 内 存), 然后 通 过 批绑 定的语 句 ins er t into …v al u e s…;将内 存的数据一 次 写 入 到 数据文 件 中 的访问 次 数 ,提 高 了 速 度,但 注 意内 存 别 溢 出了 索引使用索引使用索引使用索引使用 优化优化优化优化 在 实际的应用系统中索引问 题导 致性能问 题 可能能 占到 80 %,在 程 序 优化 上 索引问 题 是 本 节 主要 描述什么情况 索引会不 生 效 。 语 句 的 执 行 路径 可 以 使用分 布 在多 个表 上 的 多 个索引时 , ORACLE 个索引 并在运 行时对 它 们 的 记录进 行合 并 , 检 索 出 仅对全部索引有 效 的 记录 选择执 行 路径 时 ,唯一性索引的 等级 高于非唯一性索引。然 而 这 个规则 只 有 子句 中索引列和 常 量 比较 才 有 效 。如 果 索引列和其他表的索引类 相比较 在 优化器中的 等级 是非 常低 的 。 如 果 不同表中 两 个 相 同 等级 的索引 将 被 引用 ,FROM 子句 中表的 顺序将 决定 哪 个会 被 率 子句 中 最 后 的表的索引 将 有 最 高的优 先级 。 然后 通 过 批绑 定的语 句 将内 存的数据一 次 写 入 到 数据文 件 中 。相比 但 注 意内 存 别 溢 出了 。 在 程 序 优化 上 索引问 题 是 , ORACLE 会同时使用 多 检 索 出 仅对全部索引有 效 的 记录 。 然 而 这 个规则 只 有 如 果 索引列和其他表的索引类 相比较 ,这种 子句 子句 中表的 顺序将 决定 哪 个会 被 率 如 果 同一表中有 两 个 相 同 等级 的索引 被 引用 他的索引不会使用,如 果这些 相 同 等级 的索引 效 果差 不 多 用。 ¢ 不 明 确 的索引 等级 当 ORACLE 无 法判断 索引的 等级 高 低 差 别 子句 中 被 列 在最 前 面 的。 ¢ 自 动 选择 索引 如 果 表中有 两 个 以上 ( 在 这种情况下 ,ORACLE 将 使用唯一性索引 而 完 全 忽 略 非唯一性索引 9999....3.3.3.3.8 8 8 8 使用提使用提使用提使用提 示示示示 (((( HinHinHinHin ts)ts)ts)ts) 对于表的访问,可 以 使用 两种 ¢ FU LL h int 告诉 ORACLE 示 例: SEL ECT /* + FU LL(POLICY) */ * FR OM PO LICY WHER E AAC 00 1 = ‘ 1 000 1 000’; 如 果 一个大表 没 有 被 设定为 缓 存 在 SGA 中,你就 可 以 使用 CACHE h int 和 F U LL h int 一 起 使用。 示 例: SEL ECT /* + FULL(POLICY) CAC HE(POLICY)*/ * FR OM PO LICY; ¢ ROWID h int 告诉 ORACLE 采用 TABLE ACCESS B Y ROWI D 要 知道 ROIWD 的 值 或者使用索引 索引 h int 告诉 ORACLE 示 例: SEL ECT /* + ind e x( IDX_POLICY_AAC 00 2 )*/ aac 00 1 第 64 页 同一表中有 两 个 相 同 等级 的索引 被 引用 ,o racle 会分 析最 有 效 的索引 去 引用 如 果这些 相 同 等级 的索引 效 果差 不 多 ,o racle 可能会 自 动 合 并进 行使 不 明 确 的索引 等级 无 法判断 索引的 等级 高 低 差 别 ,优化器 将 只 使用一个索引 , (包 括 两 个)索引,其中有一个唯一性索引,而 其他是非唯一性 将 使用唯一性索引 而 完 全 忽 略 非唯一性索引 。 (((( HinHinHinHin ts)ts)ts)ts) 可 以 使用 两种 H ints :FU LL 和 ROWI D。 ORACLE 使用全表 扫描 的 方 式访问指定表。 SEL ECT /* + FU LL(POLICY) */ * WHER E AAC 00 1 = ‘ 1 000 1 000’; 如 果 一个大表 没 有 被 设定为 缓 存 (CACHED) 表 而 你 希望 它 的数据 在查询结 束时 仍然 停 留 CACHE h int 来 告诉 优化器 把 数据保留 在 SGA 中。 FULL(POLICY) CAC HE(POLICY)*/ * ORACLE 使用 TABLE ACCESS B Y ROWI D 的 操 作访问表 TABLE ACCESS B Y ROWI D 的 方 式 特 别 是 当 访问大表的时 候 , 使用 这种 方 式 的 值 或者使用索引 。 ORACLE 使用基于索引的 扫描 方 式,你 不必 说明 具体的索引名 称 SEL ECT /* + ind e x( IDX_POLICY_AAC 00 2 )*/ aac 00 1 会分 析最 有 效 的索引 去 引用 ,其 可能会 自 动 合 并进 行使 ,它就 是 在 WHERE 而 其他是非唯一性 。 表 而 你 希望 它 的数据 在查询结 束时 仍然 停 留 。通常 CACHE h int 的 操 作访问表 。 使用 这种 方 式 ,你 需 你 不必 说明 具体的索引名 称 。 FR OM PO LICY WHER E aac 00 2 ='888 1111111111111111 '; 在 不使用 h int 的 情况下 过 多而 你 的优化器是 CBO, 强 制 ORACLE 使用 该 索引。 ORACLE h ints 还 包 括 ALL_ROWS, FIRST _ROWS, R ULE,USE_NL, USE_MERGE, USE_ HASH 等。 使用 h int ,表 示我们 对 这 是一个 很 有 技 巧 性的 工 作 9999....3.3.3.3.9 9 9 9 表表表表 上存上存上存上存 在过在过在过在过 旧旧旧旧 的分的分的分的分 析析析析 我们现在很多 项目都存 在 性能问 题 判断 索引 级别 和 资 源 成 本 上出现 问 题 解 决 办 法 : 第 一 种 办 法 :删 除分 析 据的 CP U 消 耗就 过大。 第 二种 办 法 :重 新 分 析 9999....3.3.3.3.10 10 10 10 表表表表 上存上存上存上存 在在在在 并并并并 行行行行 表 上 存 在并 行,ORACLE 不使用索引。尽量不要 在 表 级别 定义 并 行 9999....3.3.3.3.11 11 11 11 关于索引建立关于索引建立关于索引建立关于索引建立 索引的使用是 肯 定会大大 提 高 查询 的 速 度 用 户 类型的表空间 下 的,索引建立的 越 多 越 大 不是问 题 ,但 如 果 一个表有过 多 过大的 查询 的 速 度,因 为 这些 操 作 改变了整 个表的索引 顺序 了。因此 我们 一定要合理的建立 好 有 第 65 页 WHER E aac 00 2 ='888 1111111111111111 '; 的 情况下 , 以上 的 查询 应 该也 会使用索引。然 而 ,如 果 该 索引的重复 值 CBO, 优化器 就 可能 忽 略 索引。在 这种情况下, 你 可 以 用 ALL_ROWS, FIRST _ROWS, R ULE,USE_NL, USE_MERGE, USE_ HASH 表 示我们 对 ORACLE 优化器缺 省 的 执 行 路径 不 满 意 ,需 要 手 工 修 改 这 是一个 很 有 技 巧 性的 工 作 ,除非 特 定的 情况 ,例如 :数据转换,其他 情况 最好 不用 表表表表 上存上存上存上存 在过在过在过在过 旧旧旧旧 的分的分的分的分 析析析析 我们现在很多 项目都存 在 性能问 题 ,其中有 很多 种情况 都是 由 于分 析 过 旧 判断 索引 级别 和 资 源 成 本 上出现 问 题 ,会 导 致 ORACLE 判断错 误 不使用索引 删 除分 析 ,停 止 o racle10 g 的 自 动 分 析 ,但 不使用分 析 , 重 新 分 析 ,但 过长时间 后 ,索引是否会 再次失效 ,没 有 验 证过 表表表表 上存上存上存上存 在在在在 并并并并 行行行行 ORACLE 判断 索引 级别 和 资 源 成 本 上出现 问 题 ,会 导 致 ORACLE 尽量不要 在 表 级别 定义 并 行 。 关于索引建立关于索引建立关于索引建立关于索引建立 索引的使用是 肯 定会大大 提 高 查询 的 速 度 ,但 索引其实 也 是一 种 数据 索引建立的 越 多 越 大 ,占 用的空间 也 越 大,从 用 户 的 环 境 来 说这 也 但 如 果 一个表有过 多 过大的 查询 ,必 然 会 影响 ins er t 、d ele t e 和 因 为 这些 操 作 改变了整 个表的索引 顺序 ,o racle 需 要 进 行 调整 , 因此 我们 一定要合理的建立 好 有 效 的索引,编 程 也 要符合索引的规则 , 如 果 该 索引的重复 值 你 可 以 用 INDEX h int ALL_ROWS, FIRST _ROWS, R ULE,USE_NL, USE_MERGE, USE_ HASH 等 需 要 手 工 修 改 。 其他 情况 最好 不用 。 其中有 很多 种情况 都是 由 于分 析 过 旧 ,导 致 ORACLE 判断错 误 不使用索引 。 ,o racle 访问数 没 有 验 证过 。 ORACLE 判断错 误 但 索引其实 也 是一 种 数据 ,它 也 是存 放 的 从 用 户 的 环 境 来 说这 也 和 upd a t e 索引列 ,这样 性能 就下 降 ,而 不能是索引符 合 编 程的规则。 案 例: 某 项 目数据 转换 ,采 用 游标循环 小时 ,原因 就是目 标 表里面有很多 索引 1 小时 就 完 成 了,建 立全部 的 索引 不到 半 个 小时 原因 就是 第 一种方式每 次 方式 整 体上 执 行 索引顺序 就一 次 建立索引时可 以 开 并 行 参 数 ( crea t e ind e x idx _ xxx on xxx( a , b) p arallel 2 no l ogging) 9999.3.12.3.12.3.12.3.12 EEEE xpxpxpxp ain Planain Planain Planain Plan 在 PL/SQ L D e v el op er 等工 具有一个 分 析 SQL 语 句 是否使用 了 索引 1. 选择 e xp la in p la n 2. 在上面 栏 中 输 入 SQL 下 面显示 O ptmi z er go al 优化器的默认 方 式 计 划上 能 看到哪 个 条件 语 句 使用 了 索引 引,哪些 表是全表 扫描 的 ( TABLE ACCESS F ULL) 第 66 页 采 用 游标循环 ins er t 的方式,总共 2 000 万 的数据 原因 就是目 标 表里面有很多 索引 。解 决 方法是先 删除索引 再 执 行 转换 脚 本 建 立全部 的 索引 不到 半 个 小时 。 原因 就是 第 一种方式每 次 ins er t 都 改变索引顺序 ,共 执 行 改变 2 000 方式 整 体上 执 行 索引顺序 就一 次 。 建立索引时可 以 开 并 行 参 数 (如 果 系统 支持 并 行 )且 不写 日志 ,可 以 加 快 建立索引时间 on xxx( a , b) p arallel 2 no l ogging) EEEE xpxpxpxp ain Planain Planain Planain Plan 分分分分 析析析析 索引使用索引使用索引使用索引使用 等工 具有一个 E xp a in Pla n 分 析 的 功 能,这 个 功 能可 以 帮助 我们 语 句 是否使用 了 索引 、使用 哪些 索引和使用索引的 效 果 。 e xp la in p la n 的 窗 口 SQL 语 句 ,然后 点 击 工 具 栏 上 的 E XECUTE 执 行(或按 优化器的默认 方 式 (也 可 手 工选择 ), 以 及 下 面 的 解 释计 划 看到哪 个 条件 语 句 使用 了 索引 ,哪 个 没 有使用 ;哪 个表使用 了 索引 ( TABLE ACCESS F ULL)。 万 的数据 ,总共 用了 4 个 解 决 方法是先 删除索引 再 执 行 转换 脚 本 ,结 果不用 2 000 万 次 ,而 第 二种 可 以 加 快 建立索引时间 这 个 功 能可 以 帮助 我们 或按 F8), 就 会 在 以 及 下 面 的 解 释计 划 ,从 解 释 哪 个表使用 了 索引 ,使用 了 哪 个索 3. 分 析内容 说明 : ¢ COST:根 据 优 化程 序 的 基 于 开 销 的方法所 估 计出的 操 作 开 销 值对于 使 用 基 于规则 方法的语 句 该列为 空 该列值没有 特 定的 测 量单位它 只是一个用于比 较执 行规 划 开 销 大小 的 权 重值 ¢ Car din al ity :根 据 基 于 开 销 的方法对 操 作所 访问 行数的 估 计值 ¢ Byt e s :根 据 基 于 开 销 的方法对 操 作所 访问 字 节 的 估 计 ¢ 通过 设 置 ,我 们 还能看到 更 多的信息 全表 扫描 的 ( TABLE ACCESS F ULL) 句 是 绝 对 影响 性能的。 另 外使用 了 索引 也 不一定性能 就 高 见 的使用类型: ¢ IND EX UNI Q UE SCAN ¢ IND EX RANG E SCAN Byt e s 的 大小 了 , 第 67 页 根 据 优 化程 序 的 基 于 开 销 的方法所 估 计出的 操 作 开 销 值对于 使 用 基 于规则 方法的语 句 该列为 空 该列值没有 特 定的 测 量单位它 只是一个用于比 较执 行规 划 开 销 大小 的 权 重值 ; 根 据 基 于 开 销 的方法对 操 作所 访问 行数的 估 计值 ; 根 据 基 于 开 销 的方法对 操 作所 访问 字 节 的 估 计 ; 我 们 还能看到 更 多的信息 ,例如:CPU 使 用 、时 间等等 ( TABLE ACCESS F ULL) 肯 定是 速 度 慢 的,如 果 是大数据量的表 另 外使用 了 索引 也 不一定性能 就 高 ,因 为索引使用 也 有 效率 的 情况 , IND EX UNI Q UE SCAN:唯 一 索引扫描 ,速 度 最快 IND EX RANG E SCAN:范围 索引扫描 ,使 用这个 索引时 ,就需要看 COST ,如果 特别大 ,有 时 候 还可能 速 度 低 于 全 表 扫描 的 速 度 根 据 优 化程 序 的 基 于 开 销 的方法所 估 计出的 操 作 开 销 值对于 使 用 基 于规则 方法的语 句 该列为 空 该列值没有 特 定的 测 量单位它 只是一个用于比 较执 行规 划 ; 时 间等等 。 如 果 是大数据量的表 ,那么这 个语 ,下 面 列 出 索引 常 COST、Car din al ity、 有 时 候 还可能 速 度 低 于 全 表 扫描 的 速 度 。
还剩67页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 2 人已下载

下载pdf

pdf贡献者

sdd

贡献于2016-03-30

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf