1114

Глава 13

4 enable query rewrite

5 as

6 select customer hierarchy.zip code,

7 time hierarchy.mnyyyy,

8 sum(sales.sales amount) sales amount

9 from sales, time hierarchy, customer hierarchy

10 where sales.trans date = time hierarchy.day

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

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

13 /

Materialized view created.

Выполнив запрос, который выдает данные по продажам, сгруппированные по столбцам ZIPCODE и MMYYYY, можно убедиться, что их выполнения используется это материализованное представление:

tkyte@TKYTE816> set autotrace

tkyte@TKYTE816> select customer hierarchy.zip code,

8 /

time hierarchy.mmyyyy, sum(sales.sales amount) sales amount from sales, time hierarchy, customer hierarchy where sales.trans date = tiroe hierarchy.day

and sales.cust id = customer hierarchy.cust id group by customer hierarchy.zip code, time hierarchy.mmyyyy

1250 rows selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=l Card=409 Bytes=204

1 0 TABLE ACCESS (FULL) OF SALES MV (Cost=1 Card=409 Bytes=2 Statistics

28 recursive calls

12 db block gets

120 consistent gets

Однако если запросить информацию на другом уровне агрегирования (обобщая MMYYYY до YYYY и ZIP CODE до REGION), окажется, что сервер не счел возможным использовать материализованное представление:

tkyte@TKYTE816> select customer hierarchy.region,

2 time hierarchy.yyyy,

3 sum(sales.sales amount) sales amount

4 from sales, time hierarchy, customer hierarchy 5 where sales.trans date = time hierarchy.day

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

7 group by customer hierarchy.region, time hierarchy.yyyy

9 rows selected.

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

1115

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8289 Card=9 Bytes=26

1 0 SORT (GROUP BY) (Cost=8289 Card=9 Bytes=261)

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

3 2 NESTED LOOPS (Cost=169 Card=350736 Bytes=6663984)

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

5 3 INDEX (UNIQUE SCAN) OF SYS IOT TOP 30185 (UNIQUE)

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

Statistics

0 recursive calls 15 db block gets

702589 consistent gets

Сервер учел имеющееся измерение по времени, но у него отсутствует информация о том, как соотносятся столбцы CUST ID, ZIP CODE и REGION в таблице CUSTOMER HIERARCHY. Чтобы исправить это, пересоздадим измерение так, чтобы оно включало две иерархии: одну для таблицы TIME HIERARCHY, а другую - для CUSTOMER HIERARCHY:

tkyte@TKYTE816> drop dimension time hierarchy dim

2 /

Dimension dropped.

tkyte@TKYTE816> create dimension sales dimension

10 11 12 13 14 15 16 17 18 19

cust id child of zip code child of

region

hierarchy time rollup day child of

mmyyyy child of qtr yyyy child of yyyy

attribute mmyyyy determines mon yyy.

Dimension created.

1116

Глава 13

Мы удалили исходную иерархию по времени и создали новое, более информативное измерение, описывающее все существенные взаимосвязи. Теперь сервер Oracle поймет , что по созданному представлению SALES MV можно ответить на многие другие запросы. Например, если еще раз запросить регионы по годам :

tkyte@TKYTE816> select customer hierarchy.region,

2 time hierarchy.yyyy,

3 sum(sales.sales amount) sales amount

4 from sales, time hierarchy, customer hierarchy

5 where sales.trans date = time hierarchy.day

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

7 group by customer hierarchy.region, time hierarchy.yyyy

REGION

YYYY SALES AMOUNT

2001 2002

2001

2001

2002 2001

2001

2002 2001

5.9598E+11 3123737106 6.3789E+11 6.3903E+11 3538489159 6.4069E+11 6.3885E+11 3537548948

6.0365E+11

9 rows selected.

Execution Plan

SELECT STATEMENT Optimizer=CHOOSE (Cost=ll Card=9 Bytes=576) SORT (GROUP BY) (Cost=11 Card=9 Bytes=576)

HASH JOIN (Cost=9 Card=78 Bytes=4992)

HASH JOIN (Cost=6 Card=78 Bytes=4446) VIEW (Cost=3 Card=19 Bytes=133) SORT (UNIQUE) (Cost=3 Card=19 Bytes=133) INDEX (FAST FULL SCAN) OF SYS IOT TOP 30180 (U

TABLE ACCESS (FULL) OF SALES MV (Cost=1 Card=4 0 9 В

VIEW (Cost=3 Card=100 Bytes=700) SORT (UNIQUE) (Cost=3 Card=100 Bytes=700) INDEX (FULL SCAN) OF SYS IOT TOP 30185 (UNIQUE)

Statistics

0 recursive calls

16 db block gets

14 consistent gets

Оказывается, что сервер Oracle смог использовать обе иерархии измерения и в1пол-нил запрос к материализованному представлению. Благодаря созданным измерениям он выполнил простой поиск преобразования значения столбца CUST ID в REGION (поскольку значение CUST ID определяет значение ZIP CODE, а оно, в свою очередь,