Calculate number of rows in an Oracle query

Is there a simple way to get the number of rows an Oracle SQL query returns?

I tried count in various ways but none worked. This seems to do the trick

SELECT   ROWNUM, mv.*
FROM    my_view mv
where col_a IS NOT NULL
order by ROWNUM desc

but is there something straightforward like nrow in R or .shape in Python?

Solution

I was just wondering what the best practice would be to check the size of the view (...) I don't need to display the entire view, but only need to know the number of rows.

In that case, count(*) seems to be the way to do it. Pure & simple

SELECT count(*)
FROM my_view
WHERE col_a IS NOT NULL;

(You used order by clause; it promises to be slower than query without it.)

Alternatively, if it were a table (not a view), you could use a really fast option - query user_tables:

SQL> SELECT num_rows
  2    FROM user_tables
  3   WHERE table_name = 'EVID';

  NUM_ROWS
----------
    808757

How many rows does that table really have?

SQL> SELECT COUNT (*) FROM evid;

  COUNT(*)
----------
    808761

SQL>

Not exactly the same. Why? Because you should gather statistics:

SQL> EXEC DBMS_STATS.gather_table_stats('SJERV', 'EVID');

PL/SQL procedure successfully completed.

Now those values match:

SQL> SELECT num_rows
  2    FROM user_tables
  3   WHERE table_name = 'EVID';

  NUM_ROWS
----------
    808761

SQL>

It just means that you should regularly gather (schema) statistics - for example, on a daily basis. Then you'd have pretty much close information about number of rows. But, to be sure how many rows you really have, once again - select count(*).