oracle常規與直接路徑插入區別
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- insert /*+ append */直接路徑插入APP
- 直接路徑插入 -- insert /*+append*/ into [zt]APP
- 關於直接路徑插入的工作原理
- Oracle直接路徑載入Oracle
- 直接路徑插入模式的一些討論模式
- 【Direct-Path】直接路徑載入提升插入效率及其自身限制
- 絕對路徑和相對路徑的區別，
- 絕對路徑和相對路徑的區別
- DOM常用外部插入方法與區別
- 路徑規劃: 淺談路徑規劃演算法演算法
- 【No.16異常的直接插入排序】排序
- 兩個路徑下的iptables的區別
- 機器人路徑規劃 A*與D*機器人路徑規劃演算法詳述機器人演算法
- Linux中絕對路徑和相對路徑有什麼區別?Linux
- linux中環境便令PATH+路徑 和 路徑 + PATH的區別Linux
- AMD規範與CMD規範的區別
- 插入單引號在oracle和informix中的區別OracleORM
- mysql與Oracle的區別MySqlOracle
- Oracle - @和@@、&與&& 的區別Oracle
- oracle in與exists 的區別Oracle
- 區域儲存網路（SAN）與 網路直接儲存（NAS）
- canvas 路徑與子路徑Canvas
- DMO節點內部插入的常用方法與區別
- 索引對直接路徑載入的影響索引
- Linux 常見目錄與區別Linux
- Oracle的學習路徑與方法討論Oracle
- 需求與規範的區別 - modernanalystNaN
- 直接insert與儲存過程insert效能區別儲存過程
- oracle restore與recover的區別OracleREST
- oracle truncate 與 delete 的區別Oracledelete
- HTML絕對路徑與相對路徑HTML
- php -- 取路徑：getcwd()、__DIR__、__FILE__ 的區別PHP
- Oracle 訪問路徑Oracle
- canvas路徑與子路徑詳解Canvas
- Linux中絕對路徑和相對路徑是什麼？二者有何區別？Linux
- 直接插入排序排序
- Oracle與OpenJDK之間的區別OracleJDK
- oracle ADG與DG的區別Oracle