原SELECT与改写后SELECT返回JOB字段值不一致问题咨询

背景 #

发现一段性能极差的遗留SELECT语句，其中ID列并非主键。相关信息如下：

原SQL #

SELECT 
    A.JOB,
    MIN(A.ID) AS START_ID,
    MAX(A.ID) AS END_ID,
    COUNT(DISTINCT A.R) AS CNT
FROM 
    (SELECT /*+ PARALLEL(copyfrom 20) PARALLEL(copyto 20) */
         COPYTO.ID,
         COPYTO.ROWID AS R,
         TRUNC((DENSE_RANK() OVER(ORDER BY COPYTO.ID) - 1) / 500000) AS JOB
     FROM 
         T1 COPYFROM
     JOIN 
         T2 COPYTO ON COPYFROM.ID = COPYTO.ID
     WHERE 
         COPYTO.DATE IS NULL
         AND COPYFROM.DATE IS NOT NULL
         AND NOT EXISTS (SELECT * FROM T1 COPYFROM
                         WHERE COPYFROM.ID = COPYTO.ID
                           AND COPYFROM.DATE IS NULL)) A
 WHERE 
    A.JOB < 1
 GROUP BY A.JOB

索引 #

create index idx1 on t1 (id, date);
create index idx2 on t2 (id, date);

原SQL返回结果 #

JOB,START_ID,END_ID,CNT
  0,     100,   200, 20

改写后的新SQL #

SELECT A.JOB,
 MIN(A.ID) AS START_ID,
 MAX(A.ID) AS END_ID,
 COUNT(DISTINCT A.R) AS CNT
  FROM (SELECT /*+ PARALLEL(copyfrom 20) PARALLEL(copyto 20) */ 
             COPYTO.ID,
             COPYTO.ROWID AS R,
             COPYFROM.DATE as COPYFROM_DATE,
             TRUNC((DENSE_RANK() OVER(ORDER BY COPYTO.ID) - 1) / 500000) AS JOB,
             COUNT(CASE 
                     WHEN COPYFROM.DATE IS NULL THEN 1 
                     ELSE NULL 
                   END) OVER (PARTITION BY COPYTO.ID) AS CNT_NULL_DATES_FOR_THIS_ID
              FROM T1 COPYFROM
              JOIN T2 COPYTO
                ON COPYFROM.ID = COPYTO.ID
             WHERE COPYTO.DATE IS NULL                   
) A
 WHERE A.JOB < 1 -- <-- 移除该条件后JOB列显示为10
   AND A.CNT_NULL_DATES_FOR_THIS_ID = 0
   AND A.COPYFROM_DATE IS NOT NULL
 GROUP BY A.JOB

新SQL返回结果 #

JOB,START_ID,END_ID,CNT
 10,     100,   200, 20

新SQL执行计划 #

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                            |    86M|  4222M|   817K  (1)| 00:00:32 |       |       |        |      |            |
|   1 |  HASH GROUP BY                                |                            |    86M|  4222M|   817K  (1)| 00:00:32 |       |       |        |      |            |
|   2 |   VIEW                                        | VW_DAG_0                   |    86M|  4222M|   817K  (1)| 00:00:32 |       |       |        |      |            |
|   3 |    HASH GROUP BY                              |                            |    86M|  5299M|   817K  (1)| 00:00:32 |       |       |        |      |            |
|*  4 |     VIEW                                      |                            |    86M|  5299M|   817K  (1)| 00:00:32 |       |       |        |      |            |
|   5 |      WINDOW BUFFER                            |                            |    86M|  3394M|   817K  (1)| 00:00:32 |       |       |        |      |            |
|   6 |       PX COORDINATOR                          |                            |       |       |            |          |       |       |        |      |            |
|   7 |        PX SEND QC (ORDER)                     | :TQ10001                   |    86M|  3394M|   817K  (1)| 00:00:32 |       |       |  Q1,01 | P->S | QC (ORDER) |
|   8 |         SORT ORDER BY                         |                            |    86M|  3394M|   817K  (1)| 00:00:32 |       |       |  Q1,01 | PCWP |            |
|   9 |          PX RECEIVE                           |                            |    86M|  3394M|   765K  (1)| 00:00:30 |       |       |  Q1,01 | PCWP |            |
|  10 |           PX SEND RANGE                       | :TQ10000                   |    86M|  3394M|   765K  (1)| 00:00:30 |       |       |  Q1,00 | P->P | RANGE      |
|  11 |            NESTED LOOPS                       |                            |    86M|  3394M|   765K  (1)| 00:00:30 |       |       |  Q1,00 | PCWP |            |
|  12 |             NESTED LOOPS                      |                            |    91M|  3394M|   765K  (1)| 00:00:30 |       |       |  Q1,00 | PCWP |            |
|  13 |              PX BLOCK ITERATOR                |                            |    18M|   452M|  4756   (1)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWC |            |
|  14 |               TABLE ACCESS FULL               | T2                         |    18M|   452M|  4756   (1)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |
|* 15 |              INDEX RANGE SCAN                 | I_PK_T1                    |     5 |       |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  16 |             TABLE ACCESS BY GLOBAL INDEX ROWID| T1                         |     5 |    75 |     1   (0)| 00:00:01 | ROWID | ROWID |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

疑问 #

同一数据下，原SQL返回JOB=0，新SQL却返回JOB=10，这是为什么？

核心差异在于DENSE_RANK()窗口函数的计算时机：

1. 原SQL的逻辑：

   • 内层子查询先通过WHERE条件和NOT EXISTS过滤数据，只保留满足以下条件的记录：
     • COPYTO.DATE IS NULL
     • COPYFROM.DATE IS NOT NULL
     • 该ID在T1中没有DATE为NULL的记录
   • 基于过滤后的结果集计算DENSE_RANK() OVER(ORDER BY COPYTO.ID)，再分组得到JOB值。此时参与排名的记录量少，最终目标记录落在JOB=0的分组里。

2. 新SQL的逻辑：

   • 内层子查询先执行JOIN和基础WHERE（仅COPYTO.DATE IS NULL），得到一个大得多的中间结果集，然后基于这个未过滤全部条件的结果集计算DENSE_RANK()。
   • 之后才通过外层的CNT_NULL_DATES_FOR_THIS_ID = 0和COPYFROM_DATE IS NOT NULL过滤数据。此时排名是基于所有符合JOIN和基础WHERE的记录计算的，目标记录在整体大结果集中的排名靠后，导致TRUNC计算后得到JOB=10。

简单说：原SQL是先过滤再排名，新SQL是先排名再过滤，两个步骤的顺序颠倒了，导致排名的基数完全不同，最终JOB值自然不一样。

内容的提问来源于stack exchange，提问作者Dani Che