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

SUM(SAS AMOUNT)

13 rows selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=327 Bytes=850VALVE)

1 0 SORT (GROUP BY) (Cost=4 Card=327 Bytes=8502)

2 1 TABLE ACCESS (FULL) OF SALES MV (Cost=l Card=327 Bytes

Пока все отлично: сервер Oracle переписал запрос так, что используется представление SALES MV. Однако посмотрим, что произойдет при выполнении запроса, требующего более высокого уровня агрегирования:

tkyte@TKYTE816> set timing on

tkyte@TKYTE816> set autotrace on

tkyte@TKYTE816> select time hierarchy.qtr yyyy, sum(sales amount)

2 from sales, time hierarchy

3 where sales.trans date = time hierarchy.day

4 group by time hierarchy.qtr yyyy

QTR YYYY SUM(SALES AMOUNT)

Ql FY2001 9.2969E+11

Q1 FY2002 1.0200E+10

Q2 FY2001 9.3872E+11

Q3 FY2001 9.4832E+11

Q4 FY2001 9.3936E+11

Elapsed: 00:00:05.58

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8289 Card=5 Bytes=14)

1 0 SORT (GROUP BY) (Cost=8289 Card=5 Bytes=145)

2 1 NESTED LOOPS (Cost=169 Card=350736 Bytes=10171344)

3 2 TABLE ACCESS (FULL) OF SALES (Cost=169 Card=350736 В

4 2 INDEX (UNIQUE SCAN) OF SYS IOT TOP 30180 (UNIQUE)

1112 Глава 13

Statistics

0 recursive calls

15 db block gets 351853 consistent gets

Как видите, сервер Oracle не знает того, что знаем мы. Он еще не знает, что мог бы использовать материализованное представление для ответа на данный запрос, поэтому использует исходную таблицу SALES, проделывая огромный объем работы для получения ответа. То же самое получится и при запросе обобщенных данных за финансовый год.

С помощью объекта DIMENSION проинформируем сервер Oracle о том, что материализованное представление позволяет получить ответ и на эти запросы. Сначала создадим объект DIMENSION:

tkyte@TKYTE816> create dimension time hierarchy dim

2 level day is time hierarchy.day

3 level mmyyyy is time hierarchy.mmyyyy

4 level qtr yyyy is time hierarchy.qtr yyyy

5 level yyyy is time hierarchy.yyyy

6 hierarchy time rollup

8 day child of

9 mmyyyy child of

10 qtr yyyy child of

11 yyyy

12 )

13 attribute mmyyyy

14 determines mon yyyy;

Dimension created.

Этот оператор сообщает серверу Oracle, что столбец DAY таблицы TIME HIERARCHY определяет значение столбца MMYYYY, который, в свою очередь, определяет значение столбца QTR YYYY. Наконец, значение столбца QTR YYYY определяет значение столбца YYYY. Также утверждается, что столбцы MMYYYY и MON YYYYY - синонимы, между ними есть однозначное соответствие. Так что, когда сервер Oracle обнаруживает в запросе столбец MON YYYY, он обрабатывает запрос так же, как при использовании столбца MMYYYY. Теперь, когда серверу Oracle известна взаимосвязь данных, выполнение запроса существенно ускоряется:

tkyte@TKYTE816> set autotrace on

tkyte@TKYTE816> select time hierarchy.qtr yyyy, sum(sales amount)

2 from sales, time hierarchy

3 where sales.trans date = time hierarchy.day

4 group by time hierarchy.qtr yyyy

QTR YYYY SUM(SALES AMOUNT)

Q1 FY2001 9.2969E+11

Q1 FY2002 1.0200E+10

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

Q2 FY2001 9.3872E+11

Q3 FY2001 9.4832E+11

Q4 FY2001 9.3936E+11

Elapsed: 00:00:00.20

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=5 Bytes=195)

1 0 SORT (GROUP BY) (Cost=7 Card=5 Bytes=195)

2 1 HASH JOIN (Cost=6 Card=150 Bytes=5850)

3 2 VIEW (Cost=4 Card=46 Bytes=598)

4 3 SORT (UNIQUE) (Cost=4 Card=46 Bytes=598)

5 4 INDEX (FAST FULL SCAN) OF SYS IOT TOP 3 0180 (UNI

6 2 TABLE ACCESS (FOIL) OF SALES MV (Cost=1 Card=327 Byt

Statistics

0 recursive calls

16 db block gets

12 consistent gets

Мы сократили количество логических чтений с 350000 до 12 - не так уж плохо. Если в1полнить этот пример, различие будет заметно. Для выполнения первого запроса потребовалось некоторое время (около шести секунд), а вот ответ на второй оказался на экране раньше, чем я отпустил клавишу Enter (через две сотых доли секунды).

Для одной базовой исходной таблицы можно задавать сколько угодно иерархий с помощью DIMENSION. Давайте свяжем с кажды1м клиентом в таблице продаж атрибуты ZIP CODE (почтовый индекс) и REGION (регион):

tkyte@TKYTE816> create table customerhierarchy

2 (custid primary key, zipcode, region)

3 organization index

4 as

5 select cust id,

6 mod(rownum, 6) M to char(mod( rownum, 1000 ), fm0000) zip code,

7 mod(rownum, 6) region

8 from (select distinct cust id from sales)

Table created.

tkyte@TKYTE816> analyze table customer hierarchy compute statistics; Table analyzed.

Теперь пересоздадим материализованное представление так, чтобы значения SAS AMOUNT группировались по столбцам ZIP CODE и MMYYYY:

tkyte@TKYTE816> drop materialized view sales mv;

Materialized view dropped.

tkyte@TKYTE816> create materialized view sales mv

2 build immediate

3 refresh on demand