Материализованные вьюшки - это запросы, результаты выполнения которых сохраняются в виде таблиц и могут быть обновлены.
Пока мат.вьюшка обновляется, сессии oracle видят состояние мат.вьюшки до момента начала обновления.
Мат.вьюшки обычно применяются для не очень легких запросов или, например, запоминания данных, полученных по некруглосуточно работающим database link-ам.
Создадим большие таблицы:
create table kk_tbl_for_mv1 as select level as id, dbms_random.value as val from dual connect by level <= 1000000;
create table kk_tbl_for_mv2 as select level as id, dbms_random.value as val from dual connect by level <= 1500000;
create table kk_tbl_for_mv3 as select level as id, dbms_random.value as val from dual connect by level <= 2000000;
Создадим мат.вьюшки:
create materialized view kk_mv1 as select * from kk_tbl_for_mv1 where mod(id, 4) > 0;
create materialized view kk_mv2 as select * from kk_tbl_for_mv2 where mod(id, 4) > 0;
create materialized view kk_mv3 as select * from kk_tbl_for_mv3 where mod(id, 4) > 0;
Посмотрим количества строк в этих мат.вьюшках:
select count(1) from kk_mv1;
select count(1) from kk_mv2;
select count(1) from kk_mv3;
Убедимся, что в user_objects на каждую мат.вьюшку создается 2 записи - таблица и мат.вьюшка (соответственно и в дереве объектов PL/SQL Developer-а можно увидеть каждый из этих объектов):
select * from user_objects where object_name like 'KK_MV%';
На таблицы мат.вьюшек можно создавать индексы.
Обновлять мат.вьюшки можно командой dbms_mview.refresh, например:
begin dbms_mview.refresh('KK_MV1'); end;
Для следующего примера воспользуемся таблицей логирования и сиквенсом для генерации значений ее первичного ключа ID:
create table kk_mv_log (id number, lc_date date, lc_message varchar2(200));
create sequence sq_kk_mv_log;
Запустим одновременное обновление наших мат.вьюшек в "одноразовых" job-ах, которые выполнятся в параллельных сессиях oracle, что быстрее последовательного обновления.
Воспользуемся вариантом вызова dbms_job.submit без указания параметров next и interval, что создаёт "одноразовый" джоб.
Во время выполнения нижеприведенного блока будем смотреть за активными сессиями в PL/SQL Developer-е (Tools -> Sessions -> Active sessions), а еще посматривать за состоянием системной вьюшки dba_jobs_running, где видны выполняющиеся в текущий момент job-ы:
declare
v_job_number1 integer := 0;
v_job_number2 integer := 0;
v_job_number3 integer := 0;
v_is_running integer := 1;
begin
dbms_job.submit(job => v_job_number1,
what => 'declare v_sqlerrm varchar2(300);
begin
insert into kk_mv_log(id, lc_date, lc_message) values(sq_kk_mv_log.nextval, sysdate, ''Начало обновления KK_MV1'');
dbms_mview.refresh(''KK_MV1'');
insert into kk_mv_log(id, lc_date, lc_message) values(sq_kk_mv_log.nextval, sysdate, ''Завершение обновления KK_MV1'');
exception when others then
v_sqlerrm := sqlerrm;
insert into kk_mv_log(id, lc_date, lc_message) values(sq_kk_mv_log.nextval, sysdate, ''Проблемы при обновлении KK_MV1: '' || v_sqlerrm);
end;');
dbms_job.submit(job => v_job_number2,
what => 'declare v_sqlerrm varchar2(300);
begin
insert into kk_mv_log(id, lc_date, lc_message) values(sq_kk_mv_log.nextval, sysdate, ''Начало обновления KK_MV2'');
dbms_mview.refresh(''KK_MV2'');
insert into kk_mv_log(id, lc_date, lc_message) values(sq_kk_mv_log.nextval, sysdate, ''Завершение обновления KK_MV2'');
exception when others then
v_sqlerrm := sqlerrm;
insert into kk_mv_log(id, lc_date, lc_message) values(sq_kk_mv_log.nextval, sysdate, ''Проблемы при обновлении KK_MV2: '' || v_sqlerrm);
end;');
dbms_job.submit(job => v_job_number3,
what => 'declare v_sqlerrm varchar2(300);
begin
insert into kk_mv_log(id, lc_date, lc_message) values(sq_kk_mv_log.nextval, sysdate, ''Начало обновления KK_MV3'');
dbms_mview.refresh(''KK_MV3'');
insert into kk_mv_log(id, lc_date, lc_message) values(sq_kk_mv_log.nextval, sysdate, ''Завершение обновления KK_MV3'');
exception when others then
v_sqlerrm := sqlerrm;
insert into kk_mv_log(id, lc_date, lc_message) values(sq_kk_mv_log.nextval, sysdate, ''Проблемы при обновлении KK_MV3: '' || v_sqlerrm);
end;');
commit;
loop
/* ожидание 1 секунды */
dbms_lock.sleep(1);
/* в dba_jobs_running джоб появляется не сразу, чтобы им воспользоваться надо сделать задержку до цикла, поэтому воспользуемся user_jobs */
select count(1)
into v_is_running
from user_jobs
where job in (v_job_number1, v_job_number2, v_job_number3);
exit when v_is_running = 0;
end loop;
end;
Видим, что по завершении блока одноразовые джобы исчезли из user_jobs и dba_jobs_running:
select * from user_jobs order by job desc;
select * from dba_jobs_running;
Посмотрим дату последнего обновления наших мат.вьюшек в user_mviews:
select mview_name, query, last_refresh_date from user_mviews where mview_name like 'KK_MV%';
Посмотрим в наш лог обновления мат.вьюшек:
select * from kk_mv_log order by id desc;
"Расклейка" и "склейка" строк.
В этом разделе рассмотрим часто возникающие задачи по склейке и расклейке строк. Здесь применяются регулярные выражения и иерархические запросы. Не обязательно глубоко вникать в эти примеры, главное научиться писать такие же запросы на других структурах данных.
“Расклейка” одной строки.
Часто возникают задачи, когда в запросе из строки вида 'A2,8F8,J5,I77' необходимо сделать 4 строки:
A2
8F8
J5
I77
Применяя иерархический запрос и регулярные выражения, напишем запрос, который "расклеивает" строку, делая из нее несколько строк:
select regexp_substr(s, '[^,]+', 1, level) piece
from (select 'A2,8F8,J5,I77' s from dual)
connect by regexp_substr(s, '[^,]+', 1, level) is not null;
“Расклейка” нескольких строк с группировкой.
Теперь приведу волшебный запрос, который делает “расклейку” не по одной строке, а по всей таблице с группировкой:
from (select ltrim(sys_connect_by_path(id, ','), ',') as ids, level
from (select id, lag(id) over(order by id) as prev_id
from (select 'A2' id
from dual
union all
select '8F8' id
from dual
union all
select 'J5' id
from dual
union all
select 'I77' id from dual))
start with prev_id is null
connect by prev_id = prior id
order by level desc)
where rownum = 1;
“Склейка” нескольких строк с группировкой.
Еще одним способом склеить строку является использование агрегирующей функции WM_CONCAT (она склеивает только через запятую):
select pk, wm_concat(id)
from (select 1 pk, 'A2' id
from dual
union all
select 1 pk, '8F8' id
from dual
union all
select 1 pk, 'J5' id
from dual
union all
select 1 pk, 'I77' id
from dual
union all
select 2 pk, 'GGG' id
from dual
union all
select 2 pk, 'HH69' id from dual)
group by pk;
Далее приведу запрос, который склеивает данные с группировкой и сортировкой склеиваемых данных в указанном порядке. Рассмотрим таблицу людей и их хобби с датой начала увлечения. Склеим набор хобби каждого человека в порядке начала увлечения:
