Monday, January 21, 2013

Improving Optim Delete Performance By Looking Up Multiple Keys at Once


Optim delete performance can be enhanced through many different methods (turning off "compare whole row", locking tables, disabling triggers and constraints, ensuring the primary key is indexed, etc).  By far one of the biggest bangs for your Optim buck is asking the database a question one time (can you please find and delete n rows) but having n be a long list of primary key values, rather than just one value.  There are two ways to accomplish this multi-key lookup - Array Deletes and Forced Key Lookup.