Rowid not in order when insert into an oracle table-CodePudding

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?

CodePudding user response：

There is no internal order to a SQL table, in the sense that when Oracle executes your query, it is free to return records in any order whatsoever. The way to impose an order to the result set is to add an ORDER BY clause to your query, e.g.

SELECT t.*, t.rowid
FROM TEST t
ORDER BY t.ID;

CodePudding user response：

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.