Search code examples
sqloracle-databasejoinmaxgreatest-n-per-group

make max() function faster when used as inline query join condition

I got a query where iam trying to get the max(date) value of from another table to be used as join condition.

SELECT a.col1, a.col2 
  FROM tablea a, 
       tableb b
 WHERE a.pk_id = b.fk_id
   AND a.effdt = (SELECT MAX(effdt)
                    FROM tablea c
                   where c.id= a.id
                     and c.effdt <= sysdate
                  )

Here a index is already created on the tablea for effdt column still the query is taking long time to return values. any help in joining them better would be great.

Solution

  • Use the RANK() analytic function to eliminate the correlated sub-query:

    SELECT *
FROM   (
  SELECT a.*,
         RANK() OVER ( PARTITION BY a.id ORDER BY a.effdt DESC ) AS rnk
  FROM   tablea a
         INNER JOIN
         tableb b
         ON ( a.pk_id = b.fk_id )
  WHERE  a.effdt <= SYSDATE
)
WHERE  rnk = 1;