/*
測試Oracle Materialized View的使用方式
測試Oracle Materialized View是否可使用SQLLDR
測試Oracle Materialized View重建時是否會影響引用MV的Procedures及Views
*/
--建立測試Table
CREATE TABLE DATASET
(
X INTEGER NOT NULL,
Y INTEGER NOT NULL,
XY INTEGER,
PRIMARY KEY (X,Y)
);
CREATE TABLE DATASUM
(
X INTEGER,
TOTAL INTEGER
);
--新增測試資料
INSERT INTO DATASET
SELECT X,Y,X*Y
FROM (SELECT LEVEL X FROM DUAL CONNECT BY LEVEL <= 100),
(SELECT LEVEL Y FROM DUAL CONNECT BY LEVEL <= 100);
COMMIT;
--建立一個Materialized View
SQL>CREATE MATERIALIZED VIEW MV_DATASET AS SELECT * FROM DATASET;
--跟DB2的Materialized Query Table不同的是：建立時資料就已進入MV_DATASET。DB2還必須做REFRESH
SQL>SELECT COUNT(*) FROM MV_DATASET;
COUNT(*)
-----------
10000
--測試REFRESH功能
SQL>INSERT INTO DATASET VALUES (101,101,101*101);
SQL>COMMIT;
SQL>ALTER TABLE MV_DATASET NOLOGGING;
SQL>EXEC DBMS_MVIEW.REFRESH('MV_DATASET','cf');
已順利完成PL/SQL程序.
SQL>SELECT COUNT(*) FROM MV_DATASET;
COUNT(*)
-----------
10001
--測試是否可在MV_DTASET上使用SQL LOADER
--準備一個CONTROL FILE dataset.ctl ，內容如下:
LOAD DATA
INFILE *
APPEND
INTO TABLE MV_DATASET
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
X,
Y,
XY
)
BEGINDATA
101|1|101
101|2|202
--執行SQLLDR
C:\>sqlldr userid=orion/orion control=c:\dataset.ctl log=c:\errlog.log
--開啟errlog.log，部份訊息如下:
SQL*Loader-601: 對 INSERT 選項而言, 表格必須是空的. 表格 MV_DATASET 上有錯
--清空MV_DATASET後再試
SQL>truncate table MV_DATASET;
C:\>sqlldr userid=orion/orion control=c:\dataset.ctl log=c:\errlog.log
--開啟errlog.log，部份訊息如下:
記錄 1: 被拒 - 表格 MV_DATASET 發生錯誤
ORA-01732: 不能在此視觀表進行資料操作作業
記錄 2: 被拒 - 表格 MV_DATASET 發生錯誤
ORA-01732: 不能在此視觀表進行資料操作作業
表格 MV_DATASET:
已順利載入 0 資料列.
由於資料錯誤, 2 資料列 未被載入
因為所有的 WHEN 子句均不成立, 0 資料列 未被載入
因為所有的欄位均為 NULL, 0 資料列 未被載入
※結論是DB2的Materialized Query Table是可以被load資料進去，Oracle的則不行
--測試Oracle的Materialized View重建時是否影響view或者procedure which are using MV
--建立view base on mv_dataset
SQL>DROP VIEW V_DATASET
CREATE VIEW V_DATASET
AS
SELECT * FROM MV_DATASET;
SQL>DROP MATERIALIZED VIEW MV_DATASET;
SQL>CREATE MATERIALIZED VIEW MV_DATASET AS SELECT * FROM DATASET;
SQL>SELECT COUNT(*) FROM V_DATASET;
COUNT(*)
-----------
10001
--建立procedure using mv_dataset，內容如下:
CREATE OR REPLACE PROCEDURE SP_DATASUM(nErr OUT NUMBER)
IS
BEGIN
nErr := 0;
BEGIN
INSERT INTO DATASUM
SELECT X,SUM(XY)
FROM MV_DATASET
GROUP BY X;
EXCEPTION
WHEN OTHERS THEN
nErr := SQLCODE;
ROLLBACK;
RETURN;
END
COMMIT;
END;
--重建Materialized View
SQL>DROP MATERIALIZED VIEW MV_DATASET;
SQL>CREATE MATERIALIZED VIEW MV_DATASET AS SELECT * FROM DATASET;
SQL>var nErr number
SQL>exec sp_datasum(:nErr)
已順利完成PL/SQL程序.
SQL>print nErr
NERR
-----
0
SQL>SELECT COUNT(*) FROM DATASUM;
COUNT(*)
--------
101
