下面仅说明使用append方式插入数据时日志产生的情况,如使用下面的方式进行插入(这里是表是否事先设置成nologging)
insert/*+append+*/into tb_name select colnam1,colname2 from table_name;
数据库处于归档模式
当表模式为logging状态时,无论是否使用append模式,都会生成redo.当表模式为nologging状态时,只有append模式,不会生成redo。
数据库处于非归档模式
无论是在logging还是nologing的模式下,append的模式都不会生成redo,而no append模式下都会生成redo。
alter table aa nologging
alter table aa logging
insert /*+append*/ into ...nologing
select * from ...
insert /*+ append, parallel */ into ods_list_t nologging
select * from ods_list;
但这样不行:整个表可以插入,但要某一个字段则不能加入nologging
insert /*+ append, parallel */ into ods_list_t(a,b) nologging
select a,b from ods_list;
但可以这样:
insert /*+ append, parallel */ into ods_list_t nologging(a,b)
select a,b from ods_list;
create table ods_list_t nologging as select * from ods_list;
insert /*+ append, parallel */ into ods_list_t nologging
select * from ods_list;
insert /*+ Append parallel(tablename,number) */ into ods_list_t nologging
select * from ods_list;
tablename: 表名
number: 并行度
使用批量拷贝方法
set arraysize 20
set copycommit 5000
copy from username/password@oracle_name append table_name1
using select * from table_name2;
用EXP/IMP 处理大量数据
(1)给当前的两个表分别改名
alter table tab1 rename to tab11;
alter table tab2 rename to tab1;
(2)导出改名前的tab2
exp user/pwd@... file=... log=... tables=(tab1)
(3)把名字改回来
alter table tab1 rename to tab2;
alter table tab11 rename to tab1;
(4)导入数据
imp user/pwd@... file=... log=... fromuser=user touser=user tables=(tab1)
--------------------------------------------------
(3)真正总结(有用的):
可以看出(这是insert /*+ append */ into ddddd nologging as select ...... from ...)
1. 不管哪种模式下append要与nologging方式联用才能达到很好的效果。
2. 非归档与归档方式,只用NOLOGGING是不起效果的。
3. 非归档下append已达到不错的效果,但不及与nologging的联用方式。
4. 归档下单append起不到效果。
另外,如果库处在FORCELOGGING模式下,此时的nologging方式是无效的,这个我也测试过
5.非归档模式下:
没有优化前 (1281372 redo size)
(1)、单一的使用nologging参数,对redo的产生没有什么影响。 (1214836 redo size)
(2)、单一的使用append提示,redo的减少很显著 (43872 redo size)
(3)、nologging+append,更显著 (1108 redo size)
6.归档模式下:
没有优化前:
(1)、单独使用nologging参数,(1231904 redo size)
(2)、单独使用append提示, (1245804 redo size)
(3)、nologging + append, (3748 redo size)
a、使用nologging参数并不代表在dml操作中,oracle不产生redo,只是对于指定表的更新数据不产生redo,但是 oracle还是要记录这些操作,所以无论怎么优化,dml操作肯定要产生redo,但是使用这些参数对redo size的影响还是非常可观的。
b、单独使用nologging参数,对redo size没有多少影响,只有和append配合时,才能产生效果。
c、单独使用append提示,对redo的产生影响很大,这是我到现在都不明白的道理,按说append是绕过freelists,直接去寻找新块,能减少对freelists的争用,为什么会少这么多redo呢?
d、归档模式和非归档模式下,参数影响不一样,尤其是单独使用append参数时,看来oracle对归档模式下出于安全考虑还是要多一些。
三、查询总结
1.查看不同级别的日志模式
查看数据库级别日志的归档模式、记录模式
SQL>select log_mode,force_logging from v$database;
LOG_MODE FORCE_LOGGING
------------ -------------
ARCHIVELOG NO
查看表空间级别的日志记录模式
SQL>select tablespace_name,logging,force_logging from dba_tablespaces;
TABLESPACE_NAME LOGGING FORCE_LOGGING
--------------- --------- -------------
SYSTEM LOGGING NO
UNDOTBS1 LOGGING NO
SYSAUX LOGGING NO
TEMP NOLOGGING NO
USERS LOGGING NO
查看对象级别的日志记录模式
scott@ORCL>select table_name,logging from user_tables;
TABLE_NAME LOG
------------------------------ ---
TB_A YES
TB_B YES
2.日志记录模式的转换
a.数据库从非强制日子模式切换到强制日志模式
sys@ORCL>alterdatabase force logging;
b.数据库从强制日志模式切换到非强制日志模式
sys@ORCL>alterdatabaseno force logging;
c.表空间级别从强制日志模式切换到非强制日志模式
sys@ORCL>alterdatabaseno force logging;
d.表空间级别从非强制日志模式切换到强制日志模式
sys@ORCL>alterdatabaseno force logging;
e.对象级别日志记录模式
sys@ORCL>altertable tb_a nologging; --不记录日志模式
sys@ORCL>altertable tb_a logging; --采用日志记录模式
注意:
(1)表是否是nologging模式,还需要查user_tables.logging字段才行。
我刚才在oracle 10g中测试,create table xxx nologging as select *....产生的表并不是nologging方式,还得手工改一下:
alter table xxxx nologging.
改完后,在归档模式,insert /*+append*/确实日志很少。
(2)在使用append 快速加载数据完成后,需要commit or roll ,才能进行进行对该表进程操作否则会报错:ORA-12838: cannot read/modify an object after modifying it in parallel错误。insert和append insert的原理上是完全不同的,insert append可以实现直接路径加载,速度比常规加载方式快很多。但有一点需要注意: insert append时在表上加“6”类型的Exclusive锁,即排它锁,为表级锁,会阻塞表上的所有DML语句。因此在有业务运行的上传环境下要慎重使用。
http://www.cnblogs.com/zmlctt/p/3747443.html