Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: partitioning star schema
Typical "canned" answer is try to partition by a numeric. We partition by a date when we can.
I'm torn between the order of one and two, but that is more personal preference. I generally try to partition by the fields that make joins run best and the ones that make maintainence better if I can.
I would definately try to make the primary partition the one most used in a where clause... make the access fast as you can.
9.2 has some way better partitioning options than 8i has... we have been "just" using those because we are in the process of getting up to speed on our new 9i upgrade.
April
-----Original Message-----
To: Multiple recipients of list ORACLE-L
Sent: 1/10/2003 8:33 AM
Hello,
We are still struggling with partitioning of star schema fact tables. As of yet, we haven't been able to test/compare any of the following scenarios (because we're not yet legal with the partitioning option), so I am posting in the hope that someone with more partitioning experience will comment. This will eventually be implemented on 9.2 on Solaris.
By star schema fact tables, I am referring to tables that consist mostly of surrogate key id fields (used for joining to dimension tables), and numeric fields containing a quantity measure.
The id fields are never directly referenced in WHERE clauses of queries
as
*filter* conditions, but are frequently referenced in join conditions.
The
filter conditions usually reference fields in one of the dimension
tables
joined to be the fact table.
We have developed some (untested) practical guidelines for partitioning.
They are listed from best to worst. These are intended to optimize
querying
(not the incremental loading), and they apply to tables rather than
indexes. (We are creating a similar list for indexes) Here they are:
In many cases, we have to go all the way to #5 before this applies. As I
said,
the fact table id fields are never referenced in WHERE clauses, the
dimension
tables are rarely large enough to be partitioned, and the WHERE filter
conditions
usually apply to a dimension table, so we wind up partitioning by an id
field
frequently used in a join clause. This id field is often a date_id
field,
which is used to join to a dates dimension table, because a date range
is
frequently used as a filter condition in queries.
Questions:
1) Do you agree with the ranking above?
2) Is there any substantial benefit to partitioning a fact table by an
id
field, when the id field is used to join to a non-partitioned dimension
table which is referenced in a filter condition?
Thanks to all who made it this far.
More thanks to any responders.
Most thanks to those with helpful comments.
All-thanked-out, Bill.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: becker.bill_at_marshfieldclinic.org
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies.
Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for any damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: April Wells
INET: awells_at_csedge.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jan 10 2003 - 09:08:55 CST
![]() |
![]() |