Thread: oracle rownum equivalent?
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
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
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
>
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.)
> 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
> -----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
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
> 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
"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
"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