全文

在Microsoft SQL Server 2000数据仓库中使用分区

2002-07-22 13:30作者：出处：Microsoft责任编辑：

　　分区设计概述

　　SQL Server 数据库中的分区表可以使用可更新或可查询（不可更新）的分区视图。在这两种情况下，表分区都是由每个分区都包含正确数据的 CHECK 约束来创建的。一个可更新的分区视图支持对视图进行 INSERT (或 UPDATE 或 DELETE）操作，并将操作推入至正确的基础表。这很有益处，但数据仓库应用程序通常需要进行批量加载，而这是无法通过视图执行的。下表总结了可更新和可查询分区视图的要求、优点和缺点。

要求	优点	缺点
可更新的分区视图
CHECK 约束强制使用的分区键主键的分区键部分无其他数据库限制的分区键部分在成员表上定义的 UNION ALL 视图	查询性能：查询计划仅包括解析相关查询所需的成员表。维护应用程序的简易性：数据可以被加载至 UNION ALL 视图，然后插入合适的成员表中	加载性能：通过视图加载数据的速度太慢，以至这种方式对大多数的数据仓库应用程序来说是不实用的。灵活性：数据库设计对分区键可能要求额外的约束。
可查询的分区视图
CHECK 约束强制使用的分区键在成员表上定义的 UNION ALL 视图	查询性能：查询计划仅包括解析查询所必要的成员表。加载性能：可高效地直接将数据批量加载至成员表。存储：尽管推荐声明主键并在主键上创建索引的做法，但分区视图不要求主键索引。	视图最多可有 256 个成员表。必须创建维护应用程序来管理分区和加载。

　　Microsoft 建议的做法是定义主键，并将事实表设计为本地（单个服务器上）的分区联合视图。大多数情况下，该定义会产生可更新的分区视图，但数据仓库维护应用程序应设计为直接将大多数数据批量加载至成员表（而不是通过视图进行）。

　　语法示例

　　以下代码示例用来说明定义成员表和联合视图以及将数据插入视图的语法：

-- 创建 1999 年事实表
CREATE TABLE [dbo].[sales_fact_19990101] (
[date_key] [int] NOT NULL
CHECK ([date_key] BETWEEN 19990101 AND 19991231),
[product_key] [int] NOT NULL ,
[customer_key] [int] NOT NULL ,
[promotion_key] [int] NOT NULL ,
[store_key] [int] NOT NULL ,
[store_sales] [money] NULL ,
[store_cost] [money] NULL ,
[unit_sales] [float] NULL
)
ALTER TABLE [sales_fact_19990101]
ADD PRIMARY KEY (
[date_key], [product_key], [customer_key], [promotion_key], [store_key])
;
-- 创建 2000 年事实表
CREATE TABLE [dbo].[sales_fact_20000101] (
[date_key] [int] NOT NULL
CHECK ([date_key] BETWEEN 20000101 AND 20001231),
[product_key] [int] NOT NULL ,
[customer_key] [int] NOT NULL ,
[promotion_key] [int] NOT NULL ,
[store_key] [int] NOT NULL ,
[store_sales] [money] NULL ,
[store_cost] [money] NULL ,
[unit_sales] [float] NULL
)
ALTER TABLE [sales_fact_20000101]
ADD PRIMARY KEY (
[date_key], [product_key], [customer_key], [promotion_key], [store_key])
;

--创建 UNION ALL 视图。
CREATE VIEW [dbo].[sales_fact]
AS
SELECT * FROM [dbo].[sales_fact_19990101]
UNION ALL
SELECT * FROM [dbo].[sales_fact_20000101]

--现在插入几行数据，例如：
INSERT INTO [sales_fact]
VALUES (19990125, 347, 8901, 0, 13, 5.3100, 1.8585, 3.0)

INSERT INTO [sales_fact]
VALUES (19990324, 576, 7203, 0, 13, 2.1000, 0.9450, 3.0)

INSERT INTO [sales_fact]
VALUES (19990604, 139, 7203, 0, 13, 5.3700, 2.2017, 3.0)

INSERT INTO [sales_fact]
VALUES (20000914, 396, 8814, 0, 13, 6.4800, 2.0736, 2.0)

INSERT INTO [sales_fact]
VALUES (20001113, 260, 8269, 0, 13, 5.5200, 2.4840, 3.0)

要验证分区是否正常工作，请使用查询分析器来显示查询计划，例如：

SELECT TOP 2 * FROM sales_fact WHERE date_key = 19990324

　　您应该看到查询计划中仅包括表 1999。将该查询计划与主键已删除的相同表生成的查询计划相比较，我们会发现：表 2000 仍然被排除。将这些计划与在已删除 date_key 约束的架构上生成的查询计划进行对比。这些约束被删除的情况下，表 1999 和表 2000 都被包括在查询中。

　　请注意，在通常情况下，在大型表上执行查询时，使用“TOP N”语法是好的做法，因为它可以迅速返回结果并使用最少的服务器资源。查看分区表的查询计划时，这一点尤为重要，因为由“SELECT *”语句生成的查询计划很难解析。对于偶尔进行观察的人而言，尽管在查询执行期间，查询中仅使用相关的表，但表面看起来好象查询计划包括了 UNION ALL 视图的所有组件表。

　　将条件直接应用于事实表

　　要获得最佳的查询性能，所有的查询都应将条件直接放在事实表中的筛选键上。将约束放在第二张表（例如日期矢量表）的查询将包括所有分区。对 UNION ALL 事实表的标准星号联合查询工作良好：

将条件放在任意未分区的矢量表的属性上，以标准方式创建星号查询 WHERE 子句。
包括分区矢量（日期）的属性。
在分区矢量架构上设计查询与在未分区的架构上设计完全一样，只不过将日期条件直接放在事实表中的日期键上时日期条件最为有效。

　　如果每一分区表的索引中的第一个列为带日期的簇索引，转到所有分区解析某一特定查询的开销相对较小。编写预定义的查询时应尽可能提高其效率，例如那些生成标准报表或渐变更新下行数据流数据库的查询。

共8页。 9 1 2 3 4 5 6 7 :

共8页。首页上一页 1 2 3 4 5 下一页末页

要求	优点	缺点
可更新的分区视图
CHECK 约束强制使用的分区键主键的分区键部分无其他数据库限制的分区键部分在成员表上定义的 UNION ALL 视图	查询性能：查询计划仅包括解析相关查询所需的成员表。维护应用程序的简易性：数据可以被加载至 UNION ALL 视图，然后插入合适的成员表中	加载性能：通过视图加载数据的速度太慢，以至这种方式对大多数的数据仓库应用程序来说是不实用的。灵活性：数据库设计对分区键可能要求额外的约束。
可查询的分区视图
CHECK 约束强制使用的分区键在成员表上定义的 UNION ALL 视图	查询性能：查询计划仅包括解析查询所必要的成员表。加载性能：可高效地直接将数据批量加载至成员表。存储：尽管推荐声明主键并在主键上创建索引的做法，但分区视图不要求主键索引。	视图最多可有 256 个成员表。必须创建维护应用程序来管理分区和加载。