Home » SQL & PL/SQL » SQL & PL/SQL » Returning total pages in pagination
Returning total pages in pagination [message #249317] Wed, 04 July 2007 01:36 Go to next message
kmukesh
Messages: 4
Registered: July 2007
Junior Member
Hi Everyone,

I'm implementing the pagination using following code (see BOLD)...and its working fine. But I also need to return the total number of pages this resultset will occupy. I can use COUNT (*) for that but I think I need to write the same query (the main query) again for using COUNT. (code given below)
Is there some way out so that I can return total number of pages without duplicating the query? (JAVA layer need to show a format Page x of n, so I need to return this 'n')

Thanks everyone


create or replace PROCEDURE PRC_TEST


(
in_U_ID IN NUMBER,
iv_USER_ID IN VARCHAR2,
iv_FIRST_NAME IN VARCHAR2,
iv_LAST_NAME IN VARCHAR2,
in_O_ID IN NUMBER,
in_PG_SZ IN NUMBER, -- page size (How many records to display per page)
in_PG_NO IN NUMBER, -- page number to display
on_TOTAL_PG OUT NUMBER, -- to return total number of pages
ov_RESULTSET OUT SYS_REFCURSOR
)

AS


lv_FROM_REC NUMBER(10);
lv_TO_REC NUMBER(10);

BEGIN


lv_FROM_REC := ( ( ( in_PG_NO - 1 ) * in_PG_SZ ) + 1 ); -- from record no. lv_FROM_REC
lv_TO_REC := ( in_PG_NO * in_PG_SZ); -- till record no. lv_TO_REC





BEGIN

OPEN ov_RESULTSET FOR
SELECT STATUS_VAL , U_ID, USER_ID, FIRST_NAME, LAST_NAME, O_ID, ORG_NAME
FROM
(
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY SU.FIRST_NAME , SU.LAST_NAME , SO.ORG_NAME) RNUM,
SU.STATUS_VAL, SU.U_ID, SU.USER_ID, SU.FIRST_NAME , SU.LAST_NAME , SO.O_ID, SO.ORG_NAME
FROM S_USER SU, S_USER_ORG SUO, S_ORGANIZATION SO
WHERE SU.U_ID = SUO.U_ID
AND SUO.O_ID = SO.O_ID)
WHERE RNUM BETWEEN lv_FROM_REC AND lv_TO_REC ORDER BY FIRST_NAME, LAST_NAME, O_ID, ORG_NAME;



SELECT COUNT(U_ID)
INTO on_TOTAL_PG
FROM
(
SELECT DISTINCT
SU.STATUS_VAL, SU.U_ID, SU.USER_ID, SU.FIRST_NAME , SU.LAST_NAME , SO.O_ID, SO.ORG_NAME
FROM S_USER SU, S_USER_ORG SUO, S_ORGANIZATION SO
WHERE SU.U_ID = SUO.U_ID
AND SUO.O_ID = SO.O_ID
);

END;

END PRC_TEST;
Re: Returning total pages in pagination [message #249336 is a reply to message #249317] Wed, 04 July 2007 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Then there is no way to know the number of rows returned by a query before fetching all the rows.

Finally, this is not because you count the rows (with "select count(*)") that you will get the same number of rows (of any row) in the subsequent real query (unless you lock the table).

Regards
Michel
Re: Returning total pages in pagination [message #249420 is a reply to message #249336] Wed, 04 July 2007 07:18 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I feel that it is utter waste of time, CPU cycles, perfromance to do count(*) to find out number of records in while paginiating the query.

Read this you may understand why?
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:76812348057

In this read this particular comment of Tom
Quote:


I have a similar paging query. But I need to get row count also to show the number of available
records (like page 1-25). User can change page size (the following query shows 30) and sort column
name (the example query used column 1, user can change it to any column).

I used the following query to get my results.

SELECT * FROM ( SELECT Z.*, RANK() OVER (ORDER BY First_column ASC, ROWNUM) AS RNK, MAX(ROWNUM)
OVER (ORDER
BY First_column ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MAX_ROWNUM FROM
(
--My Qyery to get the data
) Z ) WHERE RNK BETWEEN 1 AND 30

Currently it is taking long time when the data is huge. Is my query is right? Or Can you suggest
any better method/query?

Thanks in advance.




Followup April 7, 2003 - 4pm US/Eastern:

yuck, i refuse to tell them exactly how many rows -- since it is wrong the split instant you show
them it. look at google "1 thru 10 of about..."

When your result is huge, it must materialize the ENTIRE RESULT SET. What a waste of resources.
This is one practice that if I could stomp it out would save the world trillions of dollars in
hardware costs. The amount of computing power we use to tell a user "hey, you selected way more
data then you will ever actually look at" is amazing.






Re: Returning total pages in pagination [message #250044 is a reply to message #249317] Sat, 07 July 2007 04:54 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
I've blogged about this idea here. To my mind this requirement is quite silly. For small resultset you can use analytic function count(*) over (blabla).

Gints Plivna
http://www.gplivna.eu
Re: Returning total pages in pagination [message #250407 is a reply to message #250044] Mon, 09 July 2007 23:46 Go to previous messageGo to next message
kmukesh
Messages: 4
Registered: July 2007
Junior Member
Hi Gints,

Thanks for replying. I agree with you that unnecessory requirements should be avoided. Your article is good.

you said...
>> For small resultset you can use analytic function count(*) over (blabla).

I tried this but in my case it is not returning the appropriate data. Anyways thanks.
Re: Returning total pages in pagination [message #250408 is a reply to message #249420] Mon, 09 July 2007 23:49 Go to previous messageGo to next message
kmukesh
Messages: 4
Registered: July 2007
Junior Member
Thanks friend,

your reply was really helpful. Now I take it as -

"There is no way of returning total number of records, which is also NOT a performance overhead."
Re: Returning total pages in pagination [message #250410 is a reply to message #249336] Mon, 09 July 2007 23:51 Go to previous message
kmukesh
Messages: 4
Registered: July 2007
Junior Member
thanks Michel for letting me know the etiquettes of the forum.
Previous Topic: problem in the query
Next Topic: Data not found
Goto Forum:
  


Current Time: Sat Dec 03 12:27:07 CST 2016

Total time taken to generate the page: 0.08016 seconds