失败一次，总结一次。直到永远不失败。

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

上一篇 / 下一篇 2008-04-24 10:21:36 / 个人分类：ORACLE

最近系统由于每天的日加载量只不过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.

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

SQL> alter table love_table drop constraint pk_love ;

Table altered.

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.

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

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

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