1108

Глава 13

но, лучше лишний раз перепроверить согласованность данн1х, если вы требуете от сервера Oracle им доверять.

Измерения

Использование измерений - еще один метод предоставления дополнительной информации серверу Oracle. Предположим, имеется таблица исходных данных с датами транзакций и идентификаторами клиентов. По дате транзакции в другой таблице можно найти детальную информацию о том, к какому месяцу относится транзакция, к какому кварталу финансового года и т.д. Теперь предположим, что создано материализованное представление для хранения агрегированной информации о продажах по месяцам. Может ли сервер Oracle использовать это представление, выполняя запрос о продажах за квартал или год? Да, мы знаем, что по дате транзакции можно получить месяц, по месяцу - квартал, по кварталу - год, так что - да, может. Серверу Oracle (пока) об этой взаимосвязи не известно, поэтому использовать представление он не будет.

С помощью объекта базы данных DIMENSION (измерение) можно сообщить серверу Oracle эти сведения о данных, чтобы он использовал для переписывания большего количества запросов. Измерение декларирует отношение главный/подчиненный между парами столбцов. С его помощью можно указать серверу Oracle что, в строке таблицы значение столбца MONTH определяет значение, которое окажется в столбце QTR, столбец QTR определяет значение, которое окажется в столбце YEAR и т.д. Используя измерение, можно создать материализованное представление, содержащее менее подробные сведения, чем исходные записи (например, итоговые данные по месяцам). Этот уровень агрегирования может оказаться более детальным, чем требуется в запросе (в запросе, скажем, требуются данные по кварталам), но сервер Oracle разберется, что для получения ответа можно использовать материализованное представление.

Вот простой пример. Создадим таблицу SALES для хранения даты транзакции, идентификатора клиента и общей суммы продаж. В этой таблице будет около 350000 строк. Другая таблица, TIME HIERARCHY, будет содержать соответствие даты транзакции месяцу, кварталу и году. При соединении этих двух таблиц можно получить агрегированные данные по месяцам, кварталам, годам и т.д. Аналогично, если имеется таблица, сопоставляющая идентификатор клиента с почтовым индексом, а почтовые индексы - с регионом, можно легко соединить эту таблицу с таблицей SALES для агрегирования данных по почтовому индексу или региону.

В обычной базе данных (без материализованных представлений и других специфических структур) эти действия можно выполнить, но это потребует много времени. Для каждой строки данных продаж придется выполнять чтение по индексу справочной таблицы (соединение вложенным циклом NESTED LOOP JOIN) для преобразования даты транзакции или идентификатора клиента в другое значение и последующего группирования результатов по этому значению. Вот тут и пригодится материализованное представление. Можно хранить итоговые данные по продажам, агрегированные, скажем, помесячно по датам транзакции и по почтовым индексам клиентов. Теперь обобщение данных поквартально или по регионам может выполняться очень быстро.

Начнем с создания таблицы SALES и загрузки в нее случайных тестовых данных, сгенерированных на основе представления ALL OBJECTS.

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

tkyte@TKYTE816> create table sales

2 (trans date date, cust id int, sales amount number);

Table created.

tkyte@TKYTE816> insert /*+ APPEND */ into sales

2 select trunc(sysdate,year)+mod(rownum,366) TRANS DATE,

3 mod(rownum,100) CUSTID,

4 abs(dbms random.random)/100 LESAMOUNT

5 from allobjects

21921 rows created. tkyte@TKYTE816> commit; Commit complete.

Эта исходная информация будет представлять данные за год. Я задаю столбец TRANS DATE как первый день года плюс число от 1 до 365. Значение CUST ID - число от 0 до 99. Общая сумма продаж - некоторое сравнительно большое число (год выдался хороший).

В моем представлении ALL OBJECTS содержится около 22000 строк, так что после чет1рех вставок, каждая из которых удваивает размер таблицы, мы получим около 350000 записей. Я использую подсказку /* + APPEND */. чтобы избежать генерации большого объема данных в журнал повторного выполнения:

tkyte@TKYTE816> begin

2 for i in 1 .. 4

3 loop

4 insert /*+ APPEND */ into sales

5 select trans date, cust id, abs(dbms random.random)/100

6 from sales;

7 commit;

8 end loop;

9 end; 10 /

PL/SQL procedure successfully completed. tkyte@TKYTE816> select count(*) from sales;

COUNT(*)

350736

Теперь необходимо создать таблицу TIME HIERARCHY, округляющую дату до месяца, года, квартала и т.д.:

tkyte@TKYTE816> create table time hierarchy

2 (day primary key, mmyyyy, mon yyyy, qtr yyyy, yyyy)

3 organization index

4 as

5 select distinct

6 trans date DAY,

7 cast (to char(trans date,mmyyyy) as number) MMYYYY,

1110

Глава 13

8 to char(trans date,mon-yyyy) MON YYYY,

9 Q М ceil( to char(trans date,mm)/3) М FY

10 to char(trans date,yyyy) QTR YYYY,

11 cast( to char( trans date, yyyy) as number ) YYYY

12 from sales

13 /

Table created.

В данном случае все просто. Мы сгенерировали столбцы:

MMYYYY - месяц и год;

MON YYYY - то же, но с сокращенным названием месяца;

QTR YYYY - квартал и год;

YYYY - год.

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

Теперь создадим материализованное представление SALES MV. Оно суммирует исходные продажи за месяц. Можно ожидать, что в полученном материализованном представлении будет примерно 1/30 общего количества строк таблицы SALES, если данные были равномерно распределены:

tkyte@TKYTE816> analyze table sales compute statistics;

Table analyzed.

tkyte@TKYTE816> analyze table time hierarchy confute statistics; Table analyzed.

tkyte@TKYTE816> create materialized view sales mv

2 build immediate

3 refresh on demand

4 enable query rewrite

5 as

6 select sales.cust id, sum(sales.sales amount) sales amount,

7 time hierarchy.mmyyyy

8 from sales, time hierarchy

9 where sales.trans date = time hierarchy.day

10 group by sales.cust id, time hierarchy.mmyyyy

11 /

Materialized view created. tkyte@TKYTE816> set autotrace on

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

2 from sales, time hierarchy

3 where sales.trans date = time hierarchy.day

4 group by time hierarchy.mmyyyy