CODEWITHSUNDEEP
oracle-databaseindexingsql-updatesql-delete
Mathew Linton
Mathew Linton

Reputation: 33

oracle - index rebuild after DML operation

I have a table with primary key on one column and another column with non unique index. Table volumetric is around 2 mil records. No partitions in place.

  1. delete some records (20% of data volume)
  2. update the data on indexed column with non unique (100%)

What should I do after these operations with indexes? Should I rebuild them? When I do the update, should I drop the index\ and recreate it afterwards?

Upvotes: 0

Views: 50

Answers (1)

Paul W
Paul W

Reputation: 11603

The update will not mess up your index, so there's no reason to rebuild. But it will certainly slow that update down considerably. You'd be far better off disabling the index (alter index ... unusable), updating, then rebuilding (alter index ... rebuild) than keeping it in place during an update of 100% of its rows. But if you've already done the update and it did all that index maintenance while doing so, there's no further step you need to take.

As for the deletes, that definitely creates empty space, but as long as your inserts are conventional that space will be reused at some point by future rows so it isn't wasted space. You don't need to do anything. If you use direct path inserts (append), however, those future rows will never reuse that old space so it is wasted. If that's the case, then after a while you'd need to reorg the table with an alter table ... move command to release that space back to the tablespace. We typically avoid mixing deletes and direct path/append inserts on the same table for this very reason.

Upvotes: 1

Related Questions