Oracle append
来源:互联网 发布:费舍尔数据 编辑:程序博客网 时间:2024/04/30 07:44
转自:http://blog.csdn.net/cnham/article/details/5486424
/*+append*/
1. append 属于direct insert,归档模式下append+table nologging会大量减少日志,
非归档模式append会大量减少日志,append方式插入只会产生很少的undo
2.
----------------------------------------------------------------------------------------------------------------------------------------------------
oracle append有什么作用?
insert /*+append*/ into table1 select * from table2
在使用了append选项以后,insert数据会直接加到表的最后面,而不会在表的空闲块中插入数据。
使用append会增加数据插入的速度。
append 属于direct insert,归档模式下append+table nologging会大量减少日志,非归档模式append会大量减少日志,append方式插入只会产生很少的undo
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
1.非归档模式append,nologging,append+nologging 三种情况数据产生REDO的对比
PHP:
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Current log sequence 15
SQL> create table t as select * from dba_objects where 1=2;
Table created.
SQL> select name,value,class from v$sysstat where name='redo size';
NAME VALUE CLASS
---------- ---------- ----------
redo size 582728 2
SQL> insert into t select * from dba_objects;
10947 rows created.
SQL> select name,value,class from v$sysstat where name='redo size';
NAME VALUE CLASS
---------- ---------- ----------
redo size 1745704 2
SQL> insert /*+append*/ into t select * from dba_objects;
10947 rows created.
SQL> select name,value,class from v$sysstat where name='redo size';
NAME VALUE CLASS
---------- ---------- ----------
redo size 1839872 2
SQL> select (1745704-582728) redo1,(1839872-1745704) redo2 from dual;
REDO1 REDO2
---------- ----------
1162976 94168
SQL> drop table t;
Table dropped.
SQL> create table t nologging as select * from dba_objects where 1=2;
Table created.
SQL> select name,value,class from v$sysstat where name='redo size';
NAME VALUE CLASS
---------- ---------- ----------
redo size 3441836 2
SQL> insert into t select * from dba_objects;
10947 rows created.
SQL> select name,value,class from v$sysstat where name='redo size';
NAME VALUE CLASS
---------- ---------- ----------
redo size 4660204 2
SQL> insert /*+append*/ into t select * from dba_objects;
10947 rows created.
SQL> select name,value,class from v$sysstat where name='redo size';
NAME VALUE CLASS
---------- ---------- ----------
redo size 4667180 2
SQL> select (4660204-3441836) redo1,(4667180-4660204) redo2 from dual;
REDO1 REDO2
---------- ----------
1218368 6976
2.归档下:append,nologging,append+nologging 三种情况数据产生REDO的对比
PHP语言:SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> create table t as select * from dba_objects where 1=2;
Table created.
SQL> select name,value from v$sysstat where name='redo size';
NAME VALUE
---------- ----------
redo size 155624
SQL> insert into t select * from dba_objects;
10947 rows created.
SQL> select name,value from v$sysstat where name='redo size';
NAME VALUE
---------- ----------
redo size 1316420
SQL> insert /*+append*/ into t select * from dba_objects;
10947 rows created.
SQL> select name,value from v$sysstat where name='redo size';
NAME VALUE
---------- ----------
redo size 2461876
SQL> select (1316420-155624) redo1,(2461876-1316420) redo2 from dual;
REDO1 REDO2
---------- ----------
1160796 1145456
SQL> truncate table t;
Table truncated.
SQL> alter table t nologging;
Table altered.
SQL> select name,value from v$sysstat where name='redo size';
NAME VALUE
---------- ----------
redo size 2505320
SQL> insert into t select * from dba_objects;
10947 rows created.
SQL> select name,value from v$sysstat where name='redo size';
NAME VALUE
---------- ----------
redo size 3667856
SQL> insert /*+append*/ into t select * from dba_objects;
10947 rows created.
SQL> select name,value from v$sysstat where name='redo size';
NAME VALUE
---------- ----------
redo size 3670424
SQL> select (3667856-2505320) redo1,(3670424-3667856) redo2 from dual;
REDO1 REDO2
---------- ----------
1162536 2568
总结:
normal
append
nologging
Append+nologging
Noarchive
1162976
94168
1218368
6976
Archive
1160796
1145456
1162536
2568
可以看出
1. 不管哪种模式下append要与nologging方式联用才能达到很好的效果。
2. 非归档与归档方式,只用NOLOGGING是不起效果的。
3. 非归档下append已达到不错的效果,但不及与nologging的联用方式。
4. 归档下单append起不到效果。
NOLOGGING插完后最好做个备份。
另外,如果库处在FORCELOGGING模式下,此时的nologging方式是无效的,这个我也测试过。
ITPUB上也有关于NOLOGGING何时生效的讨论
http://www.itpub.net/showthread.php?threadid=239905
eygle也做过这个实验
http://www.eygle.com/faq/Nologging&append.htm
******************************************************************************************
Originally posted by jwzl at 2004-12-6 09:02:写的日志量不同^_^.
我想确认一下/*+append*/系统到底会不会写日志,
很多人说不写日志,但怎么还有另外一个参数nologing,
如果真的没有写日志,也是很麻烦的
汇总一下下面的例子中关于redo log的信息.
pure insert 587624
insert with nologging 585496
insert with append 2240
insert with append & nologging 400
[quote]
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> drop table t;
Table dropped.
SQL> create table t nologging as select * from dba_objects where null = null;
Table created.
SQL> set autot traceonly stat
SQL> insert into t select * from dba_objects;
5888 rows created.
Statistics
----------------------------------------------------------
271 recursive calls
773 db block gets
12653 consistent gets
0 physical reads
587624 redo size
617 bytes sent via SQL*Net to client
539 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
5888 rows processed
SQL> insert into t nologging select * from dba_objects;
5888 rows created.
Statistics
----------------------------------------------------------
28 recursive calls
721 db block gets
12654 consistent gets
0 physical reads
585496 redo size
618 bytes sent via SQL*Net to client
549 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5888 rows processed
SQL> insert /*+append*/ into t select * from dba_objects;
5888 rows created.
Statistics
----------------------------------------------------------
29 recursive calls
31 db block gets
12526 consistent gets
0 physical reads
2240 redo size
603 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5888 rows processed
SQL> commit;
Commit complete.
SQL> insert /*+append*/ into t nologging select * from dba_objects;
5888 rows created.
Statistics
----------------------------------------------------------
7 recursive calls
8 db block gets
12517 consistent gets
0 physical reads
400 redo size
603 bytes sent via SQL*Net to client
561 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5888 rows processed
SQL> commit;
Commit complete.
SQL> exit
- Oracle append
- Oracle append
- 【Oracle】append
- oracle nologging append 注意
- Oracle append Hint(一)
- Oracle append Hint(二)
- Oracle append Hint(三)
- Oracle append Hint(四)
- Oracle Nologging And Append
- oracle之优化二 append
- Oracle中append与Nologging
- oracle 的nologging和append
- Oracle insert /*+ APPEND */原理解析
- Oracle快速插入数据append
- append
- append
- append
- append
- 分时段显示不同的提示的网页JS特效代码
- 作业09: 简单的链表运用问题。
- 使用Eclipse开发PHP乱码的问题
- oracle常用方法白话
- 精通cookies
- Oracle append
- JDK API 中英文版 在线版、下载地址
- GameServer - 分析 数据库服务器 安全性
- java nb网站
- KML基本常识问答
- 作业10: 对图书进行简单显示、借阅和归还管理
- NSString+NSMutableString+NSValue+NSAraay用法汇总
- C# 中的四舍五入算法及 MSDN 2005 中文版 Math.Round 勘误 .
- 游戏服务器 - 浅谈 功能模块 1