Oracle数据加载速度优化

【说明】本文档介绍如何将大量的数据加载到一张表中，并需要尽可能地插入新数据；

【解决方法】

• 将表的日志属性设置为NOLOGGING。
• 在使用子查询来确定要插入哪些记录时，在查询中加入INSERT /*+ APPEND */
• 使用VALUES子句的查询中加入INSERT /*+ APPEND */
• 使用CREATE TABLE ... AS SELECT语句。

【实验测试】
1、创建测试表
SQL> create table T01  as select * from all_users where 1=0;

2、查看表的状态
SQL> select table_name, logging  from user_tables    where table_name = 'T01';

TABLE_NAME       LOG
-------------   ---------------- 
T01          YES

3、修改表为NOLOGGING
alter table T01 nologging;

4、创建表TT并插入大量的数据，用于等下对比用；
SQL> create table TT as select * from all_users ;
SQL> INSERT INTO TT SELECT * FROM TT;  (反复执行)
SQL> SELECT COUNT(1) FROM TT;  
  COUNT(1)
----------
   1245184

5、创建对比表T02，没有进行任何优化操作；
SQL> create table T02 as select * from all_users where 1=0;

6、进行数据的插入测试

采用直接路径操作与NOLOGGING的方法： 消耗的时间时6.15s

SQL> timing start kk 
SQL> insert /*+ APPEND */ into t01 select * from TT; 
1245184 rows created.
SQL>  timing show;
Elapsed: 00:00:06.15

常规的方法，显示需要的时间为： 15.39
SQL> timing start kk  ;
SQL> insert /*+ APPEND */ into t02 select * from TT;
1245184 rows created.
SQL> timing show;
  timing for: kk
Elapsed: 00:00:15.39

总结：经过测试通过直接路径插入和nologging的方式确实可以明显的提升数据的加载速度；

1、因为通过nologging可以产生最少量的重做；

2、避开了缓冲区缓存，直接将数据加载到数据文件中，提高了加载的效率。

但是同时这种方法也存在着以下的问题，毕竟不能什么好事都占完吧。不然oracle早就设置成默认的配置了；

• 当使用直接路径向表中插入数据的时候，Oracle将会自动把心的数据行插入到高水位线之上，这可能导致表占用大量的磁盘空间；
  
• 确保提交了通过直接路径加载的数据，否则将不能看到这些数据。因为Oracle会报0RA-12838的错误，表明直接路径加载的数据在能够查询之前必须先提交。
• 如果T01数据插入之后（插入之前有进行备份），系统由于介质故障需要进行恢复，这个时候把表RESTORE到故障之前，然后再RECOVER恢复到故障点，这个时候表T01的数据是不能被恢复的；

【总结】以上这种数据的加载方式并不适用于生产过程中的重要业务数据，比较适合一些类似报表类或中间库的数据抽取或比对数据的抽取；

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

转载于:http://blog.itpub.net/12679300/viewspace-2147868/