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

определяет REGION), значения столбца MMYYYY - в QTR YYYY и ответил на запрос почти моментально. Здесь нам удалось сократить количество операций логического вво-да-в1вода с более чем 700000 до 16. Если учесть, что таблица SALES со временем будет расти, а размер представления SALES MV будет увеличиваться намного медленнее (примерно 180 записей в месяц), запрос будет очень хорошо масштабироваться.

Пакет DBMS OLAP

Последним фрагментом головоломки, которую представляют собой материализован-н1е представления, является пакет DBMS OLAP. Этот пакет используется для:

оценки размера материализованного представления в строках и байтах;

проверки корректности объектов-измерений, с учетом заданных отношений первичного/внешнего ключа;

получения рекомендаций о создании дополнительных материализованных представлений и поиска лишних, которые надо удалить, с учетом их реального использования и структуры или только структуры;

оценки использования материализованного представления с помощью предоставляемых процедур, которые информируют о фактической полезности имеющихся материализованных представлений независимо от того, использовались они или нет.

К сожалению, процедуры оценки полезности выходят за рамки тем, которые я могу раскргть в одной главе. Для использования этих процедур необходимо настроить утилиту Oracle Trace и средства Enterprise Manager Performance Pack, но остальные три процедуры мы рассмотрим.

Чтобы использовать пакет DBMS OLAP, необходимо настроить использование внешних процедур, поскольку большая часть кода пакета DBMS OLAP хранится в библиотеке, написанной на языке С. Если выдается сообщение об ошибке следующего вида, выполните инструкции по настройке, представленные в главе 18:

ERROR at line 1:

ORA-28575: unable to open RPC connection to external procedure agent ORA-06512: at SYS.DBMS SUMADV , line 6 ORA-06512: at SYS.DBMS SUM4MARY , line 559 ORA-06512: at line 1

Оценка размера

Процедура ESTIMATE SUMMARY SIZE информирует о предположительном количестве строк и размере в байтах материализованного представления. Поскольку ретроспективный анализ дает наилучшие результаты, можно оценить эти значения с помощью пакета DBMS OLAP, а затем сравнить с реальными значениями.

Для запуска процедуры необходимо убедиться, что в схеме установлена таблица PLAN TABLE. Соответствующий оператор CREATE TABLE можно найти в файле [ORACLE HOME]/rdbms/admin/utlxplan.sql на сервере. При выполнении этого сцена-

1118 Глава 13

рия автоматически будет создана таблица PLAN TABLE. Эта таблица используется при выполнении оператора EXPLAIN PLAN, результаты работы которого, в свою очередь, используются пакетом DBMS OLAP для оценки размера материализованного представления. При наличии этой таблицы можно использовать встроенную процедуру ESTIMATE SUMMARY SIZE для оценки количества строк/байтов в материализованном представлении, которое предполагается создать. Я начну с удаления статистической информации (DELETE STATISTICS) о материализованном представлении SALES MV. Обычно пакету DBMS OLAP недоступно материализованное представление, размер которого оценивается, поэтому нам придется этот размер скрыть (в противном случае пакет DBMS OLAP получит точный ответ по словарю данных):

tkyte@TKYTE816> analyze table sales mv DELETE statistics; Table analyzed.

tkyte@TKYTE816> declare

2 num rows number;

3 num bytes number;

4 begin

5 dbms olap.estimate summary size

6 (SALES MV ESTIMATE,

7 select customer hierarchy.zip code,

8 time hierarchy.yyyy,

9 sum(sales.sales amount) sales amount

10 from sales, time hierarchy, customer hierarchy

11 where sales.trans date = time hierarchy.day

12 and sales.cust id = customer hierarchy.cust id

13 group by customer hierarchy.zip code, time hierarchy.mmyyyy,

14 num rows,

15 num bytes); 16

17 dbms output.put line(num rows M rows);

18 dbms output.put line(num bytes M bytes);

19 end;

20 /

409 rows 36401 bytes

PL/SQL procedure successfully completed.

Первый параметр процедуры - имя плана, под которым его надо запомнить в таблице планов. Это имя не имеет особого значения, но его надо задать в операторе DELETE FROM PLAN TABLE WHERE STATEMENT ID = SALES MV ESTIMATE no завершении эксперимента. Второй параметр - запрос, который будет использоваться для создания материализованного представления. Пакет DBMS OLAP проанализирует этот запрос на основе статистической информации о базовых таблицах, чтобы оценить размер этого объекта. Остальные два параметра предназначены для передачи результатов работы процедуры пакета DBMS OLAP - предполагаемого количества строк и байтов. Они получили значения 409 и 36401, соответственно. Теперь давайте подсчитаем реальные значения:

1119

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

tkyte@TKYTE816> analyze table sales mv COMPUTE statistics; Table analyzed.

tkyte@TKYTE816> select count(*) from sales mv; COUNT(*)

1250

tkyte@TKE816> select blocks *8* 1024

2 from user tables

3 where table name = SALES MV

4 / BLOCKS*8*1024

40960

Итак, процедура ESTIMATE SUMMARY SIZE дала хороший результат при оценке размера таблицы, но недооценила количество строк. Обычно с оценками так и происходит: какие-то параметры оцениваются верно, а какие-то - нет. Я бы использовал эту процедуру для грубой оценки предполагаемого размера объекта.

Проверка достоверности измерений

Эта процедура проверяет достоверность иерархий, входящих в указанное измерение. Так, в рассмотренном ранее примере она проверит, действительно ли значение CUST ID определяет значение ZIP CODE, а то, в свою очередь, определяет значение столбца REGION. Чтобы увидеть соответствующую процедуру в действии, создадим пример недостоверного измерения. Начнем с таблицы, содержащей строку для каждого дня года с атрибутами день, месяц и год:

tkyte@TKYTE816> create table time rollup

2 (day date,

3 mon number,

4 year number

Table created.

tkyte@TKYTE816> insert into time rollup

2 select dt, to char(dt,mm), to char(dt,yyyy)

3 from (select trunc(sysdate,year)+rovmum-l dt

4 from all objects where rownum < 366)

365 rows created.

Итак, мы создали развернутую информацию по дате, аналогично предыдущему примеру. На этот раз, однако, я не включил год в атрибут, представляющий месяц, - только две цифры, представляющие порядковый номер месяца в году. Если добавить в эту таблицу еще одну строку: