Query taking long on table with low record count

Question

I have an Oracle flat table that grew to just below 800MB. The table was truncated and new records are being inserted. Currently there are <40 records in the table but a select * takes >30secs.

Could this be because the table is still so big (~800MB) that it is taking so long?

There are no indexes.

EDIT

I found out from the person that cleared the table that they did a delete instead of a truncate

Answer 1

If the query plan involves a full table scan, Oracle has to read every block from the table up to its high water mark (HWM). If there are 800 MB of blocks below the HWM, it would make perfect sense that it would take 30 seconds to read all that data. The number of blocks that actually have data in them, in this case, is irrelevant.

If you truncated the table, however, you should have reset the HWM unless you specified REUSE STORAGE which is not the default. The fact that the table is still 800 MB implies that you either explicitly had a REUSE STORAGE in your TRUNCATE command or that you didn't actually truncate the table-- perhaps you just did a DELETE to remove all the data, for example. A DELETE will not reset the HWM.