oracle11g批量insert多条,巧用外部表避免大量的insert

昨天开发咨询我一个问题，希望我对下面的语句进行调优。

语句类似下面的形式

SELECT  subscriber_no

FROM SUBSCRIBER S

WHERE SUBSCRIBER_TYPE = 'RM'

and CONV_RUN_NO in (20, 21, 22, 23)

and not EXISTS (SELECT s.AGREEMENT_NO

FROM SERVICE_AGREEMENT S, CSM_OFFER C

WHERE s.subscriber_no = S.AGREEMENT_NO

and SOC = C.SOC_CD

and SOC_TYPE ='P'

)

这个语句的执行计划如下：

Plan hash value: 1111602366

---------------------------------------------------------------------------------------------------------

| Id  | Operation                        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                 |                      |     2 |    22 | 56648   (1)| 00:11:20 |

|*  1 |  FILTER                          |                      |       |       |            |          |

|*  2 |   TABLE ACCESS FULL              | SUBSCRIBER           |     2 |    22 | 56646   (1)| 00:11:20 |

|   3 |   NESTED LOOPS                   |                      |       |       |            |          |

|   4 |    NESTED LOOPS                  |                      |     2 |    44 |     2   (0)| 00:00:01 |

|*  5 |     INDEX RANGE SCAN             | SERVICE_AGREEMENT_PK |     8 |   104 |     1   (0)| 00:00:01 |

|*  6 |     INDEX RANGE SCAN             | CSM_OFFER_1IX        |     1 |       |     1   (0)| 00:00:01 |

|*  7 |    MAT_VIEW ACCESS BY INDEX ROWID| CSM_OFFER            |     1 |     9 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter( NOT EXISTS (SELECT 0 FROM "PRDREFSSCA"."CSM_OFFER"

"C","PRDAPPO"."SERVICE_AGREEMENT" "S" WHERE "S"."AGREEMENT_NO"=:B1 AND "SOC"="C"."SOC_CD" AND

"SOC_TYPE"='P'))

2 - filter(("CONV_RUN_NO"=20 OR "CONV_RUN_NO"=21 OR "CONV_RUN_NO"=22 OR "CONV_RUN_NO"=23) AND

"SUBSCRIBER_TYPE"='RM')

5 - access("S"."AGREEMENT_NO"=:B1)

6 - access("SOC"="C"."SOC_CD")

7 - filter("SOC_TYPE"='P')

从语句来看是真没有多少什么调优空间了。为了想尽快得到结果，我建议加了一个hint  /*+parallel(4)*/

我一再叮嘱他，这中语句最好在备份库中执行，因为备份中的数据是相对比较新的，他也同意。备份库当时负载很低，执行大概用了6分钟的样子。输出结果大约有70多万。

我以为这件事就这么过去了，结果今天早上他找到我说，希望把这70多万条记录存到一个一个临时用户下，需要关联查找更多的细节。

这种问题让人有些纠结。不过为了问题的解决，也不能完全怪他。

尝试一：

首先的一个思想就是想吐省事，在中午的时候尝试生成这些记录。

create table issue_sub_list nologging as

SELECT  /*+parallel(4)*/ subscriber_no

FROM SUBSCRIBER S

WHERE SUBSCRIBER_TYPE = 'RM'

and CONV_RUN_NO in (20, 21, 22, 23)

and not EXISTS (SELECT s.AGREEMENT_NO

FROM SERVICE_AGREEMENT S, CSM_OFFER C

WHERE s.subscriber_no = S.AGREEMENT_NO

and SOC = C.SOC_CD

and SOC_TYPE ='P'

)

这条语句结果在生产上执行了将近10分钟还没有完成的意思，我查看执行计划的输出列情况，才全表扫描了30%,所以果断终止了这个操作。

这条路不建议使用，我看还有什么别的路可走。

尝试二：

一来就是对得到的数据进一步转换为insert语句。尝试在windows的ultraEdit下面编辑，因为这些id有差不多11M左右，使用文本编辑器没过一会，就提示缓存溢出，卡在那了。

尝试三：

看来文本编辑器还是很有使用限制的，linux环境下的命令方式要相对好很多。百兆上G的日志都没有问题。

我写了如下的脚本。

while read line

do

sqlplus -s n1/n1@xxx  <

insert into issue_sublist values( $line);

EOF

done < sub_list.log

执行的速度来看，单条语句是没有问题的，但是这条语句执行了10多分钟，还没有完，进步还是让人不够满意。

尝试四：

大家使用外部表来查看数据库的日志信息，这是一种方式，我们也可以应用到这个场景中。来看看外部表的情况。

创建了directory

create directory EXT_DATAPUMP_DIR as '/oravl01/oracle/ext_datapump/DUMP';

grant read,write on directory EXT_DATAPUMP_DIR to n1;

然后使用语句创建了外部表。

create table issue_sublist_ext(text varchar2(1000)) organization external

(type oracle_loader

default directory EXT_DATAPUMP_DIR

location('sub_list.log')

);

Table created.

Elapsed: 00:00:00.01

SQL> select count(*)from issue_sublist_ext;

COUNT(*)

----------

769685

Elapsed: 00:00:00.25

SQL> drop table issue_sublist;

Table dropped.

Elapsed: 00:00:00.04

我们来创建一个表存储这些数据。

SQL> create table issue_sublist as select *from issue_sublist_ext;

Table created.

Elapsed: 00:00:00.64

SQL> !exp n1/n1@xxx  file=issue_sublist.dmp tables=issue_sublist buffer=910200

About to export specified tables via Conventional Path ...

. . exporting table                  ISSUE_SUBLIST     769951 rows exported

Export terminated successfully without warnings.

来看看dump的情况，生成的dump文件和文本的大小差不多大小。

TRUABP4 /oravl01/oracle/ext_datapump/DUMP > ll

total 24880

-rw-r--r-- 1 oracle dba 11545275 Mar 11 12:04 sub_list.log

-rw-r--r-- 1 oracle dba      357 Mar 11 12:05 ISSUE_SUBLIST_EXT_8860.log

-rw-r--r-- 1 oracle dba 13869056 Mar 11 12:07 issue_sublist.dmp

这个dump拷贝到生产中，直接imp速度是相当快的，一秒内搞定。

> time imp n1/n1 file=issue_sublist.dmp tables=issue_sublist buffer=910200 fromuser=n1 touser=cust_prsnl

. importing N1's objects into CUST_PRSNL

. . importing table                "ISSUE_SUBLIST"     769951 rows imported

Import terminated successfully without warnings.

real    0m1.277s

user    0m0.133s

sys     0m0.060s

通过这个例子我们可以看到，我们尝试尽可能减少生产的负载，使用外部表间接转换为dump，会减少大量的insert语句执行，而且效率也高。可以作为一种参考。

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