为何Oracle子查询中的MIN()/MAX()函数未按预期生效？

问题现象 #

先创建两张测试表并填充数据：

CREATE TABLE A(COL1 NUMBER);
CREATE TABLE B(COL1 NUMBER);

通过PL/SQL块随机生成测试数据：

SET TIMING ON
DECLARE
INSERT_STATEMENT VARCHAR2(200);
BEGIN 
FOR I IN 1..100
LOOP
INSERT_STATEMENT := 'INSERT INTO A(COL1) VALUES(round(DBMS_RANDOM.VALUE(1,3)))';
EXECUTE IMMEDIATE INSERT_STATEMENT;
INSERT_STATEMENT := 'INSERT INTO B(COL1) VALUES(round(DBMS_RANDOM.VALUE(1,2)))';
EXECUTE IMMEDIATE INSERT_STATEMENT;
END LOOP;
commit;
END;
/

数据分布结果：

SELECT COL1,COUNT(COL1) COUNT# FROM A GROUP BY COL1;
-- 输出结果
      COL1     COUNT#
---------- ----------
         1         26
         2         53
         3         21

SELECT COL1,COUNT(COL1) COUNT# FROM B GROUP BY COL1;
-- 输出结果
      COL1     COUNT#
---------- ----------
         1         50
         2         50

执行以下两个查询时，结果均返回79行，和预期的26、53行不符：

SELECT COUNT(*) FROM A WHERE A.COL1=(SELECT MIN(B.COL1) FROM B WHERE A.COL1=B.COL1);
-- 结果：79

SELECT COUNT(*) FROM A WHERE A.COL1=(SELECT MAX(B.COL1) FROM B WHERE A.COL1=B.COL1);
-- 结果：79

查看执行计划的Predicate信息，能看到关键逻辑：

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"."COL1"=MIN("A"."COL1"))
   3 - access("A"."COL1"="B"."COL1")

换成非关联子查询的写法后，结果符合预期：

SELECT COUNT(*) FROM A A2 WHERE A2.COL1=(SELECT MIN(B.COL1) FROM A,B WHERE A.COL1=B.COL1);
-- 结果：26

SELECT COUNT(*) FROM A A2 WHERE A2.COL1=(SELECT MAX(B.COL1) FROM A,B WHERE A.COL1=B.COL1);
-- 结果：53

原因解释 #

问题核心在于关联子查询的执行逻辑：

• 有问题的查询里，子查询是关联子查询，会针对表A的每一行单独执行：筛选出B中COL1等于当前A行COL1的记录，再取这些记录的MIN(B.COL1)。
• 但因为筛选条件是A.COL1=B.COL1，B中符合条件的行的COL1只能等于当前A行的COL1，此时MIN(B.COL1)和MAX(B.COL1)的结果必然等于A.COL1本身。
• 这就导致整个条件等价于A.COL1 IN (SELECT COL1 FROM B)，只要A的行在B中有匹配就会被计数，最终得到26+53=79行，所以两个查询结果完全一致。

而正确写法中的子查询是非关联的：它先计算出所有A和B匹配的B.COL1的全局极值（也就是1和2），再匹配A2的COL1等于这个值，自然得到A中对应COL1值的行数。

总结 #

如果要获取A中等于B里存在的最小/最大COL1值的行数，需要先计算B中与A匹配的COL1的全局极值，再关联A表；不要用关联子查询逐行计算，因为此时逐行的极值就是当前行的COL1本身，无法得到全局的极值结果。

内容的提问来源于stack exchange，提问作者Vic VKh