Re: Limit Rows in sql query output
Date: Thu, 19 Jun 2014 05:47:49 -0400
Message-ID: <CAEidWqP8F4eNMyst7qX_iu6gwuOZdrSnYy9rTqbdbA0td7OisQ_at_mail.gmail.com>
Another approach is to use a work table to store rownum and rowid from an initial query and paginate over the work table. I have done this before and it worked very well. I was able to ensure the initial query that populated the work table was only accessing the index and was very quick. The end result was the actual table rows were only accessed once over the lifetime of the search at the expense of some redo/undo. We also stored the session id from the application so the user session could bounce around app servers and not lose the state. The data was cleaned up nightly as it aged out.
I also created a set of views that matched index definitions to help guide developers down the right path. The view was limited to columns in the indexes to ensure they didn't step off into table block fetches which dramatically slowed things down.
I'd be interested in how efficient the 12c feature is, my approach was developed many years ago on 8i.
Another problem I recall is we wanted sorted data but needed realistic limits on the result set. We ordered the sub query and limited it by 10k rows and then limited the outer query by 1k rows. This gave us the sorted top 1k rows of the first 10k rows. The first 10k rows was lightening fast as long as it stayed in the index. Goal was 1s for the first 50 rows returned to the user and I believe this pre-work took around 200ms to complete back when 8ms random reads were acceptable.
Kenny
On Jun 18, 2014 5:32 AM, "Bala Krishna" <krishna000_at_gmail.com> wrote:
> Hi All,
>
> We are intrested in fetching output on rows wise for ex.
>
> in first iteration 1 .. 50
> second iteration 51 .. 100
> Third iteration 101 .. 150 so on .
>
> SELECT * FROM (SELECt m.*,rownum r FROm EM_INFO m ORDER BY DBNAME ASC)
> WHERE r >=1 AND r <=50
>
> or
>
> SELECT * FROM (SELECt m.*,rownum r FROm EM_INFO m ) WHERE r >=1 AND r <=50
> and ORDER BY DBNAME ASC
>
> I've tried with both the above queries but its not displaying in the
> sorted order Can somebody help me pls .
>
> Regards
> Bala
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 19 2014 - 11:47:49 CEST