Listagg Partition代替Group By

Question

在使用带有varchar类型数据的Listagg时，哪一个更有效？

SELECT department_id AS "Dept.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) AS "Employees"
  FROM employees
  GROUP BY department_id
  ORDER BY department_id;

或

SELECT department_id AS "Dept.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) 
       OVER (PARTITION BY department_id) AS "Employees"
  FROM employees
  ORDER BY department_id;

对于我来说，除非我只选择1/2列，否则我将始终使用分区，因为我不必在“GROUP BY”子句中包含每一列。

Answer 1

这两个查询执行的工作量相同，因为每个查询都必须对EMPLOYEES执行一次完整扫描，对DEPARTMENT_ID中的LAST_NAME值进行排序。

使用Oracle 'HR‘演示模式(其中EMPLOYEES有107行)和SQL*Plus set autotrace工具，我们可以看到它们都执行了7个一致的gets和1个sort。

SQL> set autotrace traceonly explain statistics

SQL> select department_id as "Dept."
  2       , listagg(last_name, '; ') within group(order by hire_date) as "Employees"
  3  from   employees
  4  group  by department_id
  5  order  by department_id;

12 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2107619104

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    11 |   209 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |           |    11 |   209 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |  2033 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1605  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL> select department_id as "Dept."
  2       , listagg(last_name, '; ') within group(order by hire_date) over(partition by department_id) as "Employees"
  3  from   employees
  4  order  by department_id;

107 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1919783947

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   107 |  2033 |     3   (0)| 00:00:01 |
|   1 |  WINDOW SORT       |           |   107 |  2033 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |  2033 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       3703  bytes sent via SQL*Net to client
        685  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        107  rows processed

第二个查询会有更多的网络活动，因为它返回更多的行。

Answer 2

这两个查询给出不同的输出。使用GROUP BY将为每个组返回一行，而使用OVER ( PARTITION BY .. )将返回所有行，并为分区中的每一行复制LISTAGG结果。

使用任何更适合您所需输出的解决方案-但它们并不相等。

SQL Fiddle

Oracle 11g R2架构设置

CREATE TABLE employees ( department_id, last_name, hire_date ) AS
SELECT 1, 'AAA', DATE '2018-01-01' FROM DUAL UNION ALL
SELECT 1, 'BBB', DATE '2018-01-02' FROM DUAL UNION ALL
SELECT 1, 'CCC', DATE '2018-01-03' FROM DUAL UNION ALL
SELECT 2, 'DDD', DATE '2018-01-01' FROM DUAL UNION ALL
SELECT 2, 'EEE', DATE '2018-01-02' FROM DUAL;

查询1

SELECT department_id AS "Dept.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) AS "Employees"
  FROM employees
  GROUP BY department_id
  ORDER BY department_id

| Dept. |     Employees |
|-------|---------------|
|     1 | AAA; BBB; CCC |
|     2 |      DDD; EEE |

查询2

SELECT department_id AS "Dept.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) 
       OVER (PARTITION BY department_id) AS "Employees"
  FROM employees
  ORDER BY department_id

| Dept. |     Employees |
|-------|---------------|
|     1 | AAA; BBB; CCC |
|     1 | AAA; BBB; CCC |
|     1 | AAA; BBB; CCC |
|     2 |      DDD; EEE |
|     2 |      DDD; EEE |