Simple Update via Oracle Key Preserved Inline View

Sometimes the simple way really is the best…

Recently I looked at a custom SQL script running during a client’s batch processing. They needed to update one column on approximately 300,000 rows out of 9,000,000 on a base table. The new values were coming from another staging table that had the same primary key as the base table. Best-case, the SQL was running for several minutes; worst case (like on the Saturday I took a quick look), it was over 30 minutes. The extra long run was due to bad stats, but the SQL was an overly complex update statement with subqueries, joins, update by rowid, etc — too complex and definitely inefficient. Setting aside the question about why a staging table at all, I suggested using an update to an Oracle inline, key preserved, view.

By using this approach, what used to run in 5-30 minutes on a large AIX server and SAN became less than a minute on my much smaller two-core Linux test server running 11gR2 (with the database on a single SATA drive).
Continue reading Simple Update via Oracle Key Preserved Inline View