Re: delete from table

> I altered the indices, but it does not seem to help.
> The sql statement is:
>
> delete from table_t where field_a between 140000 and 140100 and
 field_b=0
>
> where field_a is the PK and field_b has no index on it.
>
> select * from table_t where field_a between 140000 and 140100 and
 field_b=0
>
> works fine.
>

Now I recall that you mentioned that there is a foreign key on the table, and that inserts perform well. Well, I could bet that the problem is the foreign key, since
1) when deleting, Oracle has to check that the constraint is not violated by searching the referencing table for the value(s) you are deleting from the PK, while 2) when inserting, obviously no foreign key check has to be performed.

The fact that you delete the referencing rows before deleting from the table doesn't help, since Oracle can't know that there are no child rows of the parent table rows and so performs the check on the FK.

So, you may try putting an index on the foreign key and, if it's already there, try rebuilding it.

You could run an explain plan for the delete stmt to confirm or disprove this hypothesis.

• Other possible problems (written before I remembered the foreign key ;-) :

If the index get used, the only difference between the select and the delete is that the table and the index get altered by the delete.

Deleting some index entries should cause no problem since the index doesn't get reorganized for deletes; I assume that the indexed PK column is small, too.

Perhaps someone just added a big column (a LONG perhaps) to the table ? If that's the case, during delete the whole rows deleted are copied to the rollback segment, and a lot of redo infos are generated.

The same applies if you have a lot of indexes on the table, especially if you have indexed some really big columns (CHAR(2000), VARCHAR(4000)...) since a copy of the column data is always present in the index entry.

HTH
Alberto

P.S. Write to my e-mail address _too_ if you want to communicate with me, i'm likely not to read usenet for a while.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Oct 29 2000 - 14:13:51 CST