What is the fastest way of counting the number of rows in a table? All tables have Primary Keys on them.
asked
Fast way to count rows in a table
SELECT count(*)
In Oracle (at least in any version since 7.x), there's no difference between count(1) and count(*), they're exactly the same thing. If the table has a PK (or another unique index on a NOT NULL column) then the optimizer will use that for a fast full index scan, if it doesn't it will do a full table scan.
There really is no point in doing anything other than count(*). You can prove this to yourself by testing with SET AUTOTRACE ON in SQL*Plus.
@thecoop It reads each block, either table block or index leaf block, and counts the number of non-deleted row entries. I don't know for sure, but I suspect the reason that it doesn't maintain a count value is concurrency. Changing that value would need to be atomic, and would become a single point of contention if there were many concurrent DML operations happening on one table.
You can cheat, and put less of a load on the system by querying the statistics instead of counting the actual rows.
Select table_name, owner, NUM_ROWS from ALL_TABLES;
Note that stats can be stale, and stats can be estimated vs computed, so if you need the EXACT number, do the select count(1) method...
