[SOLVED] Rowid not in order when insert into an oracle table

Rowid not in order when insert into an oracle table

first,I create a table

create table TEST
(
  id   VARCHAR2(11),
  name VARCHAR2(11)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

Then I insert ,delete ,insert some data,and watch the result:

truncate table test;
insert into test values (1,1);
commit;
insert into test values (2,1);
commit;
insert into test values (3,1);
commit;
delete test where id = 2;
commit;
insert into test values (4,1);
commit;
insert into test values (5,1);
commit;
insert into test values (6,1);
commit;
delete test where id = 5;
commit;
insert into test values (7,1);
commit;
insert into test values (8,1);
commit;
insert into test values (9,1);
commit;

select t.*, t.rowid from TEST t

then I can see the query result is :

"ID","NAME","ROWID"
"1","1","AAAGXqAAEAAAAP+AAA"
"8","1","AAAGXqAAEAAAAP+AAB"
"3","1","AAAGXqAAEAAAAP+AAC"
"4","1","AAAGXqAAEAAAAP+AAD"
"6","1","AAAGXqAAEAAAAP+AAE"
"7","1","AAAGXqAAEAAAAP+AAF"
"9","1","AAAGXqAAEAAAAP+AAG"

You can see the second line is 8,but I insert 8 after 7,it should appear after 7.Seems the order of the rowid is not same as the order of the insertion. So in my real project,I insert the last data,but it do not appear at last,but jump to the middle space,then my customer can not find the last data. My question is, the order of rowid can not be guaranteed when insert data,right?Or I must add an order field?

Solution

Don't rely on ROWID as it can be changed. For example, if you export schema and then import it back, ROWID might change so - if your code relies on (wrong) assumption that it is constant - code will break and you'll have a problem.

Use something else as an identifier and a value to sort rows in a table. For example, a sequence.