What is the fastest way of counting the number of rows in a table? All tables have Primary Keys on them.
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.
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...
No one has followed this question yet.