DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Paging Next, Previous, First, Last

  1. #1
    Join Date
    Apr 2004
    Posts
    20

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    To get the first ten records and intermediate sets you also need an order by.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Apr 2004
    Posts
    20
    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..

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Then they have a bug in their code that they refuse to fix. Take it to management.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Apr 2004
    Posts
    20
    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

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Apr 2004
    Posts
    20
    But how can increase the performance of "order by rowid" the rowid is indexed column? why it's slow? in some tables

    Thanks

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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?

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    "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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width