Oracle nologging 的使用

zho833sw0x

--在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