From: | "Cary O'Brien" <cobrien(at)Radix(dot)Net> |
---|---|
To: | mikeo(at)spectrumtelecorp(dot)com (mikeo) |
Cc: | pgsql-general(at)hub(dot)org |
Subject: | Re: Re: [SQL] oracle rownum equivalent? |
Date: | 2000-06-08 12:45:32 |
Message-ID: | 200006081245.IAA05207@saltmine.radix.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | The Hermit Hacker | 2000-06-08 12:49:01 | Re: Postgresql |
Previous Message | Jan Wieck | 2000-06-08 12:21:04 | Re: Composite Types |
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Ansley | 2000-06-08 13:02:18 | RE: ORDER BY in definition of views |
Previous Message | mikeo | 2000-06-08 12:20:10 | Re: Re: [SQL] oracle rownum equivalent? |