Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

SQL Query

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
grchap
Asked:
grchap
  • 3
  • 2
  • 2
  • +1
1 Solution
 
sdstuberCommented:
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
 
Aaron ShiloChief Database ArchitectCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Aaron ShiloChief Database ArchitectCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
sdstuberCommented:
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
 
venkotchCommented:
"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
 
sdstuberCommented:
yes
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now