Использование материализованных представлений для ускорения запросов

Стив Бобровски, Russian Oracle Internet Magazine

Использование материализованных представлений (materialized view)в Oracle8i позволяет одновременно управлять сводной информацией в хранилище данных и ускорять выполнение запросов.

Источник: журнал 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 представляет несколько новых возможностей:

• Новый объект схемы данных – материализованное представление, которое может быть использовано для данных сводных таблиц;
• Расширение возможностей оптимизатора, позволяющее ему автоматически переписывать запрос таким образом, чтобы он использовал материализованные представления;
• Встроенный пакет DBMS_MVIEW, включающий процедуры обновления и другие процедуры для управления материализованными представлениями;
• Средства для анализа эффективности существующих и потенциальных материализованных представлений.

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

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

• Шаг 1. Установите параметры сервера.
• Шаг 2. Дайте привилегии соответствующей схеме.
• Шаг 3. Создайте материализованное представление.
• Шаг 4. Обновите статистику оптимизатора.
• Шаг 5. Убедитесь, что материализованное представление используется.
• Шаг 6. Вручную обновите материализованное представление.

Шаг 1. Установите параметры сервера

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

Шаг 2. Дайте привилегии соответствующей схеме

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

Шаг 3. Создайте материализованное представление

Для создания материализованного представления используйте 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;

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

• Предложение BUILD IMMEDIATE заполняет материализованное представление во время его создания (значение по умолчанию). Альтернативное предложение BUILD DEFERRED создает только структуру; заполнить материализованное представление можно позже, используя пакет DBMS_MVIEW. Используйте BUILD DEFERRED для заполнения материализованного представления по окончании рабочего дня, чтобы избежать влияния на обычные операции.
• Предложение REFRESH указывает, как Oracle8i обновляет данные материализованного представления. В приведенном примере полные (COMPLETE) обновления являются единственным вариантом. Возможны также быстрые (FAST) (инкрементные) обновления, однако для них существует ряд ограничений и предостережений, подробнее смотрите в руководстве Настройка Oracle8i правила, управляющие быстрыми обновлениями материализованных представлений.
• В предложении REFRESH также указывается, когда Oracle8i обновляет данные материализованного представления. В этом примере, материализованное представление будет обновляться по требованию (ON DEMAND) – только, когда вы явно обновляете его, используя пакет DBMS_MVIEW. Если объявленный запрос материализованного представления содержит сводные показатели и контрольные цифры только одной таблицы, или если этот запрос соединяет данные из нескольких таблиц, но не содержит никаких сводных показателей, то Oracle8i может автоматически обновлять представление при фиксации транзакции (ON COMMIT) – каждый раз когда выполняется фиксация транзакции для мастер таблицы (таблиц) представления.
• Опцию QUERY REWRITE необходимо включить, чтобы оптимизатор Oracle8i переписывал запросы таким образом, чтобы использовались материализованные представления. (Этот параметр можно также установить позднее, используя SQL-команды ALTER SYSTEM или ALTER SESSION.)
• Предложение AS описывает столбцы и строки материализованного представления, используя определяющий запрос (ничем не отличающийся от запроса в стандартном представлении или моментальном снимке). Определяющие запросы для материализованных представлений, используемых в хранилище данных (для сводных таблиц), будут вычислять один или несколько сводных показателей часто используемых аналитиками.

Шаг 4. Обновите статистику оптимизатора

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

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

Шаг 5. Убедитесь, что материализованное представление работает, и запрос переписывается

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

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

Шаг 6. Обновление материализованных представлений вручную

Для обновления данных в материализованном представлении вручную, для того, чтобы оно отображало самую свежую информацию из детальных таблиц, можно вызвать процедуры 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 упрощают управление сводными таблицами и в то же время они ускоряют обработку запроса. Кроме того, улучшение является прозрачным – запросы переписываются автоматически таким образом, чтобы использовать материализованные представления для более быстрой обработки.

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

• Dimensions, которые помогают Oracle8i выполнять продвинутые виды переписывания запросов
• Консультант по сводным данным (summary advisor) (пакет DBMS_OLAP), который помогает определить, какие запросы используют существующие материализованные представления и рекомендовать создание других материализованных представлений для повышения производительности конкретных запросов.

Стив Бобровский является главным администратором компании 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: Параметры сервера для управления и анализа сводной информации.

Листинг 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