December 19, 2011 at 1:41 pm
EAS-917374 (12/19/2011)
There is an advantage to partitioned views which in my opinion is frequently overlooked, and that is the ability to specify multiple "partition keys", by adding multiple constraints to the physical tables. For example - in my data warehouse my largest fact table is partitioned by year. Using a partitioned table I would create a partition on year_id. A query that selects on year_id (WHERE year_id = 1) will scan only the relevant partition. If I do a query like 'WHERE month_id = 11', the optimizer has no idea that month 11 is in year 1 and will scan all partitions.With a partitioned view, I can add constraints for all the time segments and the optimizer will take any/all of them into account. If I add 3 check constraints - form year_id = 1, month_id between 1 and 12, week_id between 1 and 52 the optimizer will know which physical table to scan whether I select by year, month or week.
Beth
Not sure if you noticed but are you aware this thread is about 4 years old? The OP and others aren't discussing it anymore.
As to your point I'd have to think about it, but partitioning at the week_id level and using calculated columns should perform equivalently.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 21, 2011 at 7:54 am
Yes, I did realize it, and no I don't think partitioning on week_id and using calculated columns has the same effect.
December 22, 2011 at 12:57 pm
1) I don't see where anyone asked this question so: WHY do you think you need/want partitioning at all? a few million rows per year is chump change unless you are running on a 5+ year old server with REALLY low memory and CRAPPY IO.
2) Someone said this "SQL 2005 has excellent support for real partitioned tables!!". Actually that support was rather poor and even on SQL 2008R2 continues to suffer from issues (most notably the nasty min/max bug).
3) Partitioning is a VERY advanced and complex topic and if you just read a few blog posts and have a forum thread or two you have a high likelyhood of a suboptimal situation. Get some professional help to guide you on requirements, NEED, implementation, maintenance, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 22, 2011 at 2:02 pm
EAS-917374 (12/19/2011)
There is an advantage to partitioned views which in my opinion is frequently overlooked, and that is the ability to specify multiple "partition keys", by adding multiple constraints to the physical tables. For example - in my data warehouse my largest fact table is partitioned by year. Using a partitioned table I would create a partition on year_id. A query that selects on year_id (WHERE year_id = 1) will scan only the relevant partition. If I do a query like 'WHERE month_id = 11', the optimizer has no idea that month 11 is in year 1 and will scan all partitions.With a partitioned view, I can add constraints for all the time segments and the optimizer will take any/all of them into account. If I add 3 check constraints - form year_id = 1, month_id between 1 and 12, week_id between 1 and 52 the optimizer will know which physical table to scan whether I select by year, month or week.
Well... since SQL Server supports (only) range partitioning I would not partition by year_id but for an usefull date, one year per partition range. That way any query referencing such a date will point to the proper partition. If business requirements really call for analyzing "week 32" or a particular year the usual way to solve it is by having a DIM_DATE table added to the equation, DIM_DATE will translate the two "dates" that fully qualify such "week 32" then we are back to a nice and easy range query.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 23, 2011 at 2:26 pm
I think you are making my point for me. If I partition by date, then I only get the benefit of partition elimination when I query using a date. If I use a partitioned view, and constraints on both the date and id fields, I can get the equivalent of partition elimination using a date or the id in the query.
December 27, 2011 at 12:01 pm
EAS-917374 (12/23/2011)
I think you are making my point for me. If I partition by date, then I only get the benefit of partition elimination when I query using a date. If I use a partitioned view, and constraints on both the date and id fields, I can get the equivalent of partition elimination using a date or the id in the query.
...and where is the benefit? A partitioned view would expose a number of physical tables as a single one and as already pointed by other poster this is usually useful when working with federated databases which apparently it is not the case.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply