RE: Limit Rows in sql query output
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <k3nnyp@xxxxxxxxx>, <krishna000@xxxxxxxxx>
- Date: Thu, 19 Jun 2014 08:50:47 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Kenny Payton
Sent: Thursday, June 19, 2014 5:48 AM
To: krishna000@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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
Other related posts:
