waiting waiting - 1 year ago 53
SQL Question

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.

Answer Source

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

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;