Oracle SQLLDR 加载数据的基础知识

 

本文永久地址: http://www.askmaclean.com/archives/sqlldr.html

 

 

Oracle Database的数据加载
 数据加载的种类

 

  • 利用中间语句件的项目
    • exp/imp utility、DataPump(expdp/impdp) utility
    • SQL*Loader utility
    • 外部表功能
    • 可移动表区域功能 (源数据库需要是Enterprise Edition)

 

  • 使用网络的项目
  • 分散处理功能
    • 数据库连接 + SQL
    • Oracle Transparent Gateway (与其他公司DB的合作产品) + SQL
  • Oracle Data Integrator (ETL工具)
  • Oracle GoldenGate(实时复制工具)

(实时制成数据软件,在数据迁移,数据库更新时可以利用)

 

Oracle Database的数据加载
 主要利用text语句件的项目

  • 将外部语句件的数据储存在Oracle Database的表中
    • utility(SQL*Loader)的利用

 

sqlldr1

 

SQL*Loader的概要
 SQL*Loader是指什么

  • 附属于Oracle Database 的utility之一
  • 通过准备SQL*Loader的控制语句件,从外部语句件中灵活地执行加载

sqlldr2

 

 传统路径模式 /直接路径模式

  • 准备灵活高效的执行模式
    • 灵活) 传统路径模式
      • 内部制成INSERT语句,执行INSERT

sqlldr3

  • 高速) 直接路径模式
    • 内部不会执行INSERT
    • 生成块(ORACLE_BLOCK=I/O的单位)映射
      直接进行物理写入(=直接加载)
    • 有一部分限制

 

本文永久地址: http://www.askmaclean.com/archives/sqlldr.html

sqlldr4

 

参考)直接加载HWM
HWM高水位线是指什么

  • 在表内被分配的块中,至此插入数据的最后的块
  • 高水位线(High Water Mark)

sqlldr5

  • 表的全扫描时到达高水位线为止都会执行读取

优点:表的尺寸较小,数据量较少时可以进行高速搜索

缺点:大量删除等,HWM以前的空白较多时,实际记录数就会花费较多的时间

 

sqlldr6

 

参考)直接加载HWM
 直接加载的写入

  • 直接加载会在HWM之后写入数据
  • 为了制成块映射,直接物理写入不会使用正在使用的块
  • 直接加载写入速度较快,可以使用无法利用的空白区域
    (比HWM更靠前的空白区域)

sqlldr7

  • 对频繁删除数据的表执行直接加载时,请考虑降低的高水位线
    • 参考Appendix

SQL*Loader的并行处理
 并行直接路径

 

  • 可以对同一表或者分区表的同时进行直接加载
    • 重新准备
      • 分割SQL*Loader的同时执行数、外部语句件(数据)
      • 配合外部语句件制成控制语句件
        (或者执行时,在参数DATA中指定外部语句件)
    • 执行时指定参数 PALALLEL=TRUE

sqlldr8

 

SQL*Loader使用例
 控制语句件的制成

 

sqlldr9

 

SQL*Loader使用例
 执行例

  • 传统路径模式

% sqlldr scott/xx CONTROL=emp.ctl DATA=emp.dat

  • 直接路径模式

% sqlldr scott/xx CONTROL=emp.ctl DATA=emp.dat DIRECT=true

  • 并行直接路径模式

% sqlldr scott/xx CONTROL=emp.ctl DATA=emp1.dat LOG=emp1.log

  DIRECT=true PARALLEL=true &

% sqlldr scott/xx CONTROL=emp.ctl DATA=emp2.dat LOG=emp2.log

  DIRECT=true PARALLEL=true &

 

需要事先设置主key都无效,以及参数

SKIP_INDEX_MAINTENANCE = true

 

 

  • 直接路径模式比传统型路径模式快多了

 

外部表的概要 外部表是指什么

  • Oracle Database内中不存在的表
  • 通过访问驱动,从Oracle Database从执行读取专用的访问
    • ORACLE_LOADER
    • ORACLE_DATAPUMP
  • 9i R1~
  • 10g R1~
  • 对外部表的DML(INSERT/UPDATE/DELETE)的操作、
    虽然无法添加索引,但可以制成视图以及synonym
  • 外部表的访问不会经过缓冲区高速缓存(后述)

sqlldr10

 

外部表的访问驱动
 ORACLE_LOADERORACLE_DATAPUMP

 

  • ORACLE_LOADER          9iR1
    • 外部语句件仅限text数据语句件
    • 可以使用被压缩的text数据语句件        11gR2
      • PREPROCESSOR指定(后述)
  • ORACLE_DATAPUMP                  10gR1
    • 通过访问驱动使用完成卸载的语句件
      • 外部语句件是不依赖于平台的二进制语句件
      • 无法使用DATAPUMP(expdp)的dmp语句件
    • 通过UNLOAD制成的外部语句件会使用Oracle Database相互的
      数据交換
    • 通过访问驱动的功能可以压缩、加密外部语句件
    • 比ORACLE_LOADER访问驱动速度更快

 

外部表的使用例 使用前的准备

  • 决定存放外部表的OS上的目录,制成Oracle Database的目录对象

% sqlplus / as sysdba

SQL> CREATE DIRECTORY ext_tbls AS ‘/app/oracle/admin/orcl/ext_tbls’;

  • 对于制成外部表的Oracle Database上的用户,赋予目录角色权限

SQL> GRANT READ ON DIRECTORY ext_tbls TO scott;

SQL> GRANT WRITE ON DIRECTORY ext_tbls TO scott;

 

  • 对外部表执行SELECT语句
SQL> select * from ext_emp;

     EMPNO ENAME      JOB       HIREDATE     DEPTNO
---------- ---------- --------- -------- ----------
      1001 ichiro     SALESMAN  00-10-25         10
      1002 jiro       ANALYST   01-11-25         20
      1003 saburo     MANAGER   02-12-25         30
SQL>

 

 

外部表的使用例 使用了ORACLE_DATAPUMP访问驱动定义

  • 作为制成外部表的一环,会制成外部语句(转储语句)
  • 使用外部语句制成外部表

sqlldr11

 

  • 通过使用预处理程序,可以将作为访问驱动不支持的语句,作为外部语句来使用
  • 解压被压缩的外部语句
  • 制成储存预处理程序的目录对象,赋予权限

 

外部语句件的定义语句

% cd /app/oracle/admin/orcl/exec_dir

% ln -s /bin/zcat

% sqlplus / as sysdba

SQL> CREATE DIRECTORY exec_dir AS

      '/app/oracle/admin/orcl/exec_dir';

SQL> GRANT READ ON DIRECTORY exec_dir TO scott;

SQL> GRANT EXECUTE ON DIRECTORY exec_dir TO scott;





CREATE TABLE ext_emp (
  empno    NUMBER(4),
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  hiredate DATE,
 deptno   NUMBER(2)

)

ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_tbls
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    PREPROCESSOR exec_dir:'zcat'
    FIELDS TERMINATED BY ','(
      ...
    )
  )
  LOCATION ('ext_emp.dat.gz')
);



使用了外部表数据加载

  • 直接使用DML语句(INSERT / UPDATE / MERGE / DELETE)
    • 可以不去在意访问驱动种类来使用。

INSERT INTO emp SELECT * FROM ext_emp;

 

  • 与SQL*Loader相同,可以直接加载
    • 与SQL*Loader的直接路径相同,使用比HWM较后的块

INSERT /*+ APPEND */ INTO emp SELECT * FROM ext_emp;
通过PL/SQL可以作为批量处理的一部分来处理。

 

参考)DML错误记录

  • 可以通过INSERT、UPDATE、MERGE、DELETE语句来使用
    • 至此,将大量行作为对象的单一DML中就会发生错误,所有处理就都会被回滚
    • 上述DML中通过添加「ERROR LOGS」句来使用

 

INSERT INTO emp (empno, ename, dptno, sal)
  ( SELECT empno, ename, dptno, sal FROM ext_emp )
  LOG ERRORS INTO ERR$_EMP ('WEEKLY_BATCH') REJECT LIMIT 50;

 

  • 错误记录表(上記的ERR$_EMP)是通过 DBMS_ERRLOG package来制成的
    • 执行DML与错误记录表的写入事务
    • 因为DML操作中记录失败数据的项目,并不会写入所有错误 例) ORA-01653(区域不足)、ORA-01555

 

外部表的访问驱动性能比較 ORACLE_LOADER vs ORACLE_DATAPUMP

  • ORACLE_DATAPUMP中使用的外部语句是二进制,基本不会发生型变更
  • Oracle Database之间的数据合作,推荐使用访问驱动ORACLE_DATAPUMP

sqlldr12

 

SQL*Loader与外部表的比較
 使用分期表

 

  • 业务应用所使用的表几乎都无法直接加载
    • 一般而言,分期表(*1)中加载过一次数据之后,通过检查错误以及执行变换处理,可以加载业务应用的表
  • 通过使用外部表,大部分情况都可以通过加载分期表来节省人工

sqlldr13

 

比较SQL*Loader与外部表 使用缓冲区高速缓存

 

  • 作为分区表来使用的永续表的查询处理基本上都会经过缓冲区高速缓存
    • 2次以后的访问可以实现高速化
    • 根据需要,可能会有舍弃现有高速缓存的情况
  • 对外部表的查询处理不会经过Oracle Database的缓冲区高速缓存
    • 可以不舍弃现有的高速缓存来执行处理
    • 多次访问可能会没有高速缓存,从而导致没有效率

多次访问时请加载分期表

 

sqlldr14

 

 

 SQL*Loader与外部表的区分

 

  • 适合SQL*Loader的处理
    • 通过网络加载数据
    • 通过复合对象关系加载数据
    • 加载使用了次要语句的LOB以及collection。
    • 不是固定的,而是临时的加载

 

  • 基本上都是执行使用了外部表的加载
    • 使用了任意SQL函数的变更处理
    • 直接路径插入
    • 并行加载
    • 使用压缩完成的语句(11g R2以后)

 

参考)通过SQL*Loader加载LOB

  • 可以从其他语句中读入LOB値

 

sqlldr15

 

 

  • 降低HWM的操作
  • 在正常业务中,可以以不访问的状态执行

1.理论备份utility(EMP/IMP) + 删除元表(DROP)

2.CREATE AS SELECT + TRUNCATE + INSERT SELECT

3.CREATE AS SELECT +删除元表(DROP) + RENAME

4.ALTER TABLE <表名> MOVE TABLESPACE <表区域名>) (8i ~)

※需要维护索引

  • 以online状态执行

1.重新定义online表(Enterprise Edition功能)

2.缩小online段(标准功能)              10g R1~

 

 

 

关注刘相兵的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

Speak Your Mind

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569