SQL and bit different Partitioned View

Last week I had to play with new old feature – Partitioned View. And I get in trouble. Fortunately only shortly.

For start – some recall – what is Partitioned View. This is technique putting several similar tables to behive as one. Each of tables can have different supporting indexes, they could reside in different (incl readonly) filegroups etc.

The good thing with Partitioned Views is that query against this view containing suitable where-clause will touch only tables needed for producing result. Some conditions allow threat Partitioned View as updatable view – this allow partitioned table looks like “real table” but more flexible and performing.

Short example:

create table T1 (kood int primary key (check kood between 1 and 100, .... )
create table T2 (kood int primary key (check kood between 101 and 200, .... )
create table T3 (kood int primary key (check kood between 201 and 300, .... )
GO
create view TT as
select * from T1
union all
select * from T2
union all
select * from T3
GO

Resulting view TT is actually partitioned view. Query like:

select ... from TT where kood = 107

touch only table T2 and query:

select ... from TT where kood between 170 and 210

touch tables T2 and T3.

Usually the partioning key will be some date period. This allow older rows to be placed into readonly filegroup for example and the queries will perform much better.

This feature I teach and use long time. This presume thar partitipating tables have partition key column and this column have correct and controlled constraint to divide big picture into small, non-overlaping ones.

That mean – I need to have Data Model under my control. But what to do when I haven’t?

I had a following case. Database contain hundred of tables and there is no way to change Data Model. Tables contain data for different departments and years and the name of table point to the department and year. Something like: TabXX2012, TabXX2013, Tabxx2014, TabYY2012, TabYY2013, TabYY2014. For reporting I need some summary view of those tables.

When I create union view like following

create view TabAll
as
select 'XX' as Department, 2012 as Year, * from TabXX2012
union all
select 'XX' as Department, 2013 as Year, * from TabXX2013
union all
select 'XX' as Department, 2014 as Year, * from TabXX2014
union all
select 'YY' as Department, 2012 as Year, * from TabYY2012
union all
select 'YY' as Department, 2013 as Year, * from TabYY2013
union all
select 'YY' as Department, 2014 as Year, * from TabYY2014

and query this view like:


select * from TabAll where Year = 2013

then my query plan shows that query touch all tables. Query optimizer “don’t see”, that Year 2013 contains only in 2 of tables. Now I find solution how to make real “partitioned” view (I use a script to generate that view).

create view TabAll
as
select * from (select 'XX' as Department, 2012 as Year, * from TabXX2012) T where Department = 'XX' and Year = 2012
union all
select * from (select 'XX' as Department, 2013 as Year, * from TabXX2013) T where Department = 'XX' and Year = 2013
union all
select * from (select 'XX' as Department, 2014 as Year, * from TabXX2014) T where Department = 'XX' and Year = 2014
union all
select * from (select 'YY' as Department, 2012 as Year, * from TabYY2012) T where Department = 'YY' and Year = 2012
union all
select * from (select 'YY' as Department, 2013 as Year, * from TabYY2013) T where Department = 'YY' and Year = 2013
union all
select * from (select 'YY' as Department, 2014 as Year, * from TabYY2014) T where Department = 'YY' and Year = 2014

This view works like “classical” partitioned view. Query like

select * from TabAll where Year = 2012

touch only tables TabXX2012 ja TabYY2012. Secret is in where clouse. Query optimizer “see” this as condition and knows how to optimize this execution plan.

I have some other ideas how to use that kind of view – more closer in follow up posts

This entry was posted in Määratlemata, MS SQL Server and tagged . Bookmark the permalink.

SQL and bit different Partitioned View on saanud ühe vastuse

  1. Otis ütles:

    I really like reading a post that can make men and women think.

    Also, thank you for permitting me to comment!

Lisa kommentaar

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Muuda )

Twitter picture

You are commenting using your Twitter account. Log Out / Muuda )

Facebook photo

You are commenting using your Facebook account. Log Out / Muuda )

Google+ photo

You are commenting using your Google+ account. Log Out / Muuda )

Connecting to %s