꿈꾸는 개발자, DBA 커뮤니티 구루비

머터리얼라이즈드 뷰



SELECT p.prod_category, c.country_id
     , SUM(quantity_sold) AS quantity_sold
     , SUM(amount_sold)   AS amount_sold
  FROM sales s
     , customers c
     , products p
 WHERE s.cust_id = c.cust_id
   AND s.prod_id = p.prod_id
 GROUP BY p.prod_category, c.country_id
 ORDER BY p.prod_category, c.country_id
;

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                      |    68 |  3808 |       |  2274   (4)| 00:00:28 |       |       |
|   1 |  SORT GROUP BY           |                      |    68 |  3808 |       |  2274   (4)| 00:00:28 |       |       |
|*  2 |   HASH JOIN              |                      |   968 | 54208 |       |  2273   (3)| 00:00:28 |       |       |
|   3 |    VIEW                  | index$_join$_003     |    72 |  1512 |       |     3  (34)| 00:00:01 |       |       |
|*  4 |     HASH JOIN            |                      |       |       |       |            |          |       |       |
|   5 |      INDEX FAST FULL SCAN| PRODUCTS_PK          |    72 |  1512 |       |     1   (0)| 00:00:01 |       |       |
|   6 |      INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |    72 |  1512 |       |     1   (0)| 00:00:01 |       |       |
|   7 |    VIEW                  | VW_GBC_9             |   968 | 33880 |       |  2270   (3)| 00:00:28 |       |       |
|   8 |     HASH GROUP BY        |                      |   968 | 26136 |       |  2270   (3)| 00:00:28 |       |       |
|*  9 |      HASH JOIN           |                      |   918K|    23M|  1200K|  2228   (2)| 00:00:27 |       |       |
|  10 |       TABLE ACCESS FULL  | CUSTOMERS            | 55500 |   541K|       |   406   (1)| 00:00:05 |       |       |
|  11 |       PARTITION RANGE ALL|                      |   918K|    14M|       |   494   (3)| 00:00:06 |     1 |    28 |
|  12 |        TABLE ACCESS FULL | SALES                |   918K|    14M|       |   494   (3)| 00:00:06 |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------------

   2 - access("ITEM_1"="P"."PROD_ID")
   4 - access(ROWID=ROWID)
   9 - access("S"."CUST_ID"="C"."CUST_ID")



CREATE MATERIALIZED VIEW sales_mv
AS
SELECT p.prod_category, c.country_id
     , SUM(quantity_sold) AS quantity_sold
     , SUM(amount_sold)   AS amount_sold
  FROM sales s
     , customers c
     , products p
 WHERE s.cust_id = c.cust_id
   AND s.prod_id = p.prod_id
 GROUP BY p.prod_category, c.country_id
 ORDER BY p.prod_category, c.country_id
;

M-View 에서의 ORDER BY

M-View 생성시에만 정렬이 유효함
M-View refresh 할때는 정렬 무효
M-View 구문에 정렬이 제외되어 저장되기 때문

M-View 구성

Mview Object 와 Container table 로 구성
MView Object : 데이터 사전상 오브젝트로 그저 메타데이터일 뿐
Container table : 실제 데이터가 저장되는 일반 힙테이블로 MView 와 동일한 이름

MView 직접 조회



SELECT * FROM sales_mv;

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    81 |  5346 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW ACCESS FULL| SALES_MV |    81 |  5346 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

query rewrite 제약

Enterprise Edition
ALTER SESSION SET query_rewrite_enabled = true;
ALTER MATERIALIZED VIEW sales_mv ENABLE QUERY REWRITE;
원본 데이터의 변경시 stale(유효하지 않은, 과거의) 상태가 되면 쿼리재작성 사용 불가.(refresh 후 사용 가능)

힌트 제어 가능 : rewrite, no_rewrite

MView 간접 조회(query rewrite 이용)



ALTER MATERIALIZED VIEW sales_mv ENABLE QUERY REWRITE;

SELECT p.prod_category, c.country_id
     , SUM(quantity_sold) AS quantity_sold
     , SUM(amount_sold)   AS amount_sold
  FROM sales s
     , customers c
     , products p
 WHERE s.cust_id = c.cust_id
   AND s.prod_id = p.prod_id
 GROUP BY p.prod_category, c.country_id
 ORDER BY p.prod_category, c.country_id
;

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |    81 |  5346 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY                |          |    81 |  5346 |     4  (25)| 00:00:01 |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| SALES_MV |    81 |  5346 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

옵티마이져의 query rewrite 방법

전체 문장 일치(full-text-match) 쿼리 재작성
부분 문장 일치(partial-text-match) 쿼리 재작성 : 일부 컬럼만 사용
범용(general) 쿼리 재작성 : 문맥의 의미를 분석하여 판단

범용 쿼리 재작성 시도 실패



SELECT UPPER(p.prod_category) prod_category
     , SUM(s.amount_sold) amount_sold
  FROM sales s
  JOIN products p
    ON s.prod_id = p.prod_id
 GROUP BY p.prod_category
 ORDER BY p.prod_category
;

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                      |     5 |   190 |   539  (11)| 00:00:07 |    |  |
|   1 |  SORT GROUP BY           |                      |     5 |   190 |   539  (11)| 00:00:07 |    |  |
|*  2 |   HASH JOIN              |                      |    72 |  2736 |   538  (11)| 00:00:07 |    |  |
|   3 |    VIEW                  | VW_GBC_5             |    72 |  1224 |   535  (11)| 00:00:07 |    |  |
|   4 |     HASH GROUP BY        |                      |    72 |   648 |   535  (11)| 00:00:07 |    |  |
|   5 |      PARTITION RANGE ALL |                      |   918K|  8075K|   494   (3)| 00:00:06 |     1 |    28 |
|   6 |       TABLE ACCESS FULL  | SALES                |   918K|  8075K|   494   (3)| 00:00:06 |     1 |    28 |
|   7 |    VIEW                  | index$_join$_002     |    72 |  1512 |     3  (34)| 00:00:01 |    |  |
|*  8 |     HASH JOIN            |                      |       |       |            |          |    |  |
|   9 |      INDEX FAST FULL SCAN| PRODUCTS_PK          |    72 |  1512 |     1   (0)| 00:00:01 |    |  |
|  10 |      INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |    72 |  1512 |     1   (0)| 00:00:01 |    |  |
-----------------------------------------------------------------------------------------------------------------
   2 - access("ITEM_1"="P"."PROD_ID")
   8 - access(ROWID=ROWID)

범용 쿼리 재작성 재시도 성공



ALTER TABLE sales MODIFY CONSTRAINT sales_customer_fk VALIDATE;
ALTER TABLE customers MODIFY CONSTRAINT customers_pk VALIDATE;

SELECT UPPER(p.prod_category) prod_category
     , SUM(s.amount_sold) amount_sold
  FROM sales s
  JOIN products p
    ON s.prod_id = p.prod_id
 GROUP BY p.prod_category
 ORDER BY p.prod_category
;

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |    81 |  3240 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY                |          |    81 |  3240 |     4  (25)| 00:00:01 |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| SALES_MV |    81 |  3240 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

리프레시(refresh)

어떻게 수행할지?

refresh complete : 전체 삭제 후 재생성
refresh fast : 기존 자료 재사용, 변경 데이터만 반영
refresh force : fast refresh 시도후 불가능하면 complete refresh 수행
nevwr refresh : refresh 금지

언제 수행할지?

on demand : 명시적 요청에 의해서 수행
on commit : 기반 테이블 변경 완료(commit)시 수행

MView 정보 확인



SELECT refresh_method
     , refresh_mode
     , staleness
     , last_refresh_type
     , last_refresh_date
  FROM user_mviews
 WHERE mview_name = 'SALES_MV'
;

REFRESH_METHOD REFRESH_MODE STALENESS LAST_REFRESH_TYPE LAST_REFRESH_DATE
-------------- ------------ --------- ----------------- -----------------
FORCE          DEMAND       FRESH     COMPLETE          18/01/18

dbms_mview 패키지로 MView Refresh 하기



dbms_mview.refresh(list => 'sh.sales_mv,sh.cal_month_sales_mv');
dbms_mview.refresh_all_mviews(number_of_failures => :r);
dbms_mview.refresh_dependent(number_of_failures => :r, list => 'sh.sales_mv');
mathod => ( c / f / p / ? : complete / fast / PCT / force )
atomic_refresh => false : complete refresh 수행시 deltet가 아닌 truncate 하고 수행하므로 빠름.
out_of_place => true : 새 컨테이너 테이블에 direct-path-insert 후 바꿔치기  (v12.1)

MView Refresh 주기적으로 하기



ALTER MATERIALIZED VIEW sales_mv
REFRESH COMPLETE ON DEMAND
START WITH sysdate
NEXT sysdate + TO_DSINTERVAL('0 00:10:00')
;

SELECT what, interval
  FROM user_jobs
;

WHAT                                     INTERVAL
---------------------------------------- -------------------------------------
dbms_refresh.refresh('"SH"."SALES_MV"'); sysdate + TO_DSINTERVAL('0 00:10:00')

MATERIALIZED VIEW LOG

MView log



CREATE MATERIALIZED VIEW LOG ON sales     WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON products  WITH ROWID;

SELECT master, log_table
  FROM user_mview_logs
 WHERE master IN ('SALES', 'CUSTOMERS', 'PRODUCTS')
   AND log_owner = 'SH'
;

MASTER    LOG_TABLE
--------- -----------------
CUSTOMERS MLOG$_CUSTOMERS
PRODUCTS  MLOG$_PRODUCTS
SALES     MLOG$_SALES

fast refresh 요건

기본적인 materialized view log 생성만으로는 fast refresh 지원 못함
including new values : 변경 전,후 값 로그 기록
시퀀스 추가
COUNT(*y) 추가

MView log 재생성(fast refresh 를 지원하기 위함)



DROP MATERIALIZED VIEW LOG ON sales    ;
DROP MATERIALIZED VIEW LOG ON customers;
DROP MATERIALIZED VIEW LOG ON products ;

CREATE MATERIALIZED VIEW LOG ON sales     WITH ROWID, SEQUENCE
(cust_id, prod_id, quantity_sold, amount_sold) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID, SEQUENCE
(cust_id, country_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON products  WITH ROWID, SEQUENCE
(prod_id, prod_category) INCLUDING NEW VALUES;

MView 재생성(COUNT(*) 추가)



DROP MATERIALIZED VIEW sales_mv;

CREATE MATERIALIZED VIEW sales_mv
REFRESH FORCE ON DEMAND
AS
SELECT p.prod_category
     , c.country_id
     , SUM(s.quantity_sold)   AS quantity_sold
     , SUM(s.amount_sold)     AS amount_sold
     , COUNT(*)               AS count_star
     , COUNT(s.quantity_sold) AS count_quantity_sold
     , COUNT(s.amount_sold)   AS count_amount_sold
  FROM sales     s
     , customers c
     , products  p
 WHERE s.cust_id = c.cust_id
   AND s.prod_id = p.prod_id
 GROUP BY p.prod_category, c.country_id
;

fast refresh 수행시간 확인



INSERT INTO products
SELECT 619 prod_id
     , prod_name
     , prod_desc
     , prod_subcategory
     , prod_subcategory_id
     , prod_subcategory_desc
     , prod_category
     , prod_category_id
     , prod_category_desc
     , prod_weight_class
     , prod_unit_of_measure
     , prod_pack_size
     , supplier_id
     , prod_status
     , prod_list_price
     , prod_min_price
     , prod_total
     , prod_total_id
     , prod_src_id
     , prod_eff_from
     , prod_eff_to
     , prod_valid
  FROM products
 WHERE prod_id = 136
;

INSERT INTO sales
SELECT 619 prod_id
     , cust_id
     , time_id
     , channel_id
     , promo_id
     , quantity_sold
     , amount_sold
  FROM sales
 WHERE prod_id = 136
;

COMMIT;

SQL> EXEC dbms_mview.refresh(list => 'sh.sales_mv', method => 'f');

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.06    -- 빠르게 refresh 되었음을 확인 할 수 있다.

파티션 변경 추적 (PCT, Partition Change Tracking) refresh}

파티션 추가, 삭제 작업시 뷰로그를 이용한 fast refresh 가 불가능 하다.
변경된 파티션만 refresh
PCT REFRESH 가 가능하려면 MView 가 다음 중 하나를 포함해야 한다.
- 파티션키
- ROWID
- Partition Marker
- 조인 의존 표현식(join-dependent expression)

MView 사용시기

대규모 집계나 조인에서 논리적 읽기 횟수와 반환되는 로우 개수의 비율이 아주 크게 차이 날 경우 성능 개선을 위해서 사용한다.
단일 테이블에 대한 full table scan 과 index trange scan 모두 만족스런 성능이 안나올 때 성응개선을 위해 사용한다.
기본적으로 이런 경우엔 파티션닝을 활용하면 되지만 파티셔닝을 사용하지 목할 경우 대안으로 MView 사용.

MView 함정

fast refresh 가 항상 complete refresh 보다 빠른 것은 아니다. - 대량의 데이터 변경
뷰로그를 유지관리하는데 소요되는 오버헤드