oracle百万数据insert,oracle insert 大量数据

当需要对一个非常大的表INSERT的时候，会消耗非常多的资源，因为update表的时候，oracle需要生成 redo log和undo log;

此时最好的解决办法是insert数据时, 将表设置为nologging，速度是比较快的。

这个时候oracle只会生成最低限度的必须的redo log，而没有一点undo信息。如果有可能将index也删除，重建。先看看实验结果：

archivelog

操作类型

Redo大小(byte)

insert

10896388

append insert

11143288

nologging insert

11088572

nologging+append

29404

noarchivelog

操作类型

Redo大小(byte)

insert

11105144

append insert

29404

nologging insert

10894408

nologging+append

4964

数据库版本：

SQL> select * from v$version;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

PL/SQL Release 11.2.0.4.0 – Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 – Production

NLSRTL Version 11.2.0.4.0 – Production

首先确认数据库处于非归档模式

SQL> archive log list;

Database log mode           No Archive Mode

Automatic archival              Disabled

Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch

Oldest online log sequence     692

Current log sequence           695

一、如果表采用默认的方式，也就是记录日志的方式，不管你是否使用insert into， oracle产生的日志都会很多

SQL> conn scott

Enter password:

Connected.

SQL>  create table tj as select * from dba_objects where 1=2;

SQL>  select count(*) from tj;

COUNT(*)

———-

0

SQL> select table_name,logging from user_tables where table_name=’TJ’;

TABLE_NAME                     LOG

—————————— —

TJ                             YES

通过autotrace统计redo生成

SQL> set autotrace trace stat

1.直接insert插入

SQL> insert into tj select * from dba_objects;

95167 rows created.

Statistics

———————————————————-

188  recursive calls

12890  db block gets

3592  consistent gets

10  physical reads

11105144  redo size

837  bytes sent via SQL*Net to client

793  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

12  sorts (memory)

0  sorts (disk)

95167  rows processed

SQL>  rollback;

Rollback complete.

2.采用append 的方式

SQL> insert /*+ append */ into  tj select * from dba_objects;

95167 rows created.

Statistics

———————————————————-

37  recursive calls

1715  db block gets

1240  consistent gets

0  physical reads

29404  redo size

823  bytes sent via SQL*Net to client

808  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

95167  rows processed

SQL> rollback;

Rollback complete.

通过以上1和2两种不同方式的比较，我们发现产生的日志量减少很多11105144  redo size

29404  redo size

二、修改表为不记录日志，这个时候insert into就会体现出他的优势

1. 直接insert插入

SQL> alter table tj nologging;

Table altered.

SQL> insert into tj select * from dba_objects;

95167 rows created.

Statistics

———————————————————-

40  recursive calls

10145  db block gets

3957  consistent gets

0  physical reads

10894408  redo size

840  bytes sent via SQL*Net to client

793  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

7  sorts (memory)

0  sorts (disk)

95167  rows processed

SQL>  rollback;

Rollback complete.

2.采用append 的方式

SQL> insert /*+ append */ into  tj select * from dba_objects;

95167 rows created.

Statistics

———————————————————-

0  recursive calls

1419  db block gets

1204  consistent gets

0  physical reads

4964  redo size

826  bytes sent via SQL*Net to client

808  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

95167  rows processed

通过以上1和2两种不同方式的比较，我们发现产生的日志量APPEND的方式明显会少，10894408  redo size

4964  redo size

根据上面的实验我们可以发现，为了通过减少REDO而提高语句的性能，我们要满足两个条件1.表NOLOGGING 2.在语句中使用APPEND提示。

当然我们这里只是在强调性能，作为一个DBA，在性能和安全之间一定要做一个平衡，当你选择了NOLOGGING的时候，由于表是不记录日志的，那如果数据库崩溃，这些数据是不能被恢复的。

noarchivelog:

insert:           11105144  redo size

append:              29404  redo size

nologging:        10894408  redo size

nologging+append:     4964  redo size

下面是一个小的例子，当我们把一个表设置成NOLOGGING模式的时候，通过合适的操作，可以让oracle产生很少的REDO。

首先确认数据库处于归档模式

SQL>archive log list

Database log mode           Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch

Oldest online log sequence     692

Next log sequence to archive   695

Current log sequence           695

一、如果表采用默认的方式，也就是记录日志的方式，不管你是否使用insert into， oracle产生的日志都会很多

SQL> conn scott

Enter password:

Connected.

SQL>  create table tj as select * from dba_objects where 1=2;

Table created.

SQL>  select count(*) from tj;

COUNT(*)

———-

0

SQL> select table_name,logging from user_tables where table_name=’TJ’;

TABLE_NAME                     LOG

—————————— —

TJ                             YES

通过autotrace统计redo生成

SQL> set autotrace trace stat

1.直接insert方式插入

SQL> insert into tj select * from dba_objects;

95167 rows created.

Statistics

———————————————————-

402  recursive calls

10107  db block gets

4200  consistent gets

19  physical reads

10896388  redo size

835  bytes sent via SQL*Net to client

793  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

33  sorts (memory)

0  sorts (disk)

95167  rows processed

SQL>  rollback;

Rollback complete.

2.采用append 提示的方式

SQL> insert /*+ append */ into  tj select * from dba_objects;

95167 rows created.

Statistics

———————————————————-

15  recursive calls

1395  db block gets

1208  consistent gets

22  physical reads

11143288  redo size

823  bytes sent via SQL*Net to client

808  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

95167  rows processed

SQL> rollback;

Rollback complete.

通过以上1和2两种不同方式的比较，我们发现产生的日志量没有减少10896388  redo size

11143288  redo size

二、修改表为不记录日志，这个时候insert into就会体现出他的优势

1.不采用append 提示

SQL>alter table tj nologging;

Table altered.

SQL>  select table_name,logging from user_tables where table_name=’TJ’;

TABLE_NAME                     LOG

—————————— —

TJ                             NO

SQL>insert into tj select * from dba_objects;

95167 rows created.

Statistics

———————————————————-

50  recursive calls

12847  db block gets

3423  consistent gets

3  physical reads

11088572  redo size

836  bytes sent via SQL*Net to client

793  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

2  sorts (memory)

0  sorts (disk)

95167  rows processed

SQL>  rollback;

Rollback complete.

2.采用append 的方式

SQL> insert /*+ append */ into  tj select * from dba_objects;

95167 rows created.

Statistics

———————————————————-

37  recursive calls

1714  db block gets

1240  consistent gets

0  physical reads

29404  redo size

824  bytes sent via SQL*Net to client

808  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

95167  rows processed

通过以上1和2两种不同方式的比较，我们发现产生的日志量APPEND的方式明显会少，11088572  redo size

29404  redo size

根据上面的实验我们可以发现，为了通过减少REDO而提高语句的性能，我们要满足两个条件1.表NOLOGGING 2.在语句中使用APPEND提示。

当然我们这里只是在强调性能，作为一个DBA，在性能和安全之间一定要做一个平衡，当你选择了NOLOGGING的时候，由于表是不记录日志的，那如果数据库崩溃，这些数据是不能被恢复的。