Re: Select one row

From: fumi <fumi__at_tpts5.seed.net.tw>
Date: Tue, 17 Nov 1998 09:50:02 +0800
Message-ID: <72qkns$9n5$1@news.seed.net.tw>

Nicolas Bronke ¼¶¼g©ó¤å³¹ <72el52$nld$1_at_news01.btx.dtag.de>...
>Properly we have a little misunderstanding.
>You are right, the row is like a sequence.

Here is also a counterexample. It's called "Row Migration". In my example, DB_BLOCK_SIZE=2048, PCT_FREE=10

    create table test (

      id   number,
      text varchar2(200));
    begin
      for i in 1..1000 loop
        insert into test (id) values (i);
      end loop;

    end;
    /
    commit;

This inserted rows would fill more than one db block.

    select id from test where rownum=1;     ID

---

             1

This result is undoubted. Then,

    update test set text=lpad('X', 200, 'X') where id=1;     commit;

The free space of the first block is less than (block size - header size) * pct_free% (i.e. < 200 byte),
there is no room to keep the row in the first block. Oracle would find another block and move the "first" row to the block. And now,

    select id from test where rownum=1;     ID

---

             2

Voila, now the "first" row is ID=2.

The rownum is depended on the way to fetch rows (access by index, full table scan....)
and the rows physically stored in datafile. Received on Mon Nov 16 1998 - 19:50:02 CST