acetone blade bladesystem c3000 cleaning company count county database debug disposal enclosure flashback golf hp interval lome long louisiana mail maintenance manufacturing mpio oil oracle polystyrene purification query record recovery recycler recycling restaurant select server setting software solvent system table tank thinner timestamp togo togolais ufc unit view waste water

Select count() takes too long on Oracle with very few records

You may be faced with following issue - oracle will take a very long time when running select count(*) on table only to return with very small number of records. In my case it took over 14 seconds to return back 4 as number of rows, that's about 3.5 seconds per row! How is this possible?

One simple explanation, as was in my case, was that flashback was turned on. As a result, by running count on table that had 4 records I was actually running count on table with many thousands of deleted records which were retained for flashback availability. In essence I was forcing table scan and count of active records (those that were not labeled "deleted"). Is there anything that can be done to improve the speed with flashback enabled?

Scroll down for additional info labeled How to ensure that torn down data does not interfere

More info: How to ensure that torn down data does not interfere

permanent link to article http://freecash.hogger.net/select_count_takes_too_long_on_oracle_with_very_few_records

There are several options, one of them is to create table with NOLOGGING option, which will turn off flashback. In my case I did not really need that as I was clearing table each time I needed to do computations. However, to clear data completely including flashback information, only TRUNCATE TABLE statement can do it. And to TRUNCATE TABLE, you must be dba, ordinary users do not have rights to truncate data. There isn't an equivalent of GRANT TRUNCATE ON TABLE in oracle for security reasons.