I am working on a data warehouse that loads incremental information of the business every day. The area of the load and the area of the consultation are separated (different databases on the same machine). Once the load and transformations are done, I must update the target table in the area of consultation.
So in a table T1, there is all the data that have changed, and in the T2 table there is all historical data (in the area of consultations). The update is done via a cursor (via a dblink) that does an insert or update, depending on if the record exists in T2. This PL/SQL is very slow, and we want to speed it up. The number of records to update is too large to do a direct update and then a commit.
Is there any other way to update this table? How can we do an intermediate commit? We working with Oracle 8i on Unix machine.
This would be a great place to use the MERGE command, but unfortunately, it was introduced in Oracle9i so it's not available to you. MERGE, in a single statement, updates existing target table rowsfrom source table rows if the target rows exist, and inserts the source rows into the target if they don't. If you can update to Oracle9i (a good idea in any event, since Oracle8i support is going away soon), that's what I'd suggest.
As far as "intermediate commit", I'm not sure what you mean. You could arrange to update some of the rows in batches and commit after each batch, but it wouldn't speed the transaction and might cause other problems, such as ORA-01555, "snapshot too old" errors.
Dig Deeper on Oracle database performance problems and tuning
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.