Calculate number of rows in an Oracle query-CodePudding

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?

CodePudding user response：

SELECT   count(*) over () as row_count, mv.*
FROM    my_view mv
where col_a IS NOT NULL

will give you a count but unless you can be sure that performance is not going to be a problem, its generally a bad idea to do this. Because what if the table has 1 billion rows and you only need to show the first (say) 200 rows on screen? We are going to visit all the candidate rows in order to work out that count.

That's why a Google search says "Results 1..20 of ABOUT ..."

CodePudding user response：

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(*).