Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29

    Unanswered: performance issue with rowid

    Hi,

    I have an query which is done for pagination based on rownum

    select * from ( select row_.*, rownum rownum_ from (.. my query ..) ASC) row_ where rownum <= 30) where rownum_ > 20

    Actually this pagination query is generated by hibernate ORM java tool.

    There is an issue we are getting some records are repeating in multiple pages.
    to fix this issue we added order by rowid to my query. But this is hitting performance by 2 secs in 6 lacs records in table.

    can any suggestions to fix the pagination issue with out performance hit.

    Regards,
    Charan

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    ROWID is an odd thing to order by. If the displayed order is of no importance, try ordering by an indexed column such as the primary key of the main table. This might avoid a SORT operation.

  3. #3
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    I was already sorting on column (which is not unique values).

    if i do with primary keys it is also hitting performance, than the row id.

    thats why i used row id.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, fair enough. Well your issue is really one of tuning the underlying query, not with the pagination as such. Have you used autotrace to see what it is doing?

    Since you are displaying a pagination result set, you are more interested in getting some rows back early rather than getting the whole result set quickly, so you could try adding a FIRST_ROWS hint.

  5. #5
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    Thanks for you reply

    But i can't do in my application and test with hints we write HQL queries which does not support the hints of oracle can you suggest me any alternative solution as such doing with normal quering.

Tags for this Thread

Posting Permissions

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