Oracle batch update tables via ROWID
In Oracle databases, batch updates using ROWID are an efficient way to update because it locates directly to physical row locations, avoiding the overhead of finding through indexes.
ROWID Basic Concept
ROWID is a unique physical address identifier for each row in an Oracle database, containing the following information:
- Data object number
- Data file number
- Data block number
- Line number
Performance optimization suggestions
- Get the rowid of the current table
- Update on rowid
- Every 5000 rows are updated, commit
DECLARE
maxrows number default 5000;
row_id_table dbms_sql.Urowid_Table;
cursor acnt_first_cur is
SELECT /*+parallel(h,8) */ from pos5g_rw.mstb_order_header h order by ;
begin
open acnt_first_cur;
loop
exit when acnt_first_cur%notfound;
fetch acnt_first_cur bulk collect
into row_id_table limit maxrows;
forall i in 1 .. row_id_table.count
update pos5g_rw.mstb_order_header h set name=null where rowid = row_id_table(i);
commit;
end loop;
end;
/Performance optimization suggestions
- Batch commit: For large amounts of data updates, submit once every 1000-10000 rows processed
- NOLOGGING: Use the NOLOGGING option to reduce redo log generation if allowed
- Parallel processing: Consider using parallel DML (requires ALTER SESSION ENABLE PARALLEL DML)
- Index considerations: Reconstruct or update relevant index statistics after update
Things to note
- ROWID may change after table reorganization, partition movement, etc.
- Function-based indexing is not triggered when updating with ROWID
- In distributed databases, ROWID cannot be used across databases
By using ROWID for batch updates reasonably, the performance of large table updates in Oracle databases can be significantly improved.
This is the article about Oracle batch update table through ROWID. For more related Oracle batch update table content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!