直接路徑插入模式的一些討論

我這個帖子從資料庫執行模式，表模式和是否直接路徑插入三方面結合來討論下：
實驗準備
（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	有
	logging	append	有
	nologging	No append	有
	nologging	append	無
非歸檔	logging	No append	有
	logging	append	無
	nologging	No append	有
	nologging	append	無

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28813259/viewspace-777428/，如需轉載，請註明出處，否則將追究法律責任。

直接路徑插入模式的一些討論

相關文章