I have a table which has 1 million record. When I run

Select count (*) from accounts;

It took around 2 min. But when I deleted 300 thousands record and run the same query it is still taking same time. I read in Google many things. Should I do enable row movement and shrink space or should I gather the statistics. Which is helpful or both are necessary.

  • 1
    If you check the execution plan, is it doing a full table scan? And is this actually a problem - how often do you need to count all rows?
    – Alex Poole
    Jun 27, 2018 at 16:56
  • 2
    To those voting to move the question to the dba exchange: Why do people think that knowing about mass deletes and how to handle it is not for programmers but only for DBAs? In my view this knowledge is necessary to design good applications.
    – wolφi
    Jun 29, 2018 at 19:57

3 Answers 3


You raise a good and important point. I think it is crucial to understand what happens when data is deleted. I've seen many applications, where this was not thought about until the storage was full or slow or the data protection people came round and complained.

If you DELETE rows in Oracle, the space is marked as free, but left as "holes" in the storage. If you INSERT again afterwards, these holes might be filled if the new data fits in.

Some but not by far not all queries are sped up by indexes. Those that are not faster need then to read all data, including holes (so called full table scan). Reading an 8K block from disk that is full of data takes the same amount of data than reading an completely empty block which contains only "holes". Your time measurement points to this scenario.

Regarding statistics: Oracle updates the optimizer statistics at the nightly maintenance window (usually 10pm/22:00). Personally, I'd certainly gather stats after deleting a third of a large table, especially if this is a regular job, for instance in a procedure.

Regarding row movement: I leave it always on for most tables, but I'd judge it as not so important. It hurts only code that stores rowids in tables or longer time in memory and we don't do that. It is necessary if you do a ALTER TABLE xxx MOVE.

Regarding shrink: I wouldn't be bothered to shrink or reorganize for 300k rows. For 10GB+, it may be worth the effort.


Probably shrinking the table or its indexes would reduce the number of blocks that need visiting and therefore speed up the query. At the moment the empty blocks are still there, so a full scan still has to scan them. (I'm guessing, in the absence of an execution plan. Note the execution plan might get the count from scanning an index and never touch the table.)

You might gather fresh statistics after doing this, as the number of rows will have changed significantly so the stats will be stale, and shrinking/coalescing/rebuilding indexes will affect the leaf/block details. As ever with gathering stats, you have to weigh up the benefit of accurate stats against the potential disruption of execution plans changing in unpredictable ways.


Make sure you have a clustered index on that table: ideally just add an INT identity field:

ALTER TABLE [accounts]

The PRIMARY KEY will automatically created a clustered index, and those counts should come back much faster.

  • 2
    The question is tagged oracle. Jun 27, 2018 at 17:24
  • Carp, you're right, sorry. The advice stands, but the code will not work. I vaguely remember that identity type fields have a whole different construct in Oracle. Thanks @WilliamRobertson. Jun 27, 2018 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.