The classic problem with storing an object in a database is the distinction “am I creating a new record or updating an existing one?”. What immediately comes to mind is “check if there already is a record present using SELECT”, but that requires an additional query to be run. Also we might run into a race condition if another script attempts the same operation on the same object at the same time. You might be tempted to use something like

 function update() { $this->remove(); $this->store(); }

but this incurs much nastier problems if not done properly; e.g. without using transactions, the store action might fail, effectively killing our record in the database. Besides, the performance impact is even worse than the SELECT solution since the database has to update its history, audit trail, redo logs etc. with two operations instead of one.

Oracle provides a much better solution for this problem with the MERGE INTO clause. Read the rest of this entry »