Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Select one row
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;
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
![]() |
![]() |