SQLLOADER的直接路径装载将会导致主键或者唯一约束的相关索引无效。
简单进行一个测试。
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>cd c:\
C:\>sqlplus admin/admin
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 2月 3 09:50:18 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> DROP TABLE TEST;
表已删除。
SQL> CREATE TABLE TEST(EMPNO NUMBER,ENAME VARCHAR2(20),SAL NUMBER(7,2),DEPTNO NUMBER(3));
表已创建。
SQL> ALTER TABLE TEST ADD CONSTRAINT TEST_PK PRIMARY KEY(EMPNO);
表已更改。
SQL> ALTER TABLE TEST ADD CONSTRAINT ENAME_UNIQUE UNIQUE(ENAME);
表已更改。
SQL> CREATE INDEX TEST_DEPTNO_IDX ON TEST(DEPTNO);
索引已创建。
SQL> COL CONSTRAINT_NAME FORMAT A20
SQL> COL CONSTRAINT_TYPE FORMAT A20
SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS FROM USER_CONSTRAINTS WHERE
TABLE_NAME='TEST';
CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
-------------------- -------------------- --------
TEST_PK P ENABLED
ENAME_UNIQUE U ENABLED
SQL> COL INDEX_NAME FORMAT A20
SQL> COL INDEX_TYPE FORMAT A20
SQL> SELECT INDEX_NAME,INDEX_TYPE,STATUS FROM USER_INDEXES WHERE TABLE_NAME='TEST';
INDEX_NAME INDEX_TYPE STATUS
-------------------- -------------------- --------
TEST_PK NORMAL VALID
ENAME_UNIQUE NORMAL VALID
TEST_DEPTNO_IDX NORMAL VALID
SQL> HOST
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\>
首先以常规路径方式导入一部分数据。
C:\>type test.ctl
LOAD DATA
INFILE *
APPEND
INTO TABLE TEST
FIELDS TERMINATED BY ','
(EMPNO,ENAME,SAL,DEPTNO)
BEGINDATA
7499,ALLEN,1600,30
7566,JONES,3123.75,20
7654,MARTIN,1312.5,30
7658,CHAN,3450,20
7782,CLARK,2572.5,10
7839,KING,5500,10
7934,MILLER,920,10
C:\>sqlldr admin/admin control=test.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 2月 3 10:12:16 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
达到提交点 - 逻辑记录计数 7
C:\>EXIT
SQL> SELECT * FROM TEST;
EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
7499 ALLEN 1600 30
7566 JONES 3123.75 20
7654 MARTIN 1312.5 30
7658 CHAN 3450 20
7782 CLARK 2572.5 10
7839 KING 5500 10
7934 MILLER 920 10
已选择7行。
1、普通方式装载违反唯一约束的数据
C:\>sqlldr admin/admin control=test.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 2月 3 10:14:41 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
达到提交点 - 逻辑记录计数 7
查看一下数据
SQL> SELECT * FROM TEST;
EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
7499 ALLEN 1600 30
7566 JONES 3123.75 20
7654 MARTIN 1312.5 30
7658 CHAN 3450 20
7782 CLARK 2572.5 10
7839 KING 5500 10
7934 MILLER 920 10
已选择7行。
可以看见数据没有被导入，违反唯一性约束了嘛。
SQLLOADER导入日志文件里有如下内容：
记录 1: 被拒绝 - 表 TEST 出现错误。
ORA-00001: 违反唯一约束条件 (ADMIN.TEST_PK)
记录 2: 被拒绝 - 表 TEST 出现错误。
ORA-00001: 违反唯一约束条件 (ADMIN.TEST_PK)
记录 3: 被拒绝 - 表 TEST 出现错误。
ORA-00001: 违反唯一约束条件 (ADMIN.TEST_PK)
记录 4: 被拒绝 - 表 TEST 出现错误。
ORA-00001: 违反唯一约束条件 (ADMIN.TEST_PK)
记录 5: 被拒绝 - 表 TEST 出现错误。
ORA-00001: 违反唯一约束条件 (ADMIN.TEST_PK)
记录 6: 被拒绝 - 表 TEST 出现错误。
ORA-00001: 违反唯一约束条件 (ADMIN.TEST_PK)
记录 7: 被拒绝 - 表 TEST 出现错误。
ORA-00001: 违反唯一约束条件 (ADMIN.TEST_PK)
表 TEST:
0 行 加载成功。
由于数据错误, 7 行 没有加载。
由于所有 WHEN 子句失败, 0 行 没有加载。
由于所有字段都为空的, 0 行 没有加载。
但是此时的索引和约束都是正常的。
SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS FROM USER_CONSTRAINTS WHERE T
ABLE_NAME='TEST';
CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
-------------------- -------------------- --------
TEST_PK P ENABLED
ENAME_UNIQUE U ENABLED
SQL> SELECT INDEX_NAME,INDEX_TYPE,STATUS FROM USER_INDEXES WHERE TABLE_NAME='TES
T';
INDEX_NAME INDEX_TYPE STATUS
-------------------- -------------------- --------
TEST_PK NORMAL VALID
ENAME_UNIQUE NORMAL VALID
TEST_DEPTNO_IDX NORMAL VALID
2、直接路径装载违反唯一约束的数据
C:\>type test.ctl
OPTIONS (DIRECT=TRUE)
UNRECOVERABLE
LOAD DATA
INFILE *
APPEND
INTO TABLE TEST
FIELDS TERMINATED BY ','
(EMPNO,ENAME,SAL,DEPTNO)
BEGINDATA
7499,ALLEN,1600,30
7566,JONES,3123.75,20
7654,MARTIN,1312.5,30
7658,CHAN,3450,20
7782,CLARK,2572.5,10
7839,KING,5500,10
7934,MILLER,920,10
C:\>sqlldr admin/admin control=test.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 2月 3 10:21:52 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
加载完成 - 逻辑记录计数 7。
C:\>exit
SQL> SELECT * FROM TEST;
EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
7499 ALLEN 1600 30
7566 JONES 3123.75 20
7654 MARTIN 1312.5 30
7658 CHAN 3450 20
7782 CLARK 2572.5 10
7839 KING 5500 10
7934 MILLER 920 10
7499 ALLEN 1600 30
7566 JONES 3123.75 20
7654 MARTIN 1312.5 30
7658 CHAN 3450 20
EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
7782 CLARK 2572.5 10
7839 KING 5500 10
7934 MILLER 920 10
已选择14行。
可以看见数据已经被成功插入了。虽然加载成功了，但是sqlloader在日志里记录了一下内容：
表 TEST 的以下索引已处理:
索引 ADMIN.ENAME_UNIQUE 无法使用, 因为:
ORA-00001: 违反唯一约束条件 (ADMIN.ENAME_UNIQUE)
索引 ADMIN.TEST_DEPTNO_IDX 已成功加载, 具有 7 个关键字
索引 ADMIN.TEST_PK 无法使用, 因为:
ORA-00001: 违反唯一约束条件 (ADMIN.TEST_PK)
表 TEST:
7 行 加载成功。
由于数据错误, 0 行 没有加载。
由于所有 WHEN 子句失败, 0 行 没有加载。
由于所有字段都为空的, 0 行 没有加载。
SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS FROM USER_CONSTRAINTS WHERE T
ABLE_NAME='TEST';
CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
-------------------- -------------------- --------
TEST_PK P ENABLED
ENAME_UNIQUE U ENABLED
约束还是好的，但是此时的索引已经无效了。
SQL> SELECT INDEX_NAME,INDEX_TYPE,STATUS FROM USER_INDEXES WHERE TABLE_NAME='TES
T';
INDEX_NAME INDEX_TYPE STATUS
-------------------- -------------------- --------
TEST_PK NORMAL UNUSABLE
ENAME_UNIQUE NORMAL UNUSABLE
TEST_DEPTNO_IDX NORMAL VALID
此时重建索引会报ORA-01452错误。
SQL> ALTER INDEX ENAME_UNIQUE REBUILD ONLINE;
ALTER INDEX ENAME_UNIQUE REBUILD ONLINE
*
第 1 行出现错误:
ORA-01452: 无法 CREATE UNIQUE INDEX; 找到重复的关键字
3、直接路径装载未违反唯一约束的数据
SQL> TRUNCATE TABLE TEST;
表被截断。
SQL> ALTER INDEX TEST_PK REBUILD ONLINE;
索引已更改。
SQL> ALTER INDEX ENAME_UNIQUE REBUILD ONLINE;
索引已更改。
SQL> INSERT INTO TEST SELECT EMPNO+ROWNUM,ENAME||'A',SAL,DEPTNO FROM SCOTT.EMP WHERE ROWNUM<8;
已创建7行。
SQL> COMMIT;
提交完成。
SQL> SELECT * FROM TEST;
EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
7370 SMITHA 800 20
7501 ALLENA 1600 30
7524 WARDA 1250 30
7570 JONESA 2975 20
7659 MARTINA 1250 30
7704 BLAKEA 2850 30
7789 CLARKA 2450 10
已选择7行。
SQL> HOST TYPE TEST.CTL
OPTIONS (DIRECT=TRUE)
UNRECOVERABLE
LOAD DATA
INFILE *
APPEND
INTO TABLE TEST
FIELDS TERMINATED BY ','
(EMPNO,ENAME,SAL,DEPTNO)
BEGINDATA
7499,ALLEN,1600,30
7566,JONES,3123.75,20
7654,MARTIN,1312.5,30
7658,CHAN,3450,20
7782,CLARK,2572.5,10
7839,KING,5500,10
7934,MILLER,920,10
SQL> HOST sqlldr admin/admin control=test.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 2月 3 10:40:57 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
加载完成 - 逻辑记录计数 7。
SQL> SELECT * FROM TEST;
EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
7370 SMITHA 800 20
7501 ALLENA 1600 30
7524 WARDA 1250 30
7570 JONESA 2975 20
7659 MARTINA 1250 30
7704 BLAKEA 2850 30
7789 CLARKA 2450 10
7499 ALLEN 1600 30
7566 JONES 3123.75 20
7654 MARTIN 1312.5 30
7658 CHAN 3450 20
EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
7782 CLARK 2572.5 10
7839 KING 5500 10
7934 MILLER 920 10
已选择14行。
SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS FROM USER_CONSTRAINTS WHERE T
ABLE_NAME='TEST';
CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
-------------------- -------------------- --------
TEST_PK P ENABLED
ENAME_UNIQUE U ENABLED
SQL> SELECT INDEX_NAME,INDEX_TYPE,STATUS FROM USER_INDEXES WHERE TABLE_NAME='TES
T';
INDEX_NAME INDEX_TYPE STATUS
-------------------- -------------------- --------
TEST_PK NORMAL VALID
ENAME_UNIQUE NORMAL VALID
TEST_DEPTNO_IDX NORMAL VALID
使用直接路径装载的时候，如果表的字段有主键或者唯一约束，并且装载的数据违反了唯一性约束，那么SQLLOADER将相关索引置为无效，继续装载。装载完毕后，索引不会自动置为有效，需要DBA的手工干预。
