Received: from mail1.radix.net (mail1.radix.net [207.192.128.31]) by hub.org (8.10.1/8.10.1) with ESMTP id e58CjZQ25077 for ; Thu, 8 Jun 2000 08:45:36 -0400 (EDT) Received: from saltmine.radix.net (saltmine.radix.net [207.192.128.40]) by mail1.radix.net (8.9.3/8.9.3) with ESMTP id IAA15345; Thu, 8 Jun 2000 08:45:34 -0400 (EDT) Received: (from cobrien@localhost) by saltmine.radix.net (8.8.7/8.8.7) id IAA05207; Thu, 8 Jun 2000 08:45:33 -0400 (EDT) From: "Cary O'Brien" Message-Id: <200006081245.IAA05207@saltmine.radix.net> Subject: Re: Re: [SQL] oracle rownum equivalent? In-Reply-To: <3.0.1.32.20000608082010.0092bdb0@pop.spectrumtelecorp.com> from mikeo at "Jun 8, 2000 8:20:10 am" To: mikeo@spectrumtelecorp.com (mikeo) Date: Thu, 8 Jun 2000 08:45:32 -0400 (EDT) Cc: pgsql-general@hub.org X-Mailer: ELM [version 2.4ME+ PL48 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200006/233 > 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