Oracle nologging 的使用

--在archive log模式下，nologging针对以下操作可以有效减少redo size，但必须谨慎地使用这种模式
--因为操作不产生日志，若出现介质失败后，dba 无法完全地恢复数据库，必须谨慎小心使用nologging操作
--另外的测试，在NOARCHIVELOG 模式，nologging对create table是无效的，而对于create index还是能减少redo size
--NOLOGGING执行以下操作时可以执行
/*
*索引的创建和ALTER(重建)
*ISNERT APPEND，或者SQL*Loader直接路径加载。表数据不生成redo，但是表上的索引仍会生成redo
*LOB操作
*create table as 操作
*alter table 操作，比如move 和split
*/


--ctrs操作，产生redo size 5821728，采用nologging模式 产生了79500字节
--建索引，也可以指定nologging模式
admin@ORCL> CREATE OR REPLACE VIEW V_USER_REDO AS
  2  SELECT NAME,VALUE FROM V$MYSTAT CUR_STAT INNER JOIN V$STATNAME STATE_NAME ON
  3  CUR_STAT.STATISTIC# = STATE_NAME.STATISTIC#
  4  WHERE STATE_NAME.NAME = 'redo size';

视图已创建。

admin@ORCL> select * from v_user_redo;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             15428

admin@ORCL> create table t as select * from dba_objects;

表已创建。

admin@ORCL> select * from v_user_redo;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           5837156

--nologging模式
admin@ORCL> drop table t purge;

表已删除。

admin@ORCL> select * from v_user_redo;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           5862144

admin@ORCL> create table t nologging as select * from dba_objects;

表已创建。

admin@ORCL> select * from v_user_redo;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           5941644
--需要注意nologging 不能避免所有后续操作生成redo，除非特殊的sql*loader,insert /*+ append */ 语法的不生成日志。

admin@ORCL> SELECT LOGGING FROM DBA_TABLES WHERE TABLE_NAME = 'T' AND OWNER = 'ADMIN';

LOG
---
NO

--测试索引在nologging模式下的redo size
--普通模式产生 965136 字节redo
--nologging 模式创建的索引，产生的redo 为 56264字节

admin@ORCL> create table t as select * from dba_objects;

表已创建。

admin@ORCL> select * from v_user_redo;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           5849580

admin@ORCL> create index idx_t on t(object_id);

索引已创建。

admin@ORCL> select * from v_user_redo;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           6814716

admin@ORCL> create index idx_t on t(object_id) nologging;

索引已创建。

--
admin@ORCL> select * from v_user_redo;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           6870980

--索引创建后，仍为nologging模式
admin@ORCL> SELECT LOGGING FROM DBA_INDEXES WHERE INDEX_NAME = 'IDX_T'
  2  ;

LOG
---
NO