创建索引时的nologging效果

创建索引时使用nologging选项可以加快速度，节省时间，减少产生的日志量。

SQL> select count(*) from idx_test;

  COUNT(*)
----------
   1620416

Executed in 4.453 seconds

SQL> select value from v$sysstat t where name='redo size';

     VALUE
----------
 243558888

Executed in 0.015 seconds

SQL> create index idx_object_id on idx_test(object_id);

Index created

Executed in 33.953 seconds

SQL> select value from v$sysstat t where name='redo size';

     VALUE
----------
 273336604

Executed in 0.031 seconds

SQL> select 273336604-243558888 from dual;

273336604-243558888
-------------------
           29777716

Executed in 0.031 seconds

SQL> drop index idx_object_id;

Index dropped

Executed in 0.516 seconds

SQL> select value from v$sysstat t where name='redo size';

     VALUE
----------
 273408596

Executed in 0.047 seconds

SQL> create index idx_object_id on idx_test(object_id) nologging;

Index created

Executed in 13.328 seconds

SQL> select value from v$sysstat t where name='redo size';

     VALUE
----------
 273636648

Executed in 0.031 seconds

SQL> select 273636648-273408596 from dual;

273636648-273408596
-------------------
             228052

Executed in 0.047 seconds

SQL>

从上面实验可以看出，一个1620416条记录的表，在不使用nologging的情况下，创建索引耗时33.953 秒，产生redo 29777716 ,使用nologging后，耗时13.328 秒，产生redo 228052。效果还是比较明显。

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

转载于:http://blog.itpub.net/517786/viewspace-661557/