Slow query execution in an empty table. (after deleting a large amount of inserts)

I have a table in an oracle database with 15 fields. This table had 3500000 inserts. I deleted them all.

delete
from table

After that, whenever I execute a select st开发者_如何学运维atement

I get a very slow response (7 sec) even though the table is empty. I get a normal response only in the case that I search according to an indexed field.

Why?

As Gritem says, you need to understand high water marks etc

If you do not want to truncate the table now (because fresh data has been inserted), use alter table xyz shrink space documented here for 10g

Tom Kyte has a good explanation of this issue:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:492636200346818072

It should help you understand deletes, truncates, and high watermarks etc.

In sql when you want to completely clear out a table, you should use truncate instead of delete. Let's say you have your table with 3.5 million rows in it and there is an index (unique identifier) on a column of bigint that increments for each row. Truncating the table will completely clear out the table and reset the index to 0. Delete will not clear the index and will continue at 3,500,001 when the next record is inserted. Truncate is also much faster than delete. Read the articles below to understand the differences.

Read this article Read this article that explains the difference between truncate and delete. There are times to use each one. Here is another article from an Oracle point of view.