数据仓库03

数据仓库设计编码规范 (3.0)
上海 XX 通信有限公司
金刚 (seniordba@sina.com)
2011-02 一、
培训大纲
目的和范围
 二、数据仓库体系架构
 三、分级存储(数据生命周期)
 四、数据库对象命名规范
 五、 SQL 编码规范
 六、 PL/SQL 编码规范
 七、代码优化规则
 八、日志
2目的:
目的和范围
好的命名规范,可以明显的增强程序的可读性和可维护性:
(1).
(2).
(3).
(4).
(5).
范围:
保障程序一致性
增强程序的可读性
增强程序可维护性
降低程序的 BUG 概率
提升程序可扩展性和性能
本规范主要适用于所有基于 Oracle 数据仓库设计开发的项目。
3 一、目的和范围
 二、数据仓库体系架构
 三、分级存储(数据生命周期)
 四、数据库对象命名规范
 五、 SQL 编码规范
 六、 PL/SQL 编码规范
 七、代码优化规则
 八、日志
4数据仓库基本概念 ( 一 )
数据仓库:
数据仓库是一个面向主题的,集成的,时变的和非易失的数据集合,支持管理
部门的决策过程。
面向主题的:数据仓库围绕一些主题如顾客、供应商、产品和销售来组织。
数据仓库关注决策者的数据建模与分析,而不是组织机构的日常操作和事务处理。
因此,数据仓库排除对于决策支持过程无用的数据,提供特定主题的简明视图。
集成的:通常,构造数据仓库是将多个异构数据源,如关系数据库,平面文件
和联机事务记录集成在一起。使用数据清理和数据集成技术确保命名约定、编码
结构、属性度量等的一致性。
时变的:数据存储从历史的角度(例如过去 3~5 年)提供信息。数据仓库中的
关键结构都隐式或显式的包含时间元素。
非易失的:数据仓库总是物理的分别存放数据;这些数据源于操作环境下的应
用数据。由于这种分离,数据仓库不需要事务处理、恢复和并发控制机制。通常
,它只需两种数据访问操作:数据的初始化装入和数据访问。
5数据仓库基本概念 ( 二 )
数据仓库的三种模型:
 ( 一 ) 、企业数据仓库 (EDW):
数据仓库收集了整个组织关于主题的所有信息,它提供企业范围内的数据
集成,通常来自一个或多个操作数据库系统或外部信息提供者,并且是跨功能的。
通常,它包含细节数据和汇总数据,它需要广泛的商业建模,可能需要多年设计
和建设。

( 二 ) 、数据集市
数据集市包含企业范围的一个子集,针对特定的用户群和特定的主题。例如
,销售数据集市可能限定其主题为顾客、商品和销售。包括在数据集市中的数据
通常是汇总的。
 ( 三 ) 、虚拟仓库
虚拟仓库是操作数据库视图的集合。虚拟仓库易于建立,但需要操作数据库
服务器具有剩余能力。
6ODS :
数据仓库基本概念 ( 三 )
ODS 是企业数据架构中最为复杂的一种形态,既要满足数据事务操作要求
,又要满足数据分析要求,从技术构建角度考察,难度相当之大。其主要功能
是给数据仓库提供数据,作为 EDW 的数据源。
ODS 与数据仓库的重要区别如下:
( 一 ) 、 ODS 只存储明细数据。
( 二 ) 、 ODS 中存储的数据一般不超过一个月。
( 三 ) 、 ODS 支持事务更新操作。
( 四 ) 、 ODS 是应用系统数据库的一个延迟快照。
根据延迟时间的长度,分为:
I 类 ODS, 实时或近似实时,与应用系统的数据延迟为 1~2 秒。
II 类 ODS, 与应用系统的数据延迟为 2~4 小时。
III 类 ODS, 与应用系统的数据延迟为 12~24 小时。
7数据仓库体系架构图
8数据仓库基本层次结构 ( 一 )
系统的信息模型从存储的内容方面可以分为:





( 一 ) 、 STAGE 接口信息模型
( 二 ) 、 ODS/DWD 信息模型
( 三 ) 、 MID 信息模型
( 四 ) 、 DM(DataMart) 信息模型
( 五 ) 、元数据信息模型
9数据仓库基本层次结构 ( 二 )
在各个信息模型中存储的内容如下描述:
  1) STAGE 层信息模型:
提供业务系统数据文件的临时存储,数据稽核,
数据质量保证,屏蔽对业务系统的干扰,对于主动数
据采集方式,以文件的方式描述系统与各个专业子系
统之间数据接口的内容、格式等信息。
与该模型对应的数据是各个专业系统按照该模型
的定义传送来的数据文件。
STAGE 是生产系统数据源的直接拷贝,由 ETL
过程对数据源进行直接抽取,在格式和数据定义上不
作任何改变。
10数据仓库基本层次结构 ( 三 )
STAGE 层存在的意义在于两点:
 (1). 对数据源做统一的一次获取,数据仓库中其它部分都依赖
STAGE 层的数据,不再重复进行抽取,也不在生产系统上做运
算,减少生产系统的压力。
 (2). 在生产系统数据已经刷新的情况下,保存一定量的生产系统
的历史数据,以便在运算出错的情况下可以进行回溯。
11数据仓库基本层次结构 ( 四 )
2)
ODS/DWD 层信息模型,简称 DWD 层
是数据仓库的细节数据层,是对 STAGE 层数据进行沉淀,减少
了抽取的复杂性,同时 ODS/DWD 的信息模型组织主要遵循企业业
务事务处理的形式,将各个专业数据进行集中。
为企业进行经营数据的分析,系统将数据按分析的主题的形式存
放,跟 STAGE 层的粒度一致,属于分析的公共资源。
12数据仓库基本层次结构 ( 五 )
3) MID 数据模型
轻度综合层是新模型增加的数据仓库中 DWD 层和 DM 层之间的一
个过渡层次,是对 DWD 层的生产数据进行轻度综合和汇总统计。
轻度综合层与 DWD 的主要区别在于二者的应用领域不同, DWD
的数据来源于生产型系统,并为满足一些不可预见的需求而进行沉淀;
轻度综合层则面向分析型应用进行细粒度的统计和沉淀。
13数据仓库基本层次结构 ( 六 )
4) DM 信息模型
为专题经营分析服务,系统将数据按分析的专题组织成多维库表的
形式存放,属于分析目标范畴的数据组织和汇总,属于分析的专有资
源。
其信息主要来源于 DWD 和 MID 层汇总,反映实时的经营状况,时
间维度为天。而历史经营状况的分析,时间维度一般为周,月,同时
也具有季度,年这样的维度。
14数据仓库基本层次结构 ( 七 )
5) MDW 元数据信息模型
描述数据及其环境的数据,即是对数据资源的描述,是信息共享
和交换的基础和前提,用于描述数据集的内容、质量、表示方式、空
间参考、管理方式以及数据集的其它特征。一般来说,它有两方面的
用途。
首先,元数据能提供基于用户的信息,如记录数据项的业务描述
信息的元数据能帮助用户使用数据。
其次,元数据能支持系统对数据的管理和维护,元数据机制主要
支持以下五类系统管理功能:





(1).
(2).
(3).
(4).
(5).
描述哪些数据在数据仓库中
定义要进入数据仓库中的数据和从数据仓库中产生的数据
记录根据业务事件发生而随之进行的数据抽取工作时间安排
记录并检测系统数据一致性的要求和执行情况
衡量数据质量
15 一、目的和范围
 二、数据仓库体系架构
 三、分级存储(数据生命周期)
 四、数据库对象命名规范
 五、 SQL 编码规范
 六、 PL/SQL 编码规范
 七、代码优化规则
 八、日志
16分级存储(一)
概念:
  分级存储是根据数据的重要性、访问频率、保留时间、容
量、性能等指标,将数据采取不同的存储方式存储在不同性能
的存储设备上,通过分级存储管理实现数据客体在存储设备之
间的自动迁移。
数据分级存储的工作原理是基于数据访问的局部性。通过
将不经常访问的数据自动移到存储层次中较低的层次,释放出
较高成本的存储空间给更频繁访问的数据,可以获得更好的性
价比。这样,一方面可大大减少非重要性数据在一级本地磁盘
所占用的空间,还可加快整个系统的存储性能。
17分级存储(二)
在线存储
( 核心存
储 )
近线存储
( 二级存
储 )
在分级存储体系架构中,一般分为
在分级存储体系架构中,一般分为 : :
 
 
 
在线
在线 (On-line)
(On-line) 存储
存储
近线
近线 (Near-line)
(Near-line) 存储
存储
离线存储
( 三级存
储 )
离线
离线 (Off-line)
(Off-line) 存储
存储
三级存储方式。
三级存储方式。
按照数据的不同生命周期制定相应存储机制,降低成本
按照数据的不同生命周期制定相应存储机制,降低成本
18在线存储:
分级存储(三)
  在线存储是指将数据存放在高速的磁盘系统(
如闪存存储介质、 FC 磁盘或 SCSI 磁盘阵列 ) 等存
储设备上,适合存储那些需要经常和快速访问的程
序和文件,其存取速度快,性能好,存储价格相对
昂贵。
   在线存储是工作级存储,其最大特征是存储设
备和所存储的数据时刻保持“在线”状态,可以随时
读取和修改,以满足前端应用服务器和数据库对数
据访问的速度要求。
19近线存储
分级存储 ( 四 )
近线存储是指将数据存放在低速的磁盘系统上,一般是一
些存取速度和价格介于高速磁盘与磁带之间的低端磁盘设备。
   近线存储外延相对比较广泛,主要定位于客户在线存储和
离线存储之间的应用。就是指将那些并不是经常用到(例如一
些长期保存的不常用的文件归档),或者说访问量并不大的数
据存放在性能较低的存储设备上。但对这些设备的要求是寻址
迅速、传输率高。
因此,近线存储对性能要求相对来说并不高,但又要求相
对较好的访问性能。同时多数情况下由于不常用的数据要占总
数据量的较大比重,这也就要求近线存储设备在需要容量上相
对较大。近线设备主要有 SATA 磁盘阵列、 DVD-RAM 光盘塔
和光盘库等设备。
20分级存储 ( 五 )
离线存储
离线存储则指将数据备份到磁带或磁带库上。大
多数情况下主要用于对在线存储或近线存储的数据
进行备份,以防范可能发生的数据灾难,因此又称
备份级存储。
   离线存储通常采用磁带作为存储介质,其访问
速度低,但价格低廉的海量存储。
21 一、目的和范围
 二、数据仓库体系架构
 三、分级存储(数据生命周期)
 四、数据库对象命名规范
 五、 SQL 编码规范
 六、 PL/SQL 编码规范
 七、代码优化规则
 八、日志
22数据库对象命名规范(总纲)
  命名规范总纲 :
 ( 一 ) 、所有名称的字符范围为 :A-Z,a-z,0-9 和 _( 下划线 ) ,不允许使
用其它字符作为名称。
( 二 ) 、采用英文单词或英文短语 ( 包括缩写 ) 作为名称,不能使用无
意义的字符或汉语拼音。

 ( 三 ) 、名称应该清晰明了,能够准确表达事务的含义,最好可读,遵
循” 顾名思义”的原则。
 ( 四 ) 、所有标识符(包括表名,字段名,存储过程名,参数名,变量
名等)要求简洁,不要用特殊字符,更不要使用保留字。
 ( 五 ) 、所有标识符必须以字母开头,而不要以数字开头。
。
 ( 六 ) 、所有数据库对象标识符不要用双引号扩起来
23数据库对象命名规范(表)
表命名规范:
 ( 一 ) 、不使用 TAB,TBL,T 作为前缀,本来就是一个表,为什么还要说明
 ( 二 ) 、表名以代表表内内容的一个或多个名词组成,以下划线分隔
 ( 三 ) 、表命名包括三部分: XXX_YYY_ZZZ ,总长度不超过 26 个字符
。
XXX 代表层次域, YYY 代表内容域, ZZZ 代表功能模块域
 ( 四 ) 、使用表的层次分类作为表名的前缀:如,与事实表相关的表使用
前缀 FCT_, 与短信搜索相关的使用 _SMS 后缀,后缀表示按功能划分。相
同功能模块使用相同的后缀。
 ( 五 ) 、一些表作为多对多连接的表,可以使用两个表的内容做为表名,
后缀以 _REL 结尾,主要应用于 OLTP 数据库。
 ( 六 ) 、当系统中有一些少量的,重复出现的值时,使用字典表来节约存
储空间和优化查询。如地区,系统中的用户类型等。在 OLTP 中,以
DIC_ 开头,在数据仓库中以 DMS_ 开头。
24数据库表分区设计规范:
表分区设计规范 :


( 一 ) 、分区只应用于大表。 ( 原则上规定记录数 >1,000,000)
( 二 ) 、关于时间范围的分区一律使用 NUMBER 数据类型,不要使用 VARCHAR2 或
DATE 数据类型 , 分区类型选择范围分区。
 之所有不选择 VARCHAR2 或 DATE, 一方面是基于存储和运算效率考虑,
  另一方面是是在分区列同时存储日和月维度数据时,难以分离。

( 三 ) 、关于时间分区的命名,统一要求
日分区: PART_YYYYMMDD ,分区字段名称为 DAY_ID
月分区: PART_YYYYMM , 分区字段名称为 MON_ID
周分区: PART_YYYYWW, 分区字段名称为 WEEK_ID


( 四 ) 、 尽量少使用复合分区。
( 五 ) 、 由于目前绝大部分存储都是采用 RAID 或集中存储,要求同一个表放在同一个表空
间中,减少维护和方便管理。
25数据库对象命名规范(字段名)
字段名命名规范:
 ( 一 ) 、字段不使用任何前缀,表名代表了一个名称空间,字段
前面再加前缀显得罗嗦。
 ( 二 ) 、字段名也避免采用过于简单的名称:例如,用户表中,
用户名的字段名为 user_name 要比 name 更好。
 ( 三 ) 、布尔型的字段,以一些助动词开头,更加直接生动:如
,用户是否留言 has_message, 用户是否通过检查 is_checked 等
 ( 四 ) 、入库接口缓冲表,如果接口文件是采用固定列宽分隔的
,特别注意字段类型及长度同接口保持一致。
 ( 五 ) 、如果接口文件某字段是日期时间类型,数据库也采用相
应的数据类型,不要使用字符或数字数据类型。我们要把脏数据拒
绝在系统之外,同时也可以发现脏数据,作为数据质量的一部分反
馈出来。
26数据库对象命名前/后缀
(一)
前/后缀
DMS_
IF_
DW_
DW_M_
FCT_
LOG_
BUF_
_HIS
_USR
_EXP
DM_
说明
维表
接口表
明细数据表
轻量汇总数据(汇总中间层 )
事实表
日志表
中间计算缓冲表
历史数据表
用户信息及权限相关信息表
数据导出
挖掘相关结果表
27数据库对象命名前/后缀
(二)
PK_
FK_
IXU_
CK_
IX_
IXBMP_
SEQ_
VW_
MV_
FUN_
主键
外键
唯一索引
检查约束
索引
位图索引
序列
视图
物化视图
函数
28类别
管理
应用
帐户 用途 描述
PERFDB 管理 数据库管理和监控(取代 SYS 用户)
BackGround User 后台处理 ETL 处理
ForeGround User
Data Mining
Temp REQUIRE
PROCESS
MetaData
前台应用 应用访问 , 存放维表和事实表
数据挖掘 数据挖掘相关对象及过程
临时需求 处理临时工单,非正式发版工单需求
元数据
ETL 调度,数据质量等
29数据库表空间设计规范
表空间设计规范:

( 一 ) 、每个应用用户对应独立的表空间。每个用户可以使用多个表空间,但每
个表空间只能被一个用户使用。
 ( 二 ) 、每个应用用户对应独立的临时表空间,必要时可以使用临时表空间组。
 ( 三 ) 、在数据仓库中,按层次划分表空间。比方说:
DW 层, DWM 层, FCT 层, DMS 层 ,BUF 层,入库接口缓冲表 IF 层



( 四 ) 、在超大型数据仓库中,在层次划分表空间的基础上,按功能划分表空间
。
( 五 ) 、按数据规模划分表空间,将个别超大型表划分到单独的表空间中,便于
维护和管理。
( 六 ) 、单个表空间最大空间限制在 200G~500G 。
30 一、目的和范围
 二、数据仓库体系架构
 三、分级存储(数据生命周期)
 四、数据库对象命名规范
 五、 SQL 编码规范
 六、 PL/SQL 编码规范
 七、代码优化规则
 八、日志
31SQL 编码规范:



( 一 ) 、 SQL 语句应正确、规范、高效和最优。
( 二 ) 、同一项目的 SQL 书写格式应该统一。
( 三 ) 、应避免写非常复杂的 SQL 语句。
SQL 语句尽可能避免多表联合复杂查询。





( 四 ) 、 SQL 语句不应在客户端组织,而应在服务器端组织。
( 五 ) 、 SQL 语句的语法应与所使用的数据库相适应
( 六 ) 、应确保变量和参数的类型和大小与数据库中表数据库列相匹配
。
( 七 ) 、使用 SELECT 语句时,应指出列名,不应使用列的序号或者
用” *” 替代所有列名。
( 八 ) 、使用 INSERT 语句时,应指定插入的字段名,不应不指定字段
名 , 而直接插入 values.
32SQL 编码规范:

( 九 ) 、 如果 SQL 语句连接多表时,应使用表的别名来引用列。
示例:
SELECT list.manifest_no,list.bill_no,stat.list_stat
FROM mft_list list,list_stat stat
WHERE list.manifest_no = stat.manifest_no
AND list.bill_no = stat.list_no;



( 十 ) 、 SQL 语句应避免对大表做全表扫描操作,对大表的操作应尽量
使用索引或建立分区表。
( 十一 ) 、 SQL 语句应避免不必要的排序操作。
( 十二 ) 、 SQL 语句应避免删除全表的操作,例如 :DELETE FROM
EMP;
33SQL 编码规范:

( 十三 ) 、应使用绑定变量实现 SQL 语句共享,避免使用硬编码。
示例: // 红色字体表示书写错误;蓝色字体表示书写正确
正确编码:
SELECT emp_name FROM emp WHERE emp_no = :B1;
//Bind value: 123
错误编码:
SELECT emp_name FROM emp WHERE emp_no = 123;
34SQL 编码规范:

SQL 编码规范 ( 四 )
( 十四 ) 、 SQL 语句的大小写
(1).SQL 语句中出现的所有表名,表别名,字段名,序列等数据库对象都应小写。
示例: // 红色字体表示书写错误;蓝色字体表示书写正确
SELECT list.manifest_no,list.bill_no,STAT.list_stat
FROM
mft_list list,list_stat STAT
WHERE list.manifest_no = STAT.manifest_no
AND list.BILL_NO = STAT.list_no;
ALTER SEQUENCE MANIFEST_NO CACHE 200;
SELECT manifest_no.nextval FROM DUAL;
35
SQL 编码规范 ( 五 )
( 十四 ) 、 SQL 语句的大小写
(2).SQL 语句中出现的系统保留字, SQL 保留字都应大写
// 红色字体表示书写错误;蓝色字体表示书写正确
SELECT list.manifest_no,list.list_no,stat.list_stat
from mft_list list,list_stat stat
where list.manifest_no = stat.manifest_no AND list.bill_no =
stat.list_no;
INSERT INTO employees(employee_name,date,employee_age)
VALUES (employees_seq.nextval, ‘John’,SYSDATE,30 );
36
SQL 编码规范 ( 六 )
( 十五 ) 、 SQL 语句的换行
(I). SELECT/FROM/WHERE/ORDER BY/GROUP BY 等子句独占一行。
(2).SELECT 子句内容如果只有一项,应与 SELECT 同占一行
(3).SELECT 子句内容如果多于一项,若一行可以全部显示,则同占一行,否则后续
的项同上一行的第一项对齐。行总长度在 100 到 130 内为宜(包括缩进字符在内 )
示例: //SELECT 语句书写的正确示例
SELECT bill_no
FROM mft_list
WHERE manifest_no =‘000000000000000007’;
SELECT list.manifest_no,list.list_no,stat.list_stat
FROM mft_list list,list_stat stat
WHERE list.manifest_no = stat.manifest_no
AND stat.stat != 2;
37SQL 编码规范:
 ( 十六 ) 、应尽量减少对数据库的访问次数,减少交互,降低服务器资源开销。
 ( 十七 ) 、 . 应使用 DECODE 函数避免重复扫描相同记录或者重复连接相同的表
   示例: // 一般情况下,效率较低。
   SELECT COUNT (*),SUM (salary)
   
   
FROM emp
  WHERE dept_no = ‘0020’;
***************************************************************************************
   SELECT COUNT (*), SUM (salary)
     FROM emp
    
WHERE dept_no = ‘0030’;
   ***************************************************************************************
   SELECT
COUNT(DECODE(dept_no, ‘0020’, ‘X’, NULL))   d0020_count,
         COUNT(DECODE(dept_no, ‘0030’, ‘X’, NULL))   d0030_count,
         SUM(DECODE(dept_no, ‘0020’, salary, NULL))
d0020_sal,
         SUM(DECODE(dept_no, ‘0030’, salary, NULL))   d0030_sal
      FROM emp;
   其中: X 表示任何字符或字符串。类似的, DECODE 还可用于 GROUP BY 和 ORDER BY 子句中。
38SQL 编码规范:

( 十八 ) 、 SQL 语句中变量的命名。
(1). SQL 语句中出现的变量参数命名应反映变量的实际意义。
(2).SQL 语句中的表别名应简短明了,应反映表名的实际意义。特别是在表比较
多的 SQL 语句中。但单个 SQL 语句中表数目 <=5, 则使用 a,b,c,d,e 别名简化命名
。

( 十九 ) 、 SQL 语句的缩进
(1). 如果一行有多列并超过 120 个字符,基于对齐规则,应采用下行缩进。
(2). 缩进应为 3 个字符。
(3). 同层次的 SQL 语句缩进应保持一致(纵向对齐)
示例:
SELECT first_name,last_name,salary,birthday,hire_date,depart_ment,job,
sex
FROM employees
WHERE empid=12;
39SQL 编码规范:
( 二十 ) 、 INSERT/VALUES 缩进规范。

示例:
INSERT INTO list_stat(list_no,list_stat,parent,
 
manifest_no,div_flag)
VALUES('bill020','1','0','000000000000007807',
'0');

( 二十一 ) 、单条 SQL 文中不应出现空行或注释
SELECT DMS_TIME.DAY_NAME,DMS_AREA.ALL_NAME,
       SUM (FCT_ACCESS.LOGIN_COUNT),SUM (FCT_ACCESS.ACCESS_COUNT),
     
   
中间出现空行
SUM (FCT_ACCESS.ACCESS_TIMES)
FROM DMS_TIME,DMS_AREA,FCT_ACCESS,DMS_SERVICE_TYPE_V
       WHERE (DMS_TIME.DAY_ID = FCT_ACCESS.DMS_TIME_ID)
AND (FCT_ACCESS.DMS_AREA_ID
40SQL 编码规范:

SQL 编码规范 ( 十 )
( 二十二 ) 、在 SQL 中,对表的访问次数越多,带来的性能成本越高,产生的磁盘 IO
越大。因此,减少 IO 访问是提升数据库性能的关键手段之一。
如下例:
低效 SQL:
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
高效 SQL:
UPDATE EMP
SET (EMP_CAT, SAL_RANGE) =
(SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
41SQL 编码规范 ( 十一 )
SQL 编码规范:

( 二十三 ) 、 Having 的使用规范
在 SQL 中,会需要使用 having 来做是否判断,但应该避免使用 HAVING 子句 , HAVING 只会在检索出所
有记录之后才对结果集进行过滤 . 这个处理需要排序 , 总计等操作 . 如果能通过 WHERE 子句限制记录的数目 ,
那就能减少这方面的开销相关规则如下:
:
Having 的语句只应用在需要判断 count 汇总的值是否满足条件。不能用来代替 where 条件作为判断,例如
低效 SQL:
SELECT REGION , AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
高效 SQL:
HAVING REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
SELECT REGION , AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
GROUP BY REGION
42SQL 编码规范:

( 二十四 ) 、 truncate/delete
对数据库表数据进行删除操作,如果没有任何条件的全部删除,使用 delete
的话,数据库会把被删除的数据库放入 rollback segment 中,这样既占用
Rollback 空间,也降低了性能。而 Truncate table 的操作,不需要对数据进行
丢失保护,从而具有更好的性能,因此:
在删除全部表数据的情况下,使用 truncate 来代替 delete
如果这种操作在一个大的流程中,需要确保其他的流程处理都正常结束之后再做
这个操作 , 因为 truncate 是不需要 commit 的一种自动提交操作,同样也无法 rollback ;
因
此要把握好使用的时机。
43SQL 编码规范:

( 二十五 ) 、 in/not in exists/not exists 使用规范
在 SQL 中,会用的 IN/NOT IN 的操作,用来判断相关数据是否存在于其他
表中,需要遵循规则如下:
除了 where employee_name in(‘abc’,’cde’) 这样的指明的常量(或者传入
参数的变量)以外,都不要使用 in 的操作,而应该使用 exists 来代替 in 。
  例如:
where employee_id in (seleet employee_id from ps_payment_history pph
where pph.payment_month = ‘200912’);
应该修改为:
where exists(selct pph.employee_id from ps_payment_history pph
where pph.employee_id = emp.employee_id
and pph.payment_month = ‘200912’);
同样的原理,使用 not exists 代替 not in 的操作
44SQL 编码规范 ( 十四 )
SQL 编码规范:



( 二十六 ) 、 应将 SQL 语句中的数据库函数、计算表达式等放置在等号右边。
( 二十七 ) 、 应按照业务需要使用事务,同时应保持事务简短,避免大事务。
( 二十八 ) 、 在事务完整性的基础上, SQL 语句应在程序中显示使用
COMMIT,ROLLBACK, 尽快提交事务,释放系统资源。


( 二十九 ) 、 SQL 语句应避免频繁引起数据库事务回滚。
( 三十 ) 、 SQL 语句内的算术运算符、逻辑运算符( AND 、 OR 、 NOT) 、
、


比较运算符 (= 、 <= 、 > 、 >= 、 > 、 < 、 <> 、 BETWEEN AND) 、 IN
LIKE 等运算符前后都应加一空格。
( 三十一 ) 、 不等于应统一使用符号” <>” ,不要使用” !=“
( 三十二 ) 、 如果业务规则允许结果集不需要唯一确定,应使用 UNION ALL 替代
UNION
45 一、目的和范围
 二、数据仓库体系架构
 三、分级存储(数据生命周期)
 四、数据库对象命名规范
 五、 SQL 编码规范
 六、 PL/SQL 编码规范
 七、代码优化规则
 八、日志
46PL/SQL 编码规范 ( 一 )
PL/SQL 编码规范:



( 一 ) 、存储过程名称标识:前缀 _ 名称 _ 后缀
前缀表示功能模块
名称偏向输出
后缀表明动作: INS( 插入 ),UPD( 更新 ),EXP( 导出 ) , PROC( 处理 )
( 二 ) 、所有外部 ETL 调度接口以 PROC 接尾,
其它过程不得使用 _PROC 结尾。
( 三 ) 、存储过程的参数命名
(1) 、形参统一以 p_ 开头
(2) 、过程内变量以 v_ 开头
(3) 、参数类型请尽量不要使用引用类型,减少不必要的伪依赖。
比方说 emp%rowtype,
emp.dept_id%type
(4) 、带默认值的参数,要求放在参数列表最后。
47PL/SQL 编码规范 ( 二 )
PL/SQL 编码规范:

( 四 ) 、空格、缩进和空行规范花要求
(1) 、空格
• 比较操作符左右各一个空格
• 赋值操作符左右各一个空格
• 嵌套结构,换行后缩进三个空格
(2) 、缩进
• 嵌套结构,换行后缩进三个空格
• 同一层级的语句要纵向对齐( BEGIN,EXCEPTION,END;
IF, ELSIF,ELSE,END IF;)
(3) 、空行
• 相对独立的逻辑块间加空行
• 包中各子程序间加空行
48PL/SQL 编码规范:

( 五 ) 、 PLSQL 程序规范:代码对齐规范
使用良好的对齐规范,可以明显的增强程序的可读性、以及程序的美观
程度,同时可以减少程序出错的概率,增强可维护性。
总体来说,对齐要基于程序层次情况,使用首尾对齐原则,如下例所示:
FUNCTION GET_EMPLOYEE_SALARY
(
p_employee_id NUMBER,
p_month
NUMBER
)
RETURN NUMVER
IS
V_SALARY NUMBER;
CURSOR CUR_EMPLOYEE_PAYROLL_ELEMENT IS
SELECT payroll_element_code, base_salary
FROM ps_payroll_elements ppe
WHERE ppe.EMPLOYEE_id = p_EMPLOYEE_id
AND
ppe.payment_month = p_month;
BEGIN
V_SALARY:=0;
FOR C1 IN CUR_EMPLOYEE_PAYMENT_ELEMENT LOOP
IF C1.base_salary is not null then
V_SALARY:=V_SALARY+C1.base_salary;
END IF;
END LOOP;
RETURN V_SALARY_AMOUNT;
END;
49PL/SQL 编码规范:

( 六 ) 、优良的程序结构
良好的程序结构,可以使程序有一个好的性能基础,另外也能让后续的变更更加灵活。
基本的结构概念如下:
(1) 、小步快跑原则。 尽量不要使用一个单一的超级大超级复杂的 SQL 代码去实现复杂的功能。
(2) 、结构简单清晰。 尽量是程序的结构看起来易懂,清晰,简单。不要设计只有自己才能看得懂
的程序。
(3) 、变量定义不要过多,不要太多重复,尽量使用可以重用的变量 ( 绑定变量可以提高性能 ) 。
(4) 、程序结构严谨,过程有输入参数就需要有输出参数返回状态,有正确完善的异常处理机制。
(5) 、增加程序的重用性,对于使用次数多的 Procedure, Function ,尽量做到能够共享,且有好的
运行性能。
(6) 、对于大批量数据处理的程序,良好的 COMMIT/ROLLBACK 及时提交机制。
(7) 、对于取出的大批量数据,尽量保存在内存中,不要多次重复读取大批量数据。
(8) 、适当使用临时表作为数据中转池。
50PL/SQL 编码规范 ( 五 )
PL/SQL 编码规范:
 ( 七 ) 、若无特别需要,不要或少使用游标。
 ( 八 ) 、若无特别需要,不要使用或少使用动态 SQL.



( 九 ) 、开发阶段以功能实现和满足业务需求为目标,要求快速编码,
高效完成开发功能目标。
( 十 ) 、 每一存储过程均应记录执行存储过程的日志信息。必须调用
专用写日志的存储过程,同时有 Exception 的异常处理机制。
( 十一 ) 、存储过程修改时,应注释清楚修改人,修改日期,修改原
。
因及修改内容
51PL/SQL 编码规范 ( 六 )
PL/SQL 编码规范:
( 十二 ) 、 SQL 语句中出现的常量函数,如果不影响业务规则,

要求用变量替换,减少不必要的函数计算。
    DECLARE
v_date DATE;
BEGIN
v_date:=sysdate;
 
 
INSERT INTO employees(employee_name,date,employee_age)
SELECT name, sysdate,age FROM emp ;
END;
使用 v_date 替换掉不确定函数 sysdate
52PL/SQL 编码规范 ( 七 )
PL/SQL 编码规范:

( 十三 ) 、内部块和子程序
(1) 、过程很长时,推荐写内部块来提高可读性
(2) 、用很多重复的代码时,推荐写成子程序
(3) 、动态 sql 避免使用拼 sql 的方式。推荐使用绑定变量
(4) 、要清楚明白地使用列名,而不能使用列的序号
53PL/SQL 编码规范:

( 十四 ) 、注释编码规则
(1) 、对较为复杂的 SQL 语句应注释,并说明算法和功能。
(2) 、注释应单独成行,并放在语句前面
(3) 、应对不易理解的分支条件表达式加注释
(4) 、对重要的计算应说明其功能
(5) 、过长的函数实现,应将其语句按实现的功能分段加以概括性说明
对常量或变量注释时,应注释被保存值的含义,宜包括合法取值的范围。
54PL/SQL 编码规范 ( 九 )
PL/SQL 编码规范:

( 十五 ) 、开发过程中,尽量保证设计文档和数据库物理表结构一致变化。
资源允许的情况下,安排专人负责数据库设计、文档管理和数据库同步更新。
 ( 十六 ) 、尽量不要使用触发器,不便于调试,跟踪和监控。
 ( 十七 ) 、相邻两层要求遵循最基本的守恒原则。
 
 
比方说:
STAGE1:FTP 文件下载 , 下载前后文件大小相同或误差在 10% 以内。
录数
STAGE2: 数据装载,接口文件总记录数=入库记录数 + 丢弃记录数 + 错误记
STAGE3: 调度守恒,每天所有的调度过程都被调度,没有某个过程调度缺失
55PL/SQL 编码规范:
 ( 十八 ) 、每个过程代码行控制在 200 到 250 行以内,不要太长。
 ( 十九 ) 、避免每个字段单独占一行。
 ( 二十 ) 、每个过程需要注释,包括:




 
作者,开发日期,功能简单说明,调用示例等。
( 二十一 ) 、所有过程都可以重复调度,在原始数据没有变化的情况下,应该产生同样
的输出。
( 二十二 ) 、入库接口表在该接口处理过程成功调度后,方可清除。其它任何情况下不
可清除。
( 二十三 ) 、在数据仓库中, DW 到 DW_M 层次结构中,若非必要,不要增加保留或
自增序列字段。
56 一、目的和范围
 二、数据仓库体系架构
 三、分级存储(数据生命周期)
 四、数据库对象命名规范
 五、 SQL 编码规范
 六、 PL/SQL 编码规范
 七、代码优化规则
 八、日志
57索引优化规则:

索引优化规则 ( 一 )
( 一 ) 、索引的建立应慎重考虑,不是越多越好,索引可以提高相应的 SELECT 的效率,
但同时也降低了 INSERT 、 UPDATE 和 DELETE 的效率。
 ( 二 ) 、被查询列有大量重复数据时,如状态标志,可考虑建立位图索引(仅限于 DW 环境 ).
 ( 三 ) 、在业务允许的情况下,查询列、排序列与索引列的次序保持一致。
 ( 四 ) 、应避免在 WHERE 子句索引列上使用函数或者表达式。示例如下:
    CREATE INDEX ix_emp_hire_date ON emp(hire_date);
select *
from emp
where to_char(hire_date,’yyyymmdd’)=‘20110101’;
where 子句应修改为:
where hire_date>=to_date(‘20110101’,’yyyymmdd’)
and hire_date<to_date(‘20110102’,’yyyymmdd’)
58索引优化规则:
索引优化规则 ( 二 )
( 五 ) 、索引列的比较应尽量避免使用 <>,NOT
( 六 ) 、应尽量避免在 WHERE 子句中对索引列使用 LIKE ‘%xxx%’,’%xxx’
( 七 ) 、可在 WHERE 子句中对索引列使用 LIKE ‘xxx%’
( 八 ) 、应尽量使用与索引列数据类型一致的比较值。
避免索引列值进行数据类型隐式转换,容易导致不必要的运行时错误和错误的执行计划。
在 WHERE 子句中应注意比较值与索引列数据类型的一致性,
应显示转换比较值使其与索引列数据类型保持一致。
( 九 ) 、对于复合索引, WHERE 子句中必须包含索引的主导列,才能保证使用到索引。
  例如:
CREATE INDEX ix_emp_id ON emp(emp_id,hire_date,dept_id);
其主导列是 (1).emp_id
(2).emp_id,hire_date
(3).emp_id,hire_date,dept_id
59索引优化规则:
索引优化规则 ( 三 )
( 十 ) 、排序应尽量建立在索引列上
( 十一 ) 、关于区间比较的列放在所有列的最后。
  例如: where dept_no=10 and hire_date between :d1 and :d2;
错误的索引:
create index IX_emp ON emp(hire_date,dept_no);
正确的索引:
create index IX_emp ON emp(dept_no, hire_date);
60索引优化规则

索引优化规则 ( 四 )
( 十二 ) 、对于已经存在 Index 的表,如果程序语句写法不对 , 会使得可以使用的
Index 无法使用,
具体如下:
(1) 、在 Where 条件中不要对 Index 字段进行类型转换处理,例如:
to_char(employee_number)=‘12345’,
这样的写法会让 Index 无效。
如果一定需要做转换,应该转换为: employee_number =
to_number(‘12345’)
类似的转换还有: to_number, to_date, trunc
(2) 、在 Where 条件中,不要对 Index 字段进行数据附加操作,例如:
where action_date +1 = to_date(‘2009-01-01’,’yyyy-mm-dd’)
dd’)-1
而应该修改为: where action_date = to_date(‘2009-01-01’,’yyyy-mm-
61索引优化规则

索引优化规则 ( 五 )
( 十二 ) 、对于已经存在 Index 的表,如果程序语句写法不对 , 会使得可以使用的
Index 无法使用,
具体如下:
(3) 、在 where 条件中,不要对 Index 字段进行 != 操作(不等于操作),
因为 Index 只记录了有什么,而没有记录没有什么,例如:
where salary_amount !=0
应该修改为: where salary_amount>0
(4) 、在 where 条件中,不要对两个 index 字段做拼合操作,例如:
where employee_id || employee_no = ‘123’||’C001’
应该修改为: where employee_id = 123 and employee_no = ‘C001’
62索引优化规则:

( 十三 ) 、禁用不想使用的索引
如果出现 SQL 会使用到多个 INDEX ,而被选择的 INDEX 不是最优选择的时候,
可以使用强制禁用不需要的 INDEX 的办法来迫使系统选择你希望的 INDEX 。
例如:
Select *
from ps_employee
where action_date > sysdate -365
and dept_id = 111;
如果发现使用 action_date 这个 index 的性能没有 dept_id 这个 index 好,
那么可以修改成
select *
from ps_employee
where action_date +0 > sysdate – 365
and dept_id = 111
通过强制屏蔽 action_date 这个 index 被使用,就可以迫使系统选择 dept_id
作为 index 来运作。
关于索引的更多信息,我们将在数据库中级培训(执行计划 ) 第
8讲中专题详细讲述。
63 一、目的和范围
 二、数据仓库体系架构
 三、分级存储(数据生命周期)
 四、数据库对象命名规范
 五、 SQL 编码规范
 六、 PL/SQL 编码规范
 七、代码优化规则
 八、日志
64日志价值及分类:
日志接口设计 ( 一 )
大家知道,系统出问题了,我们第一步干什么,检查系统日志。
 
 
 
由此可见,日志对于我们有多重要。
在整个数据仓库体系架构中,我们又需要输出哪些日志呢?
数据仓库日志主要按照数据仓库体系层次架构分为以下几类:
(1) 、上游 FTP 文件的到达状态,已成功上传完成文件数,已成功下载文件数,文
件大小,文件到达时间,文件记录数等信息。
(2) 、数据加载日志可以告诉我们,加载开始时间,加载结束时间,加载用时,成功
入库记录数,丢弃记录数,错误记录数,入库后使用空间等信息 .
(3) 、 ETL 作业调度日志可以告诉我们:当前运行状态,作业执行状态,成功/失
败、错误消息,开始时间,结束时间,执行用时,估计用时,客户端 IP ,当前已完成
哪些任务,还有哪些任务没有完成,估计所有任务完成时间等信息 .
65日志价值及分类:
 
日志接口设计 ( 二 )
数据仓库日志主要按照数据仓库体系层次架构分为以下几类:
(4) 、过程处理日志,包括处理过程名称,输入参数及值,影响记录数,执行开始时
间,结束时间,运行状态(成功 / 失败 / 警告),错误消息,执行节点,客户端 IP, 客
户端应用程序等信息。
(5). 数据导出监控制日志,确保导出成功并正确分发,包括导出开始时间,结束时
间,导出文件名,导出记录数,导出路径,导出文件大小等信息。
 
(6). 前台页面访问日志,用户登录日志等信息。我们可以做大量的有价值的分析。
(7).CUBE 刷新日志,包括 cube 名称,刷新开始时间,结束时间,用时,生成 cube
大小等信息。我们可以针对刷新最慢的 CUBE 重点优化,显著提升,改善处理效率。
(8). 服务器 CPU, 内存 ,IO 等资源使用日志
(9). 数据库存储资源使用日志
...
66下面我们重点以存储过程日志输出为样例,来详细描述日志输出的实现:
存储过程日志输出主要分为三级:
(1) 、外部调度相关接口过程,主要记录:
    过程名称,输入参数,调度开始时间,结束时间,执行状态(初始化,准备运行,运行中,成
功/失败/警告等 ), 执行实例节点,估计运行时间 , 客户端 IP ,错误消息
(2) 、过程内执行过程日志
过程名称,输入参数,调度开始时间,结束时间,记录数,执行状态(初始化,准备运行,运行
中,成功/失败/警告等 ), 执行实例节点,错误消息,进度描述等
(3) 、可高并行的过程内子过程执行日志。
  过程名称,调度开始时间,结束时间,记录数,执行状态 , 执行实例节点等
日志监控主要包括:
(1) 、所有日志状态是否都正常成功执行
(2) 、所有过程(外部接口)都已经被正常调度,并成功执行。
(3) 、对执行时间特别长的过程进行重点优化,可以快速提高系统的处理效率,降低资源开销。
(4) 、对数据异常的对象,进行日志跟踪,可以快速定位问题根源。
67日志过程接口设计样例(四)
日志过程接口样例设计:
CREATE OR REPLACE PROCEDURE LOG_PROCESS_INS
(
p_object_name varchar2,
p_start_date date,
p_exec_code varchar2,
p_num_rows number,
p_status number,
p_note varchar2,
p_err_message varchar2
)
-- 存储过程名称
-- 开始执行时间
-- 执行调用代脉
-- 影响行数
-- 执行状态: 1 成功, 0 失败
-- 备注信息
-- 错误消息
AS
begin
...
EXCEPTION
...
End;
/
68日志接口设计样例 ( 五 )
样例实现代码:
CREATE OR REPLACE PROCEDURE LOG_PROCESS_INS
(
p_object_name varchar2,
...
-- 存储过程名称
)
as
begin
insert into
AOS_LOG(object_name,usetime,exec_code,num_rows,status,err_message,note,oprdat
e)
values(p_object_name,numtodsinterval(sysdate-
p_start_date,'day'),p_exec_code,p_num_rows,p_status,p_err_message,p_note,sysdate)
;
commit;
exception
when others then
null;
end;
/
69日志接口设计样例 ( 六 )
接口参数说明:
p_object_name:
  外部接口名称(存储过程名称 ) 。
  若内部存在调用其它子过程,仍旧使用外部存储过程名称。
p_start_date:
  开始执行时间
p_exec_code:
  执行代码,该日志所在模块对应的存储过程名称附参数。
 要求直接粘贴即可直接执行。 ( 例如: EXEC ...)
p_num_rows:
  影响的数据行数
p_status:
  执行结果状态: 0 失败 , 1 成功, 2 警告
p_note:
  备注信息
p_err_message:
  错误消息
70日志接口设计样例(七)
日志记录注意事项:
1 、日志记录频度要适中。
单个 DML/TRUNCATE TABLE 操作超过 5 分钟必须记录。
单个 DML/TRUNCATE TABLE 操作超过 1 小时,必须优化到 1 小时内完成。
对可能存在性能问题的代码要进行重点跟踪。
日志记录也不能太频繁,特别是循环处理中,避免每个循环记录日志。
p_num_rows: 该参数值的获取必须在 DML 执行之后,
COMMIT 之前获取,否则值为 0 。
71调度规范(附录一)
一个基本的 ETL 作业调度应该具备下列要素:
(1). 异常支持
支持从产生错误作业断点自动重新启动,完成后续作业调度。
主要用于恢复因网络异常导致的调度中断。
(2). 并行支持
支持作业内并行,作业间并行,能设置最大并行度
(3). 支持循环执行
(4). 调度程序自身不包含任何业务逻辑
72一个基本的 ETL 作业调度应该具备下列要素:
(5) 、有作业执行状态输出,可设置日志输出的详细级别。
(6) 、支持将任务分布到多个节点,可指定哪些任务在哪个节点执行。
(7) 、支持全局变量,局部变量参数。
(8) 、支持存储过程(检测存储过程是否失败 ).
(9) 、支持基于时间的调度和基于事件的调度。
7374