Материализованные представления

OWNER

COUNT(*)

2 4 rows selected. Elapsed: 00:00:03.35

tkyte@TKYTE816> set timing off

tkyte@TKYTE816> set autotrace traceonly

tkyte@TKYTE816> select owner, count(*) from my all objects group by owner; 24 rows selected. Execution Plan

SELECT STATEMENT Optimizer=CHOOSE (Cost-2525 Card=24 Bytes=120) SORT (GROUP BY) <Cost=2525 Card=24 Bytes=120) TABLE ACCESS (FULL) OF MY ALL OBJECTS (Cost=547 Card=262968

Statistics

0 recursive calls

27 db block gets

3608 consistent gets

3516 physical reads

0 redo size

1483 bytes sent via SQL*Net to client

535 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

1094 Глава

О sorts (disk) 24 rows processed

Для получения результатов агрегирования необходимо просмотреть более 250000 записей в более чем 3600 блоках. К сожалению, в нашей системе этот запрос необходимо выполнять часто, по нескольку десятков раз в день. Приходится сканировать почти 30 Мбайт данных. Создав материализованное представление данных, можно избежать многократного подсчета по исходной таблице. Ниже описано, что для этого нужно сделать. Операторы GRANT и ALTER более детально будут рассмотрены в разделе Как работают материализованные представления . Кроме указанных ниже привилегий может понадобиться также привилегия CREATE MATERIALIZED VIEW (в зависимости от того, какие роли предоставлены и действуют для соответствующей учетной записи):

tkyte@TKYTE816> grant query rewrite to tkyte;

Grant succeeded.

tkyte@TKYTE816> alter session set query rewrite enabled=true;

Session altered.

tkyte@TKYTE816> alter session set query rewrite integrity=enforced; Session altered.

tkyte@TKYTE816> create materialized view my all objects aggs

2 build immediate

3 refresh on commit

4 enable query rewrite

5 as

6 select owner, count(*)

7 from my all objects

8 group by owner

Materialized view created.

tkyte@TKYTE816> analyze table my all objects aggs compute statistics;

Table analyzed.

По сути, мы заранее вычислили количество объектов и задали итоговую информацию в виде материализованного представления. Мы потребовали немедленно построить и наполнить данными это представление. Обратите внимание, что были также заданы конструкции REFRESH ON COMMIT и ENABLE QUERY REWRITE (вскоре они будут рассмотрены подробнее). Также обратите внимание, что, хотя создано материализованное представление, анализируется таблица. При создании материализованного представления создается настоящая таблица, и ее можно индексировать, анализировать и т.д.

Давайте посмотрим представление в действии, выполнив еще раз запрос, использовавшийся при создании представления:

tkyte@TKYTE816> set timing on tkyte@TKYTE816> select owner, count(*)

2 from my all objects

3 group by owner;

Материализованные представления 95

OWNER COUNT(*)

A 36

В 24

TYPES 36

24 rows selected. Elapsed: 00:00:00.10 tkyte@TKYTE816> set timing off

tkyte6TKYTE816> set autotrace traceonly tkyte@TKYTE816> select owner, count(*)

2 from my all objects

3 group by owner;

24 rows selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=l Card=24 Bytes=216)

1 0 TABLE ACCESS (FULL) OF MY ALL OBJECTS AGGS (Cost=l Card=Valve) Statistics

0 recursive calls

12 db block gets

7 consistent gets

0 physical reads

0 redo size

1483 bytes sent via SQL*Net to client

535 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

24 rows processed

tkyte@TKYTE816> set autotrace off

Вместо более чем 3600 consistent gets (логических операций ввода-вывода) использовано всего 12. Физического ввода-вывода на этот раз вообще не было - данные взят1 из кэша. Теперь буферный кэш будет значительно эффективнее, так как кэ-шировать надо намного меньше данных. Раньше кэширование рабочего множества даже не начиналось, но теперь все рабочее множество помещается в кэше. Обратите внимание, что план выполнения запроса предусматривает полный просмотр таблицы MY ALL OBJECTS AGGS, хотя запрос выполнялся к исходной таблице MY ALL OBJECTS. При получении запроса SELECT OWNER, COUNT(*)... сервер автоматически направляет его к соответствующему материализованному представлению.

Давайте пойдем дальше: добавим новую строку в таблицу MY ALL OBJECTS и зафиксируем изменение:

tkyte@TKYTE816> insert into my all objects

2 (owner, object name, object type, object id)