...
Полного сканирования таблицы при запросе, в том числе и в Oracle, стараются избежать, для этого используют индексы. При выборе столбца для индекса следует проанализировать, какие типы запросов чаще всего выполняются пользователем, и какие столбцы являются ключевыми. Не стоит индексировать столбцы, которые только считываются и не играют никакой роли в определении порядка выполнения запроса. Не следует индексировать слишком длинные столбцы, например, столбцы с адресами или названиями компаний, достигающие длины несколько десятков символов, т.к. индексирование длинных столбцов может существенно снизить производительность работы сервера. Действительно, каждая запись файла индекса должна содержать, помимо ссылки на строку таблицы и само значение в индексированном столбце, поэтому, индексируя длинные столбцы, пространство базы данных расходуется неэкономно. Кроме того, операции обновления и сравнения длинных столбцов занимают много времени. В крайнем случае, можно создать укороченный вариант такого длинного столбца (до десяти символов), и индексирование проводить по этому столбцу.
Материализованные представления
Как известно, представление (view) — это запрос на выборку, хранящийся на сервере, как отдельный объект. Так как, результат этого запроса можно рассматривать в качестве таблицы, представление допускается использовать в других запросах, также как любую обычную таблицу. Материализованное представление хранится на сервере в виде таблицы, которая автоматически обновляется при изменении данных, имеющих отношение к этому представлению. При выполнении запроса, основанного на материализованном представлении, используются не исходные таблицы и запрос, на котором основано представление, а данные, которые хранятся в этом представлении. За счет этого скорость выполнения запросов может быть повышена на порядки. Основным недостатком материализованного представления является то, что для его использования требуется дополнительная дисковое пространство и его необходимо синхронизировать с основными данными.
Создание объектов
| Блок кода | ||||
|---|---|---|---|---|
| ||||
drop table h1;
drop table h2;
create table h1(n number primary key, s varchar2(100) null, n1 number null, s1 varchar2(100));
create table h2(n number primary key, s varchar2(100) null, n1 number null, s1 varchar2(100));
insert into h1(n, s, n1, s1) select object_id, object_name, object_id, owner from all_objects;
insert into h2(n, s, n1, s1) select object_id, object_name, object_id, owner from all_objects;
analyze table h1 compute statistics for table for all columns for all indexes;
analyze table h2 compute statistics for table for all columns for all indexes;
create view v1 as select h1.n, h1.s, h2.s1, h2.n1 from h1 inner join h2 on h1.n = h2.n and h1.s = h2.s;
select * from v1 where n = 10 and s = '23423';
grant create materialized view to bor;
grant query rewrite to bor;
create materialized view v2 as select h1.n, h1.s, h2.s1, h2.n1 from h1 inner join h2 on h1.n = h2.n and h1.s = h2.s;
select * from v2 where n = 10 and s = '23423';
create index iv on v2(n);
select * from v2 where n = 10 and s = '23423';
|
Информация по материализованным представлениям
| Блок кода | ||||
|---|---|---|---|---|
| ||||
select * from user_mviews;
select * from user_views;
select * from user_segments where segment_name in('V1', 'V2');
create materialized view v3 as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;
select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;
select s1, n1 from v3;
|
Оценка требуемого размера
| Блок кода | ||||
|---|---|---|---|---|
| ||||
variable v_rows number
variable v_bytes number
exec DBMS_MVIEW.ESTIMATE_MVIEW_SIZE(1, 'select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1', :v_rows, :v_bytes);
print :v_rows :v_bytes
|
Способы построения
Существует два способа формирования представления - непосредственно при его создании или отложенное, по явной команде.
| Блок кода | ||||
|---|---|---|---|---|
| ||||
drop materialized view v3;
create materialized view v3 build immediate as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;
create materialized view v3 build deferred as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;
select * from v3;
exec dbms_mview.refresh('V3');
select * from v3;
|
Частота обновления
Материализованное представление может синхронизироваться с исходными данными или автоматически на момент завершения транзакции или по явной команде синхронизации. Возможна синхронизация по расписанию.
| Блок кода | ||||
|---|---|---|---|---|
| ||||
drop materialized view v3;
create materialized view v3 build immediate refresh complete on commit as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1;
insert into h1(n, s, n1, s1) values(-1, 'aaa', -1, 'aaa');
insert into h1(n, s, n1, s1) values(-2, 'aaa', -2, 'aaa');
select * from v3;
commit;
select * from v3;
drop materialized view v3;
create materialized view v3 build immediate refresh complete on commit as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;
create materialized view v3 build immediate refresh complete on demand as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;
insert into h1(n, s, n1, s1) values(-3, 'bbb', -3, 'bbb');
insert into h1(n, s, n1, s1) values(-4, 'bbb', -4, 'bbb');
commit;
exec dbms_mview.refresh('V3', 'F' /* ? C */);
drop materialized view v3;
create materialized view v3 build immediate refresh complete start with '17-MAY-2004' next sysdate+1 as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;
@?/rdbms/admin/utlxmv.sql
set linesize 200
column statement_id format a15
column mvowner format a5
column mvname format a10
column statement_id format a3
column related_text format a10
column msgtxt format a60
exec dbms_mview.explain_mview('v3', '111');
select * from mv_capabilities_table;
exec dbms_mview.explain_mview('select h1.s1 s1, count(h1.n1) n1 from h1 group by s1', '222');
select * from mv_capabilities_table where statement_id = 222;
|
Способ обновления
Существует два основных способа обновления материализованных представлений
- Полная перестройка. В этом случае при каждом обновлении данных происходит выполнение запроса, на основе которого построено представление и данные полностью перегружаются.
- Частичное обновление. Для каждой таблицы. Участвующей в запросе на представление, создается журнал изменений и изменения представления выполняются только данных, измененных в основных таблицах.
Блок кода sql sql drop materialized view v3; create materialized view v3 build immediate refresh complete as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1; insert into h1(n, s, n1, s1) values(-1, 'aaa', -1, 'aaa'); insert into h1(n, s, n1, s1) values(-2, 'aaa', -2, 'aaa'); commit; exec dbms_mview.refresh('v3'); select * from v3; drop materialized view v3; create materialized view v3 refresh fast as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1; create materialized view v3 refresh fast as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1; create materialized view v3 refresh fast as select h1.s1 s1, avg(n1) n, count(h1.n1) n1 from h1 group by s1; create materialized view v3 refresh fast as select h1.s1 s1, avg(n1) n from h1 group by s1; create materialized view log on h1 nologging with sequence, rowid (n1, s1) including new values; create materialized view log on h2 nologging with sequence, rowid (n1, s1) including new values; drop materialized view v3; create materialized view v3 refresh fast on commit as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1; insert into h1(n, s, n1, s1) values(-11, 'aaa1', -11, 'aaa1'); insert into h1(n, s, n1, s1) values(-21, 'aaa1', -21, 'aaa1'); commit; exec dbms_mview.explain_mview('v3', '2'); select * from mv_capabilities_table where statement_id = 2; drop materialized view v3; exec dbms_mview.explain_mview('select h1.s1 s1, count(h1.n1) n1 from h1 group by s1', '4'); select * from mv_capabilities_table where statement_id = 4; select * from user_segments where segment_name like 'MLOG%'; select * from user_mview_logs; drop materialized view log on h1; drop materialized view log on h2; create materialized view log on h1 nologging with sequence, rowid (n, n1, s1) including new values; create materialized view log on h2 nologging with sequence, rowid (n, n1, s1) including new values; truncate table mv_capabilities_table; exec dbms_mview.explain_mview('select h1.s1 s1, h2.s1 s2, h1.n n from h1, h2 where h1.n = h2.n', '5'); select * from mv_capabilities_table where statement_id = 5; truncate table mv_capabilities_table; exec dbms_mview.explain_mview('select h1.rowid h1_rowid, h2.rowid h2_rowid, h1.s1 s1, h2.s1 s2, h1.n n from h1, h2 where h1.n = h2.n', '5'); select * from mv_capabilities_table where statement_id = 5; drop materialized view v3; create materialized view v3 refresh fast as select h1.s1 s1, h2.s1 s2, h1.n n from h1, h2 where h1.n = h2.n; create materialized view v3 refresh fast as select h1.rowid h1_rowid, h2.rowid h2_rowid, h1.s1 s1, h2.s1 s2, h1.n n from h1, h2 where h1.n = h2.n; create materialized view v3 refresh fast on commit as select h1.s1 s1, h2.s1 s2, h1.n n from h1, h2 where h1.n = h2.n; create materialized view v3 refresh fast on commit as select h1.rowid h1_rowid, h2.rowid h2_rowid, h1.s1 s1, h2.s1 s2, h1.n n from h1, h2 where h1.n = h2.n;