iT邦幫忙

DAY 5
3

Oracle and MS SQL系列 第 5

[Day 5]SQL SERVER善用Indexed View#2測試

這篇我們就來測試IV相關應用
一、提高查詢效能

查看今天兩位主角資料筆數

在沒有建立IV時,來看看所花費的相關時間

select t1.POLICY_NO,t1.BILL_ADDRESS_IND from dbo.NABK t1 join dbo.podt t2 on t1.POLICY_NO=t2.POLICY_NO;

整個執行計畫果然複雜.><

建立IV後,再來看看執行相同的SQL所產生的執行計畫

CREATE VIEW dbo.IVNABK WITH SCHEMABINDING
AS
select t1.POLICY_NO,t1.BILL_ADDRESS_IND from dbo.NABK t1 join dbo.podt t2 on t1.POLICY_NO=t2.POLICY_NO;
GO
CREATE unique CLUSTERED INDEX inx_uc_POLICY_NO ON dbo.IVNABK (policy_no);
GO

(先清除buffer)

select t1.POLICY_NO,t1.BILL_ADDRESS_IND from dbo.NABK t1 join dbo.podt t2 on t1.POLICY_NO=t2.POLICY_NO;
簡單明瞭,這才是我們要的

二、複製資料

如果今天有個需求是每天備份某個主檔TABLE,是否可用IV來提高作業效率呢??
假設主檔資料筆數:1499999

確認資料內容

傳統備份資料所花費時間(使用insert)

使用IV來改善

CREATE VIEW dbo.IVPODT WITH SCHEMABINDING
AS
select POLICY_NO ,
POLICY_TYPE ,
PO_STATUS_CODE ,
COVERAGE_CNT ,
PO_OLD_STATUS_CODE ,
.........略
from dbo.PODT;
GO

秒殺

驗證IV中的資料是否正確
筆數沒錯

將IV資料塞入另一個TABLE。來確認資料可用性和內容正確性

Select data

IV在複製作業上果然效率佳也夠省時。

三、Column有UNIQUE限制,可否塞入NULL值??
這需求和效能無關,不過可以利用IV特性來達到(IV差點都被我輸入成MV,中Oracle毒太深XD)
假設你只想強制已知值(即非NULL值)的唯一性,允許出現多個NULL。
雖然indexed view上建立的cluster index必須是unique。這種索引將防止重複資料進入base table,
但卻允許多個NULL,因為NULL不是unique index的一部分(Cool
^^)。

建立測試Table and IV

CREATE TABLE dbo.test
(
mykey INT NULL,
mydata VARCHAR(10) NOT NULL
);
GO

CREATE VIEW dbo.iv1 WITH SCHEMABINDING
AS
SELECT mykey FROM dbo.test WHERE mykey IS NOT NULL;
GO

CREATE UNIQUE CLUSTERED INDEX ind_uc_keycol ON dbo.iv1(mykey);

執行INSERT語句(含 NULL果然可以正常塞入)

INSERT INTO dbo.test(mykey, mydata) VALUES(NULL,'data1');

INSERT INTO dbo.test(mykey, mydata) VALUES(NULL,'data2');

但塞入相同的mykey(非null)卻得到失敗訊息
INSERT INTO dbo.test(mykey, mydata) VALUES(1,'data3');
INSERT INTO dbo.test(mykey, mydata) VALUES(1,'data4');--失敗

結論:
建立indexed view有助於提高查詢的效能。一個indexed view同等一個table,
還有view不保證排序(可以在外部查詢中指定ORDER BY子句)。
如果有修改base talbe metadata之後也一定要refresh view的metadata。

Refresh indexed view
sp_refreshview [ @viewname = ] 'viewname'

希望能幫助大家更了解Indexed View。


上一篇
[Day 4]SQL SERVER善用Indexed View#1簡介
下一篇
[Day 6]SQL SERVER如何找出硬體Bottleneck
系列文
Oracle and MS SQL34

尚未有邦友留言

立即登入留言