Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 01FBE9FC6FC for ; Fri, 1 Jun 2007 17:09:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 42399-06 for ; Fri, 1 Jun 2007 17:09:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail01.enterprisedb.com (mail01.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id D976B9FC6FB for ; Fri, 1 Jun 2007 17:09:32 -0300 (ADT) Thread-Index: AcekiM3acY8Uh4ckRFKz6m3Uav/bxg== Received: from [192.168.0.4] ([85.211.69.99]) by mail01.enterprisedb.com with Microsoft SMTPSVC(6.0.3790.1830); Fri, 1 Jun 2007 16:09:44 -0400 Content-class: urn:content-classes:message X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826 Importance: normal Subject: Re: Deletes hurt From: "Simon Riggs" To: "Jim Nasby" Cc: In-Reply-To: <734907A8-F30D-4924-8C97-30D2898CA43C@decibel.org> References: <734907A8-F30D-4924-8C97-30D2898CA43C@decibel.org> Content-Type: text/plain; charset="iso-8859-1" Date: Fri, 01 Jun 2007 21:07:46 +0100 Message-ID: <1180728467.26297.267.camel@silverbirch.site> MIME-Version: 1.0 X-Mailer: Evolution 2.6.0 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 01 Jun 2007 20:09:44.0781 (UTC) FILETIME=[CBF6DFD0:01C7A488] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/9 X-Sequence-Number: 25677 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