Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle8i Partitioning
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