Re: Deletes hurt

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