Hi there,
I don't know a whole lot about partitioned tables&views. But I was on a course last year that spent an hour or so on it - we did a lab to actually try it out - but I haven't used it since. But maybe I'll mention what I remember from that day, in case somehow it means anything to you. (Probably most of this you will already know.)
It seems to me that people get interested in this topic for two situations, both related to performance:
[ol]
[li] They have a large table (tons of rows) that must be kept together for historical reasons, but much of it is archival and only a small portion is needed for current operations.
Say a history of issue transactions from a small parts inventory. Only the most recent ones are of current interest, but sometimes they like to query the entire history too. But when running ordinary queries, the large number of rows in the table slows down processing for even current inquiries.
So the transactions are spread across separate tables, perhaps, say, by Year. or maybe Branch. Then we create views of these partitioned tables. These views are based somehow on the partitioning column (I'm trying to remember here), so that queries can figure out 'which' table(s) they need to go to to retrieve the data. Current inquiries run quicker now because the old historical data has been isolated to other tables.
The views of course keep all this hidden from the users. They are just Selecting on tblIssuesView, unaware that the data is physically split across separate tables. And when we decide to change the underlying partitioning, the views can hide that work, making it transparent to the users.
The partitioning column must be part of the Primary Key (doesn't have to be the whole PK, as I remember.) So in this example, maybe the PK is PartNum+IssueDate. Something like that.
[li]The above scenario can loosely be referred to as 'local partitioning'. But we can take it a step farther and have 'distributed partitioning'. Obviously, the idea here is to get more servers involved in the query: having multiple CPUs crunching the data can result in faster response time if the data splits (i.e. the partitioning) is done correctly. (As usual, there's a cost involve here, and we have to factor in network delays if we are retrieving large result sets from several other servers.)
Again, through the partitioned views, the client applications do not need to know where the data is located, or even what logic was used to split and partition the data.
[/ol]
---------------------------------
That's about all I remember. Don't know if it was worth passing all this on to you. It didn't really sound like this is the kind of thing you need for your situation (did it?)
Those course materials I mentioned are at my office, I could look at them at the first of the week to see if there's anything else worth mentioning.
Hope this helped a bit,
bperry
