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

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle8i Partitioning

Re: Oracle8i Partitioning

From: Donna <dhoffman0917_at_yahoo.com>
Date: 30 Jan 2002 14:05:12 -0800
Message-ID: <79808a91.0201301405.6d0ffc7@posting.google.com>


Jonathan,
Thanks for the information. To answer some of your questions....

We are utilizing range partitioning and local indexes on version 8.1.7.2.
It is a datamart enviroment with a dimensional modeling design. The fact tables
are partitioned and store 13 weeks of data in one day partitions which comes out
to be 91 (table) partitions with approx. 7 million rows of data in each partition. There are 5-6 local indexes on each fact table with most of them being bitmap indexes and 2 or so b-trees (based on their cardinality). Based on your earlier calculations, that should put us at roughly 637 partitons for one fact table including local indexes(for example). (Thanks that reminder, I had not considered the impact the local indexes make on the partitioning grand total.

The queries access large amounts of data and span across several partitions.
We have been getting pretty good performance so far except for a few complex user queries that we are currently trying to address. But I have to add that this is a newly deployed datamart and currently holds only 14 days of the 91
day requirement.

Because of data extract challenges and the nature of the "extract and load" strategy being used to load the datamart, I have been asked to research "Whether it makes sense to make the partitions smaller?" and "What are the implications of having possibly over 1000 partitions?"

This is why I ask. I welcome any further advice you and anyone else have to offer.

Thanks
Donna Received on Wed Jan 30 2002 - 16:05:12 CST

Original text of this message

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