| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Select one row
A copy of this was sent to NBronke_at_t-online.de (Nicolas Bronke)
(if that email address didn't require changing)
On 12 Nov 1998 12:43:14 GMT, you wrote:
>Properly we have a little misunderstanding.
>
>You are right, the row is like a sequence.
>
>What I mean I would like to describe it with a small example (Oracle 7.3x)
>
I too have a small COUNTER example. 7.3 as well. DB_BLOCK_SIZE = 2048 (thats important to my example)
>I created a sample table and filled it like following:
>
>create table x (feld Varchar2(10));
>alter table x add primary key(feld);
>insert into x (feld) values ('DEM');
>insert into x (feld) values ('USD');
>insert into x (feld) values ('ATS');
>insert into x (feld) values ('FRF');
>insert into x (feld) values ('ARA');
>insert into x (feld) values ('GBP');
>insert into x (feld) values ('GRD');
>insert into x (feld) values ('SEK');
>
And I created a table:
SQL> drop table test;
Table dropped.
SQL> create table test ( x int, data varchar2(2000) ); Table created.
and inserted and DELETED some data:
SQL> insert into test values ( 1, rpad('*', 2000, '*' ) ); 1 row created.
SQL> insert into test values ( 2, rpad('*', 2000, '*' ) ); 1 row created.
SQL> delete from test where x = 1;
1 row deleted.
SQL> insert into test values ( 3, rpad('*', 2000, '*' ) ); 1 row created.
>First select: select rownum,feld from x
>returns the value DEM with the rownum 1
>
My first select was:
SQL> select x from test where rownum = 1;
X
3
SQL> select x, rownum from test;
X ROWNUM
---------- ----------
3 1
2 2
[snip]
>
>That is the behaviour of rownum. It won't have wholes in it, but represents
>the order of inserting records. With a where statement, so that you get a
It does NOT in any way shape or form represet the ORDER inserting records -- no way, no how, nada. It is assigned to a row as it passes through the query process -- it has nothing to do with the 'age' of the row -- it has everything to do with the query plan that is used to process a query (it many times does have to do with the position of the row on the disk but that is a side effect of how oracle accesses the data... consider this example that changes the rownum assigned using a very similar select to the above)
SQL> create index test_idx on test(x);
Index created.
SQL> alter table test modify x NOT NULL; Table altered.
SQL> select /*+ index(test,test_idx) */ x, rownum from test;
X ROWNUM
---------- ----------
2 1
3 2
Since the optimizer used an index this time instead of a FULL scan, the rownum is assigned to the rows DIFFERENTLY. x=2 gets rownum 1 since it was the FIRST row to pass through the query. x=3 gets rownum of 2 this time around since it came second.
>part of the table, you get new rownum's. (Btw realy unique are only the
>column rowid)
>
>So that was I would like to tell.
>
>Perhaps it helps. Any comments are welcome.
>Kind regards
>Nicolas Bronke
>
>Thor schrieb in Nachricht <72dc78$861$1_at_news.bctel.net>...
>>What???
>>Rownum is a magic column (iso term for we don't like it!)
>>it assigns a sequential number to the row set returned by the SELECT
>>statement and has absolutely nothing to do with insert order. If it did it
>>would really be breaking the laws of relational calculus!!!
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Nov 12 1998 - 09:39:16 CST
 |
 |