sql - PARTITION BY with and without KEEP in Oracle

Question

I came across two queries which seems to have the same result: applying aggregate function on partition.

I am wondering if there is any difference between these two queries:

SELECT empno,
   deptno,
   sal,
   MIN(sal) OVER (PARTITION BY deptno) "Lowest",
   MAX(sal) OVER (PARTITION BY deptno) "Highest"
FROM empl

SELECT empno,
   deptno,
   sal,
   MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) "Lowest",
   MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) "Highest"
FROM empl

The first version is more logical but second one may be some kind special case, maybe some performance optimization.

Answer 1

MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno)

The statement can be considered in (roughly) right-to-left order:

• OVER (PARTITION BY deptno) means partition the rows into distinct groups of deptno; then
• ORDER BY sal means, for each partition, order the rows by sal (implicitly using ASCending order); then
• KEEP (DENSE_RANK FIRST means give a (consecutive) ranking to the ordered rows for each partition (rows with identical values for the ordering columns will be given the same rank) and discard all rows which are not ranked first; and finally
• MIN(sal) for the remaining rows of each partition, return the minimum salary.

In this case the MIN and DENSE_RANK FIRST are both operating on the sal column so will do the same thing and the KEEP (DENSE_RANK FIRST ORDER BY sal) is redundant.

However if you use a different column for the minimum then you can see the effects:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test (name, sal, deptno) AS
SELECT 'a', 1, 1 FROM DUAL
UNION ALL SELECT 'b', 1, 1 FROM DUAL
UNION ALL SELECT 'c', 1, 1 FROM DUAL
UNION ALL SELECT 'd', 2, 1 FROM DUAL
UNION ALL SELECT 'e', 3, 1 FROM DUAL
UNION ALL SELECT 'f', 3, 1 FROM DUAL
UNION ALL SELECT 'g', 4, 2 FROM DUAL
UNION ALL SELECT 'h', 4, 2 FROM DUAL
UNION ALL SELECT 'i', 5, 2 FROM DUAL
UNION ALL SELECT 'j', 5, 2 FROM DUAL;

Query 1:

SELECT DISTINCT
  MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_sal_first_sal,
  MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS max_sal_first_sal,
  MIN(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_first_sal,
  MAX(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_first_sal,
  MIN(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_last_sal,
  MAX(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_last_sal,
  deptno
FROM test

Results:

| MIN_SAL_FIRST_SAL | MAX_SAL_FIRST_SAL | MIN_NAME_FIRST_SAL | MAX_NAME_FIRST_SAL | MIN_NAME_LAST_SAL | MAX_NAME_LAST_SAL | DEPTNO |
|-------------------|-------------------|--------------------|--------------------|-------------------|-------------------|--------|
|                 1 |                 1 |                  a |                  c |                 e |                 f |      1 |
|                 4 |                 4 |                  g |                  h |                 i |                 j |      2 |