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

oracle indexing performance response
Stefanos Kargas picture Stefanos Kargas · Dec 10, 2010 · Viewed 12k times · Source

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 statement
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?

Answer

Please delete me picture Please delete me · Dec 10, 2010

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.

Related questions

Force index use in Oracle

I encountered this question in an interview and had no clue how to answer: There is a table which has a index on a column, and you query: select * from table_name where column_having_index="some value"; The query …

Read More
Oracle - Select where field has lowercase characters

I have a table, users, in an Oracle 9.2.0.6 database. Two of the fields are varchar - last_name and first_name. When rows are inserted into this table, the first name and last name fields are supposed to be in …

Read More
Do I need to create indexes on foreign keys on Oracle?

I have a table A and a table B. A has a foreign key to B on B's primary key, B_ID. For some reason (I know there are legitimate reasons) it is not using an index when I join …

Read More