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.
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]
ADD [ID] INT IDENTITY(1,1) PRIMARY KEY
The PRIMARY KEY will automatically created a clustered index, and those counts should come back much faster.
