Thread

oracle rownum equivalent?

mikeo <mikeo@spectrumtelecorp.com> — 2000-06-07T12:24:28Z

is there an equivalent to rownum in oracle?

also, where can one find reference to "hidden columns" 
such as OID or, as in oracle, rownum?

thanks,

  mikeo

Re: [SQL] oracle rownum equivalent?

Ed Loehr <eloehr@austin.rr.com> — 2000-06-07T13:29:13Z

mikeo wrote:
> 
> is there an equivalent to rownum in oracle?
> 
> also, where can one find reference to "hidden columns"
> such as OID or, as in oracle, rownum?

oid is the equivalent.  not sure documentation exists for these...

Regards,
Ed Loehr

Re: [SQL] oracle rownum equivalent?

mikeo <mikeo@spectrumtelecorp.com> — 2000-06-07T14:14:23Z

thanks for the response.  oid is equivalent to oracle rowid.  
rownum can be used similar to the limit option of select in postgres
but in oracle it can also be used in a where clause, or as an assigment
in an update statement for instance.  

eg: update ctmaster set bsc_id = mod(rownum,3) +1;

this gives me a way to assign streams to rows in a load balanced manner
on the fly, for example.  i use it in other more involved ways than this
also.  i cannot do this with limit.  i could do this with sequence with
a max value but i'd have to define a sequence each time i wanted to do 
something "on the fly" or for what ifs.  

what i'm also interested in is how to find reference to these type of
pseudo-columns, even just the names of them, if they're listed somewhere.

thanks again,
       mikeo  

At 08:29 AM 6/7/00 -0500, Ed Loehr wrote:
>mikeo wrote:
>> 
>> is there an equivalent to rownum in oracle?
>> 
>> also, where can one find reference to "hidden columns"
>> such as OID or, as in oracle, rownum?
>
>oid is the equivalent.  not sure documentation exists for these...
>
>Regards,
>Ed Loehr
>

Re: [SQL] oracle rownum equivalent?

Ed Loehr <eloehr@austin.rr.com> — 2000-06-07T15:37:14Z

mikeo wrote:
> 
> thanks for the response.  oid is equivalent to oracle rowid.
> rownum can be used similar to the limit option of select in postgres
> but in oracle it can also be used in a where clause, or as an assigment
> in an update statement for instance.
>
> eg: update ctmaster set bsc_id = mod(rownum,3) +1;
> 
> this gives me a way to assign streams to rows in a load balanced manner
> on the fly, for example.  i use it in other more involved ways than this
> also.  i cannot do this with limit.  i could do this with sequence with
> a max value but i'd have to define a sequence each time i wanted to do
> something "on the fly" or for what ifs.
> 
> what i'm also interested in is how to find reference to these type of
> pseudo-columns, even just the names of them, if they're listed somewhere.

I think this might be the list, but you might query pgsql-hackers for
more info.  There was a recent thread involving this..

	ctid
	oid
	xmin (minimum transaction number)
	xmax
	cmin (minimum command number)
	cmax

ctid may be what you're looking for, but I don't understand very well how
these are used.  Maybe someone else can say or you can experiment...

Regards,
Ed Loehr

(PS:  Posting to only one of -general or -sql will almost always be
sufficient.)

Re: Re: [SQL] oracle rownum equivalent?

Cary O'Brien <cobrien@radix.net> — 2000-06-07T22:47:39Z

> thanks for the response.  oid is equivalent to oracle rowid.  

I think there is a fundamentel difference between oid and rownum.
Oid is just a serial number.  Rownum is a long string that tells
oracle where exactly the row is.  So *I think* rownum can be
used for fast lookups, where oid, unless indexed, can't.

Other than that they are the same pretty much.

-- cary

RE: Re: [SQL] oracle rownum equivalent?

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-06-08T04:47:34Z

> -----Original Message-----
> From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
> Behalf Of Ed Loehr
>
> mikeo wrote:
> >
> > thanks for the response.  oid is equivalent to oracle rowid.

IMHO,tid is equivalent to Oracle's rowid.

> > rownum can be used similar to the limit option of select in postgres
> > but in oracle it can also be used in a where clause, or as an assigment
> > in an update statement for instance.
> >
> > eg: update ctmaster set bsc_id = mod(rownum,3) +1;
> >
> > this gives me a way to assign streams to rows in a load balanced manner
> > on the fly, for example.  i use it in other more involved ways than this
> > also.  i cannot do this with limit.  i could do this with sequence with
> > a max value but i'd have to define a sequence each time i wanted to do
> > something "on the fly" or for what ifs.
> >
> > what i'm also interested in is how to find reference to these type of
> > pseudo-columns, even just the names of them, if they're listed
> somewhere.
>
> I think this might be the list, but you might query pgsql-hackers for
> more info.  There was a recent thread involving this..
>
> 	ctid
> 	oid
> 	xmin (minimum transaction number)
> 	xmax
> 	cmin (minimum command number)
> 	cmax
>
> ctid may be what you're looking for, but I don't understand very well how
> these are used.  Maybe someone else can say or you can experiment...
>

AFAIK,there's no pseudo-column like Oracle's rownum which is
dynamically allocated at execution time.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: Re: [SQL] oracle rownum equivalent?

mikeo <mikeo@spectrumtelecorp.com> — 2000-06-08T12:20:10Z

At 06:47 PM 6/7/00 -0400, Cary O'Brien wrote:
>
>> thanks for the response.  oid is equivalent to oracle rowid.  
>
>I think there is a fundamentel difference between oid and rownum.
>Oid is just a serial number.  Rownum is a long string that tells
>oracle where exactly the row is.  So *I think* rownum can be
>used for fast lookups, where oid, unless indexed, can't.
>
>Other than that they are the same pretty much.
>
>-- cary
>

i'll have to respectfully disagree with you on your interpretation of
rownum.  
in oracle, rownum tells only the relative position of a row in a result set.
also, it is an integer value starting at 1 up to nrows retrieved and is used,
for the most part, to limit the result set and not for fast lookups.  it also 
can be used in a DML statement within a function, such as mod(), to aid in
generating a value.  the point here is moot though, as postgres doesn't have 
an equivalent and i'll have to learn to live without that small piece of 
oracle and enjoy what i see as the greater benefits of postgres.  

:)  mikeo

Re: Re: [SQL] oracle rownum equivalent?

Cary O'Brien <cobrien@radix.net> — 2000-06-08T12:45:32Z

> At 06:47 PM 6/7/00 -0400, Cary O'Brien wrote:
> >
> >> thanks for the response.  oid is equivalent to oracle rowid.  
> >
> >I think there is a fundamentel difference between oid and rownum.
> >Oid is just a serial number.  Rownum is a long string that tells
> >oracle where exactly the row is.  So *I think* rownum can be
> >used for fast lookups, where oid, unless indexed, can't.
> >
> >Other than that they are the same pretty much.
> >
> >-- cary
> >
> 
> i'll have to respectfully disagree with you on your interpretation of
> rownum.  
> in oracle, rownum tells only the relative position of a row in a result set.
> also, it is an integer value starting at 1 up to nrows retrieved and is used,
> for the most part, to limit the result set and not for fast lookups.  it also 
> can be used in a DML statement within a function, such as mod(), to aid in
> generating a value.  the point here is moot though, as postgres doesn't have 
> an equivalent and i'll have to learn to live without that small piece of 
> oracle and enjoy what i see as the greater benefits of postgres.  
> 

Arrg.  That's what I get for emailing late at night without checking
(my Oracle book is in the office).  Where I typed rownum I meant
rowid.  You are 100% correct about rownum.  You can use rownum for
things like

	select * from foo where rownum <= 20

Where in postgresql you would say

	select * from foo limit 20

What I meant to say, and failed, was that in Oracle, rowid
"psuedo-column" are hex encoded strings containing the block, row, and
file where the tuple is stored.  So lookup on unindexed rowid *should*
be fast, although this is not spelled out in my ancient Oracle book.
If you want a PostgreSQL lookup using oid to be fast, you need an
index, I think.

PostgreSQL oid is kind of like, but not exactly the same as either
oracle rownum or oracle rowid.

Sorry for the confusion.

-- cary

Re: Re: [SQL] oracle rownum equivalent?

Gunnar R|nning <gunnar@candleweb.no> — 2000-06-08T13:13:23Z

"Cary O'Brien" <cobrien@Radix.Net> writes:

> Arrg.  That's what I get for emailing late at night without checking
> (my Oracle book is in the office).  Where I typed rownum I meant
> rowid.  You are 100% correct about rownum.  You can use rownum for
> things like
> 
> 	select * from foo where rownum <= 20
> 
> Where in postgresql you would say
> 
> 	select * from foo limit 20

Related question: A typical web search interface needs a page based
browsing system where you can list the 10 next matches. Is it possible to
do this in PostgreSQL without retrieving all the rows up to your current
page. I'm thinking of something like :

select * from foo where <some search criteria> and
	rownum >= 30 and rownum < 40 

Or is this where I should look into using cursors to access 
the result set ?

Regards, 

	Gunnar

Re: Re: [SQL] oracle rownum equivalent?

Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-08T14:38:14Z

"Cary O'Brien" <cobrien@Radix.Net> writes:
> What I meant to say, and failed, was that in Oracle, rowid
> "psuedo-column" are hex encoded strings containing the block, row, and
> file where the tuple is stored.  So lookup on unindexed rowid *should*
> be fast, although this is not spelled out in my ancient Oracle book.

Oh, in that case it's pretty much like our CTID column.  For example:

regression=# select ctid,* from int8_tbl where ctid = '(0,3)';
 ctid  |        q1        | q2
-------+------------------+-----
 (0,3) | 4567890123456789 | 123
(1 row)

There is some code in 7.0 that tries to implement this in a fast way,
but I don't know how well it works...

			regards, tom lane