ITPub博客

首页 > IT基础架构 > 网络安全 > MV定义语句中包含Fact的VIEW,能否Rewrite ?

MV定义语句中包含Fact的VIEW,能否Rewrite ?

原创 网络安全 作者:kl911 时间:2008-07-30 14:04:56 0 删除 编辑

在数据仓库环境中,复制一张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@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/661919/viewspace-1008098/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-03

  • 博文量
    20
  • 访问量
    318484