rowid의 변화

현재 데이터의 rowid가 dml 작업시 변화가 있을까하여 몇가지 테스트를 해보았다.

* TEMP 테이블 생성

create table temp_rid_test tablespace users nologging as

select 1 no,'aaaaa' depth from dual union all

select 2,'bbbb' from dual union all

select 3,'xxxxx' from dual union all

select 4,'ddddd' from dual union all

select 5,'hhhha' from dual union all

select 6,'agghha' from dual

;

* 현재 rowid

select rowid, no, depth from temp_rid_test;

ROWID	NO	DEPTH
AAA8vIAAMAABYt0AAA	1	aaaaa
AAA8vIAAMAABYt0AAB	2	bbbb
AAA8vIAAMAABYt0AAC	3	xxxxx
AAA8vIAAMAABYt0AAD	4	ddddd
AAA8vIAAMAABYt0AAE	5	hhhha
AAA8vIAAMAABYt0AAF	6	agghha

* update

update temp_rid_test set no=7 where no=1;

commit;

select rowid, no, depth from temp_rid_test;

ROWID	NO	DEPTH
AAA8vIAAMAABYt0AAA	7	aaaaa
AAA8vIAAMAABYt0AAB	2	bbbb
AAA8vIAAMAABYt0AAC	3	xxxxx
AAA8vIAAMAABYt0AAD	4	ddddd
AAA8vIAAMAABYt0AAE	5	hhhha
AAA8vIAAMAABYt0AAF	6	agghha

* delete

delete from temp_rid_test where no=7;

commit;

select rowid, no, depth from temp_rid_test;

ROWID	NO	DEPTH
AAA8vIAAMAABYt0AAB	2	bbbb
AAA8vIAAMAABYt0AAC	3	xxxxx
AAA8vIAAMAABYt0AAD	4	ddddd
AAA8vIAAMAABYt0AAE	5	hhhha
AAA8vIAAMAABYt0AAF	6	agghha

* update(특정로우에 대한 큰용량 byte로 갱신) - 2000byte

alter table temp_rid_test modify depth varchar2(2000);

select lengthb('AAA......생략')

from dual  --1710

;

update temp_rid_test

set depth = 'AAA......생략'

where no = 6

;

commit;

select rowid, no, depth from temp_rid_test;

ROWID	NO	DEPTH
AAA8vIAAMAABYt0AAB	2	bbbb
AAA8vIAAMAABYt0AAC	3	xxxxx
AAA8vIAAMAABYt0AAD	4	ddddd
AAA8vIAAMAABYt0AAE	5	hhhha
AAA8vIAAMAABYt0AAF	6	aaaa....

* update(특정로우에 대한 큰용량 byte로 갱신) - 4000byte

alter table temp_rid_test modify depth varchar2(4000);

select lengthb('AAA......생략')

from dual  --3478

;

update temp_rid_test

set depth = 'AAA......생략'

where no = 6

;

commit;

select rowid, no, depth from temp_rid_test;

ROWID	NO	DEPTH
AAA8vIAAMAABYt0AAB	2	bbbb
AAA8vIAAMAABYt0AAC	3	xxxxx
AAA8vIAAMAABYt0AAD	4	ddddd
AAA8vIAAMAABYt0AAE	5	hhhha
AAA8vIAAMAABYt0AAF	6	aaaa....

* 결론

dml 작업시 rowid의 변화는 없었다.