Oracle database - Does count(0) run faster than rownum < 500

Question

I want to optimize a certain query on my database, if results are > 500 then don't run the query.

What is the fastest way from these 2:

1)

list = createNativeQuery("select count(0) from (select * from table where rownum <= 501)")
if(list.size() > 500) throw error;
else runQuery();

or 2)

list = createNativeQuery("select * from table where rownum <= 501")
if(list.size() > 500) throw error;

Are count queries faster in general and optimized to run faster than actually bringing all the rows and counting the size of the result?

Edit: In the 1st case, if count(0) returns size < 500, then I have to re-run the query, and in my case I have a complex where clause. If my sub-query takes ~10s, it will need ~20s in scenario 1). My question was if the subquery needs ~10s, will the select count(0) from sub-query need e.g. ~1s because of indexes and optimizations by oracle?

The second query is returning multiple records while the first returns only a single record. How can you compare them? What about this query: `select count(*) from table where rownum <= 501` ? — Tim Biegeleisen, Nov 04 '15 at 08:21
Don't try to performance tune by formulating rules. Is this even an area where performance is an issue? If so, which is more important, the performance when there are fewer than 500 rows or that when there are more? And which of these row counts are more common? — Damien_The_Unbeliever, Nov 04 '15 at 08:32
I think it is better to let the database count the rows. Getting the records in java just add unnecessary network usage. — Florin Ghita, Nov 04 '15 at 08:40
In the 1st case, you have to re-run the query if count < 500, whereas in the 2nd you execute the query only once and check the size in java. I was curious if count runs e.g. some indexes and performs faster, but it seems that touching each row in the database makes both queries roughly similar. — Alex Arvanitidis, Nov 04 '15 at 08:42
@Alex The two queries are roughly similar, but this is not the end of the story. The network has to handle many records in the `select *` query, but would only need to handle 1 in the `select count(0)` query. — Tim Biegeleisen, Nov 04 '15 at 08:45
Exactly, but in the 1st case, if count(0) returns size < 500, then I have to re-run the query, and in my case I have a complex where clause. If my sub-query takes ~10s, it will need ~20s in scenario 1). My question was if the subquery needs ~10s, will the select count(0) from sub-query need e.g. ~1s because of indexes and optimizations by oracle? — Alex Arvanitidis, Nov 04 '15 at 08:56
I don't even see the point of doing query #1. I would just use `select count(*)` instead, and avoid an unnecessary subquery. — Tim Biegeleisen, Nov 04 '15 at 09:14
@AlexArvanitidis yes, the query with rownum **may** be more faster then just selecting all the rows. Especially when entire set is big(>10000). You just need to test this. But If rownum query gives the same performance as full query it is better to run once, this is clear. — Florin Ghita, Nov 04 '15 at 09:23
despite the timings I think that pulling those 500 rows may cause unneeded traffic in the database / network as you are going to pull those rows every time - if you need them and if you don't need them. I would go with count - ok? - fetch data approach rather than taking the data everytime... The second solution is a little like buying new jacket as the old one lost one button :) — przemo_pl, Nov 04 '15 at 09:45
see this for getting rows in just one step :) http://stackoverflow.com/a/8446667/319875 — Florin Ghita, Nov 04 '15 at 10:07
Seems like the [FIRST_ROWS](https://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm#4942) oracle hint is appropriate for my case as well. — Alex Arvanitidis, Nov 05 '15 at 09:47

score 2 · Accepted Answer · answered Nov 04 '15 at 08:51

the 1st approach is better bcz you don't select rows from table to client see this in SQL Plus:

1st:

SQL> SET AUTOTRACE ON STATISTICS
SQL> select count(0) from (select * from all_tables where rownum <= 501);

  COUNT(0)
----------
       501


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1606  consistent gets
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

2nd: 418 rows selected.

select * from table where rownum <= 501;
Statistics
----------------------------------------------------------
      9  recursive calls
      0  db block gets
    855  consistent gets
      0  physical reads
      0  redo size
  25012  bytes sent via SQL*Net to client
    716  bytes received via SQL*Net from client
     29  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
    418  rows processed

SQL>

draw attention on count of bytes

423 in 1st approach vs 25012 in 2nd

3rd I'm not sure about logic of your project but maybe

select count(*) from all_tables

is the easiest one to get count of rows, and if it's >501 don't run the query atall

SQL> select count(*) from all_tables;

  COUNT(*)
----------
      1711


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       2557  consistent gets
          0  physical reads
        124  redo size
        423  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

score 1 · Answer 2 · edited May 23 '17 at 11:58

Compare the following two queries:

select * from table where rownum <= 501
select count(*) from table where rownum <= 501

In general, both cases will require the database to touch 501 records (or less) from the table. From this point of view, the performance of the two queries should be roughly similar. I would not expect select count(*) to perform worse than select *. However, by calling the first query you are potentially sending massive amounts of data back to your Java code. This by itself runs the risk of being a performance problem. Databases are great at counting records, but Java code is much less so. The second query seems to be more desirable for your use case.

Please view this great SO post for more information.

_both cases will require the database to touch every record in the table_. This is **false**. Both queries will touch maximum 501 records from table. That's why exist rownum. — Florin Ghita, Nov 04 '15 at 08:38

Oracle database - Does count(0) run faster than rownum < 500

2 Answers2