http://www.itpub.net/thread-982860-1-1.html
默认表中修改块中的记录会影响本块其他记录的 ora_rowscn
如果加上ROWDEPENDENCIES ,那么修改记录只影响本记录的ora_rowscn,不会引起其他记录的改变
加上这个功能,会影响存储,每条记录会增加6字节的物理存储空间
sample
默认情况下
T@ora>create table t1 as select * from t ;
Table created.
Elapsed: 00:00:00.07
T@ora>select rowid,ora_rowscn,a from t1;
ROWID ORA_ROWSCN A
------------------ ---------- ----------
AAARN5AAGAAAXHcAAA 45387504 1
AAARN5AAGAAAXHcAAB 45387504 2
AAARN5AAGAAAXHcAAC 45387504 3
AAARN5AAGAAAXHcAAD 45387504 4
AAARN5AAGAAAXHcAAE 45387504 5
AAARN5AAGAAAXHcAAF 45387504 6
AAARN5AAGAAAXHcAAG 45387504 7
AAARN5AAGAAAXHcAAH 45387504 8
AAARN5AAGAAAXHcAAI 45387504 9
AAARN5AAGAAAXHcAAJ 45387504 10
10 rows selected.
Elapsed: 00:00:00.20
T@ora>update t1 set a = 0 where a <3;
2 rows updated.
Elapsed: 00:00:00.01
T@ora>commit;
Commit complete.
Elapsed: 00:00:00.00
T@ora>select rowid,ora_rowscn,a from t1;
ROWID ORA_ROWSCN A
------------------ ---------- ----------
AAARN5AAGAAAXHcAAA 45387522 0
AAARN5AAGAAAXHcAAB 45387522 0
AAARN5AAGAAAXHcAAC 45387522 3 《----这里开始后面的记录都没做修改,但是ora_rowscn却改变了
AAARN5AAGAAAXHcAAD 45387522 4
AAARN5AAGAAAXHcAAE 45387522 5
AAARN5AAGAAAXHcAAF 45387522 6
AAARN5AAGAAAXHcAAG 45387522 7
AAARN5AAGAAAXHcAAH 45387522 8
AAARN5AAGAAAXHcAAI 45387522 9
AAARN5AAGAAAXHcAAJ 45387522 10
10 rows selected.
增加ROWDEPENDENCIES
T@ora>create table t2 ROWDEPENDENCIES as select * from t ;
Table created.
Elapsed: 00:00:00.06
T@ora>select rowid,ora_rowscn,a from t2;
ROWID ORA_ROWSCN A
------------------ ---------- ----------
AAARN6AAGAAAXHkAAA 45387561 1
AAARN6AAGAAAXHkAAB 45387561 2
AAARN6AAGAAAXHkAAC 45387561 3
AAARN6AAGAAAXHkAAD 45387561 4
AAARN6AAGAAAXHkAAE 45387561 5
AAARN6AAGAAAXHkAAF 45387561 6
AAARN6AAGAAAXHkAAG 45387561 7
AAARN6AAGAAAXHkAAH 45387561 8
AAARN6AAGAAAXHkAAI 45387561 9
AAARN6AAGAAAXHkAAJ 45387561 10
10 rows selected.
Elapsed: 00:00:00.03
T@ora>update t2 set a = 0 where a <3;
2 rows updated.
Elapsed: 00:00:00.01
T@ora>commit;
Commit complete.
Elapsed: 00:00:00.00
T@ora>select rowid,ora_rowscn,a from t2;
ROWID ORA_ROWSCN A
------------------ ---------- ----------
AAARN6AAGAAAXHkAAA 45387578 0
AAARN6AAGAAAXHkAAB 45387578 0
AAARN6AAGAAAXHkAAC 45387561 3 《--没有修改的scn就不会改变
AAARN6AAGAAAXHkAAD 45387561 4
AAARN6AAGAAAXHkAAE 45387561 5
AAARN6AAGAAAXHkAAF 45387561 6
AAARN6AAGAAAXHkAAG 45387561 7
AAARN6AAGAAAXHkAAH 45387561 8
AAARN6AAGAAAXHkAAI 45387561 9
AAARN6AAGAAAXHkAAJ 45387561 10
存储空间的影响,dump出来就可以看到2个存储结构不一样
普通表
tab 0, row 0, @0x1f1e
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 1] 80
col 1: [ 2] c1 02
2个number类型
tab 0, row 0, @0x1edc
tl: 14 fb: --H-FL-- lb: 0x2 cc: 2
dscn 0x0000.02b48f29
col 0: [ 1] 80
col 1: [ 2] c1 02
多出6个字节记录dscn
默认表中修改块中的记录会影响本块其他记录的 ora_rowscn
如果加上ROWDEPENDENCIES ,那么修改记录只影响本记录的ora_rowscn,不会引起其他记录的改变
加上这个功能,会影响存储,每条记录会增加6字节的物理存储空间
sample
默认情况下
T@ora>create table t1 as select * from t ;
Table created.
Elapsed: 00:00:00.07
T@ora>select rowid,ora_rowscn,a from t1;
ROWID ORA_ROWSCN A
------------------ ---------- ----------
AAARN5AAGAAAXHcAAA 45387504 1
AAARN5AAGAAAXHcAAB 45387504 2
AAARN5AAGAAAXHcAAC 45387504 3
AAARN5AAGAAAXHcAAD 45387504 4
AAARN5AAGAAAXHcAAE 45387504 5
AAARN5AAGAAAXHcAAF 45387504 6
AAARN5AAGAAAXHcAAG 45387504 7
AAARN5AAGAAAXHcAAH 45387504 8
AAARN5AAGAAAXHcAAI 45387504 9
AAARN5AAGAAAXHcAAJ 45387504 10
10 rows selected.
Elapsed: 00:00:00.20
T@ora>update t1 set a = 0 where a <3;
2 rows updated.
Elapsed: 00:00:00.01
T@ora>commit;
Commit complete.
Elapsed: 00:00:00.00
T@ora>select rowid,ora_rowscn,a from t1;
ROWID ORA_ROWSCN A
------------------ ---------- ----------
AAARN5AAGAAAXHcAAA 45387522 0
AAARN5AAGAAAXHcAAB 45387522 0
AAARN5AAGAAAXHcAAC 45387522 3 《----这里开始后面的记录都没做修改,但是ora_rowscn却改变了
AAARN5AAGAAAXHcAAD 45387522 4
AAARN5AAGAAAXHcAAE 45387522 5
AAARN5AAGAAAXHcAAF 45387522 6
AAARN5AAGAAAXHcAAG 45387522 7
AAARN5AAGAAAXHcAAH 45387522 8
AAARN5AAGAAAXHcAAI 45387522 9
AAARN5AAGAAAXHcAAJ 45387522 10
10 rows selected.
增加ROWDEPENDENCIES
T@ora>create table t2 ROWDEPENDENCIES as select * from t ;
Table created.
Elapsed: 00:00:00.06
T@ora>select rowid,ora_rowscn,a from t2;
ROWID ORA_ROWSCN A
------------------ ---------- ----------
AAARN6AAGAAAXHkAAA 45387561 1
AAARN6AAGAAAXHkAAB 45387561 2
AAARN6AAGAAAXHkAAC 45387561 3
AAARN6AAGAAAXHkAAD 45387561 4
AAARN6AAGAAAXHkAAE 45387561 5
AAARN6AAGAAAXHkAAF 45387561 6
AAARN6AAGAAAXHkAAG 45387561 7
AAARN6AAGAAAXHkAAH 45387561 8
AAARN6AAGAAAXHkAAI 45387561 9
AAARN6AAGAAAXHkAAJ 45387561 10
10 rows selected.
Elapsed: 00:00:00.03
T@ora>update t2 set a = 0 where a <3;
2 rows updated.
Elapsed: 00:00:00.01
T@ora>commit;
Commit complete.
Elapsed: 00:00:00.00
T@ora>select rowid,ora_rowscn,a from t2;
ROWID ORA_ROWSCN A
------------------ ---------- ----------
AAARN6AAGAAAXHkAAA 45387578 0
AAARN6AAGAAAXHkAAB 45387578 0
AAARN6AAGAAAXHkAAC 45387561 3 《--没有修改的scn就不会改变
AAARN6AAGAAAXHkAAD 45387561 4
AAARN6AAGAAAXHkAAE 45387561 5
AAARN6AAGAAAXHkAAF 45387561 6
AAARN6AAGAAAXHkAAG 45387561 7
AAARN6AAGAAAXHkAAH 45387561 8
AAARN6AAGAAAXHkAAI 45387561 9
AAARN6AAGAAAXHkAAJ 45387561 10
存储空间的影响,dump出来就可以看到2个存储结构不一样
普通表
tab 0, row 0, @0x1f1e
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 1] 80
col 1: [ 2] c1 02
2个number类型
tab 0, row 0, @0x1edc
tl: 14 fb: --H-FL-- lb: 0x2 cc: 2
dscn 0x0000.02b48f29
col 0: [ 1] 80
col 1: [ 2] c1 02
多出6个字节记录dscn
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24383181/viewspace-742043/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24383181/viewspace-742043/