Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query

Posted on 2011-02-23
8
Medium Priority
?
351 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Industry Leaders: 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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
What we learned in Webroot's webinar on multi-vector protection.
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 …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

721 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