| Partitioning a table- "newbie" help [message #228680] |
Tue, 03 April 2007 12:55  |
karaseg
Messages: 14
Registered: January 2007
|
Junior Member |
|
|
OK- sort of a newbie question here- regarding table partitions, but more performance related- which is why I posted it here.
This is a data warehouse, we currently load weekly data, then once a month, do "monthly snapshots". I am less concerned with load performance, than I am query performance, as the whole point of a DW is to make data easier and quicker to access.
My question is- on one of our monthly snapshot tables, we are thinking of partitioning on month/year - each month there are almost 600,000 rows added to the table, so you can see it will grow tremendously just in a year. Most times queries are based on a month at a time (what were the January numbers, what about March, etc...) so I am thinking of creating a partition for each month/year (so Jan 2005, Feb 2005, etc. would each be a separate partition.)
Question 1- does this seem reasonable for partition size, or should I combine say 3 months at a time?
Question 2-
Design of the actual tables was done prior to my coming here, therefore no dates are stored in the table, only a Foreign key that looks to another table that stores the dates.
Basically, It is called the month_dim_key, and we have a month_dim table that stores all the pertinent information.
Therefore all queries that run on this table do a join to this month_dim table to select the month/year of interest.
Right now we have 2 months of data in the monthly snapshot table, so there are 2 distinct month_dim_key values, each has about 600,000 row, so a total of 1.2 million rows.
Here is the actual question:
If I create partitions on this table on the month_dim_key values, will this help query performance?
Since a query will never have in its WHERE clause anything like
WHERE month_dim_key=4842
it will always be something like
WHERE snapshot.month_dim_key=dim.month_dim_key
AND dim.month_end_date = '31-JAN-2006'
So will a partition on the month_dim_key help performance of a query like that? If not, how would I go about setting it up to help performance?
Thanks!
|
|
|
|
| Re: Partitioning a table- "newbie" help [message #228724 is a reply to message #228680] |
Tue, 03 April 2007 22:38  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
600,000 rows per month is nothing. Well,... next to nothing anyway.
Its a little on the small side for a partition, but sometimes convenience outweighs practicality. Monthly partitions are just easier to deal with than quarterly ones, and 600K is not ridiculously small. Stick with monthly.
Using surrogate keys, you should LIST partition the table, not RANGE partition. Also ensure that STAR_TRANSFORMATION_ENABLED initialisation parameter is enabled. In this way, Oracle will re-write join queries to the Month dimension (such as in your example) to use IN sub-queries instead; lookup Star Transformation in the Performance Tuning Manual. Once this transformation is done, Oracle is able to perform list partition-pruning based on the IN subquery. This works well with List-partitions, but I have had very mixed results using range partitioning, which works best on BETWEEN and "=" clauses that use the partition key.
So:
- List-Partition monthly
- STAR_TRANSFORMATION = TRUE
- Bitmap index the Month surrogate key on the snapshot table
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
