oracle修改索引为logging,重做和索引操作

如果执行带insert /*+ append */命令(数据库为no force logging)，并且设置为nologging，但仍然为有许多重做和归档日志，一般是因为在这个表的索引所影响，但怎么才能很好的消除索引带来的重做日志，而是先较快的插入呢？基于此问题的思考，便有了此篇日志

SQL> select force_logging from v$database;

FOR

---

NO

SQL> set autotrace traceonly statistics

SQL> insert into test select * from all_objects;

已创建68207行。

统计信息

----------------------------------------------------------

727  recursive calls

5642  db block gets

44860  consistent gets

0  physical reads

8166524  redo size

678  bytes sent via SQL*Net to client

611  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

768  sorts (memory)

0  sorts (disk)

68207  rows processed

SQL> rollback;

回退已完成。

SQL> insert /*+ append */ into test select * from all_objects;

已创建68207行。

统计信息

----------------------------------------------------------

432  recursive calls

1108  db block gets

42729  consistent gets

0  physical reads

14916  redo size

662  bytes sent via SQL*Net to client

625  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

768  sorts (memory)

0  sorts (disk)

68207  rows processed

SQL> rollback;

回退已完成。

SQL> create index big_table_idx on test (owner,object_type,object_name);

索引已创建。

SQL> insert /*+ append */ into test select * from all_objects;

已创建68208行。

统计信息

----------------------------------------------------------

445  recursive calls

16321  db block gets

43897  consistent gets

478  physical reads

17588824  redo size

664  bytes sent via SQL*Net to client

625  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

769  sorts (memory)

0  sorts (disk)

68208  rows processed

SQL> commit;

可以看到虽然是/*+ append */方式，但还是会产生大量的redo，一个索引能产生如此大的不同！索引是复杂的数据结构，维护它们相当昂贵。为此如何才能减少索引产生的redo呢？

1.设置索引为unusable状态，(不删除索引，只是把它们设置为unusable)。

2.设置会话跳过unusable索引并进行批量装载。

3.重新启用索引。

试验步骤如下:

SQL> create index big_table_idx on test (owner,object_type,object_name);

索引已创建。

SQL> insert /*+ append */ into test select * from all_objects;

已创建68208行。

统计信息

----------------------------------------------------------

445  recursive calls

16321  db block gets

43897  consistent gets

478  physical reads

17588824  redo size

664  bytes sent via SQL*Net to client

625  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

769  sorts (memory)

0  sorts (disk)

68208  rows processed

SQL> commit;

提交完成。

SQL> alter index big_table_idx unusable;

索引已更改。

SQL> alter session set skip_unusable_indexes=true;

会话已更改。

SQL> insert /*+ append */ into test select * from all_objects;

已创建68208行。

统计信息

----------------------------------------------------------

682  recursive calls

1109  db block gets

42791  consistent gets

0  physical reads

15160  redo size

664  bytes sent via SQL*Net to client

625  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

775  sorts (memory)

0  sorts (disk)

68208  rows processed

SQL> alter index big_table_idx rebuild nologging;

随后对受到影响的数据备份即可完成装载任务。

注意:

unusable并不会删除索引，只要我们不删除索引，索引就决不可能丢失

小结:

细节决定成败，掌握每一个细小的点，才能编织更大的网!