Solved

SQL Query

Posted on 2011-02-23
8
347 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This video teaches viewers about errors in exception handling.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…

756 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