Я бы использовал ROLLUP
как для ясности, так и для производительности.
Скажем, у вас есть пример таблицы:
create table payments (business_status_name, pending_items, datagroup) as (
select 'PROCESSING DATA', 10, 'PRODUCT' from dual union all
select 'PROCESSING DATA', 5, 'PRODUCT' from dual union all
select 'PROCESSING DATA', 2, 'SERVICE' from dual union all
select 'PROCESSING INS', 10, 'SERVICE' from dual union all
select 'PROCESSING INS', 10, 'SERVICE' from dual union all
select 'PROCESSING INS', 10, 'PRODUCT' from dual
)
Это способ с ROLLUP
(обратите внимание на круглые скобки, чтобы изменить логику группировки):
SELECT BUSINESS_STATUS_NAME,
SUM(PENDING_ITEMS) as PENDING_ITEMS,
DATAGROUP
FROM PAYMENTS
GROUP BY ROLLUP ((BUSINESS_STATUS_NAME, DATAGROUP))
результат:
BUSINESS_STATUS PENDING_ITEMS DATAGRO
--------------- ------------- -------
PROCESSING INS 10 PRODUCT
PROCESSING INS 20 SERVICE
PROCESSING DATA 15 PRODUCT
PROCESSING DATA 2 SERVICE
47
план:
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 186 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP| | 6 | 186 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | PAYMENTS | 6 | 186 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Это с UNION ALL
:
SELECT BUSINESS_STATUS_NAME,
SUM(PENDING_ITEMS) as PENDING_ITEMS,
DATAGROUP
FROM PAYMENTS
GROUP BY BUSINESS_STATUS_NAME, DATAGROUP
UNION ALL
SELECT NULL, SUM(PENDING_ITEMS), NULL
FROM PAYMENTS;
результат тот же, что и с ROLLUP
:
BUSINESS_STATUS PENDING_ITEMS DATAGRO
--------------- ------------- -------
PROCESSING INS 20 SERVICE
PROCESSING INS 10 PRODUCT
PROCESSING DATA 15 PRODUCT
PROCESSING DATA 2 SERVICE
47
план не очень, с TWO FULL SCANS
:
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 199 | 7 (58)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | HASH GROUP BY | | 6 | 186 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| PAYMENTS | 6 | 186 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 13 | | |
| 5 | TABLE ACCESS FULL| PAYMENTS | 6 | 78 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Это, конечно, только небольшой пример с несколькими записями, без индексов... так что в реальных таблицах все может быть по-другому, но я все же считаю, что ROLLUP
должен работать лучше, чем UNION ALL
.
В более простом случае, точно равном вашему, это будут планы двух подходов:
SELECT BUSINESS_STATUS_NAME,
SUM(PENDING_ITEMS) as PENDING_ITEMS,
DATAGROUP
FROM PAYMENTS
GROUP BY ROLLUP ((BUSINESS_STATUS_NAME, DATAGROUP))
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 62 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP| | 2 | 62 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | PAYMENTS | 2 | 62 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
SELECT BUSINESS_STATUS_NAME,
PENDING_ITEMS,
DATAGROUP
FROM PAYMENTS
UNION ALL
SELECT NULL,
SUM(PENDING_ITEMS),
NULL
FROM PAYMENTS
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 6 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS FULL | PAYMENTS | 2 | 62 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
| 4 | TABLE ACCESS FULL| PAYMENTS | 2 | 26 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
ROLLUP по-прежнему имеет лучший план с одним сканированием таблицы.
person
Aleksej
schedule
21.02.2017