ORacle——insert /*+append*/提高性能
发布时间:2020-12-12 15:29:28 所属栏目:百科 来源:网络整理
导读:在非归档模式下表设置为nologging用insert /*+append*/速度最快。那为什么快呢,原理是什么?下面我们来一起做一个实验: SQL create or replace view m_undo_redo as select v$statname.name,value from v$mystat,v$statname where v$mystat.statistic# =v$
在非归档模式下表设置为nologging用insert /*+append*/速度最快。那为什么快呢,原理是什么?下面我们来一起做一个实验: SQL> create or replace view m_undo_redo as select v$statname.name,value from v$mystat,v$statname where v$mystat.statistic# =v$statname.statistic# and (v$statname.name ='redo size' or v$statname.name = 'undo change vector size'); 视图已创建。 SQL> create table t (x int); 表已创建。 SQL> set timing on SQL> select * from m_undo_redo; NAME VALUE ---------------------------------------------------------------- ---------- redo size 22644 undo change vector size 7484 SQL> insert into t select rownum from dual connect by level <=1000000; 已创建1000000行。 已用时间: 00: 00: 01.03 SQL> commit; 提交完成。 SQL> select * from m_undo_redo; NAME VALUE ---------------------------------------------------------------- ---------- redo size 15722456 undo change vector size 2380000 SQL> select (2380000-7484) undo,(15722456-22644) redo from dual; UNDO REDO ---------- ---------- 2372516 15699812 SQL> truncate table t; 表被截断。 SQL> select * from m_undo_redo; NAME VALUE ---------------------------------------------------------------- ---------- redo size 15781532 undo change vector size 2396672 SQL> insert /*+append*/ into t select rownum from dual connect by level <=1000000; 已用时间: 00: 00: 00.96 SQL> commit; SQL> select * from m_undo_redo; NAME VALUE ---------------------------------------------------------------- ---------- redo size 15871640 undo change vector size 2419196 SQL> select (2419196-2396672) undo,(15871640-15781532) redo from dual; UNDO REDO ---------- ---------- 22524 90108 两次的对比: |