Re[2]: Rownum

From: <dgoulet_at_vicr.com>
Date: Wed, 19 Mar 2003 09:48:27 -0500
Message-Id: <24715.322379@fatcity.com>

Darrell,

Pretty damn close. Rowid is the actual absolute address of a row in the database. It use to come in the format of XXXX.XXXX.XXXX up to version 8.0 which was decoded as the file number, block number, and row address within the block. At version 8.0 it took on the format we see today, which has the same meaning, but all of the limitations are gone. Back in pre 8.0 days some duhvelopers use to manipulate rowid's, for what purpose I really don't know. Now if you select the rowid of a row from the table you can use it to simulate a 'for update' clause without the cursor being closed by a commit or rollback. Using the rowid is the fastest way to access a data row.

ROWNUM is assigned to a return record by the RDBMS as the particular record fulfills all of the where clause. Doing a sort or group by thereafter will mess with their order and even cause gaps. Also with few exceptions you can't use rownum in the where clause. The only exception that I know of that works all the time is 'where rownum < x'.

Dick Goulet

____________________Reply Separator____________________

Subject: Re: Rownum
Author: "Darrell Landrum" <dlandrum_at_zalecorp.com> Date: 3/19/2003 5:03 AM

Santosh,

Good question. Actually these 2 are not related. Rowid is an actual attribute assigned to each row of a table and is unique. Rownum is logically assigned to rows returned in a query (nothing to do with the table). It is assigned sequentially as rows are returned (meaning if you can't have a rownum 2 until you've gotten a rownum 1).

Ex.
SQL> select rowid from dual;

ROWID

AAAACsAABAAAAGkAAA

SQL> select rownum from dual;

ROWNUM

SQL> select rownum from dual where rownum=2;

no rows selected

SQL> select rownum from dual where rownum < 2;

ROWNUM

Hope this was helpful,
Darrell

>>> vsantosh_at_psi.soft.net 03/19/03 03:43AM >>>
Hello list,

I wanted to know the concept of Rownum.. Is it similar to Row Id ?

Thanks and Regards,
Santosh

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: dlandrum_at_zalecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Wed Mar 19 2003 - 08:48:27 CST