Home » SQL & PL/SQL » SQL & PL/SQL » count as a part of the query
count as a part of the query [message #257907] Thu, 09 August 2007 09:24 Go to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
I am working on an application where some of the queries might return records upto a few hundred thousand, for the purpose I am implmenting paging mechanisms on the server side and adding limitations to the DB access layer to limit the size of the result set. Now I have the following query

SELECT * FROM 
( 
  SELECT 
    ROWNUM RN, PORTFOLIO_ID, CUSIP, CCMSECTYPE, CCMSECSUBTYPE, ISSUER_ID, 
    ISSUER, EFF_DURATION, EFF_CONVEXITY, MARKETVALUE, STATVALUE, GAAPVALUE, PAR 
  FROM 
    PORTFOLIO 
  WHERE  
    PORTFOLIO_ID = 'BLAC' 
    AND   RECORD_DATE = TO_DATE('2006-06-30','YYYY-MM-DD')  AND CCMSECTYPE = 'CORP' 
)
WHERE  RN BETWEEN 0 AND 500




this successfully limits the no. of records to 500, i.e., the size of a page.

Now the question is, is there a way to find out the total no. of records, for this query as a part of this query itself. The reason being to calculate the total no. of pages to get the whole resultset.

Yes I can run a separate query for the purpose, but my first preference would be to do this as a part of this query itself, in order to limit the db interactions from the server.
Re: count as a part of the query [message #257910 is a reply to message #257907] Thu, 09 August 2007 09:55 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Add a windowing function to compute the number of rows:

SELECT * FROM 
( 
  SELECT 
    ROWNUM RN, PORTFOLIO_ID, CUSIP, CCMSECTYPE, CCMSECSUBTYPE, ISSUER_ID, 
    ISSUER, EFF_DURATION, EFF_CONVEXITY, MARKETVALUE, STATVALUE, GAAPVALUE, PAR, 
    count(1) over (partition by 1) as tot_rows
  FROM 
    PORTFOLIO 
  WHERE  
    PORTFOLIO_ID = 'BLAC' 
    AND   RECORD_DATE = TO_DATE('2006-06-30','YYYY-MM-DD')  AND CCMSECTYPE = 'CORP' 
)
WHERE  RN BETWEEN 0 AND 500

[Updated on: Thu, 09 August 2007 10:19] by Moderator

Report message to a moderator

Re: count as a part of the query [message #257911 is a reply to message #257907] Thu, 09 August 2007 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you limit your output to 500 rows, Oracle knows this limit so it executes a cheap query.
If you want it to give you also the total count, then it has to get tthe whole result set this will be more expensive.
Moreover, the next time you will select to get the next page, the total count may have been changed, so your first total is useless.
Conclusion: don't try to get the total and you will be faster.

Regards
Michel
Re: count as a part of the query [message #257950 is a reply to message #257907] Thu, 09 August 2007 12:12 Go to previous messageGo to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
Thanks a ton.
Re: count as a part of the query [message #258544 is a reply to message #257950] Mon, 13 August 2007 00:56 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you don't use an order by in the inner query, why use the nested query?
Why not simply select.. where rownum < 501 ?
(Or < 502, so you know there is a next page to be shown)
Previous Topic: extracting data from the field
Next Topic: Reg:To comeout from the inner blocks
Goto Forum:
  


Current Time: Sun Dec 11 02:29:14 CST 2016

Total time taken to generate the page: 0.11401 seconds