CODEWITHSUNDEEP
oracle-databasequery-optimization
Vikas Sharma
Vikas Sharma

Reputation: 1245

How to optimize query in Oracle with max in where clause

I am providing hypothetical table and query to explain my problem. Pardon me for any sntax error.

Dept table:

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

Below query returns 1st, and 3rd, record from above table:

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

Then I did below changes in the query to return 2nd record as well. Because for Dname 'B', we have one record with Bdate > 4-Aug.

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)

Above query works but it hit the performance. How could I optimize it.

I think of Union or moving max query to select part. But, not able to find a way.

Upvotes: 0

Views: 7017

Answers (5)

David Aldridge
David Aldridge

Reputation: 52346

Depending on the selectivity of the data set, it might be worth trying this approach:

select *
from   dept
where  (dno, dname) in (
         select distinct dno, dname
         from   dept
         where  BDATE > (select mydate from other_table))

With indexes on dept(bdate) and dept(dno,dname), and only few records to match, this would be very fast.

Upvotes: 0

Joe
Joe

Reputation: 1139

Avoid one of the sub queries by using the group-by clause:

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

You can also remove the other subquery using a local var:

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

Upvotes: 0

Vikas Sharma
Vikas Sharma

Reputation: 1245

Below query runs 4 times faster with the correct result:

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)

Upvotes: 0

Matthew McPeak
Matthew McPeak

Reputation: 17924

If you are expecting this query to return a small fraction of the rows in your DEPT table, this might be faster for you. I'm assuming that DEPT.DNAME is unique and there is an index on it. (And there needs to be an index on DEPT.BDATE, of course!)

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   dept WHERE dname IN ( SELECT d2.dname FROM dept d2 WHERE d2.bdate >  (select mydate from other_table) );

Upvotes: 0

Boneist
Boneist

Reputation: 23578

Assuming I understand your logic correctly (that you want all rows for a given dno and dname if the max date is greater than a specified date) and that the query to retrieve the "mydate" returns a single row, I would do something like:

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

Upvotes: 1

Related Questions