From oracle-l-bounce@freelists.org Tue May 3 13:55:15 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j43ItFf6021239 for ; Tue, 3 May 2005 13:55:15 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j43ItA4Z021118 for ; Tue, 3 May 2005 13:55:12 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1D8778E44E; Tue, 3 May 2005 12:47:31 -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 09966-10; Tue, 3 May 2005 12:47:31 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6D78118F4EB; Tue, 3 May 2005 12:47:30 -0500 (EST) Message-ID: <0F3AA39C49335840BB3EDD3BD974D6AEC39CE8@dna2k3-cex> From: "Stahlke, Mark" To: oracle-l@freelists.org Subject: RE: Index Clustered Fact Tables in DW Date: Tue, 3 May 2005 11:52:04 -0600 MIME-Version: 1.0 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 19301 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mstahlke@denvernewspaperagency.com Precedence: normal Reply-To: mstahlke@denvernewspaperagency.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=none autolearn=ham version=2.63 Hi Paul, The troublesome fact table has about 11M rows with an average length of 352 stored in 282032 blocks (16K blocksize). This table has 21 indexes. Ouch. The loading isn't done with sqlldr. Our developers have created numerous ETL processes using OWB which generates PL/SQL procedures. Hmmm... IOTs, I'l have to look into that. Thanks, Mark -----Original Message----- From: Paul Drake [mailto:bdbafh@gmail.com] Sent: Tuesday, May 03, 2005 11:33 AM To: mstahlke@denvernewspaperagency.com Subject: Re: Index Clustered Fact Tables in DW >Mark, > >You mentioned the number of rows, but not number of blocks or avg_row_len. >Are you accessing several tables at the same time? >I have seen smallish data warehouses (years ago - was only 120 GB) that used them for static data. >Loading into indexed clusters is slower than into heap tables or IOTs. >Perhaps you might want to consider an IOT, as it still supports direct loading via sqlldr. >Do you need to support secondary indexes on the data? > >Paul -- http://www.freelists.org/webpage/oracle-l