Create a unique index on the table - which it should, of course, have anyway - the PK :).

A unique index will have the same number of 'records' as the base table, but occupying much less space - assuming that your table has more than one column now.

The optimizer will generally recognize this fact and count using the smaller index instead of the larger table. *Sometimes*, it does not - I've never investigated why, however.

*If it does not*, then you will want to 'trick' it into using your index. In order to do that, you can add a bogus WHERE clause that uses the field.

For example, most PKs are on a single field populated via a sequence and, therefore, never have a record with a value less than 1.

So, assuming you had a PK on this table and it was a field called TABLE_CASE_ID, the following query would use the index to count the records, thereby saving you a lot of physical reads:

SELECT
---COUNT(*)
FROM
---TABLE_CASE
WHERE
---TABLE_CASE_ID > 0

Of course, your other, and probably simpler, option is to hint the query.

SELECT /*+ INDEX(TABLE_CASE,TABLE_CASE_PK) */
---COUNT(*)
FROM
---TABLE_CASE

Try both - one of them should work :)

Hope this helps,

- Chris