如何用where子句中的max优化Oracle查询

Question

我提供假设表和查询来解释我的问题。原谅我的任何陷阱错误。

部门表：

ID Dno Dname BDate   seq
1  1     A    5-Aug   0
2  1     B    3-Aug   0
3  1     B    7-Aug   1
4  1     C    2-Aug   0

下面的查询返回上表中的第1次和第3次记录：

select * from Dept where BDate > (select mydate from other_table)
-- mydate is 4-Aug

然后，我在查询中做了以下更改，以返回第二条记录。因为对于Dname 'B'，我们有一个记录，Bdate >4-8月。

select * from Dept D where
(SELECT MAX(BDATE)
FROM Dept D1
WHERE D1.Dno = D.Dno
AND D1.Dname = D.Dname
) > (select mydate from other_table)

上面的查询可以工作，但会影响性能。我怎么能优化它。

我想到联合或移动最大的查询来选择部分。但是，找不到办法。

Answer 1

下面的查询运行速度要快4倍，结果是正确的：

select d1.* from Dept d1,
(select dno, dname, MAX(BDATE) as maxdate from Dept group by dno, dname) d2
where
d1.dno=d2.dno and d1.dname=d2.dname
and d2.maxdate > (select mydate from other_table)

Answer 2

假设我正确地理解了您的逻辑(如果最大日期大于指定日期，则需要给定dno和dname的所有行)，并且检索"mydate“的查询返回一个行，那么我将执行以下操作：

with     dept as (select 1 id, 1 dno, 'A' dname, to_date('05/08/2015', 'dd/mm/yyyy') bdate, 0 seq from dual union all
                  select 2 id, 1 dno, 'B' dname, to_date('03/08/2015', 'dd/mm/yyyy') bdate, 0 seq from dual union all
                  select 3 id, 1 dno, 'B' dname, to_date('07/08/2015', 'dd/mm/yyyy') bdate, 0 seq from dual union all
                  select 4 id, 1 dno, 'C' dname, to_date('02/08/2015', 'dd/mm/yyyy') bdate, 0 seq from dual),
  other_table as (select to_date('04/08/2015', 'dd/mm/yyyy') mydate from dual)
select id,
       dno,
       dname,
       bdate,
       seq
from   (select d.*,
               max(bdate) over (partition by dno, dname) max_bdate
        from   dept d)
where  max_bdate > (select mydate from other_table);

        ID        DNO DNAME BDATE             SEQ
---------- ---------- ----- ---------- ----------
         1          1 A     05/08/2015          0
         2          1 B     03/08/2015          0
         3          1 B     07/08/2015          0

Answer 3

使用group-by子句避免其中一个子查询：

select Dno, Dname, max(BDate)
from Dept
group by Dno, Dname
having Max(BDate) > (select mydate from other_table)

还可以使用本地var删除另一个子查询：

declare @mydate Date = (select mydate from other_table);
select Dno, Dname, max(BDate)
from Dept
group by Dno, Dname
having Max(BDate) > (@mydate)