Partitioned Views

for RuBoard

NOTE

As of this writing (December 2000), SQL Server 2000 produces inefficient execution plans for some of the queries presented in this section. SQL Server 7.0, by contrast, produces good plans with nearly all the examples. Microsoft has been alerted to the problem and promises a fix in SQL Server 2000, Service Pack 1, which is due sometime in the spring of 2001. I demonstrate both types of plans on the assumption that the inefficient plans currently produced by SQL Server 2000 are the result of bugs that you may encounter yourself. If you run into one of these, you may want to check with Microsoft to see whether a patch or service pack has been released that addresses the issue.

Simply put, a partitioned view is a view that unions tables together that serve as partitions (or sections) of a larger set of data. For example, a partitioned view over the activity on a Web site might union together separate tables for each month of the year. Each of these tables would store the activity data for a particular month. By unioning them together, the partitioned view would allow them to be treated as a single table while still keeping their sizes manageable.

There are two types of partitioned views: local partitioned views (LPVs) and distributed partitioned views (DPVs). An LPV is one in which all the underlying tables reside on the same SQL Server instance. A DPV is one in which they live on separate instances. These instances don't have to be on different machines, but usually are. Spreading a DPV across multiple machines helps "scale out" large SQL Server implementations . It can effectively bring the processing power and resources of many machines together to process a single query.

A partitioned view is a normal view object that unions together tables with certain attributes. It is characterized by tables with the same structure being combined to provide a unified view of a set of data, but that are segmented on a clearly defined "partitioning column." This partitioning column is set up using a CHECK constraint. In addition to doing what CHECK constraints always donamely, controlling the type of data a column will acceptthe partitioning column of a local or distributed partitioned view provides a means for the SQL Server query optimizer to be able to determine in which partition a given column value resides as it creates a query plan. This allows it to eliminate searches for the other partitions from the query plan when optimizing a query that includes the partitioning column. This is best understood by way of example (Listing 9-16):

Listing 9-16 A basic partitioned view.

 CREATE TABLE CustomersUS (        CustomerID nchar (5) NOT NULL,        CompanyName nvarchar (40) NOT NULL ,        ContactName nvarchar (30) NULL ,        ContactTitle nvarchar (30) NULL ,        Address nvarchar (60) NULL ,        City nvarchar (15) NULL ,        Region nvarchar (15) NULL ,        PostalCode nvarchar (10) NULL ,        Country nvarchar (15) NOT NULL CHECK (Country='US'),        Phone nvarchar (24) NULL ,        Fax nvarchar (24) NULL,  CONSTRAINT PK_CustUS PRIMARY KEY (Country, CustomerID) ) CREATE TABLE CustomersUK (        CustomerID nchar (5) NOT NULL,        CompanyName nvarchar (40) NOT NULL ,        ContactName nvarchar (30) NULL ,        ContactTitle nvarchar (30) NULL ,        Address nvarchar (60) NULL ,        City nvarchar (15) NULL ,        Region nvarchar (15) NULL ,        PostalCode nvarchar (10) NULL ,        Country nvarchar (15) NOT NULL CHECK (Country='UK'),        Phone nvarchar (24) NULL ,        Fax nvarchar (24) NULL, CONSTRAINT PK_CustUK PRIMARY KEY (Country, CustomerID) ) CREATE TABLE CustomersFrance (        CustomerID nchar (5) NOT NULL,        CompanyName nvarchar (40) NOT NULL ,        ContactName nvarchar (30) NULL ,        ContactTitle nvarchar (30) NULL ,        Address nvarchar (60) NULL ,        City nvarchar (15) NULL ,        Region nvarchar (15) NULL ,        PostalCode nvarchar (10) NULL ,        Country nvarchar (15) NOT NULL CHECK (Country='France'),        Phone nvarchar (24) NULL ,        Fax nvarchar (24) NULL, CONSTRAINT PK_CustFR PRIMARY KEY (Country, CustomerID) ) GO DROP VIEW CustomersV GO CREATE VIEW CustomersV AS SELECT * FROM dbo.CustomersUS UNION ALL SELECT * FROM dbo.CustomersUK UNION ALL SELECT * FROM dbo.CustomersFrance GO 

As you can see, we create three tables to store partitions, or slices, of a customer table. We then union those tables back together using a partitioned view. What's the point? Why not store the data as a single table? There are two advantages to this approach: One, by segmenting the customer data, we keep the tables more manageablethe individual partitions will be a fraction of the size of the entire customer table; two, SQL Server's query optimizer can recognize partitioned views and automatically determine the right underlying table to query based on the filter criteria and the CHECK constraint on the partitioning column. For example, have a look at the execution plan of the following query:

 SELECT * FROM dbo.Customersv WHERE Country='US' 

(Results abridged)

 StmtText ---------------------------------------------------------------------------   SELECT CompanyName=CompanyName FROM dbo.CustomersV WHERE Country=@1     --Compute Scalar(DEFINE:(  CustomersUS.  CompanyName=CustomersUS.CompanyName))     --Clustered Index Scan(OBJECT:(Northwind.dbo.CustomersUS.PK_Cust)) 

Even though we reference the view in our query, the optimizer figures out that the data we're requesting could only reside in one of the view's underlying tables, so it queries that table directly and omits the others from the query plan. It uses the WHERE clause criteria and the partitioning column in each table (its primary key) to make this determination.

There are a number of requirements that a view and its base tables must meet in order for it to be a partitioned view in the first place and for the optimizer to be able to optimize queries against it in this way. The number and significance of these requirements have, in fact, discouraged many users from taking advantage of partitioned views, especially LPVs. Whether you use them is a judgment call you'll have to make. You can read up on the requirements and limitations associated with partitioned views in the Books Online. Regarding the optimizer's ability to use the partitioning column to identify the correct base table to search for data, it's been my experience that the partitioning column should be the leftmost in the primary key. This bears further examination. Consider this partitioned view and query (Listing 9-17):

Listing 9-17 A partitioning column/primary key mismatch.

 CREATE TABLE Orders1996 (        OrderID int PRIMARY KEY NOT NULL ,        CustomerID nchar (5) NULL ,        EmployeeID int NULL ,        OrderDate datetime NOT NULL CHECK (Year(OrderDate)=1996),        OrderYear int NOT NULL CHECK (OrderYear=1996),        RequiredDate datetime NULL ,        ShippedDate datetime NULL ,        ShipVia int NULL ) GO CREATE TABLE Orders1997 (        OrderID int PRIMARY KEY NOT NULL ,        CustomerID nchar (5) NULL ,        EmployeeID int NULL ,        OrderDate datetime NOT NULL CHECK (Year(OrderDate)=1997),        OrderYear int NOT NULL CHECK (OrderYear=1997),        RequiredDate datetime NULL ,        ShippedDate datetime NULL ,        ShipVia int NULL ) GO CREATE TABLE Orders1998 (        OrderID int PRIMARY KEY NOT NULL ,        CustomerID nchar (5) NULL ,        EmployeeID int NULL ,        OrderDate datetime NOT NULL CHECK (Year(OrderDate)=1998),        OrderYear int NOT NULL CHECK (OrderYear=1998),        RequiredDate datetime NULL ,        ShippedDate datetime NULL ,        ShipVia int NULL ) GO CREATE VIEW OrdersV AS SELECT * FROM Orders1996 UNION ALL SELECT * FROM Orders1997 UNION ALL SELECT * FROM Orders1998 GO SELECT * FROM OrdersV WHERE OrderYear=1997 

Will the optimizer be able to narrow its search to just the Orders1997 partition? Let's look at the execution plan:

(Results abridged)

 --------------------------------------------------------------------------- SELECT * FROM OrdersV WHERE OrderYear=@1  --Concatenation   --Filter(WHERE:(STARTUP EXPR(Convert(@1)=1996)))    --Clustered Index Scan(OBJECT:(master.dbo.Orders1996.PK__O1996), WHERE   --Filter(WHERE:(STARTUP EXPR(Convert(@1)=1997)))    --Clustered Index Scan(OBJECT:(master.dbo.Orders1997.PK__O1997), WHERE   --Filter(WHERE:(STARTUP EXPR(Convert(@1)=1998)))     --Clustered Index Scan(OBJECT:(master.dbo.Orders1998.PK__O1998), WHERE 

Even though we're only querying data from one of the partitions, the query plan includes searches for all three of them. Why is that? Because the partitioning column is not part of each table's primary key. Let's add the partitioning column to the primary key and see if that makes any difference (Listing 9-18):

Listing 9-18 A partitioned view/query combo that yields an inefficient query plan.

 CREATE TABLE Orders1996 (        OrderID int NOT NULL ,        CustomerID nchar (5) NULL ,        EmployeeID int NULL ,        OrderDate datetime NOT NULL CHECK (Year(OrderDate)=1996),         OrderYear int NOT NULL DEFAULT 1996 CHECK (OrderYear=1996),        RequiredDate datetime NULL ,        ShippedDate datetime NULL ,        ShipVia int NULL,        CONSTRAINT PK_Orders1996        PRIMARY KEY (OrderYear, OrderID) ) GO CREATE TABLE Orders1997 (        OrderID int NOT NULL ,        CustomerID nchar (5) NULL ,        EmployeeID int NULL ,        OrderDate datetime NOT NULL CHECK (Year(OrderDate)=1997),         OrderYear int NOT NULL DEFAULT 1997 CHECK (OrderYear=1997),        RequiredDate datetime NULL ,        ShippedDate datetime NULL ,        ShipVia int NULL,        CONSTRAINT PK_Orders1997        PRIMARY KEY (OrderYear, OrderID) ) GO CREATE TABLE Orders1998 (        OrderID int NOT NULL ,        CustomerID nchar (5) NULL ,        EmployeeID int NULL ,        OrderDate datetime NOT NULL CHECK (Year(OrderDate)=1998),        OrderYear int NOT NULL DEFAULT 1998 CHECK (OrderYear=1998),        RequiredDate datetime NULL ,        ShippedDate datetime NULL ,        ShipVia int NULL,        CONSTRAINT PK_Orders1998        PRIMARY KEY (OrderYear, OrderID) ) GO CREATE VIEW OrdersV AS SELECT * FROM Orders1996 UNION ALL SELECT * FROM Orders1997 UNION ALL SELECT * FROM Orders1998 GO SELECT * FROM OrdersV WHERE OrderYear=1997 

(Results abridged)

 --------------------------------------------------------------------------- SELECT * FROM OrdersV WHERE OrderYear=@1  --Concatenation   --Filter(WHERE:(STARTUP EXPR(Convert(@1)=1996)))    --Clustered Index Scan(OBJECT:(master.dbo.Orders1996.PK__O1996), WHERE   --Filter(WHERE:(STARTUP EXPR(Convert(@1)=1997)))    --Clustered Index Scan(OBJECT:(master.dbo.Orders1997.PK__O1997), WHERE   --Filter(WHERE:(STARTUP EXPR(Convert(@1)=1998)))     --Clustered Index Scan(OBJECT:(master.dbo.Orders1998.PK__O1998), WHERE 

Once again, the plan includes all three base tables. Why? We've met the requirement of having the partitioning column be part of the primary key, correct? The reason we're still getting an inefficient execution plan for this particular query is that, contrary to what logic might suggest, we need to include all the columns from the primary key in the query's filter criteria. Here's a revision that does this and its resultant execution plan (Listing 9-19):

Listing 9-19 Matching up the partitioning column and primary key solves the problem.

 SELECT * FROM OrdersV WHERE OrderYear=1997 AND OrderID=1000 

(Results abridged)

 StmtText --------------------------------------------------------------------------- SELECT * FROM OrdersV WHERE OrderYear=@1 AND OrderID=@2 -Compute Scalar(DEFINE:(Orders1997.OrderID=Orders1997.OrderID, Orders1997.Cust   -Clustered Index Scan(OBJECT:(Northwind.dbo.Orders1997.PK_Orders1997), 

Now we get an efficient query plan. Not only is the partitioning column included in its host table's primary key, but all the columns in the primary key are included in the query's search criteria. In this case, merely matching query columns to primary key columns left to right wasn't enough. The query had to include all the columns in the primary key or the optimizer produced an inefficient plan. Listing 9-20 is a variation on our partitioned view and query that demonstrates the same quirk:

Listing 9-20 Adding a column to the primary key again yields a poor plan.

 CREATE TABLE Orders1996 (        OrderID int NOT NULL ,        CustomerID nchar (5) NOT NULL ,        EmployeeID int NULL ,        OrderDate datetime NOT NULL CHECK (Year(OrderDate)=1996),        OrderYear int NOT NULL DEFAULT 1996 CHECK (OrderYear=1996),        RequiredDate datetime NULL ,        ShippedDate datetime NULL ,        ShipVia int NULL,        CONSTRAINT PK_Orders1996        PRIMARY KEY (OrderYear, OrderID, CustomerId) ) GO CREATE TABLE Orders1997 (        OrderID int NOT NULL ,        CustomerID nchar (5) NOT NULL ,        EmployeeID int NULL ,        OrderDate datetime NOT NULL CHECK (Year(OrderDate)=1997),        OrderYear int NOT NULL DEFAULT 1997 CHECK (OrderYear=1997),        RequiredDate datetime NULL ,        ShippedDate datetime NULL ,        ShipVia int NULL,        CONSTRAINT PK_Orders1997        PRIMARY KEY (OrderYear, OrderID, CustomerId) ) GO CREATE TABLE Orders1998 (        OrderID int NOT NULL ,        CustomerID nchar (5) NOT NULL ,        EmployeeID int NULL ,        OrderDate datetime NOT NULL CHECK (Year(OrderDate)=1998),        OrderYear int NOT NULL DEFAULT 1998 CHECK (OrderYear=1998),        RequiredDate datetime NULL ,        ShippedDate datetime NULL ,        ShipVia int NULL,        CONSTRAINT PK_Orders1998        PRIMARY KEY (OrderYear, OrderID, CustomerId) ) GO CREATE VIEW OrdersV AS SELECT * FROM Orders1996 UNION ALL SELECT * FROM Orders1997 UNION ALL SELECT * FROM Orders1998 GO SELECT * FROM OrdersV WHERE OrderYear=1997 AND OrderID=1000 

(Results abridged)

 --------------------------------------------------------------------------- SELECT * FROM OrdersV WHERE OrderYear=@1  --Concatenation   --Filter(WHERE:(STARTUP EXPR(Convert(@1)=1996)))    --Clustered Index Scan(OBJECT:(master.dbo.Orders1996.PK__O1996), WHERE   --Filter(WHERE:(STARTUP EXPR(Convert(@1)=1997)))    --Clustered Index Scan(OBJECT:(master.dbo.Orders1997.PK__O1997), WHERE   --Filter(WHERE:(STARTUP EXPR(Convert(@1)=1998)))     --Clustered Index Scan(OBJECT:(master.dbo.Orders1998.PK__O1998), WHERE 

Here, we've added the CustomerID column to each partition's primary key, but we haven't changed the query to include this new column in its search criteria. The result is an inefficient query plan once again. Let's see what happens when we add CustomerID to the query's search criteria (Listing 9-21):

Listing 9-21 Adding CustomerID to the search criteria remedies the problem.

 SELECT * FROM OrdersV WHERE OrderYear=1997 AND OrderID=1000 AND CustomerID = 'AAAAA' StmtText --------------------------------------------------------------------------- SELECT * FROM OrdersV WHERE OrderYear=@1 AND OrderID=@2 AND CustomerID=@3  -Compute Scalar(DEFINE:(Orders1997.OrderID=Orders1997.OrderID, Orders1997.Cus   -Clustered Index Scan(OBJECT:(Northwind.dbo.Orders1997.PK_Orders1997), WHERE 

Once again, we're getting an optimal plan because we've matched the query criteria with the primary key columns of the partitioned view one to one. In this case, we achieved an optimal plan by adding columns to the search criteria. We could just as easily have removed columns from the primary key of each partition.

As the very first example in this section illustrated , it's not a requirement that you must always filter by the entire primary key to get an efficient execution plan when querying a partitioned view. Just be aware that this is sometimes the workaround when you're getting a bad plan with a partitioned view query.

BETWEEN and Partitioned View Queries

In addition to the primary key-partition column relationship, another thing to watch out for with partitioned views is the use of theta (nonequality) operators. Even if the operators in the partitioning CHECK constraint and the query match exactly, the optimizer may be unable to identify the partition correctly to search when theta operators are used. This means that it may have to search all partitions and concatenate the results. For example, consider the partitioned view and query in Listing 9-22:

Listing 9-22 Another problematic partitioned view/query combo.

 CREATE TABLE CustomersUS (        CustomerID nchar (5) NOT NULL,        CompanyName nvarchar (40) NOT NULL ,        ContactName nvarchar (30) NULL ,        ContactTitle nvarchar (30) NULL ,        Address nvarchar (60) NULL ,        City nvarchar (15) NULL ,        Region nvarchar (15) NULL ,        PostalCode nvarchar (10) NULL ,        Country nvarchar (15) NOT NULL CHECK (Country='US'),        Phone nvarchar (24) NULL ,        Fax nvarchar (24) NULL, PRIMARY KEY (Country, CustomerID) ) CREATE TABLE CustomersUK (        CustomerID nchar (5) NOT NULL,        CompanyName nvarchar (40) NOT NULL ,        ContactName nvarchar (30) NULL ,        ContactTitle nvarchar (30) NULL ,        Address nvarchar (60) NULL ,        City nvarchar (15) NULL ,        Region nvarchar (15) NULL ,        PostalCode nvarchar (10) NULL ,        Country nvarchar (15) NOT NULL CHECK (Country='UK'),        Phone nvarchar (24) NULL ,        Fax nvarchar (24) NULL, PRIMARY KEY (Country, CustomerID) ) CREATE TABLE CustomersFrance (        CustomerID nchar (5) NOT NULL,        CompanyName nvarchar (40) NOT NULL ,        ContactName nvarchar (30) NULL ,        ContactTitle nvarchar (30) NULL ,        Address nvarchar (60) NULL ,        City nvarchar (15) NULL ,        Region nvarchar (15) NULL ,        PostalCode nvarchar (10) NULL ,        Country nvarchar (15) NOT NULL CHECK (Country='France'),        Phone nvarchar (24) NULL ,        Fax nvarchar (24) NULL, PRIMARY KEY (Country, CustomerID) ) GO DROP VIEW CustomersV GO CREATE VIEW CustomersV AS SELECT * FROM dbo.CustomersUS UNION ALL SELECT * FROM dbo.CustomersUK UNION ALL SELECT * FROM dbo.CustomersFrance GO SELECT * FROM dbo.CustomersV WHERE Country BETWEEN 'UK' AND 'US' 

Even though the view's partitioning column is each underlying table's primary key, and even though the query filter criteria and the partitioning CHECK constraint match exactly, here's the execution plan we get:

(Results abridged)

 StmtText --------------------------------------------------------------------------- SELECT * FROM dbo.CustomersV WHERE Country>=@1 AND Country<=@2  -Concatenation   -Filter(WHERE:(STARTUP EXPR(Convert(@1)<='US' AND Convert(@2)>='UK')))    -Clustered Index Seek(OBJECT:(Northwind.dbo.CustomersUKUS.PK__CustomersUKUS   -Filter(WHERE:(STARTUP EXPR(Convert(@1)<='France' AND Convert(@2)>='France')    -Clustered Index Seek(OBJECT:(Northwind.dbo.  CustomersFrance.  PK_CustomersFra 

The CustomersFrance base table is being searched even though it's not logically possible that CustomersFrance contains the data we're seeking.

The issue isn't the range of data we're seeking, it's the BETWEEN operator itself and the way that the optimizer optimizes it. Even though the optimizer should be able to determine that the partition values in the CustomersUKUS and CustomersFrance base tables cannot overlap by inspecting their CHECK constraints, it appears to fail in doing so. It searches the CustomersFrance base table for values that logically fall outside those allowed by its CHECK constraint. Listing 9-23 presents a variation on the query that further establishes that the problem has to do with how the optimizer handles the BETWEEN operator in the query itself:

Listing 9-23 Even when searching for a single value, the presence of BETWEEN is giving us grief .

 SELECT * FROM dbo.CustomersV WHERE Country BETWEEN 'UK' AND 'UK' 

(Results abridged)

 StmtText ---------------------------------------------------------------------------  SELECT * FROM dbo.CustomersV WHERE Country>=@1 AND Country<=@2  -Concatenation   -Filter(WHERE:(STARTUP EXPR(Convert(@1)<='US' AND Convert(@2)>='UK')))    -Clustered Index Seek(OBJECT:(Northwind.dbo.CustomersUKUS.PK__CustomersUKUS   -Filter(WHERE:(STARTUP EXPR(Convert(@1)<='France' AND Convert(@2)>='France')    -Clustered Index Seek(OBJECT:(Northwind.dbo.CustomersFrance.PK_CustomersFra 

Here we've changed the query to specify the same value for both terms of the BETWEEN operator. Notice that the query plan is the same as in the earlier query. Why is this? The cause of this is right in front of us. Look at the first line of the query plan (in bold type). The reason the optimizer fails to find the most efficient plan even though simple logic indicates that it should be possible has to do with the way in which the BETWEEN operator is optimized. Early in the optimization process, Column BETWEEN @1 AND @2 is translated to Column >= @1 AND Column <= @2, a compound expression. Later, the optimizer searches for the terms of the compound expression separately and concatenates them. This optimization allows other types of theta operators (e.g., <>, !>, LIKE) to be serviced with indexes, so it's normally a good thing. In this case, it causes a table to be searched that shouldn't be, so it's something to be aware of. When you write code that will query a partitioned view, keep this in mind. If your intent is to avoid touching any more partitions than absolutely necessary, don't use theta operators.

Note that the issue isn't one of search argument (SARG) identification. Both branches of the compound plan use index seeks to locate their data. The problem is one of timing: It relates to when the transformations occur that enable certain normally "non-SARG-able" expressions to be searched. Obviously, they occur early enough in the optimization process that they can negatively affect later phases of the process when partitioned views are involved. Here's the query changed to use an equality test (Listing 9-24):

Listing 9-24 Switching to an equality test fixes the problem.

 SELECT * FROM dbo.CustomersV WHERE Country='US' StmtText --------------------------------------------------------------------------- SELECT * FROM dbo.CustomersV WHERE Country=@1  -Compute Scalar(DEFINE:(CustomersUKUS.CustomerID=CustomersUKUS.CustomerID, Cu   -Clustered Index Seek(OBJECT:(Northwind.dbo.CustomersUKUS.PK__CustomersUKUS) 

Even though we continue to use a BETWEEN operator in the CHECK constraint of the CustomersUKUS base table, the query itself uses an equality test, so we get an efficient plan from the optimizer.

Distributed Partitioned Views

A distributed partitioned view is a partitioned view with base tables that are scattered across a federation (a group ) of autonomous servers. These remote base tables are accessed via linked server definitions. To set up a distributed partitioned view, follow these steps:

1. Create linked server definitions for the servers on which the remote tables you want to access reside.

2. Enable the lazy schema validation server option for each linked server. This option cannot be set using the Linked Server Properties dialog in Enterprise Manager, so you must use sp_serveroption.

3. Create a partitioned view that references the remote partitions using four part names .

4. Repeat these steps on each of the linked servers referenced by the partitioned view. Doing this will allow you to load balance your SQL Server environment by routing users to different versions of the same view.

Here's our earlier partitioned view converted to a distributed partitioned view (Listing 9-25):

Listing 9-25 A distributed partitioned view in the wild.

 CREATE VIEW OrdersV AS SELECT * FROM Orders1996 UNION ALL SELECT * FROM HOMER.Northwind.dbo.Orders1997 UNION ALL SELECT * FROM MARGE.Northwind.dbo.Orders1998 GO SELECT CustomerID FROM OrdersV WHERE OrderYear=1997 AND OrderID=1000 StmtText --------------------------------------------------------------------------- SELECT CustomerID=CustomerID FROM OrdersV WHERE OrderYear=@1 AND OrderID=@2  -Compute Scalar(DEFINE:(HOMER.Northwind.dbo.Orders1997.CustomerID=HOMER.nort   -Remote Query(SOURCE:(HOMER),QUERY:(SELECT Col1024 FROM (SELECT Tbl1003.     "OrderID" Col1023,Tbl1003."CustomerID" Col1024,Tbl1003."OrderYear" Col1027     FROM "northwind"."dbo"."Orders1997" Tbl1003) Qry1031  WHERE Col1023=(1000)  )) 

As you can see, given that we've properly matched up the query's filter criteria with the partitioned view's primary key, the optimizer correctly focuses the search on just one of the partitions. Because that partition resides on a linked server, the optimizer adds a Remote Query step to the plan and sends the query to the remote server. Note that the WHERE clause of the remote query (in bold type) does not include the partition column even though the original query does. This is because it isn't needed. Once the correct partition has been identified, the partition column itself isn't needed to locate the data in the remote table. By virtue of the CHECK constraint, the optimizer knows that Orders1997 only contains data for one partition, 1997.

for RuBoard