В этой статье я исследую опции ROLLUP, CUBE и GROUPING SETS, используемые в части GROUP BY предложения SELECT.
Для экспериментов выберем системное вью all_objects, в котором нас будут интересовать столбцы owner - владелец объекта, и object_type - тип объекта. А чтобы сделать результаты экспериментов легко обозримыми, ограничимся двумя владельцами объектов, и скопируем данные только для двух владельцев в собственную таблицу:
CREATE TABLE the_objects
AS SELECT * FROM all_objects where owner in ('SYSTEM','SCOTT');
Для начала исследуем данные, пользуясь старыми добрыми агрегатными функциями и GROUP BY:
-- запрос 1
SELECT
COUNT(*) total, -- сколько всего объектов
COUNT(DISTINCT owner) owner#, -- скольким владельцам они принадлежат
COUNT(DISTINCT object_type) type# -- к скольким разным типам они относятся
FROM the_objects;
TOTAL OWNER# TYPE#
---------- ---------- ----------
511 2 12
-- запрос 2
SELECT
owner,
COUNT(*) ownerobj# -- сколько объектов у каждого владельца
FROM the_objects
GROUP BY owner;
OWNER OWNEROBJ#
--------------- ----------
SYSTEM 505
SCOTT 6
-- запрос 3
SELECT
object_type,
COUNT(*) typeobj# -- сколько объектов каждого типа
FROM the_objects
GROUP BY object_type;
OBJECT_TYPE TYPEOBJ#
--------------------- ----------
INDEX PARTITION 52
SEQUENCE 20
TABLE PARTITION 39
PROCEDURE 1
PACKAGE 1
PACKAGE BODY 1
TRIGGER 2
TABLE 161
INDEX 213
SYNONYM 8
VIEW 12
TYPE 1
12 rows selected
-- запрос 4
SELECT
owner,
object_type,
COUNT(*) obj# -- сколько объектов каждого типа у каждого владельца
FROM the_objects
GROUP BY owner, object_type
ORDER BY owner, object_type;
OWNER OBJECT_TYPE OBJ#
--------------- --------------------- ----------
SCOTT INDEX 2
SCOTT TABLE 4
SYSTEM INDEX 211
SYSTEM INDEX PARTITION 52
SYSTEM PACKAGE 1
SYSTEM PACKAGE BODY 1
SYSTEM PROCEDURE 1
SYSTEM SEQUENCE 20
SYSTEM SYNONYM 8
SYSTEM TABLE 157
SYSTEM TABLE PARTITION 39
SYSTEM TRIGGER 2
SYSTEM TYPE 1
SYSTEM VIEW 12
14 rows selected
-- запрос 5
SELECT
object_type,
owner,
COUNT(*) -- сколько объектов каждого типа у каждого владельца
FROM the_objects
GROUP BY object_type, owner
ORDER BY object_type, owner;
Стоп! Последний запрос дает нам ту же самую информацию, что и запрос 4). В самом деле, его можно записать так:
-- запрос 6
SELECT
owner,
object_type,
COUNT(*) -- сколько объектов каждого типа у каждого владельца
FROM the_objects
GROUP BY object_type, owner
ORDER BY owner, object_type;
И тогда его результат совсем не отличается от результата выполнения запроса 4.
Возьмем на заметку, что от перестановки элементов в списке GROUP BY результат не изменяется!
Итак, были написаны и выполнены 4 разных предложения SELECT, чтобы собрать информацию о
- общем кол-ве объектов (запрос 1)
- кол-ве владельцев (запрос 1)
- кол-ве типов объектов (запрос 1)
- кол-ве объектов у каждого владельца (запрос 2)
- кол-ве объектов каждого типа (запрос 3)
- кол-ве объектов каждого типа у каждого владельца (запрос 4)
Это вообще все вопросы о кол-ве, которые можно задать, рассматривая объекты в разрезе двух выбранных признаков, owner и object_type, и их сочетаний.
Что же новго предлагают опции ROLLUP, CUBE и GROUPING SETS?
Используя ROLLUP в части GROUP BY предложения SELECT, мы, одновременно с агрегированием данных по столбцам списка GROUP BY,
получаем итоговые (общие и промежуточные) количества. Пример:
-- запрос 7
SELECT
owner,
COUNT(*) ownerobj# -- сколько объектов у каждого владельца
FROM the_objects
GROUP BY ROLLUP(owner);
OWNER OWNEROBJ#
--------------- ----------
SCOTT 6
SYSTEM 505
511
Добавилась строка с общим количеством объектов! То есть, одним запросом получены данные, для получения которых ранее понадобилось два запроса, а именно, запросы 1 и 2.
-- запрос 8
SELECT
owner,
object_type,
COUNT(*) obj#
FROM the_objects
GROUP BY ROLLUP(owner, object_type)
ORDER BY owner, object_type;
OWNER OBJECT_TYPE OBJ#
--------------- --------------------- ----------
SCOTT INDEX 2
SCOTT TABLE 4
SCOTT 6
SYSTEM INDEX 211
SYSTEM INDEX PARTITION 52
SYSTEM PACKAGE 1
SYSTEM PACKAGE BODY 1
SYSTEM PROCEDURE 1
SYSTEM SEQUENCE 20
SYSTEM SYNONYM 8
SYSTEM TABLE 157
SYSTEM TABLE PARTITION 39
SYSTEM TRIGGER 2
SYSTEM TYPE 1
SYSTEM VIEW 12
SYSTEM 505
511
17 rows selected
Добавились строки с количеством объектов у каждого владельца и общим количеством объектов! То есть, одним запросом получены данные, для получения которых ранее понадобилось три запроса, а именно, запросы 1, 2 и 4.
Результат выполнения запроса 8 можно получить, объединив результаты трех запросов:
-- запрос 9
SELECT NULL, NULL, COUNT(*) FROM the_objects -- сколько всего объектов
UNION ALL
SELECT owner, NULL, COUNT(*) -- сколько у каждого владельца
FROM the_objects GROUP BY owner
UNION ALL
SELECT owner, object_type, COUNT(*) -- сколько объектов каждого типа у каждого владельца
FROM the_objects GROUP BY owner, object_type
ORDER BY 1, 2;
Поменяем порядок столбцов в списке ROLLUP:
-- запрос 10
SELECT
owner,
object_type,
COUNT(*)
FROM the_objects
GROUP BY ROLLUP(object_type, owner)
ORDER BY owner, object_type;
OWNER OBJECT_TYPE COUNT(*)
--------------- --------------------- ----------
SCOTT INDEX 2
SCOTT TABLE 4
SYSTEM INDEX 211
SYSTEM INDEX PARTITION 52
SYSTEM PACKAGE 1
SYSTEM PACKAGE BODY 1
SYSTEM PROCEDURE 1
SYSTEM SEQUENCE 20
SYSTEM SYNONYM 8
SYSTEM TABLE 157
SYSTEM TABLE PARTITION 39
SYSTEM TRIGGER 2
SYSTEM TYPE 1
SYSTEM VIEW 12
INDEX 213
INDEX PARTITION 52
PACKAGE 1
PACKAGE BODY 1
PROCEDURE 1
SEQUENCE 20
SYNONYM 8
TABLE 161
TABLE PARTITION 39
TRIGGER 2
TYPE 1
VIEW 12
511
27 rows selected
Теперь, вместо промежуточных итоговых кол-в объектов у каждого владельца, мы получили промежуточные итоговые кол-ва объектов каждого типа! (Что соответствует запросу 3.)
Запрос 8 формирует промежуточные итоговые кол-ва в разрезе владельцев,
потому что столбец owner в списке параметров ROLLUP идет первым, а
запрос 10 формирует промежуточные итоговые кол-ва в разрезе типов объектов,
поскольку в списке параметров ROLLUP идет первым столбец owner_type.
Значит, перестановка столбцов в списке параметров ROLLUP, в отличие от списка
GROUP BY, влияет на результат!
А можно ли одним запросом (не прибегая к UNION ALL) получить
- общее количество объектов,
- промежуточные итоговые кол-ва в разрезе владельцев,
- промежуточные итоговые кол-ва в разрезе типов объектов,
- кол-ва объектов каждого типа у каждого владельца?
Оказывается, можно. Функция CUBE выполняет агрегирование данных по всем
возможным группировкам для переданного ей списка столбцов. То есть, искомый
результат дает следующий запрос:
-- запрос 11
SELECT
owner,
object_type,
COUNT(*)
FROM the_objects
GROUP BY CUBE(object_type, owner);
OWNER OBJECT_TYPE COUNT(*)
--------------- --------------------- ----------
511
SCOTT 6
SYSTEM 505
TYPE 1
SYSTEM TYPE 1
VIEW 12
SYSTEM VIEW 12
INDEX 213
SCOTT INDEX 2
SYSTEM INDEX 211
TABLE 161
SCOTT TABLE 4
SYSTEM TABLE 157
PACKAGE 1
SYSTEM PACKAGE 1
SYNONYM 8
SYSTEM SYNONYM 8
TRIGGER 2
SYSTEM TRIGGER 2
SEQUENCE 20
SYSTEM SEQUENCE 20
PROCEDURE 1
SYSTEM PROCEDURE 1
PACKAGE BODY 1
SYSTEM PACKAGE BODY 1
INDEX PARTITION 52
SYSTEM INDEX PARTITION 52
TABLE PARTITION 39
SYSTEM TABLE PARTITION 39
29 rows selected
Отметим, что в случае CUBE, от порядка столбцов в списке результат снова не зависит.
Поскольку и для (owner, object_type) и для (object_type, owner) множество всех возможных
подмножеств (подгрупп) одно и то же:
- {пустое множество} -- соответствует общему итоговому кол-ву объектов
- {owner} -- соответствует кол-ву объектов в разрезе владельцев
- {object_type} -- соответствует кол-ву объектов в разрезе типов объектов
- {owner, object_type} -- соответствует кол-ву объектов в разрезе (владелец, тип объекта)
Я только что использовал математическую нотацию для множеств - фигурные скобки, чтобы напомнить, что в математике множества {owner, object_type} и {object_type, owner} являются одним и тем же множеством.
Итак,
-
GROUP BYвыполняет агрегирование данных в разрезе списка столбцов -
ROLLUPвыполняет агрегирование данных в разрезе некоторых (не всех) подмножеств множества столбцов, причем выбор работающих подмножеств определяется порядком следования столбцов в списке -
CUBEвыполняет агрегирование данных в разрезе всех подмножеств множества столбцов
А что же делает GROUPING SETS?
GROUPING SETS позволяет автору запроса явно перечислить интересующие его группировки
(подмножества), по которым Oracle выполнит агрегирование данных.
Пусть мы хотим получить кол-ва объектов в разрезе владельцев и, отдельно, кол-ва
объектов в разрезе типов объектов. Для этого, без использования GROUPING SETS, нам
пришлось бы выполнить следующий запрос:
-- запрос 12
SELECT owner, COUNT(*) FROM the_objects GROUP BY owner
UNION
SELECT object_type, COUNT(*) FROM the_objects GROUP BY object_type;
Используя GROUPING SETS, тот же результат можно получить так:
-- запрос 13
SELECT
owner,
object_type,
COUNT(*)
FROM the_objects
GROUP BY GROUPING SETS ((object_type), (owner));
OWNER OBJECT_TYPE COUNT(*)
--------------- --------------------- ----------
INDEX PARTITION 52
SEQUENCE 20
TABLE PARTITION 39
PROCEDURE 1
PACKAGE 1
PACKAGE BODY 1
TRIGGER 2
TABLE 161
INDEX 213
SYNONYM 8
VIEW 12
TYPE 1
SYSTEM 505
SCOTT 6
14 rows selected
Для того, чтобы включить в резльтат запроса общее итоговое кол-во объектов в таблице,
в качестве списка нужно указать NULL:
-- запрос 14
SELECT
owner,
object_type,
COUNT(*)
FROM the_objects
GROUP BY GROUPING SETS ((object_type), (owner), NULL);
OWNER OBJECT_TYPE COUNT(*)
--------------- --------------------- ----------
INDEX PARTITION 52
SEQUENCE 20
TABLE PARTITION 39
PROCEDURE 1
PACKAGE 1
PACKAGE BODY 1
TRIGGER 2
TABLE 161
INDEX 213
SYNONYM 8
VIEW 12
TYPE 1
SYSTEM 505
SCOTT 6
511
15 rows selected
Кстати, с GROUPING SETS можно использовать один и тот же список больше одного раза:
-- запрос 15
SELECT
owner,
COUNT(*)
FROM the_objects
GROUP BY GROUPING SETS (NULL, (owner), (owner), NULL)
ORDER BY owner;
OWNER COUNT(*)
--------------- ----------
SCOTT 6
SCOTT 6
SYSTEM 505
SYSTEM 505
511
511
6 rows selected
Выполнение этого запроса демонстрирует еще одну вещь. А именно, что результат,
полностью аналогичный результату SELECT c GROUPING SETS, можно получить, соединяя отдельные
запросы с помощью UNION ALL, а не UNION. Ведь UNION подавляет одинаковые строки, тогда как запрос 15 порождает одинаковые строки.
В заключение, сделаю замечание об использовании GROUPING SETS с системными вью Oracle.
Если попробовать выполнить запрос 13 не на таблице the_objects, а на системном вью all_objects,
то результат разочаровывает:
ORA-00904: invalid identifier
...
К сожалению, при работе с системными вью у GROUPING SETS возникают некрасивые проблемы.
Так, по крайней мере, обстоит дело в Oracle 11gR2, которым я пользуюсь для экспериментов.
Что касается ROLLUP и CUBE, то они работают с системными вью без проблем.
Хм...
ОтветитьУдалитьПробовал выполнить 13-й запрос по sys на 11gR2 и запрос успешно выполнился. Возможно, здесь имеет место проблема с грантами...
Выполненный запрос:
SELECT
owner,
object_type,
COUNT(*)
FROM all_objects
GROUP BY GROUPING SETS ((object_type), (owner));
Однако, этот запрос под обычным пользователем выполнился успешно:
SELECT
TRUNC(created),
object_type,
COUNT(*)
FROM user_objects
GROUP BY GROUPING SETS ((object_type), (TRUNC(created)));
В самом деле, под sys запрос 13 работает на all_users. А вот уже под system возникает ошибка: ORA-00904: invalid identifier.
Удалить