Getting total number of records while fetching limited set - Oracle

Question

Hello,

I have the query below that fetches 500 records according to certain criteria. The fact that there are many rows (millions). I want to get total number of records so I can say "display 500 out of .... rows". Can I do this with this query? currently I have separate query to do that but I was wondering if I can do it in same query.

Cheers,

Tam

The Query:

SELECT * FROM APPL_PERF_STATS 
WHERE (GENERIC_METHOD_NAME != 'NULL' AND CURRENT_APPL_ID != 'NULL' AND EVENT_NAME != 'NULL')  
AND ROWNUM <  500 
AND current_appl_id  LIKE '%OrderingGUI%' 
AND event_name  LIKE '%/ccui%' 
AND generic_method_name  LIKE '%com.telus.customermgt.service.CustomerMgtSvc.getCustomer%' AND appl_perf_interval_typ_id = 1440 
AND cover_period_start_ts >= to_date('06-07-2008 11:53','dd-mm-yyyy HH24:mi') 
AND cover_period_start_ts <= to_date('11-08-2009 11:53','dd-mm-yyyy HH24:mi')  
ORDER BY CURRENT_APPL_ID, EVENT_NAME, GENERIC_METHOD_NAME, CREATE_TS

Answer 1

In practice I've found it almost never helpful to have such data - and expensive. Especially if the tables are being written to, your total count is constantly changing and therefor a pretty unreliable number to show the user.

Answer 2

ORDER BY and ROWNUM don't interact the way you think they interact. ROWNUM gets applied first:

SQL> select ename from emp
  2  where rownum < 5
  3  order by ename
  4  /

ENAME
----------
CLARKE
PADFIELD
ROBERTSON
VAN WIJK

SQL> select * from (
  2     select ename from emp
  3     order by ename
  4  )
  5  where rownum < 5
  6  /

ENAME
----------
BILLINGTON
BOEHMER
CAVE
CLARKE

SQL>

Answer 3

In practice, when I am given such a problem I generally select one more than I am willing to display (say 500, so pull 501), and if I reached this amount then tell the user "More than 500 records were returned" which gives the user the ability to refine the query. You could run a count of the query first, then return top n rows, but that will require another trip to the server and depending on query complexity and data volume, may take a long time.

Another method would be to add to a statistics table counts which you can sum up right before you execute this query. The theory is that your statistics table would hold much less data than your primary tables and therefore can be mashed by the server quickly. If you write a process (stored procedures work best) to update these and then return your results.

I'm not sure about your application, or your users. But mine generally either don't care about the total amount of records, or want only the total amount of records and don't want the detail.

Answer 4

The desire to display page 1 of 100 comes from over-exposure to Google. As in other areas what Google does is irrelevant to enterprise IT. Google guesses, but it has the architecture to make its guesses fairly accurate.

If you're using an out-of-the-box RDBMS this is not true of your set-up. You have to do it the hard way, by executing two queries - one to get the count, then one one get the rows. If the query is well indexed, doing the initial count might not be too expensive, but it's still two queries.

Answer 5

In Oracle at least you can do this using analytic functions:

For example:

  select
      count(*) over (partition by null) total_num_items,
      p.*
  from
      APPL_PERF_STATS p
  where
      ...

Note that (as APC mentioned) you'll need to embed the ordered query in a sub-query before using ROWNUM to restrict the output to n lines.

Although this is a method of getting the total number of lines in the returned resultset, in the background Oracle will be counting all the rows. So, if there are "millions of rows" then there will be a performance hit. If the performance hit is excessive, the approach I'd use would be to pre-aggregate the total row count as a separate batch job. You may find materialized views useful if this is the case.

Answer 6

SELECT rn, total_rows, x.OWNER, x.object_name, x.object_type
  FROM (SELECT COUNT (*) OVER (PARTITION BY owner) AS TOTAL_ROWS,
               ROW_NUMBER () OVER (ORDER BY 1) AS rn, uo.*
          FROM all_objects uo
         WHERE owner = 'CSEIS') x
 WHERE rn BETWEEN 6 AND 10

RN  TOTAL_ROWS  OWNER   OBJECT_NAME     OBJECT_TYPE
6   1262        CSEIS   CG$BDS_MODIFICATION_TYPES       TRIGGER
7   1262        CSEIS   CG$AUS_MODIFICATION_TYPES       TRIGGER
8   1262        CSEIS   CG$BDR_MODIFICATION_TYPES       TRIGGER
9   1262        CSEIS   CG$ADS_MODIFICATION_TYPES       TRIGGER
10  1262        CSEIS   CG$BIS_LANGUAGES                TRIGGER