SQL*Loader导入百万级别数据

生成百万级的数据文件

连接到本地数据库(自己安装的):

找一个合适大小的表:


SELECT COUNT(0) FROM DBA_OBJECTS;
COUNT(0)

----------

65389


然后找一个有20行记录的表,对其进行笛卡尔运算,即可生成130万条记录了.

为了更好地体现通用性,我们在输出时对created日期做一下格式转换:



select a.owner||',"'||a.object_name||'",'||a.object_id||','||
to_char(a.created,'yyyy-mm-dd hh24:mi:ss')||','||a.status
from dba_objects a,(select rownum rn from dual content by rownum<=20) b ;

示例代码保存为SQL文件:getobject.sql


set echo off
set term off
set line 1000 pages 0
set feedback off
set heading off
spool [当前路径]/ldr_object.csv
@[当前路径]/getobject.sql
spool off
set heading on
set feedback on
set term on
set echo on

示例代码保存为:SQL文件:call.sql

然后在SQL*Plus命令行环境中,执行下列命令即可:

SQL> @[当前路径]call.sql

然后就耐心等待把,132万的记录量,输出也是需要一定时间的.

查看:



exit
du -m ldr_object.csv

//输出的数据以M为单位.


初始化环境

创建表:


CREATE TABLE OBJECTS(
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(50),
OBJECT_ID NUMBER,
STATUS VARCHAR2(2000),
CREATED DATE);

创建Index.



create index idx_obj_owner_name on objects(owner,object_name);

执行导入

创建控制文件如下:



LOAD DATA
INFILE ldr_object.csv
INSERT INTO TABLE OBJECTS
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(owner,
object_name ,
object_id,
created date 'yyyy-mm-dd hh24:mi:ss',
status char(2000)
)
BEGINDATA

控制文件保存为:ldr_object.ctl

这里注意,对于CREATED列,我们指定了日期格式,并进行了转换,这个格式一定要与数据文件中日期格式相符,不然日期格式转换时会报错并导致数据加载失败.

按照默认参数执行SQLLDR,看看需要多长时间,同时指定ERRORS参数值为10,明确指定出现10次错误即中止加载:



sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl ERRORS=10

5.4.4 能不能再快一点呢

SQLLDR常规路径导入时默认一次加载64行,现在要加载的总行数已经达到百万级,十位数显然太小,我们首先尝试修改该值,先直接在后面加个0好了,看看能对效率起到多大的提升:

执行如下命令:sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl ERRORS=10 ROWS=640

日志节选信息:



value used for ROWS parameter changed from 640 to 84
Table OBJECTS:
1307820 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 254856 bytes(84 rows)

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 1307820

Total logical records rejected:0

Total logical records discarded: 0

Run began on Wed Jun 05 14:18:43 2013

Run ended on Wed Jun 05 14:28:56 2013

Elapsed time was: 00:10:12.39

CPU time was:00:00:16.40


注意节选信息的第一行,该信息是提示由于640行所占用的空间已经超过了参数BINDSIZE的默认值,因此自动修改到最大可承受的84行,这说明BINDSIZE参数默认值偏小.速度只比刚才小了2秒(变化几乎可以忽略)

再进一步调整BINDSIZE参数值,默认为256k,我们将其修改为10M(1024kb * 1024 * 10 = 10485760),同时将一次加载的行数提高到5000.



sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl ERRORS=10 ROWS=5000 BINDSIZE=10485760

时间变得更长,正在研究:

大约时间是:13:48

Oracle LOGO
image-1838

能不能再快一点呢

所有参数默认,只打开直接路径加载:



sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl DIRECT=TRUE

靠,这个速度才是王道啊!



Table OBJECTS:

1307820 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Date conversion cache disabled due to overflow (default size: 1000)

Bind array size not used in direct path.

Column array rows : 5000

Stream buffer bytes: 256000

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 1307820

Total logical records rejected:0

Total logical records discarded: 0

Total stream buffers loaded by SQL*Loader main thread: 1250

Total stream buffers loaded by SQL*Loader load thread: 4996

Run began on Wed Jun 05 14:55:12 2013

Run ended on Wed Jun 05 14:56:28 2013

Elapsed time was: 00:01:15.74

CPU time was:00:00:11.80


有没有可能更快呢

这究竟是希望还是欲望,已经说不清楚了,反正没个尽头。

直接导入路径导入可用的参数也有不少,不过我们这里总数据量不大,因此实际能够起到效率提升的不多,我准备主要从以下两个参数入手:

  1. STREAMSIZE : 直接路径加载默认读取全部记录,因此不需要设置ROWS参数,读取到的数据处理后存入流缓存区,即STREAMSIZE参数,该参数默认值为256kb,这里加大到10MB.
  2. DATE_CACHE : 该参数指定一个转换后日期格式的缓存区,以条为单位,默认值1000条,即保存1000条转换后的日期格式,由于我们要导入的数据中有日期列,因此加载该参数值到5000,以降低日期转换带来的开销.

修改参数后执行命令最终形式如下所示:



sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl DIRECT=TRUE STREAMSIZE=10485760 DATE_CACHE=5000

可能已经达到性能瓶颈:



Date cache:

Max Size: 5000

Entries : 1207

Hits : 1306613

Misses :0

Bind array size not used in direct path.

Column array rows : 5000

Stream buffer bytes:16777216

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 1307820

Total logical records rejected:0

Total logical records discarded: 0

Total stream buffers loaded by SQL*Loader main thread: 1250

Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Wed Jun 05 15:15:39 2013

Run ended on Wed Jun 05 15:17:07 2013

Elapsed time was: 00:01:28.14

CPU time was:00:00:09.05

SQL*Loader加载综述

事实上想在Oracle存储过程中调用SQLLDR非常麻烦,因为SQLLDR是一个执行程序而不是一个接口,在9i之前版本要在存储过程中实现类似功能也很复杂,虽然可以通过UTL_FILE之类的包间接实现,但需要编写大量脚本,考虑字符截取,过滤,判断等诸多事宜。

一个不慎就可能造成执行报错,或者更不慎,执行到一般的时候报错(可能比没执行还要麻烦),幸运的是,9i及之后的版本,Oracle提供了一个新的功能---外部表(External Tables),顾名思义就是数据存储在数据库之外的表,这是一个号称"SQL*Loader替代者"的新特性.

2013/06/05 15:25:57

--EOF--

SQL*Loader加载数据

加载有分割符的数据

数据文件

示例数据文件(保存为:oracledata.data):


"TE,SE","D,ASA",1520 //","必须在一个字符串中,比如:"D,ASA"是正确的.
TES,"DAT,E",1598
"EW,W",TET,1998
DSEW,"E,WTW",1580

控制文件

示例控制文件(另存为:ldr_case.ctl)

LOAD DATA
INFILE oracledata.data
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
//OPTIONALLY ENCLOSED BY 参数指明界定符.
(ENAME,JOB,SAL)
BEGINDATA

–>之后就是执行命令:

sqlldr SCOTT/TIGER CONTROL=ldr_case.ctl
//然后
sqlplus SCOTT/TIGER
SELECT * FROM BONUS;

导入没有分割符的数据

示例数据

示例数据(保存为:oracledata1.data):


SMITH CLEAK 3904
ALLEN SALESMAN 2891
WARDA SALESMAN 3128
KINGA PRESIDENT 2523

控制文件

控制文件如下(另存为:ldr_case1.ctl):


LOAD DATA
INFILE oracledata1.data
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),//1
JOB position(7:15),
SAL position(17:20)//position可以用于定位数据,还有以下格式:
//position(*+2:15):表示从(1)结束之后增加两个字符.
//position(*)char(9):相对偏移量+类型和长度的优势在于,
//你只需要为第一列指定开始位置,其他列只需要指定列长度就可以了,实际使用比较方便
)
BEGINDATA

接下来的事情,还用我说嘛?

Oracle LOGO
image-1829

假如导入数据的列,比表中的数据少怎么办

示例数据

表结构:

——————————-
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER
COMM NUMBER
——————————-

仔细看来,少一列是没有任何问题的.

此处就不演示怎么插入少一列的数据了.

设置默认值:只需要修改控制文件

示例数据(保存为:oracledata2.data):


SMITH CLEAK 3904
ALLEN SALESMAN 2891
WARDA SALESMAN 3128
KINGA PRESIDENT 2523

控制文件

控制文件如下(另存为:ldr_case2.ctl):


LOAD DATA
INFILE oracledata1.data
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),//1
JOB position(7:15),
SAL position(17:20),
COMM "0" //设置默认值.
)
BEGINDATA

当然也可以通过SAL列的值来设置COMM列的值,只需要修改控制文件:


LOAD DATA
INFILE oracledata1.data
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),//1
JOB position(7:15),
SAL position(17:20),
COMM "substr(:SAL,1,1)"
)
BEGINDATA

其余的就跟上述一样了.

为Oracle创建scott用户及相关数据库

简述

在Oracle 11GR2里面已经没有scott用户了(在以前的数据库中该用户默认是锁定的),为了我们测试方便,可以创建一个scott用户,后面的部分的示例也将关联到此用户.

Oracle LOGO
image-1827

创建用户与数据库的脚本

在登录Oracle数据库之后,运行如下脚本:


//用户名是:SCOTT
//密码是:TIGER

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
CONNECT SCOTT/TIGER
DROP TABLE DEPT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
) ;
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;

SET TERMOUT ON
SET ECHO ON

SQL*Loader基本加载

基本准备工作

*:此处采用控制文件与数据文件分离的方法.

新建一个控制文件(名称与类型可任意,此处默认使用:ldr_case1.ctl):


LOAD DATA
INFILE oracledata.data
INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA

详细:

  1. 开始部分,说明是要加载数据.
  2. 加载文件的地址,后面可以是相对或者绝对地址.[INFILE *]也可以
  3. [APPEND][REPLACE][INSERT][TRUNCATE] INTO tbl_name :第一个参数是追加到现有数据后面;第二个参数是替换掉现有数据,也就是先DELETE表中的数据,然后再进行INSERT操作;第三个参数是默认使用的,表必须为空,如果表非空的话,就会报错误;第四个参数类似于[REPLACE]参数,不同的是,这个参数使用TRUNCATE进行删除,然后再INSERT.
  4. 设置分隔符,此处较为复杂,下次继续分析.
  5. 表中的列名,无序,但名称必须对应.
  6. 以下为待加载数据,仅当第二行为INFILE * 时有效.

sqlldr
image-1820

示例部分

示例[控制文件与数据文件使用同一个文件]:


LOAD DATA
INFILE *
INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA

TES1T,TE1STE,1234
TE2ST,TE41STE,1234
TES7T,TES4TE,1234
TE52ST,TES41TE,1234

示例[控制文件与数据文件分离[通常这样使用,比较方便]]:

控制文件:

LOAD DATA
INFILE oracledata.data
INTO TABLE
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA

数据文件:

TES1T,TE1STE,1234
TE2ST,TE41STE,1234
TES7T,TES4TE,1234
TE52ST,TES41TE,1234

执行与产生的文件

然后再终端执行如下命令:
请先确认,Oracle数据库已经运行,表已经创建!

sqlldr user/password CONTROL=ldr_case1.ctl

//user = 用户名
//password = 密码
//CONTROL = 控制文件路径

执行完命令,会产生一个与控制文件同名的日志文件(.log结尾),错误文件(.bad,如果dba没有修改的),如果你设定了废弃文件(.dsc结尾),那么还会产生废弃文件.

VMware安装Redhat Server6.4,之后安装Oracle 11GR2视频教程

PS:新手录制,有问题请留言告知.

官方的安装教程

官方文档:database/doc/index.htm ,用浏览器打开就可以看了.

  1. 打开页面,右侧找到”Database Installation Guide “单击右侧的HTML
  2. 参照上面的步骤,可以看看[视频教程不是按照官方教程来安装的]
  3. 下面的视频教程,讲到了配置监听

Oracle LOGO
image-1807

只是一点提示

昨天花了半下午终于录制完成了,直接实际操作的,其中需要以下几个软件:

  • VMware 9.0 [英文版][32/64:依你的操作系统而定]
  • Redhat Server 6.4 [英文版][64位]
  • Oracle安装镜像[英文版][64位]
  • Winscp [用于从Windows连接到Linux]

我的配置是:

  • Windows 8 [X64]
  • 8G内存
  • 1T硬盘[分配给Redhat的是50Gm,用于虚拟机的是一个单独的分区]
  • Inter(R) Pentium(R) CPU G630 @ 2.70GHZ

其余部分在视频里面都有介绍,如果有问题,欢迎评论指正.

下载地址

视频下载地址[用屏幕录像专家录制的,总大小:约为200M,国内115网盘]:

录像4[.exe文件,可直接观看]

录像5[.exe文件,可直接观看]

录像6[.exe文件,可直接观看]

录像5[.exe文件,可直接观看]

前面试录了3个,后来被删除了.后面有时间继续录制.