-
Paging Next, Previous, First, Last
I am trying to do paging (Next, Previous, First, Last) over a table/view using web application.
I have created the following procedure to do so, after reading this thread http://asktom.oracle.com/pls/ask/f?p...D:127412348064 ...
The input of this procedure is SQL Statment (SQLSTRING), Records Per page (pageSize) and the Start Record (intFrom)
for example
to get the first 10 records the input will be like this
intFrom int := 1,
pageSize int :=10,
SQLSTRING := "select * from Emp order by empid"
to get the the last 10 records the input will be like this
intFrom int := 1,
pageSize int :=10,
SQLSTRING := "select * from Emp order by empid desc"
My problem is sometimes the performance of this query is slow when hitting the Last Button. when the query has no order by. Is it because I use rowid? Is there other ways to do this?
for example
to get the first 10 records the input will be like this
intFrom int := 1,
pageSize int :=10,
SQLSTRING := "select * from Emp"
to get the the last 10 records the input will be like this
intFrom int := 1,
pageSize int :=10,
SQLSTRING := "select * from Emp order by rowid desc"
CREATE OR REPLACE PROCEDURE UserPaging
(
intFrom int := 1,
pageSize int :=10,
SQLSTRING varchar2,
p_cursor out SYS_REFCURSOR
)
AS
intTo integer;
strSQLSTRING varchar2(32700);
begin
execute immediate 'alter session enable parallel dml';
intTo := (intFrom + pageSize);
strSQLSTRING := 'select o.* from (select rownum the_row_num, i.* from (' || SQLSTRING || ') i where rownum <' || intTo || ')o where o.the_row_num >=' || intFrom;
open p_cursor for strSQLSTRING;
end UserPaging;
Last edited by Shadis; 03-14-2006 at 10:44 AM.
-
To get the first ten records and intermediate sets you also need an order by.
-
But I do not have control over the inputed sql statment. I can not force the developers that call this procedure to use order by always..
-
Then they have a bug in their code that they refuse to fix. Take it to management.
-
This procedure is called from 500 screens, most screens does not have order by ,
So we do not want to change all the screens. plus i want the procedure to be generic for any sql statment. not forcing the sql to have order by
-
If you don't care which records are identified as being "first" or "last", and are happy to allow the optimizer to decide for you based on how the data is accessed, and therefore don't mind the definition changing over time, then you don't need an order by clause. Otherwise you do.
You ought to be aware that in 10g the presence of ROWNUM <= n can itself change the optimizer mode, and therefore the first ten records returned from the queries:
select * from emp;
... and ...
select * from emp where rownum <= 10;
...can actually be different.
-
But how can increase the performance of "order by rowid" the rowid is indexed column? why it's slow? in some tables
Thanks
-
Where did you read that rowid is an indexed column? It is a pseudo column!
What is the point of ordering by rowid anyways, what will that give you?
-
"order by rowid" is essentially meaningless -- it only gives you records in the order that they were inserted in very specific circumstances that are not practical to maintain.
If you want insert-order to be the determinant of report order then you need to apply a sequence or a timestamp to each insert, then order by that.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|