RE: Limit Rows in sql query output

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 19 Jun 2014 08:50:47 -0400
Message-ID: <05af01cf8bbd$18736810$495a3830$_at_rsiz.com>



IF I understand you correctly, your 10k/1k method would only sample from the correct 10k rows by luck or by selecting only columns in an index ordered to your desire and used by the plan.  

We’d have to see the data, index, query, and plan to know whether your method is reliable.  

There is a method based on selecting an offset downward from the max of a numeric key that is reliable.  

That looks like:  

select c.* from (select b.* from yourtable b, (select max(a.num) high from yourtable a) x where b.num > x.high-10000 order by b.num desc) c where rownum <= 100 order by c.num  

By limiting the inner queries to <keys> and rowid and making an additional outer query select d.* from yourtable b where d.rowid in ( ) c  

Knowing the relative sparseness of “num” is relevant to selecting a reasonable offset. Similar methods work for other subtract-able key types if you have some reasonable way to make a reliably large enough offset subtraction value to exceed the number of rows to be fetched by the final query sufficiently (and ideally with as small an oversample as is practical.) If there is a breakdown between your logic and the actual data received, then you are vulnerable to error. And there may indeed be no reasonable algorithm by which to filter from the index to get “enough” of the correct rows, especially when ties must be considered as well as a legitimate underflow of available rows (regardless of stopkey).  

Needing tortuous constructions like this to get the right answer quickly was a good reason for Oracle to build the stuff Mr. Hall references in his blog.  

As for the worktable solution, that is fine as long as the data being evaluated is static (or you do not want changes after you start analysis to be reflected in the results projected.) Sometimes that is true. Otherwise a materialized view type solution would be required.  

I’m not claiming your method is wrong without seeing it in detail. But I have seen a lot of folks think they had a correct solution similar to what you’ve described that was not reliable.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kenny Payton Sent: Thursday, June 19, 2014 5:48 AM
To: krishna000_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: Limit Rows in sql query output  

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-l
Received on Thu Jun 19 2014 - 14:50:47 CEST

Original text of this message