I am using Oracle database. What is the difference between these 3 queries?
select NUM_ROWS from ALL_TABLES where OWNER = 'ME' and TABLE_NAME = 'MY_TABLE'
select count(*) from ME.MY_TABLE
select count(PRIMARY_KEY) from ME.MY_TABLE
For a large table with 85 million rows, the ALL_TABLES query takes 2 milliseconds. count(*) and count(PRIMARY_KEY) take 5 seconds.
Side question: Why does Oracle database not use ALL_TABLES for count(*) or count(PRIMARY_KEY)?
Looking up column NUM_ROWS from catalog view ALL_TABLES is not equivalent to actually counting the rows from the table. The information it contains is based on the last time statistics were collected on the related table (assuming they ever were). The documentation states that it is populated only if you collect statistics on the table with the DBMS_STATS package. Whether this is accurate or not at the time when you run the query (and in which extend), highly depends on your statistic collection strategy and on the way the table is modified.
On the other hand, the two other queries are equivalent and give you the exact number of rows in the table:
select count(*) from ME.MY_TABLE
select count(PRIMARY_KEY) from ME.MY_TABLE
The first query is better written, and probably more efficient that the second one, becase it does not actually need to check if the column is empty or not on each and every row (although Oracle might be smart enough to use the metadata to infer that the PK column is never empty) - and it also works when your table has a multi-column primary key.