Re: questions to ROWID

If physical data movement occurrs, ROWIDs can (not always, ORACLE preserve ROWIDs when "row migration" occurrs) change. So, export/import of a table or using "alter table" to move a table to another tablespace will cause ROWIDs to change. In the case of an index organized table, updating the primary key will change the ROWID. In the case of range partitioned tables, updating the partition keys may cause row movement and chnage the ROWID. In the case of hash partitoned table, add a partition may cause row movement and change ROWIDs also. Here are examples related to range and hash partitoned table:

SQL> create table partest_rowid(c1 number(2), c2 varchar2(2))   2 partition by range (c1)
  3 (partition p1 values less than (10),   4 partition p2 values less than (20),   5 partition p3 values less than(maxvalue))   6 enable row movement;

Table created.

SQL>
SQL> insert into partest_rowid values(5,'a');

1 row created.

SQL> insert into partest_rowid values(15,'b');

1 row created.

SQL> insert into partest_rowid values(25,'c');

1 row created.

SQL>
SQL> select rowid, c1, c2 from partest_rowid;

ROWID                      C1 C2
------------------ ---------- --
AAALZkAAJAAAAKmAAA          5 a
AAALZlAAJAAAALGAAA         15 b
AAALZmAAJAAAAPeAAA         25 c

SQL>
SQL> update partest_rowid set c1=35 where c1=5;

1 row updated.

SQL>

• Note that the ORWID of c2='a' has changed. SQL> select rowid, c1, c2 from partest_rowid;

ROWID                      C1 C2
------------------ ---------- --
AAALZlAAJAAAALGAAA         15 b
AAALZmAAJAAAAPeAAA         25 c
AAALZmAAJAAAAPeAAB         35 a

SQL>
SQL> drop table partest_rowid;

Table dropped.

SQL>
SQL> create table partest_rowid(c1 number(2), c2 varchar2(2))   2 partition by hash(c2) partitions 4;

Table created.

SQL>
SQL> insert into partest_rowid values(1,'a');

1 row created.

SQL> insert into partest_rowid values(2,'b');

1 row created.

SQL> insert into partest_rowid values(3,'c');

1 row created.

SQL>
SQL> select rowid, c1, c2 from partest_rowid;

ROWID                      C1 C2
------------------ ---------- --
AAALZoAAJAAAAKmAAA          3 c
AAALZrAAJAAAAPuAAA          1 a
AAALZrAAJAAAAPuAAB          2 b

SQL>
SQL> alter table partest_rowid add partition p5;

Table altered.

SQL>

• Note that the ORWID of c2='c' has changed. SQL> select rowid, c1, c2 from partest_rowid;

ROWID                      C1 C2
------------------ ---------- --
AAALZsAAJAAAAP0AAA          3 c
AAALZrAAJAAAAPuAAA          1 a
AAALZrAAJAAAAPuAAB          2 b

• Jusung Yang

"Andrei Romazanov" <romazanov.andrei_at_gfos.de> wrote in message news:<atcevn$12g5da$1_at_ID-70985.news.dfncis.de>...
> Hi,
>
> The following two questions to ROWID:
>
> 1. can UPDATE change the ROWID?
> 2. can I assume that
>
> SELECT * FROM TAB ORDER BY ROWID
>
> always returns the records in the order of the production?
>
> Thank you in advance
> Andrei
Received on Fri Dec 13 2002 - 13:02:17 CST