Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Post Unanswered: The best way to do paging and get total rows count given a sql statement

    Hi,

    Just got a quick question.... what is the best way to do paging as well as the total rows count given by a sql statement.

    e.g. "select * from emp"
    1. I need to know the total row count for this statement.
    2. I also need to do a 10 rows per page....

    Is there a way to do it in a SINGLE statement for best performance?

    Thanks experts.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    10:11:38 kod:integrationSELECT COUNT(ROWNUM), 
    (
    COUNT(ROWNUM)/10FROM CUSTOMER;

    COUNT(ROWNUM) (COUNT(ROWNUM)/10)
    ------------- ------------------
               
    86                8.6 
    Be more specific or give an example if this is not what you are looking for.

    in sql_plus: set pagesize 10 to get 10 rows per page
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Oct 2003
    Posts
    4
    A good example is to get a list of the post in this forum...

    - It basically tells how many pages (or how many records in total) in this Topic...
    - It also do paging in 20 per page for example....

    How does it accomplish in PHP... Does it do it in a single statement.

    I know there are many ways to get the same results but what is the best way.... Thanks.

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by lamshinglai
    A good example is to get a list of the post in this forum...

    - It basically tells how many pages (or how many records in total) in this Topic...
    - It also do paging in 20 per page for example....

    How does it accomplish in PHP... Does it do it in a single statement.

    I know there are many ways to get the same results but what is the best way.... Thanks.
    Assuming that you want the records ordered by creation time, as reflected by column created_seq:

    create table recs (created_seq int, text varchar2(100));

    insert into recs (created_seq, text) select rownum, 'text of ' || rownum from all_objects
    where rownum <= 20;

    SQL> select * from recs order by created_seq;

    CREATED_SEQ TEXT
    ----------- --------------------
    1 text of 1
    2 text of 2
    3 text of 3
    4 text of 4
    5 text of 5
    6 text of 6
    7 text of 7
    8 text of 8
    9 text of 9
    10 text of 10
    11 text of 11
    12 text of 12
    13 text of 13
    14 text of 14
    15 text of 15
    16 text of 16
    17 text of 17
    18 text of 18
    19 text of 19
    20 text of 20

    The statement may use two analytic functions (let's paginate in bach of 5 instead of 20):

    SQL> select created_seq, text,
    2 trunc ((row_number() over(order by created_seq)-1) / 5) page,
    3 count(*) over() tot_recs
    4 from recs
    5 order by created_seq;



    CREATED_SEQ TEXT PAGE TOT_RECS
    ----------- -------------------- ---------- ----------
    1 text of 1 0 20
    2 text of 2 0 20
    3 text of 3 0 20
    4 text of 4 0 20
    5 text of 5 0 20
    6 text of 6 1 20
    7 text of 7 1 20
    8 text of 8 1 20
    9 text of 9 1 20
    10 text of 10 1 20
    11 text of 11 2 20
    12 text of 12 2 20
    13 text of 13 2 20
    14 text of 14 2 20
    15 text of 15 2 20
    16 text of 16 3 20
    17 text of 17 3 20
    18 text of 18 3 20
    19 text of 19 3 20
    20 text of 20 3 20

    20 rows selected.

    Then, you have the total number of records as the last column, and the page number as the third.

    I don't know whether this is the "best way" - actually there's not a best way, only one that is ok for your unique situation. This is a possible way.

    Obviously, please note that in order to compute the total number of recs, Oracle will need to read all records into the server process memory before sending the first rows - this is independent of the method you choose, of course.

    Your idea of using a single statement is ok, also because Oracle will make a "logical snapshot" of the database at statement creation time - so the records will be always consistent in time, and if the last column says "20", you will fetch exactly 20 records, not 19 or 21 as it would happen if you issued a count(*) statement and then another select * statement, and in the meanwhile someone inserts or deletes a record, and commits.
    Last edited by alberto.dellera; 10-05-03 at 06:25.

Posting Permissions

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