[SQL]使用Partitioned View

2012-09-11

1946
0
SQL

練習一下

問題描述

目前員工檔資料庫硬碟空間不足，希望能夠分散目前資料庫主機上的資料並且減輕查詢時server負擔。

解決方式

建立Partitioned View，主機的分配情況如下：

查詢的資料庫：拿本機做測試。

在職員工資料庫：INDBT1。

離職員工資料庫：INDBT2。

步驟

1. 建立Linked server

步驟可以參考下列網址

http://www.jensbits.com/2010/11/10/create-linked-server-sql-server-2008/

建立好後的畫面如下

2. 進行資料分割及新增條件限制

執行的SQL如下


-- 建立Partitioned view
-- 資料來源 : 德瑞克老師的上課筆記+自行修改
-- 建立測試資料(在職員工)
-- INDBT1
USE ProductDB
GO
IF exists
(SELECT NAME
 FROM
	 sys.tables
 WHERE
	 name = 'EMPLOYEELinkTest') DROP TABLE dbo.EMPLOYEELinkTest;

SELECT STAFF
	 , BRNO
	 , [STATUS]
INTO
	ProductDB.dbo.EMPLOYEELinkTest
FROM
	ProductDB.dbo.EMPLOYEE
WHERE
	[STATUS] = 1;
GO

-- 檢查資料是否建立
SELECT *
FROM
	ProductDB.dbo.EMPLOYEELinkTest

-- 建立條件約束	
ALTER TABLE [dbo].[EMPLOYEELinkTest]  
WITH CHECK ADD 
    CONSTRAINT [CK_STATUS] CHECK  (([STATUS] = 1))

-- 看條件約束是否建立
SELECT name N'物件名稱'
	 , type N'物件類型'
	 , type_desc N'物件類型描述'
	 , definition N'「CHECK」條件約束的 SQL 運算式'
FROM
	sys.check_constraints
WHERE
	parent_object_id = object_id('EMPLOYEELinkTest')
GO

-- 建立測試資料(離職員工)
-- INDBT2
USE ProductDB
GO
IF exists
(SELECT NAME
 FROM
	 sys.tables
 WHERE
	 name = 'EMPLOYEELinkTest') DROP TABLE dbo.EMPLOYEELinkTest;

SELECT STAFF
	 , BRNO
	 , [STATUS]
INTO
	ProductDB.dbo.EMPLOYEELinkTest
FROM
	ProductDB.dbo.EMPLOYEE
WHERE
	[STATUS] = 2;
GO

-- 檢查資料是否建立
SELECT *
FROM
	ProductDB.dbo.EMPLOYEELinkTest

-- 建立條件約束	
ALTER TABLE [dbo].[EMPLOYEELinkTest]  
WITH CHECK ADD 
    CONSTRAINT [CK_STATUS] CHECK  (([STATUS] = 2))

-- 看條件約束是否建立
SELECT name N'物件名稱'
	 , type N'物件類型'
	 , type_desc N'物件類型描述'
	 , definition N'「CHECK」條件約束的 SQL 運算式'
FROM
	sys.check_constraints
WHERE
	parent_object_id = object_id('EMPLOYEELinkTest')
GO

3. 在查詢資料庫建立View

執行的SQL如下


-- 在查詢的資料庫建立view
USE EMPDATA
GO
IF EXISTS
(SELECT name
 FROM
	 sys.views
 WHERE
	 name = 'vi_AllEmployee') DROP VIEW dbo.vi_AllEmployee
GO	 
CREATE VIEW [dbo].[vi_AllEmployee]
AS
SELECT *
FROM
	INDBT1.chb_pub.dbo.EMPLOYEELinkTest
UNION ALL
SELECT *
FROM
	INDBT2.chb_pub.dbo.EMPLOYEELinkTest
GO

-- 測試檢查結果
SELECT *
FROM
	dbo.vi_AllEmployee;
SELECT *
FROM
	dbo.vi_AllEmployee
WHERE
	[STATUS] = 1;
SELECT *
FROM
	dbo.vi_AllEmployee
WHERE
	[STATUS] = 2;

結果畫面

當查詢不分條件時，成本是依照資料分布情況分散在各遠端主機。

當查詢條件符合條件限制時，可以看出來loading會集中在某幾台機器。

參考資料

德瑞克老師的上課筆記

Create Linked Server SQL Server 2008

設計分散式資料分割檢視

Creating a Partitioned View

Using Partitioned Views

回首頁