Manage Materialized Views to Improve Rewrite and Refresh Performance

Tutorials, Solutions
1

Дараах дадлагын ажлаар бид 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;
1 Like