0

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.

Improve this question

5 Answers 5

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

it works but took longer time. and the Explain plan cost is 373037. Explain plan cost was 86831 with the query mentioned in the question.
I wouldn't get too bothered about comparing the costs of different queries. If the performance of this query isn't as good as another, then don't use it! *{:-)
0

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) );
Improve this answer

Comments

0

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)
Improve this answer

Comments

0

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)
Improve this answer

6 Comments

The second query is not valid SQL for Oracle.
@MTO - thanks, I'm not an Oracle person, but that's how I'd do it in MS-SQL.
In the actual scenario, I have many columns, so I don't think grouping around 50 columns will enhance the performance.
@Vikas - wow... 50 columns. First, you don't need to use all columns available, only select and group by using the relevant columns. or second, consider refactoring your schema to break out employee data into a separate table, removing it from your department table. (I'm making a few assumptions here).
Above query won't work as Max group by function not allowed in where clause. On moving it to having clause, the query returns only 1st and 3rd record not the 2nd record.
|
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.