重做和索引操作

  如果执行带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并不会删除索引，只要我们不删除索引，索引就决不可能丢失

 

小结:

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