ETL基础及常用技术培训

1、LOGOETL基础及基础及 主要内容主要内容nETL基本概念基本概念nETL常用逻辑架构常用逻辑架构nETL实施过程实施过程nETL常用技术（常用技术（shell,oracle,datastage）ETL基本概念基本概念 ET L(Extract-Transform-Load)即数据的抽取、转换与加载。即数据的抽取、转换与加载。ETL是从各是从各种原始的业务系统种原始的业务系统(异构多源异构多源)中提取数据，按照预先设计好的规则将抽取到的中提取数据，按照预先设计好的规则将抽取到的数据进行转换，最后将转换完的数据按计划增量或全部导人到目标数据库，成数据进行转换，最后将转换完的数据按计划增量或全部

2、导人到目标数据库，成为联机分析处理、数据挖掘的基础。为联机分析处理、数据挖掘的基础。 ETL为为BI/DW的核心和灵魂的核心和灵魂. ETL就是一个批量数据加工的过程就是一个批量数据加工的过程.ETL常用逻辑架构常用逻辑架构 n数据抽取数据抽取n数据清洗与转换数据清洗与转换n数据加载数据加载 ETL实施过程实施过程 数据抽取n确定所有数据源来源于哪些源系统，核心系统，信贷，信用卡等n定义数据接口对每个源文件及接口的每个字段进行详细说明n数据抽取方法主动抽取还是源系统提供文件，增量还是全量,每日还是每月ETL实施过程实施过程 1.代码标准化 2.数据粒度的转换 3.根据业务规则计算 确定数据清洗

3、和转换规则后细化mapping,编码实现数据清洗与转换n数据清洗 1.不完整的数据 2.错误的数据 3.重复的数据 n数据转换ETL实施过程实施过程 数据加载将数据缓冲区的数据直接加载到数据库对应的表中，如果是全量采用load方式，如果是增量则根据业务规则merge进数据库 ETL实施过程实施过程ETL常用技术常用技术nSHELL(unix基本操作基本操作)nSQLnPL/SQLnPROCnDATASTAGEshell基础基础Linux中有好多种不同的中有好多种不同的shell，如，如bsh，csh ，同其他语言一样，可以通过我们，同其他语言一样，可以通过我们使用任意一种文字编辑器，比如使用任

4、意一种文字编辑器，比如vi等来编写我们的等来编写我们的shell程序。程序。程序必须以下面的行开始（必须放在文件的第一行）：程序必须以下面的行开始（必须放在文件的第一行）：#!/bin/sh符号符号#!用来告诉系统它后面的参数是用来执行该文件的程序。在这个程序中我们用来告诉系统它后面的参数是用来执行该文件的程序。在这个程序中我们使用使用/bin/sh来执行程序。来执行程序。当编辑好脚本时，如果要执行该脚本，还必须使其可执行。当编辑好脚本时，如果要执行该脚本，还必须使其可执行。要使脚本可执行：要使脚本可执行：chmod +x 然后，可以通过输入：然后，可以通过输入： ./ 来执行脚本。来执行脚本

5、。.shell基础基础Vi（一）、进入（一）、进入vi 在系统提示字符在系统提示字符(如如$、#)下敲入下敲入vi 档案名称，档案名称，vi 可以自动帮你载入所要编辑的文件或可以自动帮你载入所要编辑的文件或是开启一个新是开启一个新 文件（如果该文件不存在或缺少文件名）。进入文件（如果该文件不存在或缺少文件名）。进入 vi 后萤幕左方会出现波浪符号，凡是列后萤幕左方会出现波浪符号，凡是列首有该符号就代首有该符号就代 表此列目前是空的。表此列目前是空的。 (二）、两种模式二）、两种模式 如上所述，如上所述，vi存在两种模式：指令模式和输入模式。在指令模式下输入的按键将做为指令存在两种模式：指令模式

6、和输入模式。在指令模式下输入的按键将做为指令来处理：如输入来处理：如输入 a，vi即认为是在当前位置插入字符。而在输入模式下，即认为是在当前位置插入字符。而在输入模式下，vi则把输入的按键当作插入的字符则把输入的按键当作插入的字符来处理。指令来处理。指令 模式切换到输入模式只需键入相应的输入命令即可（如模式切换到输入模式只需键入相应的输入命令即可（如a,A），而要从输入模式切换到指），而要从输入模式切换到指令模式，则需在令模式，则需在 输入模式下键入输入模式下键入ESC键，如果不晓得现在是处於什麽模式，可以多按几次键，如果不晓得现在是处於什麽模式，可以多按几次 ESC，系统如发出哔哔声就表，系

7、统如发出哔哔声就表 示已处于指令模式下了。示已处于指令模式下了。 .shell基础基础Vi由指令模式进入输入模式的指令：由指令模式进入输入模式的指令： 新增新增 (append) a ：从光标所在位置後面开始新增资料，光标後的资料随新增资料向後移动。：从光标所在位置後面开始新增资料，光标後的资料随新增资料向後移动。 A： 从光标所在列最後面的地方开始新增资料。从光标所在列最後面的地方开始新增资料。 插入插入 (insert) i： 从光标所在位置前面开始插入资料，光标後的资料随新增资料向後移动。从光标所在位置前面开始插入资料，光标後的资料随新增资料向後移动。 I ：从光标所在列的第一个非空白字

8、元前面开始插入资料。：从光标所在列的第一个非空白字元前面开始插入资料。 开始开始 (open) o ：在光标所在列下新增一列并进入输入模式。：在光标所在列下新增一列并进入输入模式。 O: 在光标所在列上方新增一列并进入输入模式。在光标所在列上方新增一列并进入输入模式。 .shell基础基础Vi（三）、基本编辑（三）、基本编辑 配合一般键盘上的功能键，像是方向键、配合一般键盘上的功能键，像是方向键、Insert 、Delete 等等，现在你应该已经可以等等，现在你应该已经可以利用利用 vi 来编辑文件来编辑文件 了。当然了。当然 vi 还提供其他许许多多功能让文字的处理更为方便。还提供其他许许多

9、多功能让文字的处理更为方便。 何谓编辑？一般认为是文字的新增、修改以及删除，甚至包括文字区块的搬移、复制等等。何谓编辑？一般认为是文字的新增、修改以及删除，甚至包括文字区块的搬移、复制等等。先这里介绍先这里介绍 vi 的如何做删除与修改。的如何做删除与修改。删除与修改文件的命令：删除与修改文件的命令： x： 删除光标所在字符。删除光标所在字符。 dd ：删除光标所在的列。：删除光标所在的列。 r ：修改光标所在字元，：修改光标所在字元，r 後接著要修正的字符。後接著要修正的字符。 R： 进入取替换状态，新增文字会覆盖原先文字，直到按进入取替换状态，新增文字会覆盖原先文字，直到按 ESC 回到指

10、令模式下为止。回到指令模式下为止。 s： 删除光标所在字元，并进入输入模式。删除光标所在字元，并进入输入模式。 S： 删除光标所在的列，并进入输入模式。删除光标所在的列，并进入输入模式。 在指令模式下移动光标的基本指令是在指令模式下移动光标的基本指令是 h, j, k, l 。.shell基础基础Vi（四）、退出（四）、退出vi 在指令模式下键入在指令模式下键入:q,:q!,:wq或或:x(注意注意:号），就会退出号），就会退出vi。其中。其中:wq和和:x是存盘退出，是存盘退出，而而:q是直接退出，是直接退出，如果文件已有新的变化，如果文件已有新的变化，vi会提示你保存文件而会提示你保存文件

11、而:q命令也会失效，这时你可以用命令也会失效，这时你可以用:w命令保命令保存文件后再用存文件后再用:q 退出，或用退出，或用:wq或或:x命令退出，如果你不想保存改变后的文件，你就需要用命令退出，如果你不想保存改变后的文件，你就需要用:q!命令，这个命令，这个命令将不保存文件命令将不保存文件 而直接退出而直接退出vi。shell基础基础n注释注释 #n变量变量在shell编程中，所有的变量都由字符串组成，并且不需要对变量进行声明。要赋值给一个变量，可以这样写：变量名=值取出变量值可以加一个美元符号（$）在变量前面nshell脚本中的三类命令脚本中的三类命令1）Unix 命令2）管道, 重定向和

12、 backtick3）流程控制shell基础基础unix命令命令在shell脚本中可以使用任意的unix命令，这些命令通常是用来进行文件和文字操作的。常用命令语法及功能:echo some text: 将文字内容打印在屏幕上ls: 文件列表wc l filewc -w filewc -c file: 计算文件行数计算文件中的单词数计算文件中的字符数cp source: 文件拷贝mv oldname newname : 重命名文件或移动文件rm file: 删除文件grep pattern file: 在文件内搜索字符串比如：grep searchstring cut -b colnum fil

13、e: 指定欲显示的文件内容范围，并将它们输出到标准输出设备比如：输出每行第5个到第9个字符cut -b5-9 千万不要和cat命令混淆，这是两个完全不同的命令.shell基础基础unix命令命令ftp:远程传输文件 然后输入用户名密码，put ，get cat : 输出文件内容到标准输出设备（屏幕）上: 得到文件类型read var: 提示用户输入，并将输入赋值给变量sort : 对文件中的行进行排序uniq: 删除文本文件中出现的行列比如： sort | uniqexpr: 进行数学运算Example: add 2 and 3expr 2 + 3find: 搜索文件比如：根据文件名搜索fin

14、d . -name -printtee: 将数据输出到标准输出设备(屏幕) 和文件比如：somecommand | tee outfilebasename file: 返回不包含路径的文件名比如： basename /bin/tux将返回 tux.shell基础基础unix命令命令dirname file: 返回文件所在路径比如：dirname /bin/tux将返回 /binhead file: 打印文本文件开头几行tail file : 打印文本文件末尾几行sed: Sed是一个基本的查找替换程序。可以从标准输入（比如命令管道）读入文本，并将结果输出到标准输出（屏幕）。该命令采用正则表达式

15、（见参考）进行搜索。不要和shell中的通配符相混淆。比如：将linuxfocus 替换为 LinuxFocus ：cat text.file | sed s/linuxfocus/LinuxFocus/ newtext.fileawk: awk 用来从文本文件中提取字段。缺省地，字段分割符是空格，可以使用-F指定其他分割符。cat | awk -F, print $1 , $3 这里我们使用，作为字段分割符，同时打印第一个和第三个字段。如果该文件内容如下： Adam Bor, 34, IndiaKerry Miller, 22, USA命令输出结果为：Adam Bor, IndiaKerry

16、 Miller, USA tar:将文件或文件夹打包解包，tar cvf a.tar abc tar xvf a.tarshell基础基础管道管道、重定向和重定向和 backtick命令命令n管道管道 (|)将一个命令的输出作为另外一个命令的输入。grep hello | wc -l在中搜索包含有”hello”的行并计算其行数。在这里grep命令的输出作为wc命令的输入n重定向重定向将命令的结果输出到文件，而不是标准输出（屏幕） 写入文件并覆盖旧文件 加到文件的尾部，保留旧文件内容。n反短斜线（反短斜线（）使用反短斜线可以将一个命令的输出作为另外一个命令的一个命令行参数。命令：V_date=d

17、ate +%F.shell基础基础流程控制命令流程控制命令(if)if Then .elif Then .Else .fi大多数情况下，可以使用测试命令来对条件进行测试。比如可以比较字符串、判断文件是否存在及是否可读等等通常用 来表示条件测试。注意这里的空格很重要。要确保方括号的空格。 -f somefile ：判断是否是一个文件 -x /bin/ls ：判断/bin/ls是否存在并有可执行权限 -n $var ：判断$var变量是否有值 $a = $b ：判断$a和$b是否相等.shell基础基础流程控制命令流程控制命令(if) 字符串比较: string1 = string2 如果相等则为

18、真 string1 != string2 如果不等则为真 -n string 如果不空则为真 -z string 如果为空则为真 算术比较: expression1 -eq expression2 如果相等则为真 expression1 -ne expression2 如果不等则为真 expression1 -gt expression2 如果大于则为真 expression1 -ge expression2 大于等于则为真 expression1 -lt expression2 如果小于则为真 expression1 -le expression2 小于等于则为真 .shell基础基础流程控

19、制命令流程控制命令(if)文件: -d file 如果为目录则为真 -e file 如果存在则为真(在这里要注意的是,由于历史原因,-e选项并不可移植,所以常用的是-f选项 -f file 如果为常规文件则为真 -g file 如果设置了组ID则为真 -r file 如果文件可读则为真 -s file 如果文件大小不为零则为真 -u file 如果设置了用户ID则为真 -w file 如果文件可写则为真 -x file 如果文件可执行则为真 .shell基础基础流程控制命令流程控制命令1)while . Do .Donewhile-loop 将运行直到表达式测试为真。will run whil

20、e the expression that wetest for is true. 关键字break 用来跳出循环。而关键字”continue”用来不执行余下的部分而直接跳到下一个循环。2)for var in . do.Donefor-loop表达式查看一个字符串列表 (字符串用空格分隔) 然后将其赋给一个变量：3)case . in.) .;Esaccase表达式可以用来匹配一个给定的字符串.oracle基础基础数据库安装数据库安装nWindows环境下环境下: 网上下载安装包网上下载安装包 点击安装点击安装 全选默认配置即可。全选默认配置即可。 其它机器上已有其它机器上已有server端

21、，可只安端，可只安client端。端。 PLSQL软件是一个优秀的软件是一个优秀的oracle工具，建议安装工具，建议安装nunix环境下环境下: 建建oracle组和用户组和用户 在在oracle用户下安装用户下安装 授予其它用户执行授予其它用户执行oracle命令的权限命令的权限.oracle基础基础数据库的连接数据库的连接nClient端连接配置端连接配置到oracle安装目录下 搜索tnsnames.ora增加修改相应连接内容 ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = cg)(PORT = 1521) (CONNE

22、CT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )mdb = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 1)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SID = mdb) ) ).oracle基础基础数据库的连接数据库的连接nUnix环境环境 先设置编辑器先设置编辑器 export EDITOR=vi select parameter,value from nls_database_para

23、meters where parameter=NLS_CHARACTERSET;-显示数据库字符集 NLS_LANG应与数据库字符集一致 export NLS_LANG=“SIMPLIFIED CHINESE.ZHS16GBK” export NLS_DATE_FORMAT=YYYY-MM-DD-HH24.MI.SS n命令行：命令行： sqlplus username/passwordserver as sysdba as sysoper sqlplus /as sysdba -管理员进入管理员进入 sqlplus etl/etlmdb -普通用户进入普通用户进入SQLset linesiz

24、e 300; -设置行长度设置行长度SQLset pagesize 1; -设置页长度设置页长度SQLcol name format a30; -设置列格式设置列格式SQLselect sysdate from dual; -运行语句运行语句.oracle基础基础数据库的连接数据库的连接nPlsql是一款优秀的操作oracle图形界面软件Sql window 可单条执行可单条执行sqlCommand window 可批量执行多条可批量执行多条sql.oracle基础基础表空间表空间ORACLE数据库被划分成称作为表空间的逻辑区域形成ORACLE数据库的逻辑结构。一个ORACLE数据库能够有一个

25、或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。每个ORACLE数据库均有SYSTEM表空间,这是数据库创建时自动创建的。SYSTEM表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息(关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它ORACLE软件产品要求的表)。一个小型应用的ORACLE数据库通常仅包括SYSTEM表空间,然而一个稍大型应用的ORACLE数据库采用多个表空间会对数据库的使用带来更大的方便。.orac

26、le基础基础表空间表空间n建立本地管理表空间建立本地管理表空间 CREATE TABLESPACE tablespace_name DATAFILE SIZE UNIFORM SIZE extent_size|AUTOALLOCATE CREATE TABLESPACE DATATBS01_32K DATAFILE /home/mdb/oracle/oradata/mdb/datatbs01_01.dbf SIZE 100G, /home/mdb/oracle/oradata/mdb/datatbs01_02.dbf SIZE 100G, /home/mdb/oracle/oradata/md

27、b/datatbs01_03.dbf SIZE 100G, EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32M SEGMENT SPACE MANAGEMENT AUTO blocksize 32k .oracle基础基础表空间表空间n建立建立UNDO表空间表空间 CREATE UNDO TABLESPACE tablespace_name DATAFILE SIZE UNIFORM SIZE extent_size|AUTOALLOCATEn建立临时表空间建立临时表空间 :存放查询、排序、分组等生成的临时数据存放查询、排序、分组等生成的临时数据 CREATE T

28、EMPORARY TABLESPACE tablespace_name TEMPFILE SIZE UNIFORM SIZE extent_size|AUTOALLOCATE.oracle基础基础表空间表空间n改变表空间可用性改变表空间可用性 ALTER TABLESPACE tablespace_name online|offlinen改变表空间读写状态改变表空间读写状态 ALTER TABLESPACE tablespace_name READ WRITE | READ ONLYn改变表空间名称改变表空间名称 ALTER TABLESPACE tablespace_name RENAME

29、TO new_tbs_namen设置默认表空间设置默认表空间 ALTER DATABASE DEFAULT TABLESPACE tablespace_name -数据库数据库默认表空间默认表空间 ALTER DATABASE DEFAULT TEMPORARY tablespace_name -数据库默数据库默认临时表空间认临时表空间n删除表空间删除表空间 DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;.oracle基础基础用户管理用户管理n建立用户建立用户create user ETL identified

30、 by default tablespace ETLDATA01 temporary tablespace TEMP1 pro;n给用户授权给用户授权grant connect to etl;grant select_catalog_role to etl;grant select any table to etl;grant create session to etl;grant alter session to etl;grant create table to etl;grant create view to etl;grant create type to etl;grant crea

31、te synonym to etl;grant create sequence to etl;grant create procedure to etl;.oracle基础基础用户管理用户管理n修改口令修改口令ALTER USER user_name IDENTIFIED BY new_password;n修改默认表空间修改默认表空间ALTER USER user_name DEFAULT TABLESPACE new_tablespace_name;n锁定用户锁定用户 ALTER USER etl ACCOUNT LOCK;n解锁用户解锁用户 ALTER USER etl ACCOUNT U

32、NLOCK;n删除用户删除用户 DROP USER username CASCADE;.oracle基础基础表建立及管理表建立及管理n普通建表 CREATE TABLE schema.table_name ( column_name datatype DEFAULT expr, ) TABLESPACE tablespace_name;n使用子查询建表CREATE TABLE schema.table_name (column_name,) TABLESPACE tablespace_nameAS subquery;n建临时表-事务临时表 当前事务内有效CREATE GLOBAL TEMPOR

33、ARY TABLE table_name ( column_name,) ON COMMIT DELETE ROWS;-会话临时表 当前会话内有效CREATE GLOBAL TEMPORARY TABLE table_name ( column_name,) ON COMMIT PRESERVE ROWS;.oracle基础基础表建立及管理表建立及管理n增加列增加列 ALTER TABLE table_name ADD (column datatype DEFAULT expr);n修改列定义修改列定义 ALTER TABLE table_name MODIFY (column datatyp

34、e DEFAULT expr);n删除列删除列ALTER TABLE table_name DROP (column);n修改列名修改列名ALTER TABLE table_name RENAME COLUMN column_name to new_column_name;n增加注释增加注释COMMENT ON TABLE table_name IS text;COMMENT ON COLUMN table_name.column_name IS text;n迁移非分区表的表空间迁移非分区表的表空间ALTER TABLE table_name MOVE new_tablespace_name;

35、-表对应索引需重建.oracle基础基础表建立及管理表建立及管理n截断表截断表 -快速删除数据快速删除数据 释放空间释放空间 保留表结构保留表结构 -是是DDL语句语句 不可回退不可回退TRUNCATE TABLE table_name ; -delete 是是DML语句语句 可回退可回退n删除表删除表 - PURGE是是oracle 10g 新特性新特性 表永久删除表永久删除DROP TABLE table_name CASCADE CONSTRAINTS PURGE;n从回收站从回收站Recyclebin恢复被删除表恢复被删除表FLASHBACK TABLE table_name TO B

36、EFORE DROP;n清空回收站清空回收站PURGE RECYCLEBIN;n查看回收站查看回收站SELECT * FROM RECYCLEBIN;.oracle基础基础表建立及管理表建立及管理n表表 SELECT * FROM USER_TABLES;n对象对象 SELECT * FROM USER_OBJECTS;n表占用的空间表占用的空间select sum(bytes) from user_segments where segment_name=table_name;n清空回收站清空回收站PURGE RECYCLEBIN;n查看回收站查看回收站SELECT * FROM RECYCL

37、EBIN;n查看表的列查看表的列SELECT * FROM USER_TAB_COLUMNS;n查看注释查看注释SELECT * FROM USER_TAB_COMMENTS;SELECT * FROM USER_COL_COMMENTS;n查看建表语句查看建表语句SELECT dbms_metadata.get_ddl(TABLE,) FROM dual;SELECT dbms_metadata.get_ddl(TABLE,upper(tmpcg_ctl_paravalue),upper(etl) ) FROM dual;.oracle基础基础索引索引n建索引建索引CREATE INDEX

38、UNIQUE index_name ON table_name (column_name,.) TABLESPACE tablespace_name ;-不设表空间 则建在用户默认表空间上n维护索引维护索引ALTER INDEX index_name REBUILD TABLESPACE tablespace_name ;-可改变索引表空间n显示索引信息显示索引信息SELECT * FROM USER_INDEXES;SELECT * FROM USER_IND_COULMNS;-查找失效的非分区索引SELECT * FROM USER_INDEXES WHERE STATUS=UNUSABL

39、E;n查看建索引语句查看建索引语句SELECT dbms_metadata.get_ddl(INDEX,) FROM dual; SELECT dbms_metadata.get_ddl(INDEX,upper(idx_tmpcg_ctl_paravalue),upper(etl) ) FROM dual;.oracle基础基础序列序列n序列（序列（Sequence）是用于生成唯一数字的数据库对象，自动生成顺序递增）是用于生成唯一数字的数据库对象，自动生成顺序递增的序列号，可提供唯一的主键值，事务回滚值不会减少的序列号，可提供唯一的主键值，事务回滚值不会减少n建序列建序列CREATE SEQU

40、ENCE seq_name INCREMENT BY nSTART WITH n MAXVALUE n |NOMAXVALUE ;MINVALUE n |NOMINVALUECYCLE | NOCYCLECACHE n | NOCACHE;n使用序列使用序列INSERT INTO t1 (id) VALUES (t1id_seq.NEXTVAL) ;SELECT t1id_seq.CURRVAL FROM dual;n删除序列删除序列 DROP SEQUENCE seq_name;n显示序列信息显示序列信息SELECT * FROM SEQ;n查看建序列语句查看建序列语句SELECT dbms

41、_metadata.get_ddl(SEQUENCE,) FROM dual;.oracle基础基础分区表分区表ORACLE的分区(Partitioning Option)是一种处理超大型表的技术。分区是一种“分而治之”的技术，通过将大表和索引分成可以管理的小块，从而避免了对每个表作为一个大的、单独的对象进行管理，为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元，减少了需要进行管理操作的时间，并通过增强的并行处理提高了性能，通过屏蔽故障数据的分区，还增加了可用性。ORACLE的分区表的划分方法包括：按字段值进行划分的范围分区；按字段的HASH函数值进行的划分HASH分区；先按范

42、围划分，再按HASH划分的复合分区；在ORACLE9i中又增强了按字段值列表进行划分的列表(Listing)分区方法。管理员可以指定每个分区的存储属性，分区在宿主文件系统中的放置情况，这样便增加了对超大型数据库的控制粒度(granularity)。分区可以被单独地删除、卸出或装入、备份、恢复，因此减少了需要进行管理操作的时间。还可以为表分区创建单独的索引分区，从而减少了需要进行索引维护操作的时间。此外，还提供了种类繁多的局部和全局的索引技术。分区操作也可以被并行执行。分区技术还提高了数据的可用性。当部分数据由于故障或其它原因不可用时，其它分区内的数据可不受影响继续使用。分区对应用是透明的，可以

43、通过标准的SQL语句对分区表进行操作。Oracle 的优化器在访问数据时会分析数据的分区情况，在进行查询时，那些不包含任何查询数据的分区将被忽略，从而大大提高系统的性能。总之，Oracle的分区表技术能满足高性能、高可用性、数据易管理性等综合需求。 .oracle基础基础分区表设计原则分区表设计原则n表的大小表的大小对于大表进行分区，将有益于大表操作的性能和大表的数据维护。通常当表的大小超过1.5GB2GB，或对于OLTP系统，表的记录超过1000万，都应考虑对表进行分区。n数据访问特性数据访问特性基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分区，可充分利用分区排除无关数据查询

44、的特性。n数据维护数据维护某些表的数据维护，经常按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。因为删除（Delete）大量的数据，对系统开销很大，有时甚至是不可接受的。n只读数据只读数据如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备份是非常有益的。n并行数据操作（并行数据操作（Parallel DML）对于经常执行并行操作（如Parallel Insert,Parallel Update等）的表应考虑进行分区。n表的可用性表的可用性当对表的部分数据可用性要求很高时，应考虑进行表分区。.orac

45、le基础基础分区表分区表n范围分区表创建脚本范围分区表创建脚本CREATE TABLE HIS_EVT_DCC_FCMAUDI ( cm_rec_ll integer, cm_tx_dt varchar2(8), )PARTITION BY RANGE(CM_TX_DT) ( PARTITION ETL_LOAD_DATE_0508 VALUES LESS THAN (20050901)TABLESPACE etl0_r_jyrq_data_200508, PARTITION ETL_LOAD_DATE_0509 VALUES LESS THAN (20051001)TABLESPACE et

46、l0_r_jyrq_data_200509, PARTITION ETL_LOAD_DATE_0510 VALUES LESS THAN (20051101)TABLESPACE etl0_r_jyrq_data_200510, PARTITION ETL_LOAD_DATE_0511 VALUES LESS THAN (20051201)TABLESPACE etl0_r_jyrq_data_200511, PARTITION ETL_LOAD_DATE_0512 VALUES LESS THAN (20060101)TABLESPACE etl0_r_jyrq_data_200512);C

47、REATE UNIQUE INDEX HIS_EVT_DCC_FCMAUDI_UIDX ON HIS_EVT_DCC_FCMAUDI( CM_TX_DT ASC, CM_TX_LOG_NO ASC, CM_TX_COD ASC, CM_EC_FLG ASC)LOCAL( PARTITION ETL_LOAD_DATE_0508 TABLESPACE etl0_r_jyrq_idx_200508, PARTITION ETL_LOAD_DATE_0509 TABLESPACE etl0_r_jyrq_idx_200509, PARTITION ETL_LOAD_DATE_0510 TABLESP

48、ACE etl0_r_jyrq_idx_200510, PARTITION ETL_LOAD_DATE_0511 TABLESPACE etl0_r_jyrq_idx_200511, PARTITION ETL_LOAD_DATE_0512 TABLESPACE etl0_r_jyrq_idx_200512, );.oracle基础基础分区表分区表nHASH分区表创建脚本分区表创建脚本CREATE TABLE PERSONAL_CUSTOMER_INFO（ ）PARTITION BY HASH(CI-CUST-NO) PARTITIONS 64TABLESPACE TS_ODS_DATA;n复

49、合分区表创建脚本复合分区表创建脚本CREATE TABLE FACT_DCC_SAACNACN_PERSONAL (txn_date,sa-acct-no)PARTITION BY RANGE(时间字段) SUBPARTITION BY HASH(SA-ACCT-NO) SUBPARTITION TEMPLATE (SUBPARTITION s1 , SUBPARTITION s2 , SUBPARTITION s3 ,);.oracle基础基础分区表分区表n查询查询SELECT * FROM his_evt_dcc_fcmaudi PARTITION (ETL_LOAD_DATE_0508

50、);n清分区数据清分区数据ALTER TABLE his_evt_dcc_fcmaudi TRUNCATE PARTITION (ETL_LOAD_DATE_0508 );n新增分区新增分区ALTER TABLE his_evt_dcc_fcmaudi ADD PARTITION ETL_LOAD_DATE_0601 VALUES LESS THAN (20060201)TABLESPACE etl0_r_jyrq_data_200612;n删除分区删除分区ALTER TABLE his_evt_dcc_fcmaudi DROP PARTITION ETL_LOAD_DATE_0508 ;.o

51、racle基础基础分区表分区表n分区信息查询分区信息查询SELECT * FROM USER_TAB_PARTITIONS;SELECT * FROM USER_TAB_SUBPARTITIONS;SELECT * FROM USER_IND_PARTITIONS; 加上 WHERE STATUS=UNUSABLE 可查询失效的分区索引SELECT * FROM USER_IND_SUBPARTITIONS; 加上 WHERE STATUS=UNUSABLE 可查询失效的子分区索引SELECT * FROM USER_SEGMENTS;.oracle基础基础sqlldrn用法用法: SQLLD

52、R keyword=value ,keyword=value,.sqlldr $1 control=tb.ctl errors=10000 rows=100000 bindsize=8192000 readsize=8192000 log=tbldr.log bad=tbldr.bad direct=truen有效的关键字有效的关键字: n userid - ORACLE 用户名用户名/口令口令 n control - 控制文件名控制文件名 n log - 日志文件名日志文件名 n bad - 错误文件名错误文件名 n data - 数据文件名数据文件名 n discard - 废弃文件名废弃

53、文件名ndiscardmax - 允许废弃的文件的数目允许废弃的文件的数目 (全部默认全部默认)n skip - 要跳过的逻辑记录的数目要跳过的逻辑记录的数目 (默认默认 0)n load - 要加载的逻辑记录的数目要加载的逻辑记录的数目 (全部默认全部默认)n errors - 允许的错误的数目允许的错误的数目 (默认默认 50)n rows - 常规路径绑定数组中或直接路径保存数据间的行数常规路径绑定数组中或直接路径保存数据间的行数n (默认默认: 常规路径常规路径 64, 所有直接路径所有直接路径)n bindsize - 常规路径绑定数组的大小常规路径绑定数组的大小 (以字节计以字节计

54、) (默认默认 256000)n silent - 运行过程中隐藏消息运行过程中隐藏消息 (标题标题,反馈反馈,错误错误,废弃废弃,分区分区)n direct - 使用直接路径使用直接路径 (默认默认 FALSE)n par 参数文件参数文件: 包含参数说明的文件的名称包含参数说明的文件的名称n parallel - 执行并行加载执行并行加载 (默认默认 FALSE)n 要从以下对象中分配区的文件要从以下对象中分配区的文件 .oracle基础基础sqlldr控制文件LOAD DATAINFILE test.txtINTO TABLE DEPTREPLACEFIELDS TERMINATED B

55、Y , OPTIONALLY ENCLOSED BY (DEPTNO, DNAME, LOC).oracle基础基础文本数据导出文本数据导出Oracle没有提供将数据导出到一个文件的工具。但是我们可以用SQL*Plus的select及 format 数据来输出到一个文件：set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool onspool oradata.txtselect col1 | , | col2 | , | col3from tab1where col2 = XYZ;spool off .oracl

56、e基础基础数据库备份导入导出数据库备份导入导出EXP: exp help=y 有三种主要的方式（完全、用户、表）有三种主要的方式（完全、用户、表） 1、完全：、完全： EXP SYSTEM/MANAGER BUFFER=64000 FILE=C:FULL.DMP FULL=Y 如果要执行完全导出，必须具有特殊的权限如果要执行完全导出，必须具有特殊的权限 2、用户模式：、用户模式： EXP SONIC/SONIC BUFFER=64000 FILE=C:SONIC.DMP OWNER=SONIC 这样用户这样用户SONIC的所有对象被输出到文件中。的所有对象被输出到文件中。 3、表模式：、表模式

57、： EXP SONIC/SONIC BUFFER=64000 FILE=C:SONIC.DMP OWNER=SONIC TABLES=(SONIC) 这样用户这样用户SONIC的表的表SONIC就被导出就被导出 .oracle基础基础数据库备份导入导出数据库备份导入导出n IMP: imp help=y 具有三种模式（完全、用户、表）具有三种模式（完全、用户、表） 1、完全：、完全： IMP sys/sys123 as sysdba BUFFER=64000 FILE=C:FULL.DMP FULL=Y 2、用户模式：、用户模式： IMP SONIC/SONIC BUFFER=64000 FI

58、LE=C:SONIC.DMP FROMUSER=SONIC TOUSER=SONIC 这样用户这样用户SONIC的所有对象被导入到文件中。必须指定的所有对象被导入到文件中。必须指定FROMUSER、TOUSER参数，这样才能导入数据。参数，这样才能导入数据。 3、表模式：、表模式： imp SONIC/SONIC BUFFER=64000 FILE=C:SONIC.DMP OWNER=SONIC TABLES=(SONIC) 这样用户这样用户SONIC的表的表SONIC就被导入。就被导入。.oracle基础基础查看执行计划查看执行计划n若有若有PLSQL等等windows界面的工具，可点执行计

59、划菜单查看界面的工具，可点执行计划菜单查看 n命令行查看执行计划命令行查看执行计划SQL explain plan for sql语句； -并不真正执行，只是生成执行计划 例：SQL explain plan for select * from etlcusorg where ecif_cus_num=12345;已解释。SQL ?/rdbms/admin/utlxplp -查看PLAN_TABLE_OUTPUT-| Id | Operation | Name | Rows | Bytes | Cost |-| 0 | SELECT STATEMENT | | 1 | 279 | 3 | 1

60、| TABLE ACCESS BY INDEX ROWID| ETLCUSORG | 1 | 279 | 3 | 2 | INDEX UNIQUE SCAN | SYS_C007050 | 1 | | 2 |-.oracle基础基础常用函数常用函数n数值型函数数值型函数ABS(x) 【功能功能】返回返回x的绝对值的绝对值 mod(x,y) 【功能功能】返回返回x除以除以y的余数的余数 power(x,y) 【功能功能】返回返回x的的y次幂次幂 round(x,y) 【功能功能】返回四舍五入后的值返回四舍五入后的值 【参数参数】x,y，数字型表达式，数字型表达式,如果如果y不为整数则截取不为整数