grchap
asked on
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
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
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_nam e||' '||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_nam e||' '||v_emp_rec.v_id||' '||v_emp_rec.v_row);
exit when emp_cur%notfound;
end loop;
end;
end get_group_rows;
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
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
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
exit when emp_cur%notfound;
end loop;
end;
end get_group_rows;
you can use the row_number() function:
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.
select t.*
from (select yourtable.*, row_number() over (order by <somefieldshere> ) rn
from yourtable
) t
where t.rn between 51 and 100
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.
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
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
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.
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.
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)
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)
"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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select *
from (
select query that returns 10000 rows
where rownum <= &to
) a
where rownum >= &from