Oracle insert /*+ APPEND */原理解析

最新推荐文章于 2025-06-18 21:05:10 发布
最新推荐文章于 2025-06-18 21:05:10 发布
阅读量5.5w 收藏 84
点赞数 5
分类专栏： ORACLE
本文深入探讨了在非归档和归档模式下，利用INSERT/*+APPEND*/语句进行数据插入时的性能优化策略。通过对比非归档模式下仅使用APPEND与增加NOLOGGING选项的性能提升，以及在归档模式下插入与追加操作对redo日志的影响。同时，阐述了直接路径加载的高效性及其对表级锁定的影响。最后，强调了插入与追加操作对并发事务的影响，并提供了一个实例演示了如何通过这些策略提高数据库性能。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

关于insert /*+ append */我们需要注意以下三点： a、非归档模式下，只需append就能大量减少redo的产生；归档模式下，只有append+nologging才能大量减少redo。 b、insert /*+ append */时会对表加锁（排它锁），会阻塞表上的除了select以外所有DML语句；传统

关于insert /*+ append */我们需要注意以下三点：

a、非归档模式下，只需append就能大量减少redo的产生；归档模式下，只有append+nologging才能大量减少redo。
b、insert /*+ append */ 时会对表加锁（排它锁），会阻塞表上的除了select以外所有DML语句；传统的DML在TM enqueue上使用模式3（row exclusive），其允许其他DML在相同的模式上获得TM enqueue。但是直接路径加载在TM enqueue使用模式6（exclusive），这使其他DML在直接路径加载期间将被阻塞。
c、insert /*+ append */ 直接路径加载，速度比常规加载方式快。因为是从HWM的位置开始插入，也许会造成空间浪费。

1.环境介绍

数据库版本：

1
2
3
4
5
6
7
8
SQL> select * from v$version;
BANNER
---------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

网上说测试时不要使用auto trace来查看redo size，这个值是有偏差的.建议建立一个视图：

1
2
3
4
5
6
SQL> create or replace view redo_size as
select value
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
-- 视图已创建。

2.示例演示：

2.1 非归档模式

1
2
3
4
5
6
SQL> archive log list
数据库日志模式             非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     95
当前日志序列           97

2.1.1 nologging表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SQL> create table test1 nologging as select * from dba_objects where 1=0;
表已创建。
  
SQL> select * from redo_size;
     VALUE
----------
  25714940
  
SQL> insert into test1 select * from dba_objects;
已创建72753行。
  
SQL> select * from redo_size;
     VALUE
----------
  34216916
  
SQL> insert /*+ APPEND */  into test1 select * from dba_objects;
已创建72753行。
  
SQL> select * from redo_size;
     VALUE
----------
  34231736
  
SQL> select (34231736-34216916) redo_append , (34216916-25714940) redo_normalfrom dual;
REDO_APPEND REDO_NORMAL
----------- -----------
      14820     8501976

2.1.2 logging表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SQL> create table test2 as select * from dba_objects where 1=0;
  
表已创建。
  
SQL> select * from redo_size;
     VALUE
----------
  34273348
  
SQL> insert into test2 select * from dba_objects;
  
已创建72754行。
  
SQL> select * from redo_size;
     VALUE
----------
  42775336
  
SQL> insert /*+ APPEND */  into test2 select * from dba_objects;
已创建72754行。
  
SQL> select * from redo_size;
     VALUE
----------
  42790156
  
SQL> select (42790156-42775336) redo_append , (42775336-34273348) redo_normalfrom dual;
REDO_APPEND REDO_NORMAL
----------- -----------
      14820     8501988

2.2归档模式下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE例程已经关闭。
SQL> startup mount
ORACLE例程已经启动。
  
Total System Global Area  477073408 bytes
Fixed Size                  1337324 bytes
Variable Size             293603348 bytes
Database Buffers          176160768 bytes
Redo Buffers                5971968 bytes
数据库装载完毕。
SQL> alter database archivelog;
数据库已更改。
  
SQL> alter database open;
数据库已更改。
  
SQL> archive log list
数据库日志模式            存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     95
下一个存档日志序列   97
当前日志序列           97

2.2.1 nologging表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> select * from redo_size;
    VALUE
----------
     17936
  
SQL> insert into test1 select * from dba_objects;
已创建72754行。
  
SQL> select * from redo_size;
     VALUE
----------
   8490972
  
SQL> insert /*+ APPEND */  into test1 select * from dba_objects;
已创建72754行。
  
SQL> select * from redo_size;
     VALUE
----------
   8506164
  
SQL> select (8506164-8490972) redo_append , (8490972-17936) redo_normal fromdual;
REDO_APPEND REDO_NORMAL
----------- -----------
      15192     8473036

2.2.2 logging表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> select * from redo_size;
     VALUE
----------
   8506780
  
SQL> insert into test2 select * from dba_objects;
已创建72754行。
  
SQL> select * from redo_size;
     VALUE
----------
  16979516
  
SQL> insert /*+ APPEND */  into test2 select * from dba_objects;
已创建72754行。
  
SQL> select * from redo_size;
     VALUE
----------
  25518172
  
SQL> select (25518172-16979516) redo_append , (16979516-8506780) redo_normalfrom dual;
REDO_APPEND REDO_NORMAL
----------- -----------
    8538656     8472736

在归档模式下,对于常规表的insert append产生和insert同样的redo
此时的insert append实际上并不会有性能提高.
但是此时的append是生效了的。

3.insert /*+ append */会阻塞除select以外的DML语句，direct-path insert操作是单独一个事务。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
SQL> select count(*) from test2;
  COUNT(*)
----------
    291016
  
SQL> insert into test2 select * from dba_objects;
已创建72754行。
  
SQL> select count(*) from test2;
  COUNT(*)
----------
    363770
  
SQL> insert /*+ APPEND */ into test2 select * from dba_objects;
已创建72754行
  
同一个session下：
  
SQL> select count(*) from test2;
select count(*) from test2
*
第 1 行出现错误:
ORA-12838: 无法在并行模式下修改之后读/修改对象
  
SQL> commit;
提交完成。
  
SQL> select count(*) from test2;
  COUNT(*)
----------
    436524
  
SQL> insert /*+ APPEND */ into test2 select * from dba_objects;
已创建72754行。
  
SQL> shutdown immediate
ORA-01097: 无法在事务处理过程中关闭 - 请首先提交或回退
  
SQL> select  * from v$mystat where rownum<2;
  
       SID STATISTIC#      VALUE
  
---------- ---------- ----------
  
       224          0          1
  
SQL> select KADDR,TYPE,LMODE from v$lock where sid=224;
  
KADDR            TY      LMODE
---------------- -- ----------
0000000071BAE180 TM          6
0000000070CB11B8 TX          6

另外开启一个会话，就会发现只能select，其他DML全部阻塞。

【Oracle】Oracle多线程插入表，追加，不记录日志
tttzzzqqq2018的博客
05-18 77
在Oracle数据库中，可以通过多线程方式高效地插入数据，并且不记录日志以减少I/O开销。以下SQL语句展示了如何实现这一操作：使用/*+ append, parallel(t,8) */提示符，将数据从tzq_log_tmp表插入到tzq.tzq_log_t表中，同时指定nologging模式以避免日志记录。parallel(t,8)表示使用8个并行线程进行插入操作，append提示符则指示数据以追加方式插入，适用于大数据量场景。此方法适用于需要快速插入大量数据且不要求日志记录的场景。
Oracle Hint /*+APPEND*/插入性能总结
u012272367的博客
05-31 1905
项目实战，oracle单表亿级别DML效率优化
Oracle数据库的配置和SQL语句的优化 /*+ rule */ & INSERT/*+append*/INTO
caihorse的专栏
06-27 4718
Oracle数据库的配置和SQL语句的优化 。   INSERT/*+append*/INTO t_servicexx(serviceid,clientid,prod_id,serviceno,addrid,                                      connectno,fgsid,gl_serviceid,up_serviceid,servlev,       ...
oracle appen,Oracle中关于insert中使用/*+append*/和nologging的总结
weixin_39922929的博客
04-03 805
关于Nologging和append,一直存在很多误解，经过一系列研究,终于发现了Nologging的真相，我们来看一下：1.对于logging 的table处理a.非归档模式下:SQL> archivelog list;Database logmode No Archive ModeAutomaticarchival EnabledArch...
Oracle 数据库数据操作：精通 INSERT, UPDATE, DELETE
最新发布
大数据领域创作新星
06-18 1917
在Oracle里，增加数据用INSERT，修改用UPDATE，删除则用DELETE。进行修改和删除时，建议总是带上WHERE条件来指定范围，这样可以确保操作的准确性
Oracle 使用append对insert大批量数据进行优化
weixin_44203221的博客
07-09 1604
Oracle insert 语句执行优化
Oracle hint 之 insert /*+ append */ 快速插入数据
qq_41206741的博客
07-05 3706
向表中插入大量数据时，可以通过使用hint直接路径加载(insert /*+ append */)提升性能。具体分析如下 1、非归档模式下，只需 /*+ append / 就能大量减少REDO日志；归档模式下，只有 APPEND+NOLOGGING 才能大量减少REDO日志。 2、INSERT /+ APPEND / 是从高水位标记（HWM）的位置开始插入，速度比常规加载方式快，但也可能造成空间浪费。 3、INSERT /+ APPEND / 是一个单独的事务，会阻塞同一会话中的所有DML语句，也会阻塞其他
oracle insert /+append/,insert /*+append */ 带来的问题
weixin_42180863的博客
04-10 1006
insert /*+append */在有些情况下我们为了加快插入速度使用insert /*+append */1、无论归档模式、还是非归档模式，append+nologing插入方式效果最好，生成最小的redo日志2、非归档模式下，append插入模式效果不错3、归档模式下，append插入模式没有效果我们做个测试session1create table ljl as select * from...
Oracle快速插入数据append
weixin_34217711的博客
04-24 657
1:用merge into 进行匹配更新和插入，2： 开启并行，提高速度3: 如果可以的话加NOLOGING 不写入日志4：如果数据有规律的话，分批次执行 1.使用marge快速插入; MERGE /*+ append */ INTO A dUSING (select * B where ...) f ON (d.account_no = f.account_no)WHEN MATC...
orcale中的\*+ append*\
weixin_37353303的博客
08-26 1171
*+ append*\一般用于INSERT INTO中，比如： INSERT INTO \*+ append*\ TABLEA select * from TABLEB append顾名思义，是将数据加到表的最后，不会在表的空闲快中插入数据，使用append可以提高数据的插入速度。 数据库归档与非归档： Oracle数据库有联机重做日志，这个日志是记录对数据库所做的修改，比如插入，删除，更新数据等，对这些操作都会记录在联机重做日志里。一般数据库至少要有2个联机重做日志组。当一个联机重做日志组被写满的时候
Oracle中关于insert中使用/*+append*/和nologging的总结
cuixu4010的博客
03-17 2654
关于Nologging和append,一直存在很多误解，经过一系列研究,终于发现了Nologging的真相， 我们来看一下： 1.对于logging 的table处理 a.非归档模式下...
/* append*/ oracle append 知识点
04-11
INSERT /*append */ INTO TABLE1 AS SELECT * FROM TABLE2;
oracle append into,insert /*+ append */ into 与insert into 的区别
weixin_39947396的博客
04-06 1005
分别在这两种情况下执行插入，用tkprof生成统计信息文件，对比如下：insert into test select * from bc_log_1call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Pa...
ORacle——insert /*+append*/提高性能
Leesire的专栏
01-05 5395
在非归档模式下表设置为nologging用insert /*+append*/速度最快。那为什么快呢，原理是什么？下面我们来一起做一个实验：   SQL> create or replace view m_undo_redo as     select  v$statname.name,value     from v$mystat, v$statname     where v$mys
Oracle——插入效率普通insert和insert /*+append*/
热门推荐
Leesire的专栏
01-05 1万+
在归档模式和非归档模式下，设定表为logging和nologging，测量普通的insert 和insert /*+append*/生成redo大小。第一次测试我对下列结果报怀疑态度，于是又重新测试一遍，结果差不多，想想是测试的数量太少。如果是在正式环境上做数据迁移，最好就选择最后一种模式。              模式 普通insert下redo生成量（Byte） ins
Oracle的insert into 加上append参数会快很多的
JUST DO IT.
06-01 2216
因为这样不会产生这么多日志，学习了吧。
oracle /+append/好用吗,ORACLE-insert /*+append*/提高性能
weixin_42300167的博客
04-03 540
在非归档模式下表设置为nologging用insert /*+append*/速度最快。那为什么快呢，原理是什么？下面我们来一起做一个实验：SQL> create or replace view m_undo_redo asselect v$statname.name,valuefrom v$mystat, v$statnamewhere v$mystat.statistic# =v$sta...
Oracle直接路径加载--append的深度解析
数据库
02-06 400
㈠ 直接路径加载和buffer cache 直接路径插入的数据不经过buffer cache，从PGA直接把数据格式化成Oracle块 然后由普通的Oracle Server Process进程把数据块写入数据文件 因为不经过buffer cache，所以不需要DBWn介入 假如有表a，现要将a中的数据插入表b，在普通的插入下，需先将a的数据块I/O到buffer cache...
oracle批量insert几百万条数据
06-13
<think>我们参考了站内关于Oracle批量插入数据的引用信息，结合数据库操作的最佳实践，总结出以下高效批量插入数百万条数据的优化方法：###1.使用批处理绑定（BulkBinding）技术-**原理**：通过减少SQL解析次数和网络往返次数提升性能。引用[1]提到MyBatis中每1000条一批次插入，类似地Oracle支持`FORALL`语句实现PL/SQL批量绑定：```sqlDECLARETYPEt_tabISTABLEOFtest_data%ROWTYPE;l_datat_tab:=t_tab();--初始化集合BEGIN--填充数据到l_data（示例）FORiIN1..1000000LOOPl_data.EXTEND;l_data(l_data.LAST):=('id_'||i,i,'nme2_'||i,'name3_'||i);ENDLOOP;--批量插入FORALLjIN1..l_data.COUNTINSERTINTOtest_dataVALUESl_data(j);END;/```**优势**：相比逐条插入（如引用[4]的循环INSERT），速度提升10-100倍。###2.使用直接路径插入（Direct-PathInsert）-**方法**：添加`/*+APPEND*/`提示使插入绕过缓冲区缓存直接写入数据文件：```sqlINSERT/*+APPEND*/INTOtest_dataSELECT'id_'||LEVEL,LEVEL,'nme2_'||LEVEL,'name3_'||LEVELFROMdualCONNECTBYLEVEL<=1000000;```**注意**：-需要提交后才可见数据（非日志减少I/O）。-表级锁（插入期间其他DML阻塞），适合非并发写入场景。###3.分区表并行插入-**策略**：1.创建分区表（如按ID范围分区）：```sqlCREATETABLEtest_data_part(IDvarchar2(32),...)PARTITIONBYRANGE(ID)(PARTITIONp1VALUESLESSTHAN('id_500000'),PARTITIONp2VALUESLESSTHAN(MAXVALUE));```2.开启并行插入（调整`PARALLEL`度）：```sqlALTERSESSIONENABLEPARALLELDML;INSERT/*+PARALLEL(test_data_part,4)*/INTOtest_data_part...;```**效果**：利用多进程加速（需足够CPU资源）[^4]。###4.调整数据库参数-**关键配置**（需DBA权限）：-`db_writer_processes`：增加写进程数。-`log_buffer`和`log_file_size`：避免日志切换瓶颈。-引用[1]强调需设置合适的**连接池参数**（如最大连接数）。###5.外部表+SQL*Loader方案-**步骤**：1.生成数据文件（如CSV格式）。2.创建外部表映射文件：```sqlCREATEDIRECTORYdata_dirAS'/path/to/file';CREATETABLEext_test_data(...)ORGANIZATIONEXTERNAL(TYPEORACLE_LOADERDEFAULTDIRECTORYdata_dirACCESSPARAMETERS(RECORDSDELIMITEDBYNEWLINE...)LOCATION('data.csv'));```3.用`INSERT/*+APPEND*/INTOtest_dataSELECT*FROMext_test_data;`加载。-**适用场景**：初始数据迁移（速度最快）。###优化对比表|方法|百万条耗时|适用场景||----------------------|------------|-----------------------||循环单条INSERT|>10分钟|不推荐||FORALL批处理|~30秒|程序生成数据||直接路径INSERT|~15秒|非并发大事务||SQL*Loader|~10秒|文件导入|>**警示**：引用[3]指出避免用`INSERTALL`插入大量数据（SQL文本过长导致CPU耗尽）。§§相关问题§§1.如何监控Oracle批量插入时的性能瓶颈？2.分区表策略如何选择范围分区或哈希分区？3.直接路径插入与常规插入的事务隔离性有何差异？</think> ### Oracle 批量插入数百万条数据的最佳实践与优化方法 根据引用资料和数据库操作经验，以下是针对Oracle高效插入海量数据的核心方案（按优先级排序）： --- #### 1. **批处理绑定（Bulk Binding）** **核心技术**：`FORALL` + `BULK COLLECT` ```sql DECLARE TYPE t_tab IS TABLE OF test_data%ROWTYPE; l_data t_tab := t_tab(); BEGIN -- 1. 生成数据到集合 FOR i IN 1..1000000 LOOP l_data.EXTEND; l_data(l_data.LAST) := ( 'id_' || i, i, 'nme2_' || i, 'name3_' || i ); END LOOP; -- 2. 批量插入（分批次提交） FORALL j IN 1..l_data.COUNT INSERT INTO test_data VALUES l_data(j); COMMIT; -- 建议每10000-50000条提交一次 END; ``` **优化点**： - 引用[1][4]验证：比逐条INSERT快10-100倍 - 关键参数：`SIZE`控制批处理量（推荐1000-5000/批） - 内存管理：避免单次加载全部数据，分段处理 --- #### 2. **直接路径插入（Direct-Path Insert）** **核心技术**：`/*+ APPEND */`提示符 ```sql INSERT /*+ APPEND */ INTO test_data SELECT 'id_' || LEVEL, LEVEL, 'nme2_' || LEVEL, 'name3_' || LEVEL FROM dual CONNECT BY LEVEL <= 1000000; ``` **优势**： - 绕过缓冲区缓存，直写数据文件 - 减少重做日志生成（配合`NOLOGGING`） - 引用[2][4]实测：百万级数据6-20秒完成 --- #### 3. **分区表并行插入** ```sql ALTER SESSION ENABLE PARALLEL DML; INSERT /*+ PARALLEL(test_data, 8) */ INTO test_data SELECT ... -- 并行查询加速数据生成 ``` **配置要点**： - 根据CPU核数设置PARALLEL度（通常2-8） - 结合分区裁剪（Partition Pruning） - 引用[4]推荐：SSD存储环境下性能提升3-5倍 --- #### 4. **外部工具链方案** **SQL*Loader终极方案**： ```bash sqlldr user/pwd@db control=load.ctl direct=true ``` 控制文件示例： ```control LOAD DATA INFILE 'data.csv' APPEND INTO TABLE test_data FIELDS TERMINATED BY "," (ID, NAME1, NAME2, NAME3) ``` **优势**： - 引用[3]对比：比程序插入快20倍+ - 支持：直接路径加载、错误跳过、多文件并发 --- ### ⚠️ 关键优化措施 1. **事务控制**： - 分批次COMMIT（避免UNDO表空间溢出） - 设置`ALTER SESSION SET COMMIT_WRITE = BATCH` 2. **存储优化**： ```sql ALTER TABLE test_data NOLOGGING; -- 禁用重做日志 ALTER INDEX test_data_idx UNUSABLE; -- 禁用索引（重建更快） ``` 3. **参数调整**： - `db_writer_processes`：增加写进程数 - `log_buffer`：增大至256MB+ - `PGA_AGGREGATE_TARGET`：保证排序内存 --- ### 性能对比参考 | 方法 | 100万条耗时 | 适用场景 | |----------------------|------------|-----------------------| | 程序循环单条INSERT | >30分钟 | 绝对避免 | | 批处理绑定(FORALL) | 15-60秒 | 程序生成数据 | | 直接路径INSERT+并行 | 8-25秒 | 数据库内生成 | | SQL*Loader | 5-12秒 | 文件导入 | > **引用说明**： > - 批处理大小设置 [^1] > - Oracle直接路径验证 [^2][^4] > - 避免INSERT ALL的警告 [^3]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值