# Nos ubicamos en el directorio del esquema SH
cd $ORACLE_HOME/demo/schema/sales_history/
# Iniciamos SQL*Plus
sqlplus as sysdba
SQL> @sh_main.sql
specify password for SH as parameter 1:
Enter value for 1: sh
specify default tablespace for SH as parameter 2:
Enter value for 2: USERS
specify temporary tablespace for SH as parameter 3:
Enter value for 3: TEMP
specify password for SYS as parameter 4:
Enter value for 4: *********
specify directory path for the data files as parameter 5:
Enter value for 5: u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/
writeable directory path for the log files as parameter 6:
Enter value for 6: u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/
specify version as parameter 7:
Enter value for 7: v3
CREATE MATERIALIZED VIEW SALES_MV AS---带数据
SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;
SELECT * FROM SALES_MV WHERE ROWNUM < 5;
SELECT * FROM DBA_MVIEWS WHERE MVIEW_NAME='SALES_MV';
DROP MATERIALIZED VIEW SALES_MV;
CREATE MATERIALIZED VIEW sales_mv BUILD DEFERRED AS---不带数据，发现创建非常快
SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;
SELECT * FROM SALES_MV WHERE ROWNUM < 5;
EXEC DBMS_MVIEW.REFRESH('SALES_MV');
DROP MATERIALIZED VIEW SALES_MV;
SHOW PARAMETER QUERY_REWRITE_ENABLED
SELECT MVIEW_NAME, REWRITE_ENABLED FROM DBA_MVIEWS;
SQL> SELECT MVIEW_NAME, REWRITE_ENABLED FROM DBA_MVIEWS;
MVIEW_NAME R
------------------------------ -
MGMT_ECM_MD_ALL_TBL_COLUMNS N
SALES_MV N
SET AUTOTRACE TRACE EXPLAIN
SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
AND s.prod_id = 13
GROUP BY t.calendar_year, p.prod_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2577704037
--------------------------------------------------------------------------------
-----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
-----------------------------
| 0 | SELECT STATEMENT | | 4 | 132 | 550 (3
)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 4 | 132 | 550 (3
)| 00:00:07 | | |
|* 2 | HASH JOIN | | 6002 | 193K| 548 (3
)| 00:00:07 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 1826 | 29216 | 18 (0
)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1826 | 29216 | 18 (0
)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | 4 | 0 (0
)| 00:00:01 | | |
| 6 | TABLE ACCESS FULL | TIMES | 1826 | 21912 | 18 (0
)| 00:00:01 | | |
| 7 | PARTITION RANGE JOIN-FILTER| | 6002 | 99K| 530 (3
)| 00:00:07 |:BF0000|:BF0000|
|* 8 | TABLE ACCESS FULL | SALES | 6002 | 99K| 530 (3
)| 00:00:07 |:BF0000|:BF0000|
--------------------------------------------------------------------------------
-----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."TIME_ID"="S"."TIME_ID" AND "P"."PROD_ID"="S"."PROD_ID")
5 - access("P"."PROD_ID"=13)
8 - filter("S"."PROD_ID"=13)
ALTER MATERIALIZED VIEW SALES_MV ENABLE QUERY REWRITE;
SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
AND s.prod_id = 13
GROUP BY t.calendar_year, p.prod_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1420257564
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 4 | 156 | 3 (0)| 0
0:00:01 |
|* 1 | MAT_VIEW REWRITE ACCESS FULL| SALES_MV | 4 | 156 | 3 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SALES_MV"."PROD_ID"=13)
Note
-----
- dynamic sampling used for this statement (level=2)
@?/rdbms/admin/utlxmv.sql—sh用户执行
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW('SALES_MV');
SELECT * FROM MV_CAPABILITIES_TABLE;
DROP MATERIALIZED VIEW SALES_MV;
CREATE MATERIALIZED VIEW sales_mv REFRESH FAST AS
SELECT COUNT(*), COUNT(s.amount_sold), t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;
TRUNCATE TABLE MV_CAPABILITIES_TABLE;
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW('SALES_MV');
SELECT * FROM MV_CAPABILITIES_TABLE;
DROP MATERIALIZED VIEW SALES_MV;
DROP MATERIALIZED VIEW SALES_MV_DAILY;
DROP MATERIALIZED VIEW SALES_MV_CURRENT;
DROP MATERIALIZED VIEW LOG ON TIMES;
DROP MATERIALIZED VIEW LOG ON PRODUCTS;
DROP MATERIALIZED VIEW LOG ON SALES;
CREATE MATERIALIZED VIEW SALES_G AS
SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD
FROM SH.SALES;
CREATE MATERIALIZED VIEW PRODUCTS_G AS
SELECT PROD_ID, PROD_NAME, PROD_DESC
FROM SH.PRODUCTS;
EXEC DBMS_REFRESH.MAKE ('TEST_GROUP','SALES_G,PRODUCTS_G', SYSDATE, 'SYSDATE+1');
EXEC DBMS_REFRESH.REFRESH('TEST_GROUP');--注意回滚段大小
SELECT MVIEW_NAME, TO_CHAR(LAST_REFRESH_DATE,'YYYY/MM/DD HH24:MI:SS')
from DBA_MVIEWS WHERE MVIEW_NAME IN ('SALES_G','PRODUCTS_G');--查看刷新时间
EXEC DBMS_REFRESH.DESTROY('TEST_GROUP');
DROP MATERIALIZED VIEW SALES_G;
DROP MATERIALIZED VIEW PRODUCTS_G;
1.激活hr用户
cd $ORACLE_HOME/demo/schema/human_resources
SQL> @hr_main.sql
specify password for HR as parameter 1:
Enter value for 1: hr
specify default tablespeace for HR as parameter 2:
Enter value for 2: USERS
specify temporary tablespace for HR as parameter 3:
Enter value for 3: TEMP
specify password for SYS as parameter 4:
Enter value for 4: ************
specify log path as parameter 5:
Enter value for 5: tmp/
vi $ORACLE_HOME/network/admin/tnsnames.ora
# Añadimos estas lineas
OCM=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm.dbajunior.com)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=OCM)))
tnsping ocm
2.创建物化视图，注意自己的监听及tns名称
CREATE MATERIALIZED VIEW LOG ON EMPLOYEES;-hr用户执行
CREATE PUBLIC DATABASE LINK NUODA1 CONNECT TO HR IDENTIFIED BY "hr" USING 'NUODA';
SELECT COUNT(*) FROM EMPLOYEES@NUODA1;
CREATE MATERIALIZED VIEW EMP REFRESH FAST AS
SELECT * FROM EMPLOYEES@NUODA1;
EXEC DBMS_MVIEW.REFRESH('EMP','F');
3.
CREATE MATERIALIZED VIEW LOG ON DEPARTMENTS; -hr用户执行
CREATE MATERIALIZED VIEW DEP REFRESH FAST AS
SELECT * FROM DEPARTMENTS@ NUODA1;
BEGIN
DBMS_REFRESH.MAKE (
NAME => 'REFRESH_GROUP_TEST',
LIST => 'EMP,DEP',
NEXT_DATE => SYSDATE,
INTERVAL => 'SYSDATE+1/1440',
IMPLICIT_DESTROY => TRUE);
END;
/
EXEC DBMS_REFRESH.DESTROY('REFRESH_GROUP_TEST');
DROP MATERIALIZED VIEW EMP;
DROP MATERIALIZED VIEW DEP;
DROP PUBLIC DATABASE LINK NUODA;
DROP MATERIALIZED VIEW LOG ON EMPLOYEES;
DROP MATERIALIZED VIEW LOG ON DEPARTMENTS;
扫描二维码关注我的微学堂
搜索刘老师微信号：Rman-2014，备注“Oracle学习与咨询”，即可添加好友；或者扫描下面二维码，关注我的“微学堂”公众号，了解最新OCP认证动态、题库及答案解析、培训机构及讲师介绍、课堂授课内容等。每天还有一篇技术文章发布哦!
