Oracle NCLOB column update query taking long time

Question

I am trying to copy data from CLOB datatype column to NCLOB datatype column in same table. The table has around 25k records. I am trying to update 5k records at a time. It is a simple update query but taking 4mins for 5k records.

UPDATE TABLE_NAME SET NCLOB_COLUMN=CLOB_COLUMN WHERE ID IN(SELECT ID FROM TABLE_NAME WHERE NCLOB_COLUMN IS NULL FETCH FIRST 5000 ROWS ONLY);

ID is the primary key of the table.

Please help to fine tune the query to reduce the execution time.

Answer 1

You can do it without subquery:

UPDATE TABLE_NAME 
SET NCLOB_COLUMN=CLOB_COLUMN 
WHERE NCLOB_COLUMN IS NULL and rownum<=5000;