| Репликация на данни чрез материализирани изгледи |
| Четвъртък, 02 Април 2009 16:24 |
|
Когато говорим за изглед (или view) в една база данни обикновено имаме предвид съхранена заявка, която се достъпва като виртуална таблица. Изгледите Нека имаме две таблици. Едната се нарича DEPARTMENTS и съдържа наименованията на всички отдели в една компания, а другата се нарича EMPLOYEES и съдържа списък на всички служители в компанията. Таблиците са създадени по следния начин CREATE TABLE DEPARTMENTS ( ID NUMBER NOT NULL, DEPARTMENT_NAME VARCHAR2(20), CONSTRAINT DEPARTMENTS_PK PRIMARY KEY (ID) ENABLE ); CREATE TABLE EMPLOYEES ( ID NUMBER NOT NULL, FIRST VARCHAR2(20), LAST VARCHAR2(20), DEPARTMENT NUMBER, CONSTRAINT EMPLOYEES_PK PRIMARY KEY (ID) ENABLE, CONSTRAINT EMPLOYEES_DEPARTMENTS_FK FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENTS (ID) ENABLE ); В таблицата EMPLOYEES имаме колона DEPARTMENT, която сочи към ID в таблицата DEPARTMENTS. По този начин можем да задаваме отдел за всеки един служител, а интегритета на отношението се осигурява от ограничението EMPLOYEES_DEPARTMENTS_FK. Ако искаме да изкараме справка за служителите и името на отдела, в който работят използваме следната заявка: SELECT FIRST, LAST, DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPARTMENT = D.ID Резултатът от изпълнението на заявката изглежда по следния начин: FIRST LAST DEPARTMENT_NAME -------------------- -------------------- Kage Baker Q&A Steven Barnes ACCOUNTING William Barton MARKETING Harry Bates Q&A Ted Chiang R&D Jo Clayton Q&A Hal Clement ACCOUNTING Graham Diamond SALES David Drake MARKETING 9 rows selected Ако често ни се налага да извършваме тази заявка можем да създадем изглед, който формира данните си чрез нея. За целта изпълняваме SQL> CREATE VIEW EMPLOYEE_DEPARTMENTS AS SELECT FIRST, LAST, DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPARTMENT = D.ID CREATE VIEW succeeded. SQL> Сега можем да изпълним следната заявка и да получим същите данни: SQL> SELECT * FROM EMPLOYEE_DEPARTMENTS FIRST LAST DEPARTMENT_NAME -------------------- -------------------- Kage Baker Q&A Steven Barnes ACCOUNTING William Barton MARKETING Harry Bates Q&A Ted Chiang R&D Jo Clayton Q&A Hal Clement ACCOUNTING Graham Diamond SALES David Drake MARKETING 9 rows selected Освен че ни спестяват писане, изгледите са незаменими при комбинирането на данни от повече таблици, при заявки със сложна логика и при управление на правата върху данните. Важно е винаги да помним, че стандартните изгледи не копират данните в себе си. Те просто съдържат заявката, която извлича информацията от оригиналните таблици.  Когато потребителят поиска данни от изгледа, базата данни изпълнява заявката, с която изгледа е създаден (момент 1 на диаграмата). Тя извлича данните от оригиналната таблица и ги предоставя на потребителя (момент 2) Материализираните изгледи Основната разлика между стандартния и материализирания изглед е, че материализираният изглед не се обръща всеки път към оригиналните таблици. Вместо това, той държи копие на данните при себе си.  Когато потребителят изпълни командата CREATE MATERIALIZED VIEW, заявката с която се създава изгледа се изпълнява еднократно към оригиналните таблици и данните от тях се копират в самия изглед (момент 1 от диаграмата). Когато по-късно потребителят изпълни заявка към изгледа, данните се извличат направо от него, а не се прави обръщение към таблиците EMPLOYEES и DEPARTMENTS (момент 2). Материализираните изгледи работят по-бързо от обикновените по ред причини (не се налага изваждане на данни от няколко таблици, пресяват се по-малко данни, данните са по-компактно разположени, поддържат се индекси, може да се използва partitioning и така нататък). Цената, която плащаме за бързодействието на материализирания изглед е заеманото от него допълнително дисково пространство (все пак той прави копие на оригиналните данни). Трябва да отбележим и какво се случва с информацията в материализирания изглед, когато оригиналните таблици се променят. Нека създадем изгледа по следния начин: SQL> CREATE MATERIALIZED VIEW M_EMPLOYEE_DEPARTMENTS AS SELECT FIRST, LAST, DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPARTMENT = D.ID CREATE MATERIALIZED succeeded. Сега да погледнем броя на записите в обикновения изглед EMPLOYEE_DEPARTMENTS и в материализирания M_EMPLOYEE_DEPARTMENTS: SQL> SELECT COUNT(*) FROM EMPLOYEE_DEPARTMENTS COUNT(*) ---------------------- 9 1 rows selected SQL> SELECT COUNT(*) FROM M_EMPLOYEE_DEPARTMENTS COUNT(*) ---------------------- 9 1 rows selected Нека добавим нов запис в таблицата EMPLOYEES. SQL> INSERT INTO "APP2"."EMPLOYEES" (ID, FIRST, LAST, DEPARTMENT) VALUES ('10', 'Donald', 'Wandrei', '3') 1 row created. SQL> COMMIT; Commit complete. Да погледнем отново броя записи: SQL> SELECT COUNT(*) FROM EMPLOYEE_DEPARTMENTS COUNT(*) ---------------------- 10 1 rows selected SQL> SELECT COUNT(*) FROM M_EMPLOYEE_DEPARTMENTS COUNT(*) ---------------------- 9 1 rows selected Очевидно така създадения материализиран изглед е статичен. Той не се влияе от промените в оригиналните таблици. Стандартния изглед от друга страна веднага отразява промените, тъй като всеки път изпълнява заявката директно към EMPLOYEES и DEPARTMENTS. Детайлното разглеждане на процесите по опресняване на материализирани изгледи излиза извън обхвата на темата. Въпреки това е добре да знаем, че съществуват различни видове материализирани изгледи. Някои са статични, други се опресняват цялостно, към трети само се добавят промените, но пък изискват следене на измененията в оригиналните таблици. За да накараме материализирания изглед да се опреснява трябва да използваме ключовата дума REFRESH при неговото създаване. Това обаче ще направим малко по-късно. Репликация на данни чрез материализираните изгледи Интересна възможност предоставяна от изгледите е, че основните таблици над които ги изграждаме могат да бъдат разположени в друга инстанция на базата данни. Тази друга база данни може да бъде разположена и на отдалечен сървър. Ако комбинираме тази възможност с използването и на материализирани изгледи, тогава можем ефективно да държим локално копие на данни, намиращи се в отдалечени таблици. Нека си представим следната ситуация – имаме приложение APP1, което съхранява списък с потребителите си и техните пароли в таблица USERS. Таблицата е разположена в база данни EL5.  Инсталираме друго приложение, нека го наречем APP2. То ще има същите потребители като APP1, но ще използва друга база данни – EL6. Най-лесният начин да държим синхронизирани потребителите е да направим EL5 видима за EL6 чрез DATABASE LINK и да създадем материализиран изглед в EL6 върху таблицата USERS от EL5. Разбира се, можем да направим обикновен изглед, но материализираният ни дава две предимства: достъпът до потребителите за APP2 ще бъде по-бърз, ако данните са локално. Освен това, ако отпадне връзката между EL6 и EL5, потребителите на APP2 ще могат да продължат да използват системата без проблем. И така... Нека първо се свържем с EL5 и създадем потребителя APP1 и таблицата USERS: SQL> CONNECT SYSTEM/*********@EL5 Connected. SQL> CREATE USER APP1 IDENTIFIED BY APP1; User created. SQL> GRANT CONNECT,RESOURCE TO APP1; Grant succeeded. SQL> CONNECT APP1/APP1@EL5 Connected. SQL> CREATE TABLE USERS (USER_ID INTEGER PRIMARY KEY, FIRST_NAME VARCHAR2(30), LAST_NAME VARCHAR2(30), PASSWORD VARCHAR2(20)); Table created. SQL> Вмъкваме малко примерни данни в USERS: SQL> INSERT INTO USERS VALUES (1, 'BRIAN', 'ALDISS', 'PASS1'); 1 row created. SQL> INSERT INTO USERS VALUES (2, 'POUL', 'ANDERSON', 'PASS2'); 1 row created. SQL> INSERT INTO USERS VALUES (3, 'NEAL', 'ASHER', 'PASS3'); 1 row created. SQL> COMMIT; Commit complete. SQL> Прехвърляме се на другия сървър – EL6. Нека започнем с конфигурирането на DATABASE LINK между двете бази данни. Първо трябва да редактираме tnsnames.ora файла, и да опишем начина на свързване към EL5. Единственото, което трябва да направим е да добавим един запис (нека го кръстим EL5), който съдържа протокола, IP адреса и името на базата EL5. За моята конфигурация, при която EL5 слуша на порт 1522 на 192.168.10.101 и базата се нарича ORCL записът изглежда: EL5 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.101)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) Ако записът е коректен можем да проверим дали EL5 отговаря, като изпълним tnsping EL5 или направо като опитаме да се свържем чрез sqlplus app1/app1@el5. След като сме сигурни, че имаме свързаност между двете бази нека се свържем локално към EL6 и да създадем потребителя APP2: $ sqlplus system@el6 SQL*Plus: Release 10.2.0.1.0 - Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> CREATE USER APP2 IDENTIFIED BY APP2; User created. SQL> GRANT CONNECT, RESOURCE, CREATE MATERIALIZED VIEW, CREATE DATABASE LINK TO APP2; Grant succeeded. Така създаденият потребител APP2 има и нужните права за да създава DATABASE LINK и материализиран изглед. Нека започнем с LINK-а. Дефинираме го така, че връзката към EL5 винаги да се осъществява като потребител APP1: SQL> CONNECT APP2@EL6 Enter password: Connected. SQL> SQL> CREATE DATABASE LINK EL5 CONNECT TO APP1 IDENTIFIED BY APP1 USING 'EL5'; Database link created. SQL> Нека проверим, дали виждаме таблицата USERS през DATABASE LINK-а: SQL> SELECT COUNT(*) FROM USERS@EL5; COUNT(*) ---------- 3 SQL> Сега вече можем да създадем изгледа, като използваме връзката към EL5. В допълнение ще се възползваме от конструкцията REFRESH NEXT, чрез която можем да инструктираме базата данни да опреснява данните в него през определен период от време. SQL> CREATE MATERIALIZED VIEW V_USERS REFRESH NEXT SYSDATE+INTERVAL '10' MINUTE WITH ROWID AS SELECT * FROM USERS@EL5; Materialized view created. SQL> В случая REFRESH NEXT SYSDATE + INTERVAL '10' MINUTE задава интервал на опресняване 10 минути. Нека проверим дали създадения материализиран изглед е достъпен. SQL> SELECT COUNT(*) FROM V_USERS; COUNT(*) ---------- 3 SQL> Виждаме, че в материализираният изглед са копирани трите записа от таблицата USERS. Да проверим и синхронизацията. Връзваме се към EL5: SQL> CONNECT APP1@EL5 Enter password: Connected. SQL> Вмъкваме един нов запис: SQL> INSERT INTO USERS VALUES (4, 'ROBERT', 'ASPRIN', 'PASS4'); 1 row created. SQL> COMMIT; Commit complete. SQL> Закачаме се към EL6 и изпълняваме заявката SELECT TO_CHAR(SYSDATE,'HH:MI'), FIRST_NAME,LAST_NAME FROM V_USERS, която ще ни покаже всички имена на потребителите заедно с времето към момента на нейното изпълнение. SQL> SELECT TO_CHAR(SYSDATE,'HH:MI'), FIRST_NAME,LAST_NAME FROM V_USERS; TO_CH FIRST_NAME LAST_NAME ----- ------------------------------ ------------------------------ 05:29 BRIAN ALDISS 05:29 POUL ANDERSON 05:29 NEAL ASHER SQL> Виждаме, че записите все още са три. Изчакваме малко и изпълняваме отново. SQL> SELECT TO_CHAR(SYSDATE,'HH:MI'), FIRST_NAME,LAST_NAME FROM V_USERS; TO_CH FIRST_NAME LAST_NAME ----- ------------------------------ ------------------------------ 05:32 BRIAN ALDISS 05:32 POUL ANDERSON 05:32 NEAL ASHER SQL> Отново изчакваме и изпълняваме. SQL> SELECT TO_CHAR(SYSDATE,'HH:MI'), FIRST_NAME,LAST_NAME FROM V_USERS; TO_CH FIRST_NAME LAST_NAME ----- ------------------------------ ------------------------------ 05:39 BRIAN ALDISS 05:39 POUL ANDERSON 05:39 NEAL ASHER 05:39 ROBERT ASPRIN SQL> Виждаме, че минути по-късно материализираният изглед е синхронизиран и в него присъства записът въведен в таблицата USERS. Някои допълнителни съображения Опресняването на материализираният изглед се извършва чрез изпълняване на процедурата REFRESH от DBMS_REFRESH. Това, което конструкцията REFRESH NEXT SYSDATE + INTERVAL '10' MINUTE прави е да създаде JOB, изпълняван всеки 10 минути, който изпълнява DBMS_REFRESH.REFRESH. Можем да погледнем създадения за нашия материализиран изглед JOB, като направим справка в таблицата USER_JOBS (изпълнена като потребител APP2): SQL> SELECT JOB, LOG_USER, TO_CHAR(LAST_DATE,'DD/MM/YY HH:MI') LAST, TO_CHAR(NEXT_DATE,'DD/MM/YY HH:MI') NEXT, FAILURES, WHAT FROM USER_JOBS JOB LOG_USER LAST NEXT FAILURE WHAT --------- ------------- -------------- -------------- -------- ----------------------------------------- 21 APP2 24/03/09 05:36 24/03/09 05:46 0 dbms_refresh.refresh('"APP2"."V_USERS"'); 1 rows selected Колоната LAST показва момента на последната синхронизация, а NEXT – кога ще бъде следващото изпълнение на REFRESH. В USER_JOBS има и колона INTERVAL. Ако погледнете нейната стойност за JOB 21 ще видите, че тя е SYSDATE+INTERVAL '10' MINUTE. Възможно е не всички опити за синхронизация да са успешни. Това най-често се случва когато оригиналната база е недостъпна. Тя може да е изключена или да има проблем с мрежовата свързаност до нея. Неуспешните опити се отразяват в колоната FAILURES на USER_JOBS. Ако един JOB не успее няколко поредни пъти (точния брой зависи от стойността на параметъра max_failures)той ще влезе в повредено състояние (колоната BROKEN в USER_JOBS ще добие стойност Y). Когато това се случи, опитите за неговото изпълнение съгласно определения интервал ще бъдат прекратени. За да поправите това, ще трябва ръчно да премахнете флага за повредено състояние. Тъй като следенето на всички такива JOB-ове е досадно занимание, може да го автоматизирате с една доста хитра съхранена процедура, която също може да се изпълнява по график (например всеки час, веднъж дневно и така нататък). Процедурата изглежда по следния начин и е взета от книгата PL/SQL Built-in Packages на Стив Фойерщайн: /* Filename on companion disk: job5.sql */* CREATE OR REPLACE PROCEDURE job_fixer AS /* || calls DBMS_JOB.BROKEN to try and set || any broken jobs to unbroken */ /* cursor selects user's broken jobs */ CURSOR broken_jobs_cur IS SELECT job FROM user_jobs WHERE broken = 'Y'; BEGIN FOR job_rec IN broken_jobs_cur LOOP DBMS_JOB.BROKEN(job_rec.job,FALSE); END LOOP; END job_fixer; |
Коментари
Хубав ден!