신규 기능 - 원격 저장소를 가진 실체화 뷰

신규 기능 - 원격 저장소를 가진 실체화 뷰

---

원격 저장소를 가진 실체화 뷰

테스트 환경

OS : OEL release 7.2
Tibero : 6 FS04
Oracle : 12.2.0.1.0

• 원격 저장소를 가진 실체화 뷰
• 기능 테스트
  
  • 사전 작업
  • Materialized view 생성
• 여담

원격 저장소를 가진 실체화 뷰

---

• 뷰(VIEW)란, SELECT 문으로 표현되는 질의에 이름을 부여한 가상 테이블이다.1
  즉, SELECT 쿼리(질의)만을 담고 있는 객체로 해당 객체를 호출하면 객체가 담고 있는 질의가 실행된다.
  따라서, 뷰는 실제로 데이터를 갖지 않으며 질의를 통해 기반이 되는 테이블에서 데이터를 불러오며 이러한 테이블을 뷰의 기반 테이블(base table)이라고 한다.
• 실체화 뷰(Materialized view)는 흔히 M-View라고 부르며 간단하게 말해서 데이터를 갖는 뷰를 의미한다.
  즉, 뷰가 담고 있는 질의의 결과를 물리적으로 저장하여 뷰가 호출될 때 저장된 데이터를 불러오게 된다.
  따라서, 일반 뷰가 질의를 담고 있다면 실체화 뷰는 질의의 결과를 담고있다고 보면된다.
• 본 내용인 원격 저장소를 가진 실체화 뷰 기능은 이름에서 알 수 있듯 실체화 뷰가 가진 질의의 결과를 원격지에 저장하는 기능이다.
• 이기종 데이터베이스(?)에서 실체화 뷰를 이용하여 Tibero에 있는 베이스 테이블의 데이터를 동기화하고 싶을 때 사용하는 기능이다.2
• 여기서 말하는 이기종이란 Oracle을 말한다. 즉, 기반 테이블은 Tibero에 존재하고 실체화 뷰는 Oracle에 존재하게 된다.
• 여담에도 썼지만 Tibero가 원격지인 경우는 안된다.

기능 테스트

---

사전 작업

---

• 우선 원격 저장소를 가진 실체화 뷰 기능의 각 단어를 띄어 놓고 생각해보면 전반적인 작업 순서가 정해진다.
• 원격이란 기능을 Tibero DBMS내의 객체로 지원하는 것은 DB Link(Database Link)가 유일하다.
• 저장소 또한, 유저가 원하는 대로 저장할 수 있는 공간은 Table이 유일하다.
• 따라서, 해당 기능을 사용하기 위해서는 원격 접속을 할 수 있는 DB Link와 해당 원격지에 저장소가 될 Table이 필요하다.
• 추가적으로 해당 기능 구현에 필요한 Tibero에서 제공하는 SQL Script 수행이 필요하다.
• Oracle DB Link는 Local Gateway나 Listener를 참고해 편한것으로 구성하면 된다.(생략)

• 참고로 본 테스트에서는 ORA라는 이름으로 DB Link를 생성했다.

• Table은 View의 내용을 담게 되므로 View 쿼리의 결과에 맞는 구조로 테이블을 생성해야 한다.

• View로 동기화 할 쿼리는 아래와 같다.
  (참고로 Oracle의 HR 계정의 구조와 데이터가 동일하다.)

SQL> select d.department_name,
            e.last_name,
            l.city,
            j.job_title
       from departments d,
            employees e,
            locations l,
            jobs j
      where d.manager_id = e.employee_id
        and e.job_id = j.job_id
        and d.location_id = l.location_id
     ;

• 각 Table의 컬럼 정의를 확인해서 Oralce에 Table을 생성한다.
• 위의 DB Link에 사용된 계정으로 Oracle에 접속한다.
• 다른 방법으로 위의 쿼리로 CTAS로 Table을 만든 후 해당 Table의 정의를 이용해도 된다.
  (쿼리에 참여하는 Table 등이 많아 복잡한 경우 해당 방법이 편리하다.)

SQL>
SQL> CREATE TABLE REMOTE_MVIEW_ORA
     ( DEPARTMENT_NAME VARCHAR(30) NOT NULL
     , LAST_NAME       VARCHAR(25) NOT NULL
     , CITY            VARCHAR(30) NOT NULL
     , JOB_TITLE       VARCHAR(35) NOT NULL
     )
     ;

Table created.

SQL>

• 추가적으로 Oracle에서 $TB_HOME/scripts/mview_remote_install.sql script 파일을 수행해주어야 한다.
• 마찬가지로 DB Link에 사용된 계정으로 Oracle에 접속한다.
• Function 2개가 생성된다.

SQL> @mview_remote_install.sql

Function created.


Function created.

SQL>

Materialized view 생성

---

• 이제 Tibero에서 Materialized view를 만들 차례다.
• 기본적으로 Materialized view는 View를 만들때와 비슷하지만 추가적인 옵션들이 존재한다.
  (해당과 관련한 부분은 별다른 설명없이 지나가겠다.)
• 그리고 원격 저장소를 가진 실체화 뷰 기능에서는 이외에 ON PREBUILT TABLE 옵션과 AT 옵션이 필요하다.
• AT 옵션은 뒤에 DB Link를 지정함으로써 원격지의 정보를 제공한다.
• 또한, ON PREBUILT TABLE 옵션으로 미리 생성된 Table을 저장소로 사용하는 기능이다.
• 따라서, AT 옵션과 같이 사용되어 원격지에 있는 Table 즉, TABLE@DB_LINK Table을 저장소로 사용하게 된다.

SQL> /* -- 생성문법
      * CREATE MATERIALIZED VIEW MView명   -- 생성할 MView명
      * ON PREBUILT TABLE                  -- 기존 생성된 Table을 저장소로 사용(MView명과 동일한 Table이 지정된다)
      * AT 링크명                          -- 원격지 링크명
      *    WITHOUT REDUCED PRECISION       -- ON PREBUILT TABLE의 옵션으로 Table 컬럼의 정밀도의 차이를 허용하지 않음(WITH는 허용)
      * REFRESH FORCE ON COMMIT            -- MView의 REFRESH 방법 및 수행 이벤트 지정(COMMIT 발생 시 FORCE로 REFRESH 수행)
      * AS
      *    (SELECT 구문)                   -- View의 SELECT 구문
      * ;
      */
SQL> CREATE MATERIALIZED VIEW remote_mview_ora
     ON PREBUILT TABLE
     AT ORA
        WITHOUT REDUCED PRECISION
     REFRESH FORCE ON COMMIT
     AS
        select d.department_name,
               e.last_name,
               l.city,
               j.job_title
          from departments d,
               employees e,
               locations l,
               jobs j
         where d.manager_id = e.employee_id
           and e.job_id = j.job_id
           and d.location_id = l.location_id
     ;

Materialized View 'REMOTE_MVIEW_ORA' created.

SQL>

• 실제 데이터 적제는 REFRESH가 필요하다.

SQL> exec dbms_mview.refresh('REMOTE_MVIEW_ORA' , 'C') ;

PSM completed.

SQL>

• Oracle에서 조회해서 확인해 본다.

SQL> SELECT * FROM REMOTE_MVIEW_ORA ;

11 rows selected.

SQL>

• Tibero에서 데이터를 추가한다.

SQL> INSERT INTO COUNTRIES VALUES ('KR', 'Korea', 3) ;

1 row inserted.

SQL> INSERT INTO LOCATIONS(LOCATION_ID, CITY, COUNTRY_ID) VALUES (3300, 'Seoul', 'KR') ;

1 row inserted.

SQL> INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) VALUES (280, 'Tibero', 3300) ;

1 row inserted.

SQL> INSERT INTO JOBS VALUES ('TB_MGR','Tibero Manager', 10000, 20080) ;

1 row inserted.

SQL> INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
     VALUES (208, 'Libero', 'TMAX', 'tmax.co.kr', SYSDATE, 'TB_MGR', 10000, 100, 280) ;

1 row inserted.

SQL> UPDATE DEPARTMENTS
        SET MANAGER_ID = 208
      WHERE DEPARTMENT_ID = 280 ;

1 row updated.

SQL> COMMIT ;

Commit completed.

SQL>

• Oralce에서 추가된 데이터가 조회되는지 보자.
• 언제나 현실은 시궁창이다.

SQL> SELECT * FROM REMOTE_MVIEW_ORA ;

11 rows selected.

SQL> SELECT * FROM REMOTE_MVIEW_ORA
      WHERE DEPARTMENT_NAME = 'Tibero' ;

no rows selected

SQL>

• 설마 했지만 역시나 조회되지 않는다.
• Tibero 해당 MVIEW를 DB Link로 조회하면 조회된다.(읭?)

SQL> SELECT * FROM REMOTE_MVIEW_ORA@ORA
      WHERE DEPARTMENT_NAME = 'Tibero' ;

DEPARTMENT_NAME LAST_NAME CITY  JOB_TITLE
--------------- --------- ----- ---------------
Tibero          TMAX      Seoul Tibero Manager

1 row selected.

SQL>

• Tibero에서 Oracle로 INSERT문은 수행했으나 COMMIT이 안되는걸로 보인다.

• 사실, 원격지를 Tibero로 한 경우에 해당 기능을 테스트해도 같은 증상을 보인다.

• 위의 INSERT를 수행한 세션을 종료하면 COMMIT된다.

• EXITCOMMIT이 기본적으로 켜져있어 위와 같이 동작한다.
• 세션을 종료 후 Oracle에서 조회하면 보인다.

SQL> SELECT * FROM REMOTE_MVIEW_ORA@ORA
      WHERE DEPARTMENT_NAME = 'Tibero' ;

DEPARTMENT_NAME LAST_NAME CITY  JOB_TITLE
--------------- --------- ----- ---------------
Tibero          TMAX      Seoul Tibero Manager


SQL>

여담

---

해당 기능이 Tibero 간의 원격 저장소 기능인줄 알았지만 아니다.
Tibero 간의 구성은 가능하나 실제 데이터 저장이 되지는 않는다.
이래저래 여러가지 의미로 대단하다.(티맥스는 역시 대다나다)

---

1. Tibero 관리자 안내서 ↩
2. Tibero SQL 참조 안내서 ↩