oracle常规与直接路径插入区别

最新推荐文章于 2024-02-28 16:25:56 发布
最新推荐文章于 2024-02-28 16:25:56 发布
阅读量300 收藏 1
点赞数
文章标签： 数据库 嵌入式
SQL> create table t_test(a int);
 
Table created
 
SQL> insert into t_test select 1 from dual connect by level<=9e5;
 
900000 rows inserted
 
SQL> commit;
 
Commit complete
--查询表区分配信息,共计26 extent
SQL> select segment_name,extent_id,blocks from user_extents where segment_name='T_TEST';
 
SEGMENT_NAME                                                                      EXTENT_ID     BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST                                                                                    0          8
T_TEST                                                                                    1          8
T_TEST                                                                                    2          8
T_TEST                                                                                    3          8
T_TEST                                                                                    4          8
T_TEST                                                                                    5          8
T_TEST                                                                                    6          8
T_TEST                                                                                    7          8
T_TEST                                                                                    8          8
T_TEST                                                                                    9          8
T_TEST                                                                                   10          8
T_TEST                                                                                   11          8
T_TEST                                                                                   12          8
T_TEST                                                                                   13          8
T_TEST                                                                                   14          8
T_TEST                                                                                   15          8
T_TEST                                                                                   16        128
T_TEST                                                                                   17        128
T_TEST                                                                                   18        128
T_TEST                                                                                   19        128
 
SEGMENT_NAME                                                                      EXTENT_ID     BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST                                                                                   20        128
T_TEST                                                                                   21        128
T_TEST                                                                                   22        128
T_TEST                                                                                   23        128
T_TEST                                                                                   24        128
T_TEST                                                                                   25        128
 
26 rows selected
 
--删除部分数据
SQL> delete from t_test where rownum<=100000;
 
100000 rows deleted
 
SQL> commit;
 
Commit complete
---为表分配的26个extent并未因删除数据而free
SQL> select segment_name,extent_id,blocks from user_extents where segment_name='T_TEST';
 
SEGMENT_NAME                                                                      EXTENT_ID     BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST                                                                                    0          8
T_TEST                                                                                    1          8
T_TEST                                                                                    2          8
T_TEST                                                                                    3          8
T_TEST                                                                                    4          8
T_TEST                                                                                    5          8
T_TEST                                                                                    6          8
T_TEST                                                                                    7          8
T_TEST                                                                                    8          8
T_TEST                                                                                    9          8
T_TEST                                                                                   10          8
T_TEST                                                                                   11          8
T_TEST                                                                                   12          8
T_TEST                                                                                   13          8
T_TEST                                                                                   14          8
T_TEST                                                                                   15          8
T_TEST                                                                                   16        128
T_TEST                                                                                   17        128
T_TEST                                                                                   18        128
T_TEST                                                                                   19        128
 
SEGMENT_NAME                                                                      EXTENT_ID     BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST                                                                                   20        128
T_TEST                                                                                   21        128
T_TEST                                                                                   22        128
T_TEST                                                                                   23        128
T_TEST                                                                                   24        128
T_TEST                                                                                   25        128
 
26 rows selected
---常规路径插入
SQL> insert into t_test select 1 from dual connect by level<=1000;
 
1000 rows inserted
 
SQL> commit;
 
Commit complete
 
----常规路径插入会在hwm之下搜索可重用的数据块,并未分配新的extent,仍是26个extent
SQL> select segment_name,extent_id,blocks from user_extents where segment_name='T_TEST';
 
SEGMENT_NAME                                                                      EXTENT_ID     BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST                                                                                    0          8
T_TEST                                                                                    1          8
T_TEST                                                                                    2          8
T_TEST                                                                                    3          8
T_TEST                                                                                    4          8
T_TEST                                                                                    5          8
T_TEST                                                                                    6          8
T_TEST                                                                                    7          8
T_TEST                                                                                    8          8
T_TEST                                                                                    9          8
T_TEST                                                                                   10          8
T_TEST                                                                                   11          8
T_TEST                                                                                   12          8
T_TEST                                                                                   13          8
T_TEST                                                                                   14          8
T_TEST                                                                                   15          8
T_TEST                                                                                   16        128
T_TEST                                                                                   17        128
T_TEST                                                                                   18        128
T_TEST                                                                                   19        128
 
SEGMENT_NAME                                                                      EXTENT_ID     BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST                                                                                   20        128
T_TEST                                                                                   21        128
T_TEST                                                                                   22        128
T_TEST                                                                                   23        128
T_TEST                                                                                   24        128
T_TEST                                                                                   25        128
 
26 rows selected
---删除部分数据
SQL> delete from t_test where rownum<=200000;
 
200000 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> select segment_name,extent_id,blocks from user_extents where segment_name='T_TEST';
 
SEGMENT_NAME                                                                      EXTENT_ID     BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST                                                                                    0          8
T_TEST                                                                                    1          8
T_TEST                                                                                    2          8
T_TEST                                                                                    3          8
T_TEST                                                                                    4          8
T_TEST                                                                                    5          8
T_TEST                                                                                    6          8
T_TEST                                                                                    7          8
T_TEST                                                                                    8          8
T_TEST                                                                                    9          8
T_TEST                                                                                   10          8
T_TEST                                                                                   11          8
T_TEST                                                                                   12          8
T_TEST                                                                                   13          8
T_TEST                                                                                   14          8
T_TEST                                                                                   15          8
T_TEST                                                                                   16        128
T_TEST                                                                                   17        128
T_TEST                                                                                   18        128
T_TEST                                                                                   19        128
 
SEGMENT_NAME                                                                      EXTENT_ID     BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST                                                                                   20        128
T_TEST                                                                                   21        128
T_TEST                                                                                   22        128
T_TEST                                                                                   23        128
T_TEST                                                                                   24        128
T_TEST                                                                                   25        128
 
26 rows selected
 
---直接路径插入
SQL> insert /*+ append */ into t_test select 1 from dual connect by level<=10000;
 
10000 rows inserted
 
SQL> commit;
 
Commit complete
 
---直接在hwm之上分配extent,并未重用hwm之下已删除的空间
SQL> select segment_name,extent_id,blocks from user_extents where segment_name='T_TEST';
 
SEGMENT_NAME                                                                      EXTENT_ID     BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST                                                                                    0          8
T_TEST                                                                                    1          8
T_TEST                                                                                    2          8
T_TEST                                                                                    3          8
T_TEST                                                                                    4          8
T_TEST                                                                                    5          8
T_TEST                                                                                    6          8
T_TEST                                                                                    7          8
T_TEST                                                                                    8          8
T_TEST                                                                                    9          8
T_TEST                                                                                   10          8
T_TEST                                                                                   11          8
T_TEST                                                                                   12          8
T_TEST                                                                                   13          8
T_TEST                                                                                   14          8
T_TEST                                                                                   15          8
T_TEST                                                                                   16        128
T_TEST                                                                                   17        128
T_TEST                                                                                   18        128
T_TEST                                                                                   19        128
 
SEGMENT_NAME                                                                      EXTENT_ID     BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T_TEST                                                                                   20        128
T_TEST                                                                                   21        128
T_TEST                                                                                   22        128
T_TEST                                                                                   23        128
T_TEST                                                                                   24        128
T_TEST                                                                                   25        128
T_TEST                                                                                   26        128
 
27 rows selected
 
SQL>

----再测试常规与直接路径插入在产生redo方面的区别
SQL> create table t_test(a int);
Table created.
SQL> set autot exp stat
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autot trace exp stat
SQL> insert into t_test select 1 from dual connect by level<=9e5;
900000 rows created.

Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825
-------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Cost (%CPU)| Time
-------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |        |     1 |     2   (0)| 00:00:01
|   1 |  LOAD TABLE CONVENTIONAL      | T_TEST |       |            |
|*  2 |   CONNECT BY WITHOUT FILTERING|        |       |            |
|   3 |    FAST DUAL                  |        |     1 |     2   (0)| 00:00:01
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(LEVEL<=9e5)

Statistics
----------------------------------------------------------
       1093  recursive calls
      16025  db block gets
       2900  consistent gets
          0  physical reads
   12302340  redo size
        835  bytes sent via SQL*Net to client
        815  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     900000  rows processed
SQL> set autot off
SQL> select count(*) from t_test;
  COUNT(*)
----------
    900000
SQL> truncate table t_test;
Table truncated.
SQL> set autot trace exp stat
SQL> insert /*+ append */ into t_test select 1 from dual connect by level<=9e5;
900000 rows created.

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
        845  recursive calls
       2114  db block gets
        191  consistent gets
          2  physical reads
      62320  redo size
        822  bytes sent via SQL*Net to client
        832  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     900000  rows processed
    
小结:直接路径插入产生的redo size大大小于常规路径插入  
 
---测试常规路径与直接路径插入持锁信息区别
---11会话是执行常规与直接路径插入的会话
SQL> select * from v$lock where sid=11;
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
000007FF62C55BB0 000007FF62C55C08         11 AE          100          0          4          0      24507          0
000007FF62C56A68 000007FF62C56AC0         11 TO         5003          1          3          0       1097          0
----在11会话进行常规路径插入
SQL> insert  into t_test select 1 from dual connect by level<=9e5;
900000 rows created.
---再次查询常规路径插入之后的持锁信息,多2条记录,持锁模式为3和6级锁,即行级锁和表级排它锁
SQL> /
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
000007FF62C55BB0 000007FF62C55C08         11 AE          100          0          4          0      24521          0
000007FF62C56A68 000007FF62C56AC0         11 TO         5003          1          3          0       1111          0
000000000D5B62A0 000000000D5B6300         11 TM        67162          0          3          0          3          0
000007FF5ED150A0 000007FF5ED15118         11 TX       589827        707          6          0          3          0
 
SQL> /
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
000007FF62C55BB0 000007FF62C55C08         11 AE          100          0          4          0      24540          0
000007FF62C56A68 000007FF62C56AC0         11 TO         5003          1          3          0       1130          0

----释放常规路径插入事务  
SQL> rollback;
Rollback complete.
-----运行直接路径插入
SQL> insert /*+ append */ into t_test select 1 from dual connect by level<=9e5;
900000 rows created.
SQL>
----查询直接路径插入之后的持锁信息,新增2条记录全是6级锁,全是表级排它锁
SQL> /
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
000007FF62C55BB0 000007FF62C55C08         11 AE          100          0          4          0      24783          0
000007FF62C56A68 000007FF62C56AC0         11 TO         5003          1          3          0       1373          0
000000000D5B62A0 000000000D5B6300         11 TM        67162          0          6          0         49          0
000007FF5ED150A0 000007FF5ED15118         11 TX        65551        703          6          0         49          0
小结:常规路径插入持锁与直接路径插入持锁级别不同,在编写代码要充分考虑,不然会影响业务的并发
     再回到上述的错误:
     ERROR:
      ORA-12838: cannot read/modify an object after modifying it in parallel
     因为加在表级排它锁,即便在自己会话也不能操作自己.
    
    
----直接路径插入的一些操作限制:尤为重要  
 ---如果违反任何一个操作限制,oracle自动转变化常规路径插入,不会报错
Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially
without returning any message, unless otherwise noted:
 
 ---单一事务中,可以存在多个直接路径插入;但是,一个dml操作变更了某个表或者分区或索引之后,这个事务中其它的dml语句不能访问这个表或者分区或索引
You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a
particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.
 
---直接路径插入之前可以查询表,分区,索引,之后,不能访问这些对象;
Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.
 
----如果在同一个事务中,任何串行或并行操作想去访问某个正处于直接路径插入的对象.数据库返回错误,并拒绝此操作
If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the
database returns an error and rejects the statement.
 
---直接路径插入的目标对象不能是cluster
The target table cannot be of a cluster.
 
---同上,不能包含对象列
The target table cannot contain object type columns.
 
----如果iot未分区,不能用于直接路径插入;或者它有一个映射表,或它被一个物化视图引用
Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view.
 
----直接路径插入到iot的一个分区或者仅有一个分区的iot,会串行执行,即使iot开启了并行模式或者你指定了append提示;
Direct-path INSERT into a single partition of an index-organized table (IOT), or into a partitioned IOT with only one partition, will be done serially,
---但是,直接路径插入到分区的iot会开启并行模式,只要未使用分区扩展的名字且iot有多个分区
even if the IOT was created in parallel mode or you specify the APPEND or APPEND_VALUES hint. However, direct-path INSERT operations into a partitioned IOT will
honor parallel mode as long as the partition-extended name is not used and the IOT has more than one partition.
 
----用于直接路径插入的目标表不能定义触发器或者外键引用关系
The target table cannot have any triggers or referential integrity constraints defined on it.
 
---用于直接路径插入的目标表不能用于复制
The target table cannot be replicated.
 
----如果某个事务包含直接路径插入,不能用于分发(分布式环境)
A transaction containing a direct-path INSERT statement cannot be or become distributed.
 
---下面测试直接与常规路径插入生产undo的区别
---常规路径插入
SQL> insert  into t_test select 1 from dual connect by level<=9e5;
900000 rows created.
---占用undo blocks 8855
SQL> select used_ublk,ubablk from v$transaction where addr=(select taddr from v$
session where sid=11);
 USED_UBLK     UBABLK
---------- ----------
       271       8855
SQL> rollback;
Rollback complete.
---直接路径插入
SQL> insert /*+ append */ into t_test select 1 from dual connect by level<=9e5;
900000 rows created.
---占用undo blocks 7825,节约了1000多
SQL> select used_ublk,ubablk from v$transaction where addr=(select taddr from v$
session where sid=11);
 USED_UBLK     UBABLK
---------- ----------
         2       7825
        
--小结:二者在undo产生方面也有区别        
 
    
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-753150/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-753150/

clg10051
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle insert /+append/,insert /*+append */ 带来的问题
weixin_42180863的博客
04-10 859
insert /*+append */在有些情况下我们为了加快插入速度使用insert /*+append */1、无论归档模式、还是非归档模式，append+nologing插入方式效果最好，生成最小的redo日志2、非归档模式下，append插入模式效果不错3、归档模式下，append插入模式没有效果我们做个测试session1create table ljl as select * from...
[Oracle] 常用工具集之SQL*Loader的用法
01-19
其示意图如下： 控制文件：用于控制数据导入的行为方式的文件（最重要的文件）。 参数文件（可选） 数据文件：放置源数据的平面文件 ...Oracle提供两种类型的插入语句：常规插入（conventional insert）和直
Oracle insert /*+ APPEND */原理解析
热门推荐
xiaobluesky的专栏
01-10 5万+
关于insert /*+ append */我们需要注意以下三点： a、非归档模式下，只需append就能大量减少redo的产生；归档模式下，只有append+nologging才能大量减少redo。 b、insert /*+ append */时会对表加锁（排它锁），会阻塞表上的除了select以外所有DML语句；传统 关于insert /*+ append */我们需要注意以下三点：
Oracle hint 之 insert /*+ append */ 快速插入数据
qq_41206741的博客
07-05 2720
向表中插入大量数据时，可以通过使用hint直接路径加载(insert /*+ append */)提升性能。具体分析如下 1、非归档模式下，只需 /*+ append / 就能大量减少REDO日志；归档模式下，只有 APPEND+NOLOGGING 才能大量减少REDO日志。 2、INSERT /+ APPEND / 是从高水位标记（HWM）的位置开始插入，速度比常规加载方式快，但也可能造成空间浪费。 3、INSERT /+ APPEND / 是一个单独的事务，会阻塞同一会话中的所有DML语句，也会阻塞其他
Oracle 直接路径插入（Direct-Path Insert）
最新发布
Vincent的博客
02-28 1100
直接路径插入（Direct Path Insert）是Oracle一种数据加载提速技术，可以在使用insert语句或SQL*Loader工具大批量加载数据时使用。直接路径插入处理策略与普通insert语句完全不同，Oracle会通过牺牲空间，安全性，并发性能来换取加载速度。
oracle append 分区,insert append 到底扩展几个数据块？
weixin_33147807的博客
04-14 333
最近从《oracle9iSTATSPACK高性能调整》上看到关于insert append的一段话：“我们假定数据库有一个表名为transaction，有200个数据操作员在不断向其中加入行。这个transaction表定义了200个自由表(freelist)，这个表会通过提升oracle表的高水位(high-water)标志来收集自由块。这个例子假定当在insert语句上使用append提示的时...
oracle /+append/好用吗,ORACLE-insert /*+append*/提高性能
weixin_42300167的博客
04-03 462
在非归档模式下表设置为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...
经典SQL语句大全
12-20
插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * from table1 where field1 like ’%value...
数据库操作语句大全(sql)
08-15
插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * from table1 where field1 like ’%value...
/* append*/ oracle append 知识点
04-11
INSERT /*append */ INTO TABLE1 AS SELECT * FROM TABLE2;
Oracle——插入效率普通insert和insert /*+append*/
Leesire的专栏
01-05 1万+
在归档模式和非归档模式下，设定表为logging和nologging，测量普通的insert 和insert /*+append*/生成redo大小。第一次测试我对下列结果报怀疑态度，于是又重新测试一遍，结果差不多，想想是测试的数量太少。如果是在正式环境上做数据迁移，最好就选择最后一种模式。              模式 普通insert下redo生成量（Byte） ins
oracle appen,Oracle中关于insert中使用/*+append*/和nologging的总结
weixin_39922929的博客
04-03 595
关于Nologging和append,一直存在很多误解，经过一系列研究,终于发现了Nologging的真相，我们来看一下：1.对于logging 的table处理a.非归档模式下:SQL> archivelog list;Database logmode No Archive ModeAutomaticarchival EnabledArch...
Direct-Path INSERT(直接路径插入)
渔夫数据库笔记
10-29 3265
直接路径插入 Direct-Path INSERT
【知识点整理】NOLOGGING、APPEND、ARCHIVE和PARALLEL下，REDO、UNDO和执行速度的比较...
coco3600的博客
09-30 191
【知识点整理】Oracle中NOLOGGING、APPEND、ARCHIVE和PARALLEL下，REDO、UNDO和执行速度的比较 1BLOG文档结构图 ...
oracle之优化二 append
fengfengfeifei的专栏
09-19 928
在insert into table_name select语句中，如果select出来的数据量很庞大，可以使用insert /*+append+*/ into table_name select以提高效率。 oracle append有什么作用，为什么能提高效率呢？因为在使用
Direct-Path Insert 直接路径加载
落落的专栏 专注SQL调优 性能调优
02-17 4996
直接路径加载直接路径加载表示绕过buffer cache，直接将数据写入表中。直接路径加载不会将数据写入HWM以下的数据块，而是在HWM之后写入数据，由于绕过了buffer cache，所以直接路径加载的时候性能好于常规路径加载。直接路径加载可以并行运行，也可以串行运行，既可以对分区表做直接路径加载，也可以对非分区表做直接路径加载。指定 APPEND hint就表示启用了直接路径加载特征。在11G之前，直接路径加载只支持insert ...select,从11G 开始，Oracle支持insert ....
oracle无法访问dump路径
06-08
如果Oracle无法访问dump路径，可能有以下几个原因： 1. 权限问题。请确保Oracle进程具有访问该路径的适当权限。您可以检查该路径的访问权限以及Oracle用户的权限设置。 2. 检查路径是否正确。请确保您指定的路径...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值