sqlldr直接路径direct=y导数方式导致索引失效全表扫描

1.create创建集合
create table t nologging parallel 8 as select rownum x from dual connect by level<5000000;
****************************
ROYE@oggm >create table t nologging parallel 8 as select rownum x from dual connect by level<5000000;
create table t nologging parallel 8 as select rownum x from dual connect by level<5000000
                                                            *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation
***************************
2.insert采用集合写法(不到3分钟)
insert into t_insert select rownum from dual connect by level<=20000000;
**************************
3.xmltable并行不记日志
create table t nologging parallel 8 as select rownum id from xmltable('1 to 50000000');--30s
由于建立的字段属于number型，number型字段列靠右，导出前面会有空格
Table created.

Elapsed: 00:00:32.12
col SEGMENT_NAME for a20
select segment_name,bytes/1024/1024 m from user_segments where segment_name='T';

SEGMENT_NAME                  M
--------------------                     ----------
T                                       604

create table t nologging parallel 8 as select rownum||'' id from xmltable('1 to 50000000');--字段是varchar2型

16:41:25 ROYE@oggm >create table t nologging parallel 8 as select rownum||'' id from xmltable('1 to 50000000');

Table created.

Elapsed: 00:00:38.91

select segment_name,bytes/1024/1024 m from user_segments where segment_name='T';

SEGMENT_NAME                  M
--------------------                     ----------
T                                       754.875

--分析表
analyze table roye.t compute statistics;
col OWNER for a10
col table_name for a10
select t.owner,t.table_name,t.tablespace_name,t.num_rows,t.last_analyzed from dba_tables T where T.owner='SCOTT' and table_name='T';

OWNER      TABLE_NAME TABLESPACE_NAME        NUM_ROWS LAST_ANALYZED
---------- ---------- -------------------- ---------- -------------------
ROYE       T          USERS                  50000000 2016-07-06 14:16:07

3、将表数据到出到文件中
--1.sqlplus的方式很慢

cd /home/oracle/dir
vi net.sql

set echo off
set feedback off
set newpage none
set verify off 
set pagesize 0  
set term off
set trims on
set linesize 600
set heading off
set timing off
set numwidth 38 
SPOOL aa.txt 
select id FROM roye.t; 
SPOOL OFF

sqlplus / as sysdba;
conn scott/tiger;
@/home/oracle/dir/net.sql
----------------------------------------------------
[oracle@PHY-026 ~]$ ps -ef|grep p0
oracle    45799      1  0 14:26 ?        00:00:00 ora_p000_oggm
oracle    45801      1  0 14:26 ?        00:00:00 ora_p001_oggm
oracle    45803      1  0 14:26 ?        00:00:00 ora_p002_oggm
oracle    45808      1  0 14:26 ?        00:00:00 ora_p003_oggm
oracle    45810      1  0 14:26 ?        00:00:00 ora_p004_oggm
oracle    45813      1  0 14:26 ?        00:00:00 ora_p005_oggm
oracle    45815      1  0 14:26 ?        00:00:00 ora_p006_oggm
oracle    45817      1  0 14:26 ?        00:00:00 ora_p007_oggm
oracle    50590  47841  0 14:28 pts/6    00:00:00 grep p0
oracle    86857      1  0 Jun14 ?        00:07:36 ora_psp0_oggm

--查看活动会话(touse)
SET LINE 1000 PAGES 1000
col  username for a20
col  machine for a20
col  event for a40
col MODULE for a15
select INST_ID,sid,serial#,username, machine,status,MODULE,last_call_et,event,p1,p2,p3 from gv$session
where  username is not null
and status='ACTIVE'
order by 1,4,8,7,5;
----------------------------------------------------------------------------

   INST_ID        SID    SERIAL# USERNAME   MACHINE              STATUS   MODULE          LAST_CALL_ET EVENT                        P1         P2             P3
---------- ---------- ---------- ---------- -------------------- -------- --------------- ------------ -------------------- ---------- ---------- --------------
         1        118         31 SYS        PHY-026.PRI01.SF.Bei ACTIVE   sqlplus@PHY-026            0 SQL*Net message to c 1650815232          1              0
                                            jing                          .PRI01.SF.Beiji              lient
                                                                          ng (TNS V1-V3)

         1         94       4061 SYS        PHY-026.PRI01.SF.Bei ACTIVE   sqlplus@PHY-026          274 PX Deq Credit: send   268566527          1    10099505456
                                            jing                          .PRI01.SF.Beiji              blkd
                                                                          ng (TNS V1-V3)

         1         97       1665 SYS        PHY-026.PRI01.SF.Bei ACTIVE   sqlplus@PHY-026          274 PX Deq Credit: send   268566527          1    10099502704
                                            jing                          .PRI01.SF.Beiji              blkd
                                                                          ng (TNS V1-V3)

         1        100        611 SYS        PHY-026.PRI01.SF.Bei ACTIVE   sqlplus@PHY-026          274 PX Deq Credit: send   268566527          1    10099501328
                                            jing                          .PRI01.SF.Beiji              blkd
                                                                          ng (TNS V1-V3)

         1        106        153 SYS        PHY-026.PRI01.SF.Bei ACTIVE   sqlplus@PHY-026          274 PX Deq Credit: send   268566527          1    10099528160
                                            jing                          .PRI01.SF.Beiji              blkd
                                                                          ng (TNS V1-V3)

         1        109        123 SYS        PHY-026.PRI01.SF.Bei ACTIVE   sqlplus@PHY-026          274 PX Deq Credit: send   268566527          1    10099497784
                                            jing                          .PRI01.SF.Beiji              blkd
                                                                          ng (TNS V1-V3)

         1        112        119 SYS        PHY-026.PRI01.SF.Bei ACTIVE   sqlplus@PHY-026          274 PX Deq Credit: send   268566527          1    10099490904
                                            jing                          .PRI01.SF.Beiji              blkd
                                                                          ng (TNS V1-V3)

         1         91       2689 SYS        PHY-026.PRI01.SF.Bei ACTIVE   sqlplus@PHY-026          274 PX Deq Credit: send   268566527          1    10099499160
                                            jing                          .PRI01.SF.Beiji              blkd
                                                                          ng (TNS V1-V3)

         1        103        293 SYS        PHY-026.PRI01.SF.Bei ACTIVE   sqlplus@PHY-026          274 PX Deq Credit: send   268566527          1    10099500640
                                            jing                          .PRI01.SF.Beiji              blkd
                                                                          ng (TNS V1-V3)

9 rows selected.
---------------------------------------------------------
--恢复表的并行度
alter table t parallel(degree 1);
--2.通过存储过程导出数据
cd /home/oracle/dir
mkdir roye_dir
conn /  as sysdba
grant create any directory to roye;
conn roye/roye
create directory r_dir as '/home/oracle/dir/roye_dir';
DECLARE 
row_result varchar2(1024); 
selectsql varchar2(1024); 
qrycursor SYS_REFCURSOR; 
txt_handle UTL_FILE.file_type; 
BEGIN 
  selectsql := 'select id from roye.t'; 
  txt_handle := UTL_FILE.FOPEN('R_DIR','bb.txt','w'); 
  open qrycursor for selectsql; 
  loop   
      fetch qrycursor into row_result; 
      exit when qrycursor%notfound;   
      UTL_FILE.PUT_LINE(txt_handle,row_result); 
  end loop;   
  --关闭游标   
  close qrycursor; 
  UTL_FILE.FCLOSE(txt_handle); 
end;
/

********************
用时：15分12秒
生成文件大小
[oracle@PHY-026 ~]$ du -sh bb.txt
419M    bb.txt
***************************

--3.通过sqluldr2工具来导出
./sqluldr2_linux64_10204.bin user=roye/roye query="select * from t" file="t.txt"
       0 rows exported at 2016-07-07 18:21:06, size 0 MB.
 1000000 rows exported at 2016-07-07 18:21:07, size 4 MB.
 2000000 rows exported at 2016-07-07 18:21:08, size 12 MB.
 3000000 rows exported at 2016-07-07 18:21:09, size 24 MB.
 4000000 rows exported at 2016-07-07 18:21:11, size 32 MB.
 5000000 rows exported at 2016-07-07 18:21:12, size 40 MB.
 6000000 rows exported at 2016-07-07 18:21:14, size 48 MB.
 7000000 rows exported at 2016-07-07 18:21:15, size 56 MB.
 8000000 rows exported at 2016-07-07 18:21:16, size 64 MB.
 9000000 rows exported at 2016-07-07 18:21:18, size 72 MB.
10000000 rows exported at 2016-07-07 18:21:19, size 80 MB.
11000000 rows exported at 2016-07-07 18:21:21, size 88 MB.
12000000 rows exported at 2016-07-07 18:21:22, size 100 MB.
13000000 rows exported at 2016-07-07 18:21:23, size 108 MB.
14000000 rows exported at 2016-07-07 18:21:25, size 116 MB.
15000000 rows exported at 2016-07-07 18:21:26, size 124 MB.
16000000 rows exported at 2016-07-07 18:21:27, size 132 MB.
17000000 rows exported at 2016-07-07 18:21:29, size 140 MB.
18000000 rows exported at 2016-07-07 18:21:30, size 148 MB.
19000000 rows exported at 2016-07-07 18:21:31, size 156 MB.
20000000 rows exported at 2016-07-07 18:21:33, size 164 MB.
21000000 rows exported at 2016-07-07 18:21:34, size 172 MB.
22000000 rows exported at 2016-07-07 18:21:35, size 180 MB.
23000000 rows exported at 2016-07-07 18:21:36, size 192 MB.
24000000 rows exported at 2016-07-07 18:21:38, size 200 MB.
25000000 rows exported at 2016-07-07 18:21:39, size 208 MB.
26000000 rows exported at 2016-07-07 18:21:40, size 216 MB.
27000000 rows exported at 2016-07-07 18:21:41, size 224 MB.
28000000 rows exported at 2016-07-07 18:21:42, size 232 MB.
29000000 rows exported at 2016-07-07 18:21:44, size 240 MB.
30000000 rows exported at 2016-07-07 18:21:45, size 248 MB.
31000000 rows exported at 2016-07-07 18:21:46, size 256 MB.
32000000 rows exported at 2016-07-07 18:21:48, size 264 MB.
33000000 rows exported at 2016-07-07 18:21:49, size 272 MB.
34000000 rows exported at 2016-07-07 18:21:50, size 280 MB.
35000000 rows exported at 2016-07-07 18:21:51, size 292 MB.
36000000 rows exported at 2016-07-07 18:21:53, size 300 MB.
37000000 rows exported at 2016-07-07 18:21:54, size 308 MB.
38000000 rows exported at 2016-07-07 18:21:55, size 316 MB.
39000000 rows exported at 2016-07-07 18:21:57, size 324 MB.
40000000 rows exported at 2016-07-07 18:21:58, size 332 MB.
41000000 rows exported at 2016-07-07 18:22:00, size 340 MB.
42000000 rows exported at 2016-07-07 18:22:01, size 348 MB.
43000000 rows exported at 2016-07-07 18:22:02, size 356 MB.
44000000 rows exported at 2016-07-07 18:22:03, size 368 MB.
45000000 rows exported at 2016-07-07 18:22:05, size 372 MB.
46000000 rows exported at 2016-07-07 18:22:06, size 384 MB.
47000000 rows exported at 2016-07-07 18:22:07, size 392 MB.
48000000 rows exported at 2016-07-07 18:22:08, size 400 MB.
49000000 rows exported at 2016-07-07 18:22:10, size 408 MB.
50000000 rows exported at 2016-07-07 18:22:11, size 416 MB.
         output file t.txt closed at 50000000 rows, size 419 MB.
用时：1分04秒
大小419MB
4、建立被导入的表

--创建表
create table t_sqlldr(id number);
--创建索引
create index idx_ldr_id on t_sqlldr(id);

--查看索引是否失效
set linesize 300
col owner for a10
col index_name for a15
col table_name for a15
col INDEX_TYPE for a15
select owner,index_name,index_type,table_name,num_rows,status from dba_indexes where index_name='IDX_LDR_ID';

5、创建sqlldr控制文件
vi ldr.ctl
load data
infile 't.txt'
into table roye.t
fields terminated by ','
optionally enclosed by '"'
TRAILING NULLCOLS
(id)
6、直接路径导入数据，死循环查询数据
--非直接路径写法(索引是valid有效的)
sqlldr userid=scott/tiger control=ldr.ctl
--直接路径写法
sqlldr userid=scott/tiger control=ldr.ctl direct=true
declare
begin
while (true) loop
execute immediate 'select id from roye.t_sqlldr where id=round(DBMS_RANDOM.VALUE(1,10000))';
end loop;
end ;
/
--索引会自动失效，导入成功之后变为valid有效状态
select owner,index_name,index_type,table_name,num_rows,status from dba_indexes where index_name='IDX_LDR_ID';
OWNER                INDEX_NAME           INDEX_TYPE           TABLE_NAME           NUM_ROWS STATUS
-------------------- -------------------- -------------------- -------------------- -------- ----------
ROYE                 IDX_LDR_ID           NORMAL               T_SQLLDR                    0 UNUSABLE
--查看持有锁的进程
set line 1000 pages 1000
col machine for a20
col  EVENT FOR a30
col username for a10
col name for a10
SELECT C.INST_ID,c.sid,c.serial#,c.username,c.machine,b.name,C.status,c.last_call_et,c.EVENT--,c.p1,c.p2,c.p3
FROM Gv$locked_object a,sys.obj$ b,gv$session c
WHERE a.OBJECT_ID=b.obj#
AND a.session_id=c.sid
AND A.INST_ID=C.INST_ID and c.status='ACTIVE'
order by 1,4,7,9,5,6
;

   INST_ID        SID    SERIAL# USERNAME   MACHINE              NAME       STATUS           LAST_CALL_ET EVENT
---------- ---------- ---------- ---------- -------------------- ---------- ---------------- ------------ ------------------------------
         1         91       2945 ROYE       PHY-026.PRI01.SF.Bei T_SQLLDR   ACTIVE                      0 direct path write temp
                                            jing

SYS@oggm >/

   INST_ID        SID    SERIAL# USERNAME   MACHINE              NAME       STATUS           LAST_CALL_ET EVENT
---------- ---------- ---------- ---------- -------------------- ---------- ---------------- ------------ ------------------------------
         1         91       2945 ROYE       PHY-026.PRI01.SF.Bei T_SQLLDR   ACTIVE                     14 direct path write
                                            jing

--查看活动会话(touse)
SET LINE 1000 PAGES 1000
col  username for a20
col  machine for a20
col  event for a40
col MODULE for a15
select INST_ID,sid,serial#,username, machine,status,MODULE,last_call_et,event--,p1,p2,p3
from gv$session
where  username is not null
and status='ACTIVE'
order by 1,4,8,7,5;
   INST_ID        SID    SERIAL# USERNAME             MACHINE              STATUS           MODULE          LAST_CALL_ET EVENT
---------- ---------- ---------- -------------------- -------------------- ---------------- --------------- ------------ ----------------------------------------
         1         85       5087 ROYE                 PHY-026.PRI01.SF.Bei ACTIVE           SQL*Plus                 842 library cache lock
 
结论：sqlldr直接路径导入会导致索引失效，进而查询变成全表扫面，生成library cache lock事件

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29302187/viewspace-2121746/，如需转载，请注明出处，否则将追究法律责任。