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
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
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> 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> 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>
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;
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;
SQL> select count(*) from t_test;
COUNT(*)
----------
900000
----------
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;
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大大小于常规路径插入
----------------------------------------------------------
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
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;
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> /
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;
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
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
因为加在表级排它锁,即便在自己会话也不能操作自己.
----直接路径插入的一些操作限制:尤为重要
再回到上述的错误:
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.
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;
---常规路径插入
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);
SQL> select used_ublk,ubablk from v$transaction where addr=(select taddr from v$
session where sid=11);
USED_UBLK UBABLK
---------- ----------
271 8855
---------- ----------
271 8855
SQL> rollback;
Rollback complete.
---直接路径插入
SQL> insert /*+ append */ into t_test select 1 from dual connect by level<=9e5;
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);
SQL> select used_ublk,ubablk from v$transaction where addr=(select taddr from v$
session where sid=11);
USED_UBLK UBABLK
---------- ----------
2 7825
--小结:二者在undo产生方面也有区别
---------- ----------
2 7825
--小结:二者在undo产生方面也有区别
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-753150/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-753150/