MV定義語句中包含Fact的VIEW，能否Rewrite ?

在資料倉儲環境中，複製一張dimension表是比較容易的事，但對於fact表，也許很多人更傾向於建立一個view，因為在新的環境裡複製一張十幾億條記錄的表，而且將來還要花心思去同步更新，其工作量是難以想象的。於是我們通常把MV的定義在IFCT對應的VIEW上，為了證明是否MV 定義語句裡面包含VIEW,也 可以達到REWRITE的效果，我們做如下測試:

1. 以SUM_SALES_PSCAT_WEEK_MV為原型，建立測試MV和定於語句中的所有Fact和dimension;
###SUM_SALES_PSCAT_WEEK_MV
### Definition: ######################
SELECT p.prod_subcategory, t.week_ending_day,
sum(s.amount_sold) AS sum_amount_sold
FROM sales s, products p, times t
where s.time_id = t.time_id and s.prod_id = p.prod_id
group by p.prod_subcategory, t.week_ending_day

---- 察看mview的定義語句和table的ddl:
select * from dba_mviews where mview_name='SUM_SALES_PSCAT_WEEK_MV'
select * from dba_tables where table_name='SUM_SALES_PSCAT_WEEK_MV'

######################## Build new MV ##############

------- create new products (新的dimension,用來對應新的fact view)
CREATE TABLE PRODUCTS_TEST_QR
(
PROD_ID NUMBER(6) NOT NULL,
PROD_NAME VARCHAR2(50 BYTE) NOT NULL,
PROD_DESC VARCHAR2(4000 BYTE) NOT NULL,
PROD_SUBCATEGORY VARCHAR2(50 BYTE) NOT NULL,
PROD_SUBCATEGORY_ID NUMBER NOT NULL,
PROD_SUBCATEGORY_DESC VARCHAR2(2000 BYTE) NOT NULL,
PROD_CATEGORY VARCHAR2(50 BYTE) NOT NULL,
PROD_CATEGORY_ID NUMBER NOT NULL,
PROD_CATEGORY_DESC VARCHAR2(2000 BYTE) NOT NULL,
PROD_WEIGHT_CLASS NUMBER(3) NOT NULL,
PROD_UNIT_OF_MEASURE VARCHAR2(20 BYTE),
PROD_PACK_SIZE VARCHAR2(30 BYTE) NOT NULL,
SUPPLIER_ID NUMBER(6) NOT NULL,
PROD_STATUS VARCHAR2(20 BYTE) NOT NULL,
PROD_LIST_PRICE NUMBER(8,2) NOT NULL,
PROD_MIN_PRICE NUMBER(8,2) NOT NULL,
PROD_TOTAL VARCHAR2(13 BYTE) NOT NULL,
PROD_TOTAL_ID NUMBER NOT NULL,
PROD_SRC_ID NUMBER,
PROD_EFF_FROM DATE,
PROD_EFF_TO DATE,
PROD_VALID VARCHAR2(1 BYTE)
)
TABLESPACE EXAMPLE
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE BITMAP INDEX PRODUCTS_PROD_TEST_QR ON PRODUCTS_test_qr
(PROD_STATUS)
NOLOGGING
TABLESPACE EXAMPLE
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE UNIQUE INDEX PRODUCTS_TEST_QR_PK ON PRODUCTS_test_qr
(PROD_ID)
NOLOGGING
TABLESPACE EXAMPLE
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX PRODUCTS_PROD_TEST_QR_IX ON PRODUCTS_test_qr
(PROD_SUBCATEGORY)
NOLOGGING
TABLESPACE EXAMPLE
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX PRODUCTS_PROD_CA_TEST_QR_IX ON PRODUCTS_test_qr
(PROD_CATEGORY)
NOLOGGING
TABLESPACE EXAMPLE
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;



ALTER TABLE PRODUCTS_test_qr ADD (
CONSTRAINT PRODUCTS_TEST_QR_PK
PRIMARY KEY
(PROD_ID)
USING INDEX
TABLESPACE EXAMPLE
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));

INSERT INTO PRODUCTS_TEST_QR select * from products;

------ 建立fact所對應的VIEW:
CREATE view sales_view as select * from sales;
------ 注意，在view上建立外來鍵，透過trigger控制pf約束；
alter view sales_view add constraint product_vw_fk foreign key (PROD_ID) references PRODUCTS_TEST_QR (PRODUCTS_TEST_QR_PK) disable;
alter view sales_view add constraint time_vw_fk foreign key (TIME_ID) references TIMES (TIME_ID) disable;
ALTER VIEW sales_view MODIFY CONSTRAINT product_vw_fk RELY;
ALTER VIEW sales_view MODIFY CONSTRAINT time_vw_fk RELY;--( Rely為了在query_rewrite_integrity為trust和stale_tolerated的情況下能夠rewrite)

--- 建立mv的基表:
CREATE TABLE SUM_SALES_TEST_QR_MV
(
PROD_SUBCATEGORY VARCHAR2(50 BYTE) NOT NULL,
WEEK_ENDING_DAY DATE NOT NULL,
SUM_AMOUNT_SOLD NUMBER
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX I_SNAP$_SUM_SALES_TEST_QR_WE ON SUM_SALES_TEST_QR_MV
(SYS_OP_MAP_NONNULL("PROD_SUBCATEGORY"), SYS_OP_MAP_NONNULL("WEEK_ENDING_DAY"))
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

--- 建立新的MV :SUM_SALES_TEST_QR_MV
DROP MATERIALIZED VIEW SUM_SALES_TEST_QR_MV;

CREATE MATERIALIZED VIEW SUM_SALES_TEST_QR_MV
ON PREBUILT TABLE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT p.prod_subcategory, t.week_ending_day,
sum(s.amount_sold) AS sum_amount_sold
FROM sales_view s, PRODUCTS_TEST_QR p, times t
where s.time_id = t.time_id and s.prod_id = p.prod_id
group by p.prod_subcategory, t.week_ending_day;


################### test Rewrite:
1. 測試sales_view與products_test_qr的查詢:
explain plan for
SELECT p.prod_subcategory,
sum(s.amount_sold) AS sum_amount_sold
FROM sales_view s, products_test_qr p
where s.prod_id = p.prod_id
group by p.prod_subcategory;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 3 (34)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 40 | 3 (34)| 00:00:01 |
| 2 | MAT_VIEW REWRITE ACCESS FULL| SUM_SALES_TEST_QR_MV | 1 | 40 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

2. 測試sales_view與Times的查詢:

explain plan for

SELECT t.week_ending_day,
sum(s.amount_sold) AS sum_amount_sold
FROM sales_view s, times t
where s.time_id = t.time_id
group by t.week_ending_day;

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 3 (34)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 22 | 3 (34)| 00:00:01 |
| 2 | MAT_VIEW REWRITE ACCESS FULL| SUM_SALES_TEST_QR_MV | 1 | 22 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

以上結果證明view也可以做query rewrite, 但前提dimension要不同；

[@more@]