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?

Answer 1

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>