oracle直接路径插入,直接路径插入模式的一些讨论

我这个帖子从数据库运行模式，表模式和是否直接路径插入三方面结合来讨论下：

实验准备

(1)创建计算当前session产生的redo size的视图 ：

create view redo_size as

select a.name,b.value

from v$statname a,v$mystat b

where a.statistic#=b.statistic# and a.name='redo size';

(2)建表结构：

sys@HX> create table t1 as select * from dba_objects where 1=2;

Table created.

1. 讨论数据库运行在归档模式的情况：

sys@HX> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     0

Next log sequence to archive   77

Current log sequence           77

(1)表模式是logging的情况

sys@HX> select table_name,logging from user_tables where table_name='T1';

TABLE_NAME                    |LOG

------------------------------|---

T1                            |YES

1 row selected.

sys@HX> select * from redo_size;

NAME                          |     VALUE

------------------------------|----------

redo size                     |     70768

sys@HX> insert into t1 select * from dba_objects;

15621 rows created.

sys@HX> select * from redo_size;

NAME                          |     VALUE

------------------------------|----------

redo size                     |   1777308

1 row selected.

sys@HX> insert /*+ append */ into t1 select * from dba_objects;

15621 rows created.

sys@HX> select * from redo_size;

NAME                          |     VALUE

------------------------------|----------

redo size                     |   3474632

1 row selected.

比较两种插入方式的日志量：

sys@HX> select 3474632-1777308 as redo_append,1777308-70768 as redo_normal from dual;

REDO_APPEND|REDO_NORMAL

-----------|-----------

1697324|    1706540

1 row selected.

基本一致，也就是说归档模式下，对于logging的表，即时使用了append插入方式也会产生redo

(2)表模式是nologging的情况

sys@HX> alter table t1 nologging;

Table altered.

sys@HX> select table_name,logging from user_tables where table_name='T1';

TABLE_NAME                    |LOG

------------------------------|---

T1                            |NO

sys@HX> select * from redo_size;

NAME                          |     VALUE

------------------------------|----------

redo size                     |         0

sys@HX> insert into t1 select * from dba_objects;

15621 rows created.

sys@HX> select * from redo_size;

NAME                          |     VALUE

------------------------------|----------

redo size                     |   1707544

1 row selected.

sys@HX> insert /*+ append */ into t1 select * from dba_objects;

15621 rows created.

sys@HX> select * from redo_size;

NAME                          |     VALUE

------------------------------|----------

redo size                     |   1710220

1 row selected.

sys@HX> select 1710220-1707544 as redo_append,1707544  as redo_normal from dual;

REDO_APPEND|REDO_NORMAL

-----------|-----------

2676|    1707544

1 row selected.

可以看到，归档模式下，对于nogging的表，普通方式插入数据还是会产生redo，使用了APPEND插入数据则不会产生redo：

2.讨论非归档模式：

sys@HX> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     0

Current log sequence           77

(1)表模式是logging的情况

sys@HX> alter table t1 logging;

Table altered.

sys@HX> select table_name,logging from user_tables where table_name='T1';

TABLE_NAME                    |LOG

------------------------------|---

T1                            |YES

sys@HX> select * from redo_size;

NAME                          |     VALUE

------------------------------|----------

redo size                     |         0

1 row selected.

sys@HX> insert   into t1 select * from dba_objects;

15621 rows created.

sys@HX> select * from redo_size;

NAME                          |     VALUE

------------------------------|----------

redo size                     |   1706524

1 row selected.

sys@HX> insert /*+ append */ into t1 select * from dba_objects;

15621 rows created.

sys@HX> select * from redo_size;

NAME                          |     VALUE

------------------------------|----------

redo size                     |   1709244

1 row selected.

sys@HX> select 1709244-1706524 redo_append,1706524 redo_normal from dual;

REDO_APPEND|REDO_NORMAL

-----------|-----------

2720|    1706524

1 row selected.

在非归档模式，表模式是logging情况下，直接路径插入方式不会产生redo

(2)表模式是nologging的情况

sys@HX> alter table t1 nologging;

Table altered.

sys@HX> select table_name,logging from user_tables where table_name='T1';

TABLE_NAME                    |LOG

------------------------------|---

T1                            |NO

1 row selected.

sys@HX> select * from redo_size;

NAME                          |     VALUE

------------------------------|----------

redo size                     |         0

1 row selected.

sys@HX> insert   into t1 select * from dba_objects;

15621 rows created.

sys@HX> select * from redo_size;

NAME                          |     VALUE

------------------------------|----------

redo size                     |   1707544

1 row selected.

sys@HX> insert /*+ append */ into t1 select * from dba_objects;

15621 rows created.

sys@HX> select * from redo_size;

NAME                          |     VALUE

------------------------------|----------

redo size                     |   1710220

1 row selected.

sys@HX> select 1710220-1707544 redo_append,1707544 redo_normal from dual;

REDO_APPEND|REDO_NORMAL

-----------|-----------

2676|    1707544

1 row selected.

非归档模式下，表模式是nologging时，append方式插入不会有redo，普通插入方式还是会产生redo

可以用一个表格展示这个关系：

数据库运行模式

表模式

插入模式

是否有redo生成

归档

logging

No  append

有

append

有

nologging

No  append

有

append

无

非归档

logging

No  append

有

append

无

nologging

No  append

有

append

无