plsql - Deletion is slow in oracle DB

Question

We have a table that doesn't have much data. The table has 3 partitions and we are deleting data in one partition only. delete from table AB partition(A) where id=value; here id has an index also but still delete is slow. The datatype of id is varchar2 and the value is number.

Please help me to understand why the delete statement is slow.

question from:https://stackoverflow.com/questions/66048564/deletion-is-slow-in-oracle-db

Answer 1

I don't think the index has much use in this case. It has to evaluate every single row in the partition to see if it matches id=value. Typically this will be a full table scan and no index will be used. It totally depends on the number of rows in the partition how long it will take. But maybe i did not understand the question properly. I presumed "value" is a column in the same table, like ID.