Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index Clustered Fact Tables in DW

RE: Index Clustered Fact Tables in DW

From: Stahlke, Mark <mstahlke_at_denvernewspaperagency.com>
Date: Tue, 3 May 2005 11:52:04 -0600
Message-ID: <0F3AA39C49335840BB3EDD3BD974D6AEC39CE8@dna2k3-cex>


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_at_gmail.com] Sent: Tuesday, May 03, 2005 11:33 AM
To: mstahlke_at_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
Received on Tue May 03 2005 - 13:55:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US