Message-ID: <428A3B54.4070902@pobox.com>
Date: Tue, 17 May 2005 15:43:32 -0300
From: Alain <alainm@pobox.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Sql-Postgre <pgsql-sql@postgresql.org>
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
References: 
 <DCD4903594CCF34CAFE200BDF3448D8D5B291A@tshuscodenmbx01.ERF.THOMSON.COM>	
	<20050517170636.GC12405@phlogiston.dyndns.org>
	<428A2E81.2060501@pobox.com>
	<1116353539.26520.5.camel@localhost.localdomain>
In-Reply-To: <1116353539.26520.5.camel@localhost.localdomain>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>>>
>>>No.  What is the purpose of your query?  You could use ORDER BY and
>>>LIMIT..OFFSET to do what you want. I think.
>>
>>The problem is probably speed. I have done a lot of tests, and when 
>>OFFSET gets to a few thousands on a multimega-recs database, it gets 
>>very very slow... 
> 
> 
> is there not a similar loss of speed using ROWNUM on oracle?
> 
> 
>>... Is there any other to work around that?
> 
> 
> if you are ordering by a unique key, you can use the key value
> in a WHERE clause.
> 
> select ... where ukey>? order by ukey limit 100 offset 100;
> 
> (the ? is placeholder for the last value of ukey returned
> from previous select)

I tried that. It does not work in the generic case: 6 MegaRec, telephone 
listing, alphabetical order. The problem is that somewhere there is a 
single user with too many entries (over 1000). I even tried to filter 
the repetitions, but somewhere I get stuck if one guy has too mny 
entries (one for each phone number).

I tried using both the name and the primary key (with a combined index), 
to get faster to the record I want, but I was not sucessfull in building 
a where clause.

I would appreciate any help, in fact this is my primary reason for 
joining this list ;-)

Alain