sql - 包含带 OR 条件的分组规则的 GROUP BY 优化

Question

有一些表 T1(在 Oracle 数据库中)有一些字段 A、B、C , D, E, F:

Upd 0:让以上字段的类型相同。

假设，我们需要按照以下规则对表进行分组:A & B & (C | D)

更新 1:A & B & (C | D) 表达式可以转换为以下表达式:

(A & B & C) | (A & B & D)。

因此，为了解决这个任务，我必须合并两个分组查询，分别针对组 A、B、C 和 A、B、D:

select A, B, C, count(*) 
from T1 
group by A, B, C

  union all

select A, B, D, count(*) 
from T1 
group by A, B, D

如果分组规则会更复杂:A & B & (C | D) & (E | F)，那么解决方案会更庞大，因为我必须联合分组查询对于以下群体:

A & B & C & E, A & B & D & E, A & B & C & F, >A & B & D & F.

有没有可能优化这样的解决方案？或者是否有更好的方法来解决此类任务？

更新 2:我使用了简短的表达式 A & B & (C | D) 和 A & B & (C | D) & (E | F) 来强调它们有公共(public)部分 A & B。而且我不希望它被计算很多次。

Answer 1

GROUPING SETS子句可以简化代码，提高多个分组组合的性能。

更简单的代码

举个例子，让我们从一个简单的表格开始:

create table t1(a number, b number, c number, d number);
insert into t1
select 0,0,0,0 from dual union all
select 1,0,0,0 from dual union all
select 0,1,0,0 from dual union all
select 1,1,0,0 from dual union all
select 0,0,1,0 from dual union all
select 1,0,1,0 from dual union all
select 0,1,1,0 from dual union all
select 1,1,1,0 from dual union all
select 0,0,0,1 from dual union all
select 1,0,0,1 from dual union all
select 0,1,0,1 from dual union all
select 1,1,0,1 from dual union all
select 0,0,1,1 from dual union all
select 1,0,1,1 from dual union all
select 0,1,1,1 from dual union all
select 1,1,1,1 from dual;

下面的查询表示按“A & (B | C)”分组。 (与您的示例不同，我将包含一些空列来演示分组的工作原理。)

select a, b, null c, count(*)
from t1
group by a, b
union all
select a, null b, c, count(*)
from t1
group by a, c;

A   B   C   COUNT(*)
-   -   -   --------
1   0              4
0   0              4
1   1              4
0   1              4
1      0           4
0      0           4
1      1           4
0      1           4

使用 GROUPING SETS 重写会产生与前面查询相同的结果:

select a, b, c, count(*)
from t1
group by grouping sets((a, b), (a, c));

更好的表现

使用 explain plan for ... 运行上述查询，然后 select * from table(dbms_xplan.display(format => 'basic')); 返回遵循执行计划。

对于 UNION ALL 版本:

------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|   1 |  UNION-ALL          |      |
|   2 |   HASH GROUP BY     |      |
|   3 |    TABLE ACCESS FULL| T1   |
|   4 |   HASH GROUP BY     |      |
|   5 |    TABLE ACCESS FULL| T1   |
------------------------------------

对于 GROUPING SETS 版本:

-------------------------------------------------------------------------------
| Id  | Operation                                | Name                       |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6787_464CF95 |
|   3 |    TABLE ACCESS FULL                     | T1                         |
|   4 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6788_464CF95 |
|   5 |    HASH GROUP BY                         |                            |
|   6 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6787_464CF95 |
|   7 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6788_464CF95 |
|   8 |    HASH GROUP BY                         |                            |
|   9 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6787_464CF95 |
|  10 |   VIEW                                   |                            |
|  11 |    TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6788_464CF95 |
-------------------------------------------------------------------------------

UNION ALL 执行计划为每个不同的分组从源表中读取一次。 GROUPING SETS 执行计划只从源表中读取一次，将信息存储在临时表中，然后从该临时表中读取。

如果查询仅使用行的一小部分或列的一小部分，GROUPING SETS 计划可能会明显更快，因为它只需要读取完整数据一次。