Oracle - Does deleting a large number of rows result in similar index contention due to inserting a large number of rows?

user_1391717 0
Viewed 211

In Oracle RDBMS, if you use multiple threads to insert a very large amount of data that affects a single index segment (i.e., a regular index on a non-partitioned table, or a local index on a partitioned table), the inserts will become slow due to contention on the index. Each thread is competing for latches and locks on the index, which is the root cause of the performance problem. This can be resolved by partitioning the table and running one thread per partition that inserts a subset of the very large data.

Does deleting a very large amount of data have the same problem? I would think that a delete also requires locking a subset of the index, which would block other threads that are inserting/deleting on that subset. However I'm not clear if the degree of locking while performing a delete is similar to the degree of locking while performing an insert. Perhaps the amount of locking is much smaller, or the time required to lock is smaller, and therefore the contention would also be much smaller.

Maybe there are other cases to consider: perhaps deletes block other deletes, but maybe deletes do not block other inserts, because most likely, a parallel delete and insert would not be working on the same data blocks.

Any references would be great.

oracle12c oracle19c oracle
2 Answers

You can do: 1.

  • Start cleaning all indexes at the beginning, just drop them.
  • Execute your threads.
  • When the process is finished you might create the index, depending on if it is partitioned or not, you might create it as LOCAL. Drop and Create do the same action as an update. If you want to insert data with indexes, that will cause waste a lot of time. W Indexes always must be cleaned before inserting the data.
  • Create partitions into your table.
  • The reference is to create the most important fields to your partition.
user_128415580

Deleting a large amount of rows is a surprisingly costly operation in Oracle.

Above a certain threshold, it is even recommended to create a new table with the remaining rows, swap names and drop the old table.

As Vahram said, given a certain amount, some people switch off the index (ALTER INDEX xxx UNUSABLE), delete the rows and rebuild the index (ALTER INDEX xxx REBUILD).

Regarding contention, I'd guess you'll not run into the same contention as with large inserts (as there will be no index block splits), but I am not sure.

user_18640290
Related