CUBE, ROLLUP и "материализованные" представления: "добывая" золото Oracle

Джон Джей Кинг, Oracle Magazine/RU Online, #8/2000

Введение

СУБД Oracle8i предоставляет новые средства, которые снижают ресурсоемкость запросов с суммированием (summary queries) и в целом значительно упрощают эти операции. Применение опций CUBE и ROLLUP во фразе GROUP BY запроса позволяет производить функционально более полные операции суммирования. CUBE и ROLLUP предоставляют информацию, которую в ином случае можно можно было получить только посредством дополнительных запросов или кодирования. Воспользовавшись "материализованными" представлениями (Materialized Views) можно предварительно создавать часто используемые таблицы итогов (to pre-build tables of summary data). Результаты типовых операций суммирования в материализованных представлениях могут быть проиндексированы, обеспечивая тем самым ускорение выполнения типовых запросов. Материализованные представления могут быть обновлены (refreshed ) тем же способом, что и снапшоты (Snapshots) с частотой нужной пользователям. Использование CUBE и ROLLUP в материализованных представлениях совместно с другими возможностями GROUP BY обеспечивает получение более полной информации при выполнении менее ресурсоемких запросов.

Все коды примеров этой статьи были протестированы с Oracle 8.1.5. Oracle8i Release 2 (Oracle version 8.1.6) должна выйти вскоре после написания этой статьи, новинки этого релиза только упоминаются, к сожалению, никакие примеры этих новинок недоступны в настоящее время. Некоторые из наиболее важных усовершенствований нового релиза Oracle8i - это аналитические ("Analytic") функции, позволяющие производить ранжирование (ranking), перенос сводных результатов (moving aggregates), сравнение данных за различные периоды (period comparisons), соотношение итогов (ratio of total), получение совокупных сводных результатов (cumulative aggregates), добавление фразы ORDER BY к определениям материализованных представлений и другие функции.

Фраза GROUP BY (без CUBE или ROLLUP)

Следующий запрос показывает "нормальную" функциональность GROUP BY. Оператор SQL сортирует детальные данные, используя столбцы, указанные в GROUP BY, затем вычисляет подытоги и результаты - одна строка для каждой группы.

SQL> select deptno Department
2 ,job
3 ,sum(sal) "Total SAL"
4 from emp
5 group by deptno,job
6 /

DEPARTMENT JOB Total SAL
---------- --------- ---------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

CUBE и ROLLUP во фразе GROUP BY

Деловым людям для принятия решений часто требуются статистическая информация из базы данных. Возрастающий интерес к добыче данных (data-mining - или же "интеллектуальный анализ данных" - ИАД) требует средств более высокого уровня их агрегирования. Oracle8i предоставляет опции CUBE и ROLLUP, чтобы расширить возможности GROUP BY посредством возможности выполнения следующих действий:

• ROLLUP строит агрегаты-подытоги (subtotal aggregates) на каждом запрошенном уровне, включая окончательные итоги (grand total);
  
• CUBE расширяет возможности ROLLUP для вычисления всех возможных комбинаций подытогов для некоторого заданного GROUP BY;
  
• данные для кросс-табличных (cross-tabulation) отчетов легко получить, используя CUBE.
  

Функция GROUP BY в стандарте языка SQL позволяет агрегацию (подведение подытогов) по некоторому заданному столбцу или набору столбцов. До появления Oracle8i в SQL-операторах требовалось применение JOIN или UNION для комбинирования информации подытогов (одна строка результата на группу строк детальных данных) и окончательных итогов (одна строка результата на весь набор строк) в одном SQL-запросе. ROLLUP позволяет создавать подытоги и окончательные итоги в одном и том же запросе совместно с получением промежуточных подытогов на каждом уровне агрегирования. CUBE добавляет информацию для кросс-табличных отчетов на основе столбцов группировки GROUP BY.

Все примеры в этой статье были созданы с предоставленными корпорацией Oracle таблицами примера SCOTT/TIGER. Выполните процедуру DEMOBLD со своим userid для создания личных копий этих таблиц. Все примеры должны работать как показано.
Некоторые из примеры используют SQL*Plus-команды COL и BREAK, чтобы результат
выглядел более презентабельно, так как его обычно преподносят начальству. Если вы используете для доступа к базе данных Oracle иное, чем SQL*Plus, средство, то такое форматирование может быть излишним.

Этот запрос вычисляет сумму зарплат для групп служащих, отсортированных по отделам и должностям. Например, сумма зарплат клерков (CLERKs) в отделе 10 (Department 10) вычисляется отдельно от суммы зарплат клерков в отделе 20. Заметьте, что сумма зарплат для всех должностей в отделе 10 не показана, так же как и общая сумма всех зарплат.

GROUP BY с опцией ROLLUP

ROLLUP обеспечивает агрегирование на каждом уровне, заданном столбцами в GROUP BY. Это приводит к получению информации, которую нельзя было получить раньше без дополнительного кодирования. Следующий оператор показывает это:

SQL> col Department format a20
SQL> break on Department
SQL> select nvl(to_char(deptno),'Whole Company') Department
2 ,nvl(job,'All Employees') job
3 ,sum(sal) "Total SAL"
4 from emp
5 group by rollup (deptno,job)
6 /

DEPARTMENT JOB Total SAL
------------- ---------- ---------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
All Employees 8750
20 ANALYST 6000
CLERK 1900
MANAGER 2975
All Employees 10875
30 CLERK 950
MANAGER 2850
SALESMAN 5600
All Employees 9400
Whole Company All Employees 29025

Отметим, что ROLLUP создает подытоги для каждого возможного уровня (Подытоги по отделам) и общий итог.

Например, международная фирма, производящая спортивные товары, вычисляет суммы продаж с использованием столбцов GROUP BY, а именно Country (Страна), Customer_ID (номер клиента), Product (Товар). GROUP BY стандартно вычислит агрегаты (подытоги) для каждой уникальной комбинации этих трех столбцок, показывая агрегаты для каждого товара заказанного каждым клиентом в каждой стране.

Country Customer_ID Product Sales
France FR1234 Tennis Balls 34,000

ROLLUP добавляет агрегаты, показывающие сумму продаж товаров в разрезе по странам и номерам клиентов, общая сумма продаж по странам и сумму продаж всех товаров.

Country Customer_ID Product Sales
France FR1234 Tennis Balls 34,000 -- produced by GROUP BY
France FR1234 100,000 -- Rollup produces total by country+customer
France 340,000 -- Rollup produces total by country
Total 1,000,345 -- Rollup produces Grand total (all records)

ROLLUP предоставляет полезную, высокого уровня обобщения, информацию такого типа, которую часто запрашивают управленцы. До ROLLUP, чтобы предоставить такую информацию, необходима была дополнительная обработка или ручная работа. Строки с подытогами и общими итогами, генерируемые ROLLUP, заменяют не представляемые NULL-значения столбцов при формировании выходных строк. Для того, чтобы заменить NULL-значения, выше была использована функция NVL. Проблема, связанная с применением этой техники, заключается в том, что, возможно, для некоторых столбцов естественны NULL-значения и, следовательно, эти NULL-значения должны быть сгруппированы со строками, формируемыми ROLLUP или CUBE.

Функция GROUPING

Для того, чтобы улучшить обработку NULL-значений в строках, созданных ROLLUP (и CUBE - это обсуждается позже), корпорация Oracle представила новую функцию GROUPING, которая возвращает значение 1, если строка - это подытог, созданная ROLLUP или CUBE, и 0 в противном случае. Пример ниже показывает запрос, уже использованный ранее, с функциями DECODE и GROUPING, применение которых позволяет более элегантно обрабатывать null-значения, созданные ROLLUP и CUBE. (Внимание: данные примера не содержат null-значения, результаты этого запроса и предыдущего одни и те же).

SQL> col Department format a20
SQL> break on Department
SQL> select decode(grouping(deptno),1,'Whole Company',
2 'Department ' || to_char(deptno)) Department
3 ,decode(grouping(job),1,'All Employees',job) job
4 ,sum(sal) "Total SAL"
5 from emp
6 group by rollup (deptno,job)
7 Input truncated to 1 characters
8 /
DEPARTMENT JOB Total SAL
------------- --------- ---------
Department 10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
All Employees 8750
Department 20 ANALYST 6000
CLERK 1900
MANAGER 2975
All Employees 10875
Department 30 CLERK 950
MANAGER 2850
SALESMAN 5600
All Employees 9400
Whole Company All Employees 29025

В этом примере подытоги по отделам и общие итоги теперь имеют больше специфических заголовков и значений. Так как ROLLUP генерирует строку с суммой зарплат всех служащих (независимо от должности) в отделе, в этом примере используется "All Employees" (Все служащие) чтобы показать этот подытог вместо наименования должности. Когда ROLLUP генерирует сумму зарплат по всем отделам (общий итог), столбец отдела покажет "Whole Company" (Вся компания) вместо номера конкретного отдела.

GROUP BY с опцией CUBE

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

SQL> col Department format a20
SQL> break on Department
SQL> select decode(grouping(deptno),1,'Whole Company',
2 'Department ' || to_char(deptno)) Department
3 ,decode(grouping(job),1,'All Employees',job) job
4 ,sum(sal) "Total SAL"
5 from emp
6 group by cube (deptno,job)
7 /

DEPARTMENT JOB Total SAL
------------- ------------- ---------
Department 10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
All Employees 8750
Department 20 ANALYST 6000
CLERK 1900
MANAGER 2975
All Employees 10875
Department 30 CLERK 950
MANAGER 2850
SALESMAN 5600
All Employees 9400
Whole Company ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
All Employees 29025

В ранее приведенном примере с CUBE вся информация, полученная при применении ROLLUP (предыдущий пример), соединяется при выводе с подытогами для каждой комбинации категорий (столбцов GROUP BY). Итоговые данные по зарплате для каждой профессии добавляются к уже полученным данным. Окончательные итоги приведены в категории "Whole Company" ("Вся компания"), так как в них учтены данные каждого служащего заданной профессии независимо от отдела.
Подобно ROLLUP опция CUBE создает подытоги для каждого уровня промежуточных итогов, а также окончательный итог. Помимо этого, итоги создаются для каждой комбинации категорий, перечисленных в столбцах GROUP BY. Если было три столбца GROUP BY (например, страна (country), номер клиента (customer_id), товар (product)), то GROUP BY должна производить агрегаты (подытоги) для каждой уникальной комбинации этих трех столбцов, показывая агрегированную информацию для каждого товара, купленного или заказанного каждым клиентом каждой страны. ROLLUP должна добавить агрегаты, показывающие итоговые данные по товарам в разрезе по странам и номерам клиентов, в разрезе по странам и окончательные итоговые данные по всем проданным товарам. CUBE должна добавить агрегированную информацию по каждому товару, независимо от страны и или номера клиента, агрегированную информацию по каждому номеру клиента, независимо от страны или заказанных товаров и информацию по каждому товару в разрезе страны независимо от номера клиента.

Country Customer_ID Product Sales
France FR1234 Tennis Balls 34,000 -- GROUP BY
France FR1234 100,000 -- Rollup produces
France 340,000 -- Rollup produces
Customer ID 135,000 -- Cube produces
Customer ID Tennis Balls 99,000 -- Cube produces
France Tennis Balls123,000 -- Cube produces
Total 1,000,345 -- Rollup produces

Информация, получаемая функцией CUBE полезна в кросс-табличных итоговых отчетах,
часто необходимых руководству.

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

Oracle-механизм снапшота (SNAPSHOT) - это таблица результатов некоторого запроса, которая периодически создается для облегчения распределения или репликации данных. Механизм "материализованное представление" (materialized view) в Oracle8i использует аналогичную технологию для хранения результатов некоторого представления, которые как бы материализованы в базе данных для использования последующими SQL-предложениями. Это материализованное представление периодически обновляется: по требованию или по временному критерию, заданному при создании представления. Данные представления являются "старыми", пока это представление не обновится. Для материализованных представлений могут быть определены индексы. Это идеальный механизм для улучшения производительности часто выполняемых отчетов, запускаемых для получения агрегированной информации.

create materialized view dept_summary
refresh start with sysdate next sysdate + 1
as
select dept.deptno,
dname,
count(*) nbr_emps,
sum(nvl(sal,0)) tot_sal
from scott.emp emp
,scott.dept dept
where emp.deptno(+) = dept.deptno
group by dept.deptno,dname;

Эта технология является расширением технологии снапшотов, впервые реализованной в Oracle 7. Механизм обновления и схемы индексирования надежны и эффективны. Далее приведено краткое описание параметров, более подробную информацию смотрите в документации по Oracle:

• ORACLE рекомендует, чтобы имена материализованных представлений не превосходили 19 символов, тогда полное сгенерированное имя не будет больше 30 символов;

• (нет в скрипте) физические атрибуты (PCTFREE, PCTUSED, INITRANS, MAXTRANS, etc...), TABLESPACE, LOB, CACHE, LOGGING, CLUSTER, и секционирование (partitioning) подобны атрибутам команд CREATE SNAPSHOT и CREATE TABLE;

• (нет в скрипте) BUILD IMMEDIATE действует по умолчанию, а BUILD DEFERRED помещает данные в представление только при обновлении;

• (нет в скрипте) ON PREBUILT TABLE разрешает использование материализованных представлений для существующих таблиц (очень удобно!). В этом случае имена материализованного представления и таблицы должны быть идентичны;

• REFRESH управляет частотой обновления, START WITH определяет время первого автоматического обновления и NEXT определяет время очередного обновления. Другие опции REFRESH включают: FAST, COMPLETE, FORCE, ON COMMIT, ON DEMAND, START WITH, NEXT, WITH PRIMARY KEY, WITH ROWID, USING ROLLBACK SEGMENT. FAST использует заранее определенный LOG и требует, чтобы запрос, создающий материализованное представление, соответствовал требованиям руководства Oracle8i Replication manual;
  

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

• ссылка на эту таблицу не может принадлежать пользователю "SYS",
  
• столбцы с атрибутом LONG не разрешены,
  
• представления, которые включают операции соединения (join) и GROUP BY не могут быть выбраны из IOT (Index-Organized Table). Отметьте, что в примере, когда имя таблицы полностью квалифицируется схемой владельца таблицы, это предлагается, но не требуется Oracle.
  

Запрос может включать GROUP BY, CUBE, ROLLUP, операции соединения, вложенные запросы (динамические представления) и почти любую другую конструцию;

Oracle8i Release 2 (8.1.6) позволяет запросу содержать фразу ORDER BY и for INSERT...SELECT в материализованном представлении, что также оговаривается ORDER BY.

Использование предварительно построенных таблиц

Определение материализованного представления на существующей таблице (ON PREBUILT TABLE) позволяет использовать существующие таблицы и индексы.

drop table dept_summary_tab;
drop snapshot dept_summary_tab;
create table dept_summary_tab
as
select dept.deptno
,dname
,count(*) nbr_emps
,sum(nvl(sal,0)) tot_sal
from scott.emp emp
,scott.dept dept
where emp.deptno(+) = dept.deptno
group by dept.deptno,dname;
create materialized view dept_summary_tab
on prebuilt table with reduced precision
refresh start with sysdate next sysdate + 1
as
select dept.deptno
,dname
,count(*) nbr_emps
,sum(nvl(sal,0)) tot_sal
from scott.emp emp
,scott.dept dept
where emp.deptno(+) = dept.deptno
group by dept.deptno,dname;

Использование фразы ON PREBUILT TABLE требует, чтобы исходная таблица и материализованное представление использовали одни и те же имя и схему. WITH REDUCED PRECISION позволяет обновлению работать должным образом, даже если некоторые столбцы генерируют значения с точностью, отличной от той, что была первоначально определена.
Для обновления существующей таблицы используйте предоставляемый корпорацией Oracle PL/SQL-пакет DBMS_MVIEW как показано ниже:
begin
dbms_mview.refresh('dept_summary_tab');
end;
/

Будьте осторожны! Эта процедура фиксирует (COMMITs) изменения активной транзакции как часть своей функциональности.
Далее приведен пример использования материализованного представления, определенного на базовой таблице:

select dname,ename,sal,sal/tot_sal pct_dept
from emp,dept_summary_tab
where emp.deptno = dept_summary_tab.deptno
order by dname
/

Заключение

В этой статье рассматриваются опции CUBE и ROLLUP (в Oracle8I) в фразе GROUP BY и материализованные представления. Использование CUBE и ROLLUP сокращает работу, необходимую для кодирования и создания агрегированной информации, часто требуемой руководством. Итоговые данные часто необходимы для поддержки принятия решений руководством. Использование CUBE и ROLLUP обеспечивает механизм для применения одного SQL-предложения, которое предоставляет данные, которые ранее можно было получить, используя множество SQL-предложений, программирование или ручное суммирование. Материализованные представления сокращают ресурсоемкость часто выполняемых запросов для получения агрегированной информации, сохраняя результаты и обновляя их на периодической основе. Используемые вместе, все эти средства могут применяться для "раскапывания" в базах данных Oracle "золотой" информации, которая столь часто необходима в наше время.

Об авторе

Джон Кинг (John Jay King) партнер King Training Resources, фирмы, осуществляющей с 1988 обучение в США и других странах. Джон работал с продуктами Oracle, начиная с Version 4, обучал разработчиков приложений еще Oracle Version 5. Его доклады звучали на различных конференциях, включая IOUG-A Live!, UKOUG Conference, EOUG Conference, ECO/SEOUC, RMOUG Training Days и ODTUG conference.