Search code examples
sqloracle-databaserow-numberrownum

Find the lastest date in oracle database

I have a big trouble with the query in the oracle database ver.10. What I want is to find the last date dateofstat I have try many solutions, but it works but it take too much time. - Using rownum
- Using row_number()
- Using rank()

There are my tries:
1. rownum

select dateofstat from (
select  stat.dateofstat from dhg.statistics stat
join (
  select distinct assetid from dhg.relatedasset
     where (`CONDITION1`)
  MINUS
  select distinct assetid from dhg.relatedasset
 where (`CONDITION2`)
) grs 
on stat.assetid = grs.assetid
order by stat.dateofstat desc
)where rownum = 1

Explain plan:
enter image description here

row_number() 

select dateofstat from (
  select stat.dateofstat,
    row_number() over (order by stat.dateofstat desc) rnumber
  from dhg.statistics stat
  join (
    select distinct assetid from dhg.relatedasset
    where (`CONDITION1`)
    MINUS
    select distinct assetid from dhg.relatedasset
    where (`CONDITION2`)
  ) grs 
  on stat.assetid = grs.assetid
) where rnumber = 1

Explain plan:
enter image description here

rank(): This solution I did try but it gives repetitive rank number, because of it, I dont think that I should used this solution to find the top one.

I dont know what should I do now, really need help. For testing, I use sqlplus on emacs, without rownum I take less than 3 second to get the first rows of this query. 

select  stat.dateofstat from dhg.statistics stat
join (
  select distinct assetid from dhg.relatedasset
     where (`CONDITION1`)
  MINUS
  select distinct assetid from dhg.relatedasset
 where (`CONDITION2`)
) grs 
on stat.assetid = grs.assetid
order by stat.dateofstat desc

I wonder that I could have any workaround solution by this tweak.

UPDATE SOLUTION STATUS FROM @ANTON 

select  max(stat.dateofstat) from dhg.statistics stat
join (
 select distinct assetid from dhg.relatedasset
     where relatedasset.assetid in (191759.0, 3.0, 5.0, 98.0, 99.0)
   or relatedasset.linkid in (3232.0, 1049.0, 1057.0, 1067.0, 102.0, 1032.0, 104.0, 105.0, 1051.0)
 MINUS
 select distinct assetid from dhg.relatedasset
     where relatedasset.assetid in (192106.0, 1014.0, 10302.0)
   or relatedasset.linkid in (210.0, 737.0, 126.0, 1053.0, 1054.0, 119.0, 3133.0)
 ) grs 
 on stat.assetid = grs.assetid

I must say, I did work for once, very weird behavior. In the first executing I take only 3 seconds to execute, but in second time, I take much much more time (I did not count). Here are explain plan:

enter image description here

In addition, I also did try the second solution which use exist and not exists, however, it does not work well. 

select  max(stat.dateofstat)
from dhg.statistics stat
where exists(select *
           from dhg.relatedasset rasset
           where stat.assetid = rasset.assetid
           and rasset.assetid in (191759.0, 3.0, 5.0, 98.0, 99.0)
           or rasset.linkid in (3232.0, 1049.0, 1057.0, 1067.0, 102.0, 1032.0, 104.0, 105.0, 1051.0)
            )
and not exists (select *
           from dhg.relatedasset rasset2
           where stat.assetid = rasset2.assetid
           and rasset2.assetid in (192106.0, 1014.0, 10302.0)
           or rasset2.linkid in (210.0, 737.0, 126.0, 1053.0, 1054.0, 119.0, 3133.0)
            )

This query, the plan give me a painful result. enter image description here

Solution

  • Due to the various OR conditions (IN and OR) on relatedasset the DBMS decides to perform a full table scan. That seems reasonable. So what can we optimize? We can see to it that the full table scan is done just once. Query the table, group by assetid and check with HAVING if the first condition was true for any record and the second for none.

    You can also use a parallel hint to make Oracle perform the full table scan in parallel if possible.

    select  max(dateofstat)
from dhg.statistics
where assetid in
(
  select /*+ parallel(relatedasset,4) */ assetid
  from dhg.relatedasset 
  group by assetid
  having 
    max( case when assetid in (191759.0, 3.0, 5.0, 98.0, 99.0) 
                or linkid in (3232.0, 1049.0, 1057.0, 1067.0, 102.0, 1032.0, 104.0, 105.0, 1051.0) 
         then 1 else 0 end ) = 1
  and
    max( case when assetid in (192106.0, 1014.0, 10302.0)
                or linkid in (210.0, 737.0, 126.0, 1053.0, 1054.0, 119.0, 3133.0) 
         then 1 else 0 end ) = 0
);