Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query

Posted on 2011-02-23
8
Medium Priority
?
352 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
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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 2000 total points
ID: 34998722
yes
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
Suggested Courses

972 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