Previously I've posted
Partitioning Part 1 and
Partitioning Part 2 that describe the overall options when it comes to
partitioning and also looks at archiving as a closely related operation. This
time we'll take a look at partitioned views, a technique that can be applied in
SQL 2000 or SQL 2005.
The essence of partitioned views is that we create many tables from one table
and then union all the tables back together. What we're trying to do is reduce the overall number of records
that we have to seek or scan against to find our matching rows. It's important
to understand that aside from the possible benefits gained from putting the
tables on separate filegroups (increasing the available IO), partitioning will
only help queries where the query can be restricted to one of the tables in the
To get it to work you have to add a check constraint to each table so that
the optimizer can recognize that the rows can only exist in one of the tables. In the examples that follow I'm
going to do it wrong to start with so you can see the results, then we'll fix
our mistakes and see the final correct results.
We'll start by creating two tables
based on the Person.Contact table, each holding about one half of the records
because we're splitting the data based on lastname. We'll also add a clustered index to each
(same clustered index that is on Person.Contact):
select * into ContactAM from Person.contact where lastname <'N' select * into ContactNZ from Person.contact where lastname >='N' create unique clustered index ndxContactID1 on ContactAM(ContactID) create unique clustered index ndxContactID2 on ContactNZ(ContactID)
Now we'll create a view to make the two tables look like one when we write our
create view Contact as select ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, --AdditionalContactInfo] [xml] --deliberately excluded for now rowguid, modifieddate from dbo.ContactAM union select ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, --AdditionalContactInfo] [xml] --deliberately excluded for now rowguid, modifieddate from dbo.ContactNZ
We had to comment out the XML column because no distinct operation is possible on an XML column. The distinct is required because of our UNION.
Now let's run a simple query:
select * from dbo.Contact where lastname = 'green'
We know we're getting the clustered index scan because we have no index on last
name. We're scanning both tables because we didn't apply a check constraint that
would allow the optimizer to limit the query to one table. Let's add the
alter table dbo.ContactAM with check add constraint CKContactAM check ([lastname] like '[a-m]%') alter table dbo.ContactNZ with check add constraint CKContactNZ check ([lastname] like '[n-z]%')
If we repeat the query we get the same query plan that still has two scans. What did we miss? It turns out that check constraints using LIKE don't work for partitioned views, even though they work perfectly well on the table itself. We'll drop the original constraints and add them back in slightly different form (actually the same way we executed our select * into operation):
alter table dbo.ContactAM drop constraint CKContactAM alter table dbo.ContactNZ drop constraint CKContactNZ alter table dbo.ContactAM with check add constraint CKContactAM check ([lastname] < 'N') alter table dbo.ContactNZ with check add constraint CKContactNZ check ([lastname] >= 'N')
Now if we run our query again, we get a much better plan:
We are only scanning the ContactAM table and because we are only referencing one
table, the union operator falls out of the query plan. This reduces the query
cost by roughly 50% because we are only scanning one table.
If we replace UNION with
UNION ALL we can add back the xml column we commented out earlier:
alter view Contact as select ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, AdditionalContactInfo, rowguid, modifieddate from dbo.ContactAM union all select ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, AdditionalContactInfo rowguid, modifieddate from dbo.ContactNZ
go sp_refreshview contact
Our test query will now return the XML column as well, without any change to the
query plan. More importantly, by using UNION ALL we've taken the first step
towards making the view updateable. There are a lot of rules around when and how
you can update a partitioned view without doing extra work (See
http://msdn2.microsoft.com/en-us/library/ms187956.aspx and look at
"Conditions for Modifying Partitioned Views" to see the list). You can work
around most of these by using an instead of trigger on the view.
A few close out notes:
- Partitioned views work on all versions of SQL 2005 and they are not hard
to implement, but use them only after careful thought! Partitioning works
best when you have a very effective partitioning column that will be
included in the majority of your queries
- The example I gave here is meant to just illustrate partitioning,
clearly we could have improved performance by limiting the columns in the
select statement (or the underlying view) and by adding an index on lastname.
- A less used variation of this is the distributed partitioned view, where
the tables are spread across different (federated) servers. This definitely
adds complexity over and above standard partitioning and I suspect that it's
often more effective to just replicate slices of the data.
Next time we'll finish up by looking at partitioned tables, a feature only
available in the Enterprise Edition of SQL 2005.