Solved

SQL Query

Posted on 2011-02-23
8
349 Views
Last Modified: 2012-05-11
Hi there,

I have SQL query that returns 10000 rows.

But I want only 50 rows at a time out of 10000 rows.

That is
1 to 50
51 to 100
101 to 150
..

So, I thought of using like the below

select *
from (
select query that returns 10000 rows
) a
where rownum between &from and &to

Is there a  better way with oracle 11g release 2 database?

Thanks
0
Comment
Question by:grchap
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 34962533
you still pagination with rownum, in 11gR2,  but there is a better way

select *
from (
select query that returns 10000 rows
where rownum <= &to
) a
where rownum >=  &from

0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34962549
hi

why dont you use a cursor and fetch a number of rows you need.

look an my example a packege with a two procedures to fetch rows from the cursor.

after complie use : exec get_group_rows.get_10_rows
to get the first 10 rows
exec get_group_rows.get_next_10_rows
to fetch the next 10 rows.

here is a sample :

create package get_group_rows
is
procedure get_10_rows;
procedure get_next_10_rows;
end;
/


create or replace package body get_group_rows
is
cursor emp_cur is
 select last_name ,employee_id,rownum
 from employees;
 
 type emp_rec is record
 (v_name employees.last_name%type,
  v_id   employees.employee_id%type,
  v_row number);
 
 v_emp_rec emp_rec;
procedure get_10_rows
is
 
begin

  open emp_cur;
  for i in 1..10 loop
  fetch emp_cur into v_emp_rec;
  dbms_output.put_line(v_emp_rec.v_name||' '||v_emp_rec.v_id||' '||v_emp_rec.v_row);
  exit when emp_cur%notfound;
end loop;

end get_10_rows;


procedure get_next_10_rows
is
  begin
  for i in 1..10 loop
  fetch emp_cur into v_emp_rec;
  dbms_output.put_line(v_emp_rec.v_name||' '||v_emp_rec.v_id||' '||v_emp_rec.v_row);
  exit when emp_cur%notfound;
end loop;  
end;
end get_group_rows;

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34963367
you can use the row_number() function:

select t.*
  from (select yourtable.*, row_number() over (order by <somefieldshere> ) rn
            from yourtable
       ) t
 where t.rn between 51 and 100

Open in new window


oracle does a good stop understanding that it will not have to return the full inner query ...
the order by for the row_number() function will need to ensure that the row numbering is consistent between the different executions.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34963604
hi angelIII:

why would he want to do that ??

using rownumber analitical function will cost him dearly in performance.

he can just use an inline subquery and use rownum .
or use a pl/sql block

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34963636
the answer to that is: it depends
and you cannot claim that the row_number() function WILL cost more.
if the ORDER BY is based on a index, it can cost much less than the classical rownum approach.

the explain plan comparing the 2 queries will tell.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34963739
using rownum and row_number "should" be equivalent in terms of performance barring no other criteria.

Using the double nesting with separate rownum filters allows for more efficient querying due to STOPKEY operations.

Using row_number filter won't generate a STOPKEY

(if it can, I've never seen it and welcome an example to illustrate)
0
 
LVL 6

Expert Comment

by:venkotch
ID: 34998608
"rownum > &something" never works because the first row always have value 1, so greather-than cannot be satisfied.
I guess sdstuber wanted to say something like this?

select *
from (
select t.*, rownum AS row_num from t10000 t
 where rownum <= &to
) a
where row_num >=  &from

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34998722
yes
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question