19 марта 2001 г.

Using Materialized Views to Speed Up Queries, by Steve Bobrowski

Стив Бобровски

Источник: журнал Oracle Magazine, no.5, 1999 (http://www.oracle.com/oramag/oracle/99-Sep/59bob.html)

Просто cложите данные, и хорошее хранилище данных обеспечивает бизнес информацией, то есть фактами, необходимыми для принятия обоснованных и своевременных решений. Однако, для преобразования фактов в бизнес-знания часто необходимо выполнять сложные запросы, которые суммируют данные из нескольких детализированных (detail) таблиц, а этот процесс требует большого объема вычислений. До сих пор единственным решением было использование сводных таблиц в хранилище данных, но этот подход имеет свои недостатки. В Oracle8i появилась новая возможность – создание материализованных представлений, суммирующих данные, которые могут существенно улучшить выполнение запросов к хранилищам данных. Перед тем, как углубиться в детали, полезно взглянуть на некоторые проблемы, связанные со сводными таблицами в хранилищах данных.

Одним из наиболее общих типов запросов в системах поддержки принятия решений (decision-support system - DSS) или хранилищах данных является запрос, возвращающий сводную: или сгруппированную или подробную информацию, такую как сумма, среднее или количество. Например, финансовый аналитик по прогнозам продаж может запросить общий доход от продаж, полученный в каждом из предыдущих 12 месяцев. Или специалист по изучению рынков сбыта, оценивая новую рекламную кампанию, может выдать запрос для поиска всех заказов, размещенных правительственными организациями в прошлом месяце. Запросы, суммирующие данные, являются очень важными, поскольку объединяют подробную информацию индивидуальных транзакций и представляют их в удобной форме, которая показывает общее направление и помогает пользователям принимать обоснованные решения.

К сожалению, для выполнения сводных запросов могут потребоваться существенные накладные расходы при обработке данных, поскольку при этом сканируются огромные детальные таблицы в хранилище данных. Результатом является недопустимо медленное время ответа (много минут и даже часов). Вместе с тем выполнение других запросов оценивается как хорошее.

Чтобы лучше понять проблему, рассмотрим следующий запрос, который вычисляет сумму заказа для каждого заказа на закупку, используя детальные данные в связанных таблицах фактов и размеров в типичной схеме ввода заказа (PARTS, ORDERS, ITEMS):

SELECT i.ord_ord_id AS order_id,

MAX(TO_CHAR(orderdate,'MonthYYYY'))

AS orderdate,

SUM(i.quantity * p.unitprice) AS total

FROM orders o, items i, parts p

WHERE o.ord_id = i.ord_ord_id

AND p.part_id = i.part_part_id

GROUP BY i.ord_ord_id;

ORDER_ID ORDERDATE TOTAL

-------- ----------- --------

1 Август 1999 7768.25

2 Август 1999 3000.2

3 Август 1999 4623.5

4 Август 1999 1207.5

. . .

В отчете, выданном командой EXPLAIN PLAN, показаны операции, участвующие в выполнении этого запроса, и их стоимость (относительное значение, оцениваемое оптимизатором):

OPERATION COST

------------------------------- ------

SORT GROUP BY 354

HASH JOIN 252

HASH JOIN 148

TABLE ACCESS FULL parts 10

TABLE ACCESS FULL items 580

TABLE ACCESS FULL orders 311

Если таблицы содержат много записей, то стоимость этого конкретного сводного запроса может быть весьма существенной. Чтобы избежать затрат на вычисление сводной информации на лету (например, каждый раз, когда аналитик ее запрашивает) в схемах хороших хранилищ данных существуют сводные таблицы. Сводная таблица хранит результаты одного или нескольких заранее вычисленных запросов, часто запрашиваемых пользователями хранилища данных. Например, можно создать таблицу SALES_SUMMARY, которая содержит идентификатор заказа, дату и сумму для каждого заказа из таблицы ORDERS, и затем каждое приложение, которое вы создадите, может использовать таблицу SALES_SUMMARY, вместо детальных таблиц, получая необходимую информацию с гораздо меньшими издержками:

SELECT order_id, orderdate, total

FROM sales_summary;

ORDER_ID ORDERDATE TOTAL

-------- ----------- --------

1 Август 1999 7768.25

2 Август 1999 3000.2

3 Август 1999 4623.5

4 Август 1999 1207.5

. . .

В результате использования сводной таблицы мы получаем гораздо более простой план выполнения запроса с меньшей стоимостью, чем без нее:

OPERATION COST

---------------------------------- -------

TABLE ACCESS FULL sales_summary 31

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

Чтобы помочь решить проблему управления сводными таблицами, сохраняя при этом высокую производительность - Oracle8i представляет несколько новых возможностей:

Материализованное представление является ядром всех этих новых возможностей. В Oracle8i необходимо реализовать каждую сводную таблицу в хранилище данных как материализованное представление. Материализованное представление похоже на моментальный снимок таблицы (table snapshot). Например, вы обновляете материализованное представление, чтобы поддерживать актуальность сводной информации. Материализованное представление также похоже на индекс: после того как материализованное представление создано, база данных может сама использовать его для более быстрого выполнения запросов, обращающихся к детальным таблицам, лежащим в его основе.

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

Перед созданием материализованного представления необходимо установить несколько параметров сервера. В таблице 1 перечислены все параметры; дано их краткое описание, и рекомендованы значения для обновления сводной информации, переписывания запроса, и последующего анализа. Как нужно отредактируйте файл параметров сервера (init.ora) и затем перезапустите экземпляр, чтобы использовались новые настройки.

Oracle8i предоставляет некоторые новые привилегии базы данных для создания и использования материализованных представлений, а также для обеспечения возможности переписывания запроса для материализованных представлений. Предположим, что вы создали материализованное представление у того же пользователя (в той же схеме), который является владельцем лежащих в основе базовых таблиц (рекомендуемая конфигурация). Этому пользователю нужно дать системные привилегии CREATE MATERIALIZED VIEW, CREATE TABLE, CREATE VIEW и QUERY REWRITE. Ему необходима также существенная квота табличного пространства для хранения материализованного представления, точная квота колеблется в зависимости от размеров сводной таблицы.

Для создания материализованного представления используйте SQL-команду CREATE MATERIALIZED VIEW. Следующая ниже команда создает простое материализованное представление, содержащее сводную информацию, вычисляемую из данных детальных связанных таблиц ORDERS, ITEMS, и PARTS:

CREATE MATERIALIZED VIEW sales_summary

BUILD IMMEDIATE

REFRESH

COMPLETE

ON DEMAND

ENABLE QUERY REWRITE

AS

SELECT i.ord_ord_id AS order_id,

MAX(TO_CHAR(orderdate,'MonthYYYY')) AS orderdate,

SUM(i.quantity * p.unitprice) AS total

FROM orders o, items i, parts p

WHERE o.ord_id = i.ord_ord_id

AND p.part_id = i.part_part_id

GROUP BY i.ord_ord_id;

Обратите внимание на следующие ключевые слова в приведенном выше предложении:

Возможность переписывания запроса в Oracle8i связана с оптимизатором Oracle, основанным на стоимости. Следовательно, необходимо убедиться, что статистика для всех таблиц и материализованных представлений в схеме хранилища данных является актуальной. Для обновления статистики можно выдавать индивидуальную команду ANALYZE для каждого объекта схемы, после того как в него загружены новые данные, либо обновлять статистику оптимизатора для всех объектов схемы, используя процедуру ANALYZE_SCHEMA пакета DBMS_UTILITY. Следующий PL/SQL блок обновляет статистику для всех объектов схемы SALES_APP, включая детальные таблицы и материализованные представления, используемые в примере:

BEGIN

dbms_utility.analyze_schema('SALES_APP','ESTIMATE',15);

END;

Теперь можно использовать команду EXPLAIN PLAN для проверки работы материализованного представления и убедиться в том, что Oracle8i будет переписывать сводный запрос таким образом, чтобы использовалось материализованное представление. Запрос, приведенный в Листинге 1, обращается к детальным таблицам ORDERS, ITEMS, и PARTS. План выполнения показывает, что Oracle8i автоматически переписал запрос, чтобы использовать преимущества материализованного представления SALES_SUMMARY, которое содержит данные, необходимые для выполнения запроса.

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

Для обновления данных в материализованном представлении вручную, для того, чтобы оно отображало самую свежую информацию из детальных таблиц, можно вызвать процедуры REFRESH, REFRESH_ALL_MVIEWS, или REFRESH_DEPENDENT пакета DBMS_MVIEW. Следующий PL/SQL блок обновляет материализованное представление SALES_SUMMARY в схеме SALES_APP.

BEGIN

dbms_mview.refresh('SALES_APP.SALES_SUMMARY', 'A');

END;

Когда важные решения бизнеса зависят от информации, находящейся в вашем хранилище данных, то чем быстрее пользователи могут добраться до этой информации, тем лучше. Сводные таблицы обеспечивают быстрый доступ к заранее вычисленным запросам, но ими достаточно сложно управлять. Материализованные представления Oracle8i упрощают управление сводными таблицами и в то же время они ускоряют обработку запроса. Кроме того, улучшение является прозрачным – запросы переписываются автоматически таким образом, чтобы использовать материализованные представления для более быстрой обработки.

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

Стив Бобровский является главным администратором компании Animated Learning/The Database Domain (http://www.dbdomain.com/). Он также является автором книг “Mastering Oracle7 and Client/Server Computing” (“Овладение Oracle7 и вычисления клиент/сервер”), издательство Sybex, 1996; “Oracle8 Architecture” (“Архитектура Oracle8”), издательство Oracle Press, 1997; и “Oracle8i for Windows NT Starter Kit” (“Oracle8i для Windows NT. Руководство для начинающих”), издательство Oracle Press, 1999.

Листинг 1

Это предложение EXPLAIN PLAN и запрос на его вывод из таблицы PLAN_TABLE объясняют, как Oracle8i перезаписывает запрос, адресуемый к детальным таблицам, чтобы получить преимущества от использования маитериализованных представлений.

EXPLAIN PLAN

SET STATEMENT_ID = '1.0'

INTO plan_table

FOR

SELECT i.ord_ord_id AS order_id,

MAX(TO_CHAR(orderdate,'MonthYYYY')) AS orderdate,

SUM(i.quantity * p.unitprice) AS total

FROM orders o, items i, parts p

WHERE o.ord_id = i.ord_ord_id

AND p.part_id = i.part_part_id

GROUP BY i.ord_ord_id;

SELECT LPAD(' ',2*LEVEL)||operation||' '

||options||' '||object_name AS exec_plan,

cost

FROM plan_table

WHERE statement_id = '1.0'

CONNECT BY PRIOR id = parent_id AND statement_id = '1.0'

START WITH id = 1;

OPERATION COST

--------------------------------- -------

TABLE ACCESS FULL sales_summary 31