RiCo技術農場

這篇簡單測試Indexed View相關應用。

 

一、提高查詢效能

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

在沒有建立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後，再來看看執行計畫

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;

簡單明瞭，這才是我們要的

總表

總類	CPU時間花費(ms)	實體IO讀取花費	查詢時間花費(ms)
沒有建立IV	22750	1489+2580	150706
建立IV	328	0	6186

 

二、複製資料

如果今天有個需求是每天備份某個主檔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在複製作業上果然效率佳也夠省時。

種類	CPU時間(ms)	實體IO	花費時間(ms)
insert	35797	109	173500
Using 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'