oracle index nolog,创建索引时的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。效果还是比较明显。