[FUN] How to tune select count(*) from t?

A user mentioned one call is faster in test than in prod. And the table has the same size. How could we tune the production to make it quick?


user007@PROD> select count(*) from t;
COUNT(*)
----------
19832501

Elapsed: 00:03:05.00


Let’s try in test :

user007@TEST> select count(*) from t
select count(*) from t
*
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.16


Wow, 00:00:00.16! This is a much quicker response time. Probably the ORA-942 was catched by the application and the user did not realise it…

Next time someone ask you to tune a count(*), just revoke access to the table to optimize the response time 😈