Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
2  
The person that cleared out the table confirmed they only did a delete instead of a truncate. We shrunk the table and performance is back to what is expected. –  Pieter van Niekerk Jul 23 '12 at 14:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.