nologging索引可以大大减少日志量

最近系统由于每天的日加载量只不过10个G而已，然而产生的日志确是将近上千个G。原来系统在加载的时候，表的索引都是先删除，加载完成后再创建。每天产生的归档日志特别的多，项目其他组成员问是否可以把归档日志缩小些。所以想到了在创建索引的时候用NOLOGGING参数。下面测试如下：

SQL> create table love_table(
  2  id char(10),
  3  name varchar2(20))
  4  partition by hash(id) tablespace p_data;

Table created.

SQL> insert into love_table
  2  select rownum,rownum from dual
  3  connect by rownum<10000;

10000 rows created.

SQL> commit;

Commit complete.
--查询现在的REDO 大小
SQL> select a.name,b.value
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) like '%'||lower('&s')||'%';
Enter value for s: redo size
old   4: and lower(a.name) like '%'||lower('&s')||'%'
new   4: and lower(a.name) like '%'||lower('redo size')||'%'

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           6613564
--创建非NOLOGGING的索引
SQL> alter table love_table add constraint pk_love primary key (id) using index tablespace n_data;

Table altered.
--查询创建索引后的REDO大小
SQL> select a.name,b.value
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) like '%'||lower('&s')||'%';
Enter value for s: redo size
old   4: and lower(a.name) like '%'||lower('&s')||'%'
new   4: and lower(a.name) like '%'||lower('redo size')||'%'

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           6923708
--删除索引
SQL> alter table love_table drop constraint pk_love ;

Table altered.
--查询删除索引后的REDO 大小
SQL> select a.name,b.value
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) like '%'||lower('&s')||'%';
Enter value for s: redo size
old   4: and lower(a.name) like '%'||lower('&s')||'%'
new   4: and lower(a.name) like '%'||lower('redo size')||'%'

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           6935904
--创建NOLOGGIN索引
SQL> alter table love_table add constraint pk_love primary key (id) using index nologging tablespace
 n_data;

Table altered.
--创建NOLOGGING索引后生成的REDO大小
SQL> select a.name,b.value
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) like '%'||lower('&s')||'%';
Enter value for s: redo size
old   4: and lower(a.name) like '%'||lower('&s')||'%'
new   4: and lower(a.name) like '%'||lower('redo size')||'%'

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           6955856
--创建NOLOGGING索引生成的REDO大小
SQL> select  ( 6955856-6935904)/1024 from dual;

(6955856-6935904)/1024
----------------------
             19.484375
--创建正常索引生成的REDO大小
SQL> select (6923708-6613564)/1024 from dual;

(6923708-6613564)/1024
----------------------
               302.875

SQL>
SQL> truncate table love_table;

Table truncated.

SQL> insert into love_table
  2  select rownum,rownum from dual
  3  connect by rownum<=100000;

100001 rows created.

SQL> rollback;

Rollback complete.

SQL> insert into love_table
  2  select rownum,rownum from dual
  3  connect by rownum<100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> select a.name,b.value
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) like '%'||lower('&s')||'%';
Enter value for s: redo size
old   4: and lower(a.name) like '%'||lower('&s')||'%'
new   4: and lower(a.name) like '%'||lower('redo size')||'%'

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          73659500

SQL> alter table love_table add constraint pk_love primary key (id) using index tablespace n_data;

Table altered.

SQL> select a.name,b.value
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) like '%'||lower('&s')||'%';
Enter value for s: redo size
old   4: and lower(a.name) like '%'||lower('&s')||'%'
new   4: and lower(a.name) like '%'||lower('redo size')||'%'

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          76579956

SQL> alter table love_table drop constraint pk_love ;

Table altered.

SQL> select a.name,b.value
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) like '%'||lower('&s')||'%';
Enter value for s: redo size
old   4: and lower(a.name) like '%'||lower('&s')||'%'
new   4: and lower(a.name) like '%'||lower('redo size')||'%'

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          76595836

SQL> alter table love_table add constraint pk_love primary key (id) using index nologging tablespace
 n_data;

Table altered.

SQL> select a.name,b.value
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) like '%'||lower('&s')||'%';
Enter value for s: redo size
old   4: and lower(a.name) like '%'||lower('&s')||'%'
new   4: and lower(a.name) like '%'||lower('redo size')||'%'

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          76644016

SQL> select (76644016-76595836)/1024 from dual;

(76644016-76595836)/1024
------------------------
              47.0507813

SQL> select (76579956-73659500)/1024 from dual;

(76579956-73659500)/1024
------------------------
              2852.00781

SQL> select 302/20 from dual;

    302/20
----------
      15.1

SQL> select 2852/47 from dual;

   2852/47
----------
60.6808511

SQL>
当我测试100000条记录的时候不加NOLOGGING限制的创建索引产生的REDO大小是用NOLOGGING产生的大小的60倍。

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

转载于:http://blog.itpub.net/728254/viewspace-250331/