[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 :twisted:

3 thoughts on “[FUN] How to tune select count(*) from t?”

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>