From oracle-l-bounce@freelists.org Wed Jan 12 08:29:41 2005 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id j0CETff09055 for ; Wed, 12 Jan 2005 08:29:41 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id j0CETbn09033 for ; Wed, 12 Jan 2005 08:29:37 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1388572C989; Wed, 12 Jan 2005 09:36:12 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27075-98; Wed, 12 Jan 2005 09:36:11 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3206C72C5DC; Wed, 12 Jan 2005 09:33:28 -0500 (EST) Message-ID: <41E5341F.9010503@allegientsystems.com> Date: Wed, 12 Jan 2005 09:28:47 -0500 From: Mladen Gogala User-Agent: Mozilla Thunderbird 0.9 (X11/20041127) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Eric.Buddelmeijer@elegant.nl Cc: "'Oracle-L Freelists'" Subject: Re: Local partitioned indexes and partition exchange in 8.1.7 References: <20050112141532.VSVX1537.amsfep14-int.chello.nl@wst116> In-Reply-To: <20050112141532.VSVX1537.amsfep14-int.chello.nl@wst116> Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-archive-position: 14679 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mgogala@allegientsystems.com Precedence: normal Reply-To: mgogala@allegientsystems.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Comments inline. Eric Buddelmeijer wrote: >Hi Listers, > >One of our customers has problems with an application that keeps getting >slower. It has a relatively large central table without indexes which is >loaded daily with about 3 million rows. > I wonder why it keeps getting slower? It must be a miracle! Blessed are the cheesemakers! >The table is range partitioned per >day. Looks like a lot of querying is done on the table with the necessary >partition full scan. Approximately 3% of the rows is updated after >insertion, judging from the contents of an audit table. > > We are talking about 3 million rows a day? 3% is approximately 90,000 rows. No indexes? Hmmm, have you considered bigger HW? Much bigger HW? >In oracle 9i I would suggest a local partitioned index on the relevant >columns . And I am thinking of building the index using partition exchange >to minimize down-time. But the customer is still on 8.1.7 and I have no >experience using that version with these features . Are there any known >issues with this approach in 8.1.7 that any of the listers knows of? >Better approaches are always welcome of course. > > You can do it in 8i, it works just fine. No problems there. Partitions and local indexes are 8i concepts. Using table which is being loaded with 3,000,000 daily and has 90,000 of those records update without indexes (indices?) is an outlandish concept. -- Mladen Gogala Oracle DBA -- http://www.freelists.org/webpage/oracle-l