On Thu, 2007-05-31 at 09:14 -0700, Jim Nasby wrote: > I'm currently doing EnterpriseDB training at a well-known > entertainment company. I found out something yesterday that I thought > the community would find interesting... > > In their game (MMORPG) databases, they have fields on all their > tables that indicate whether a record has been deleted or not. I've > seen this done before, typically for data retention reasons. But they > had a daily process that went through each night and physically > deleted the records that had been marked as deleted. > > The reason they weren't actually deleting rows real-time is because > it cost to much in Oracle to do so. My guess is it's because Oracle > has to copy the entire deleted row to the undo log as part of the > delete, which would be pretty costly. I believe it has more to do with the cost of removing each row from the index, which might then require parent uplink updates also, which is a major source of contention. DB2 type 2 indexes deliberately avoid that issue, and opt for an approach that requires an offline utility to clear up afterwards. Sounds like a great plan to me... -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
