Дараах дадлагын ажлаар бид scott schema ашигладагтай адил sh schema-г ашиглах болно. Анх баазыг үүсгэхдээ жишээ датаг идэвхжүүлдэг өгдөг билээ.
Materialized view нь
- Нэгт, хүснэгтний хуулбарыг үүсгэх, эсвэл өөр баазын мэдээллийн хуулбарыг дуудаж харуулах зэргээр ашиглагддаг. Уг хуулбарыг шинэчилэх үйлдэл нь гар аргаар болон автомат хэлбэрээр хийгдэж болно.
- Хоёрт, Join болон том хэмжээний олон хүснэгтээс мэдээлэл татан харуулж буй үйлдэл их хийгддэг бол уг үйлдлийг дахин дахин хийх нь ачаалал үүсгэнэ. Тиймээс materialized view үүсгэснээр тухайн үйлдлийн үр дүнг хадгалдаг бөгөөд мөн тохиргооноос хамаарч шинэчилэлтүүдийг татан хадгалж байдаг.
Мэдээллийг эх үүсвэрээс хуувбарт татаж хадгалах үйлдлийг REFRESH гэж нэрлэх бөгөөд FULL болон FAST гэсэн хоёр төрөл байна.
- FULL
- Бүх бичлэгийг устгаад шинээр нэмнэ
- atomic=false үед mview-ийг устгах замаар мэдээллийг устгана.
- atomic=true үед DELETE коммандаар устгах үйлдэл хийдэгтэй ижил
- Бүх бичлэгийг устгаад шинээр нэмнэ
- FAST
- mlog$ ашиглана
sh хэрэглэгчийг идэвхжүүлье.
SQL>
SQL> alter user sh identified by s account unlock;
User altered.
SQL>
Тухайн хэрэглэгчийн хүснэгтүүдийг харцгаая.
SQL>
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
DIMENSION_EXCEPTIONS
SALES
COSTS
DR$SUP_TEXT_IDX$K
DR$SUP_TEXT_IDX$N
SALES_TRANSACTIONS_EXT
SUPPLEMENTARY_DEMOGRAPHICS
TIMES
PRODUCTS
CUSTOMERS
CAL_MONTH_SALES_MV
TABLE_NAME
------------------------------
DR$SUP_TEXT_IDX$R
COUNTRIES
FWEEK_PSCAT_SALES_MV
PROMOTIONS
DR$SUP_TEXT_IDX$I
CHANNELS
17 rows selected.
SQL>
FULL REFRESH
Build Immediate
Materialized view-ийг үүсгэх үед мэдээллийг шууд татаж хадгална.
TIMES болон PRODUCTS хүснэгтнүүдийг холбосон materialized view үүсгэж мэдээллийг харцгаая.
SQL> -- шинэлэгдсэн мэдээллийг шууд авах. (BUILD IMMEDIATE төрөл)
SQL> 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;
Materialized view created.
SQL>
SQL> SELECT * FROM SALES_MV WHERE ROWNUM < 5;
CALENDAR_YEAR PROD_ID SUM_SALES
------------- ---------- ----------
1998 13 936197.53
1998 26 567533.83
1998 27 107968.24
1998 28 644480.02
SQL>
Үүссэн materialized view ийн тухай мэдээллийг DBA_MVIEWS -с харна.
SELECT * FROM DBA_MVIEWS WHERE MVIEW_NAME='SALES_MV' AND OWNER='SH';
Устгах үйлдэл:
DROP MATERIALIZED VIEW SALES_MV;
Build Deferred
Энэ нь тухайн materialized view-ийг үүсгэх хэдий ч мэдээллийг шууд татахгүй хоосон view үүсгэнэ гэсэн үг юм.
SQL> 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;
Materialized view created.
SQL> select sum(sum_sales) from sales_mv;
SUM(SUM_SALES)
--------------
SQL> SELECT * FROM SALES_MV WHERE ROWNUM < 5;
no rows selected
SQL>
Бид мэдээллийг уг view рүү хадгалахын тулд дараах коммандыг ашиглана.
SQL> EXEC DBMS_MVIEW.REFRESH('SALES_MV');
PL/SQL procedure successfully completed.
SQL> SELECT * FROM SALES_MV WHERE ROWNUM < 5;
CALENDAR_YEAR PROD_ID SUM_SALES
------------- ---------- ----------
1998 13 936197.53
1998 26 567533.83
1998 27 107968.24
1998 28 644480.02
SQL>
Мэдээллийг мөн өөр аргаар татаж болно.
-- SALES хүснэгттэй холбоотой бүх views-ийг шинэчилэх
VARIABLE failures NUMBER;
EXEC DBMS_MVIEW.REFRESH_DEPENDENT(:failures,'SALES');
-- Бүх materialized views-ийг шинэчилэх (SYSDBA эрхээр)
VARIABLE failures NUMBER;
EXEC DBMS_MVIEW.REFRESH_ALL_MVIEWS(:failures);
Зөвхөн шинэчилэлтийг татах /FAST REFRESH/
Дээрх жишээн дээр бид мэдээллийг бүтнээр нь татан авч буй талаар туршсан. Харин энэ удаад хэрхэн зөвхөн өөрчлөлтийг татан авах талаар сонирхоё.
Үүний тулд бид ашиглагдах хүснэгт бүрт Materialized View Log үүсгэж өгөх бөгөөд уг логын тусламжтайгаар зөвхөн шинэчилэлтүүдийг materialized view рүү татан авах юм.
Лог үүсгэхэд харгалзах mlog$_TABLENAME view -үүд үүснэ.
SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON sales
WITH SEQUENCE, ROWID (prod_id, time_id, amount_sold),
COMMIT SCN INCLUDING NEW VALUES;
Materialized view log created.
SQL>CREATE MATERIALIZED VIEW LOG ON products
WITH PRIMARY KEY, SEQUENCE, ROWID, COMMIT SCN
INCLUDING NEW VALUES;
Materialized view log created.
SQL>CREATE MATERIALIZED VIEW LOG ON times
WITH PRIMARY KEY, SEQUENCE, ROWID (calendar_year),
COMMIT SCN INCLUDING NEW VALUES;
Materialized view log created.
SQL> desc mlog$_sales;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
PROD_ID NUMBER
TIME_ID DATE
AMOUNT_SOLD NUMBER(10,2)
M_ROW$$ VARCHAR2(255)
SEQUENCE$$ NUMBER
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
XID$$ NUMBER
SQL>
Лог үүсгэсний дараагаар өөрчлөлтийг татах materialized view үүсгэе.
-- Шууд шинэчилэгдэх MVIEW
CREATE MATERIALIZED VIEW sales_mv REFRESH FAST 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;
-- өдөрт бүр шинэчилэгдэх MVIEW
CREATE MATERIALIZED VIEW sales_mv_daily REFRESH FAST NEXT SYSDATE+1 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;
-- COMMIT хийх үе шинэчилэгдэх MVIEW
CREATE MATERIALIZED VIEW sales_mv_current REFRESH FAST ON COMMIT 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;
SALES хүснэгт рүү мэдээлэл оруулж шалгая. Commit хийхээс өмнө бусад хэсгийг шалгаарай.
SQL> insert into sales values(37,13053,'07-OCT-98', 2, 999,1, 100000);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> -- Мэдээллийн анхны хэлбэр
SQL> select sum(SUM_SALES) from sales_mv;
SUM(SUM_SALES)
--------------
98305831.2
SQL> select sum(SUM_SALES) from sales_mv_daily;
SUM(SUM_SALES)
--------------
98305831.2
SQL> select sum(SUM_SALES) from sales_mv_current;
SUM(SUM_SALES)
--------------
98305831.2
SQL> ---- Insert хийгдсэн. Гэхдээ commit хийгдээгүй үед
SQL> select sum(SUM_SALES) from sales_mv;
SUM(SUM_SALES)
--------------
98305831.2
SQL> select sum(SUM_SALES) from sales_mv_daily;
SUM(SUM_SALES)
--------------
98305831.2
SQL> select sum(SUM_SALES) from sales_mv_current;
SUM(SUM_SALES)
--------------
98305831.2
SQL> --- commit хийсний дараа
SQL> select sum(SUM_SALES) from sales_mv;
SUM(SUM_SALES)
--------------
98305831.2
SQL> select sum(SUM_SALES) from sales_mv_daily;
SUM(SUM_SALES)
--------------
98305831.2
SQL> select sum(SUM_SALES) from sales_mv_current;
SUM(SUM_SALES)
--------------
98405831.2
SQL>
Мэдээлэл татах хугацааг өөрчлөх
ALTER MATERIALIZED VIEW sales_MV REFRESH FAST NEXT SYSDATE+(1/1440);
Шинэчилэлт хэзээ татах талаар мэдээллийг харах
SQL> col next for a40
SQL> SELECT NAME, NEXT FROM DBA_SNAPSHOTS WHERE NAME LIKE 'SALES_MV%';
NAME NEXT
------------------------------ ----------------------------------------
SALES_MV_CURRENT
SALES_MV SYSDATE+(1/1440)
SALES_MV_DAILY SYSDATE+1
SQL>
QUERY OPTIMIZATION TECHNIQUE
QUERY_REWRITE_ENABLED
- FALSE: Query Rewrite disabled
- TRUE: Choose the lowest cost plan with or without Query Write
- FORCE: Whenever Query Rewrite can be used, it will be used
SHOW PARAMETER QUERY_REWRITE_ENABLED
SELECT MVIEW_NAME, REWRITE_ENABLED FROM DBA_MVIEWS;
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;
-- SALES_MV-т QUERY REWRITE идэвхжүүлэх
ALTER MATERIALIZED VIEW SALES_MV ENABLE QUERY REWRITE;
-- Өмнөх ижил query ажиллуулъя. MAT_VIEW REWRITE ACCESS FULL хэсгийг хараарай
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;
Бусад
MVIEW талаарх бусад мэдээллийг харах
-- MV_CAPABILITIES_TABLE хүснэгт үүсгэх
@?/rdbms/admin/utlxmv.sql
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW('SALES_MV');
-- MSGTXT баганы утгуудыг анхаарах
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;